Loading By Sproc With Children

Because most databases do not support multiple result sets, a special syntax has been developed so that children can be loaded with parents. Example:

//Get the current thread static safe instance of the helper

IDataHelper db = DataHelper.SessionFactory();

//Specify User Name and License Key from the receipt, leave blank for trial mode

//db.UserName = "John Smith 1234";

//db.LicenseKey = "asdfl219==";

//Open the database connection

db.OpenConnection();

//Drop the Sproc if it exists

if (db.StoredProcedureExists("GetCustomersWithChildren"))

{

db.ExecuteNonQuery("DROP PROCEDURE [GetCustomersWithChildren]");

}

//Create a stored procedure.

//Notice the column names have the table name prefix

string sql = "CREATE PROCEDURE [GetCustomersWithChildren]" + Environment.NewLine + " @CustomerId Int = null" + Environment.NewLine + " AS" + Environment.NewLine + " BEGIN" + Environment.NewLine + " SELECT Customers.CustomerID AS 'Customers.CustomerID'," + Environment.NewLine + " Customers.FirstName AS 'Customers.FirstName'," + Environment.NewLine + " Customers.LastName AS 'Customers.LastName'," + Environment.NewLine + " Orders.OrderID AS 'Orders.OrderID'," + Environment.NewLine + " Orders.CustomerID AS 'Orders.CustomerID'," + Environment.NewLine + " Orders.OrderDate AS 'Orders.OrderDate'," + Environment.NewLine + " OrderDetails.OrderDetailID AS 'OrderDetails.OrderDetailID'," + Environment.NewLine + " OrderDetails.OrderID AS 'OrderDetails.OrderID'," + Environment.NewLine + " OrderDetails.Cost AS 'OrderDetails.Cost'" + Environment.NewLine + " FROM Customers" + Environment.NewLine + " INNER JOIN Orders" + Environment.NewLine + " ON Customers.CustomerID = Orders.CustomerID" + Environment.NewLine + " INNER JOIN OrderDetails" + Environment.NewLine + " ON Orders.OrderID = OrderDetails.OrderID" + Environment.NewLine + " WHERE Customers.CustomerId = @CustomerId or @CustomerId is null" + Environment.NewLine + " END";

db.ExecuteNonQuery(sql);

//Insert some Dummy Data

sql = "INSERT INTO Customers (FirstName, LastName)" + Environment.NewLine + " VALUES ('First', 'Last')";

db.ExecuteNonQuery(sql);

int customerId = Convert.ToInt32(db.GetIdentity("Customers"));

List<int> ordersIdList = new List<int>();

List<int> orderDetailsIdList = new List<int>();

for (int i = 0; i <= 2; i++)

{

sql = "INSERT INTO Orders (CustomerId, OrderDate)" + Environment.NewLine + " VALUES (@CustomerId, @OrderDate)";

db.ExecuteNonQuery(sql, new Dictionary<string, object>() {{"@CustomerId", customerId}, {"@OrderDate", DateTime.Today.AddDays(i)}});

int orderId = Convert.ToInt32(db.GetIdentity("Orders"));

for (int j = 0; j <= 2; j++)

{

sql = "INSERT INTO OrderDetails (OrderId, Cost)" + Environment.NewLine + " VALUES (@OrderId, @Cost)";

db.ExecuteNonQuery(sql, new Dictionary<string, object>() {{"@OrderId", orderId}, {"@Cost", i + Convert.ToDecimal(j) / 10}});

orderDetailsIdList.Add(Convert.ToInt32(db.GetIdentity("OrderDetails")));

}

ordersIdList.Add(orderId);

}

// Set Parameters

string storedProcedure = "GetCustomersWithChildren";

Dictionary<string, object> parameters = new Dictionary<string, object>() {{"@CustomerId", customerId}};

//Load a customer with all orders and order details

List<Customer> customers = db.LoadFromSprocWithChildren<Customer>(storedProcedure, parameters);