CoNatural Blogging

//-------------------------------------------------------
//-- uspGetEmployeeManagers.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 1:38:42 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.dbo {
	public class uspGetEmployeeManagers : ICommand {
		public uspGetEmployeeManagers() {}
 
		public string GetScript() { 
			return 
@"
BEGIN
SET NOCOUNT ON;
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], 0 -- Get the initial Employee
FROM [HumanResources].[Employee] e 
INNER JOIN [Person].[Person] as p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e 
INNER JOIN [EMP_cte]
ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
INNER JOIN [Person].[Person] p 
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
[EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
FROM [EMP_cte] 
INNER JOIN [HumanResources].[Employee] e 
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p 
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25) 
END;
"; 
		}
 
		public Int32? BusinessEntityID { get; set; }
	}
}

 
//-------------------------------------------------------
//-- DepartmentInsert.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 12:39:08 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.HumanResources {
	public class DepartmentInsert : ICommand {
		public DepartmentInsert() {}
 
		public string GetScript() { 
			return 
@"
INSERT INTO [HumanResources].[Department] (
   [Name],
   [GroupName],
   [ModifiedDate])
VALUES (
   @Name,
   @GroupName,
   @ModifiedDate)
SET @DepartmentID = SCOPE_IDENTITY();
"; 
		}
 
		[Parameter(50)] public string Name { get; set; }
		[Parameter(50)] public string GroupName { get; set; }
		public DateTime? ModifiedDate { get; set; }
		[Parameter(ParameterDirection.Output)] public Int16? DepartmentID { get; set; }
	}
}

 
//-------------------------------------------------------
//-- DepartmentSelect.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 12:39:08 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.HumanResources {
	public class DepartmentSelect : ICommand {
		public DepartmentSelect() {}
 
		public string GetScript() { 
			return 
@"
SELECT
   @DepartmentID = [DepartmentID],
   @Name = [Name],
   @GroupName = [GroupName],
   @ModifiedDate = [ModifiedDate]
FROM
   [HumanResources].[Department]
WHERE
   [DepartmentID] = @DepartmentID"; 
		}
 
		[Parameter(ParameterDirection.InputOutput)] public Int16? DepartmentID { get; set; }
		[Parameter(ParameterDirection.Output, 50)] public string Name { get; set; }
		[Parameter(ParameterDirection.Output, 50)] public string GroupName { get; set; }
		[Parameter(ParameterDirection.Output)] public DateTime? ModifiedDate { get; set; }
	}
}

 
//-------------------------------------------------------
//-- DepartmentUpdate.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 12:39:08 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.HumanResources {
	public class DepartmentUpdate : ICommand {
		public DepartmentUpdate() {}
 
		public string GetScript() { 
			return 
@"
UPDATE [HumanResources].[Department] SET
   [Name] = @Name,
   [GroupName] = @GroupName,
   [ModifiedDate] = @ModifiedDate
WHERE
   [DepartmentID] = @DepartmentID"; 
		}
 
		public Int16? DepartmentID { get; set; }
		[Parameter(50)] public string Name { get; set; }
		[Parameter(50)] public string GroupName { get; set; }
		public DateTime? ModifiedDate { get; set; }
	}
}

 
//-------------------------------------------------------
//-- DepartmentDelete.cs
//--
//-- Created by Roger with CoNatural Visual Studio AddIn 
//-- Created on 1/31/2010 12:39:08 PM
//-------------------------------------------------------
 
using System;
using System.Data;
using CoNatural.Data;
 
namespace ConsoleApplication1.Commands.HumanResources {
	public class DepartmentDelete : ICommand {
		public DepartmentDelete() {}
 
		public string GetScript() { 
			return 
@"
DELETE FROM [HumanResources].[Department] WHERE
   [DepartmentID] = @DepartmentID"; 
		}
 
		public Int16? DepartmentID { get; set; }
	}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoNatural.Data;
using CoNatural.Data.SqlClient;
 
namespace ConsoleApplication1 {
	class Program {
		static void Main(string[] args) {
			try {
				IConnection c = new SqlClientConnection(@"Data Source=PHENOM-II\PHENOM_II;Initial Catalog=AdventureWorks2008;Integrated Security=True");
				Commands.dbo.uspGetEmployeeManagers cmd = new Commands.dbo.uspGetEmployeeManagers();
 
				new int[] { 1, 2, 3, 4 }.All(i => {
					Console.WriteLine("BusinessEntityId = " + i);
					cmd.BusinessEntityID = i;
					c.ExecuteReader<Model.Employee>(cmd).All(e => {
						Console.WriteLine(e);
						return true;
					});
					return true;
				});
			}
			catch (Exception ex) {
				Console.WriteLine(ex.Message);
			}
			Console.ReadLine();
		}
	}
}
 
namespace ConsoleApplication1.Model {
	public class Employee {
		public string FirstName { get; set; }
		public string LastName { get; set; }
		public string ManagerFirstName { get; set; }
		public string ManagerLastName { get; set; }
 
		public override string ToString() {
			return string.Format("{0} {1} - managed by {2} {3}", FirstName, LastName, ManagerFirstName, ManagerLastName);
		}
	}
}