//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]
@CustomerId Int = null
AS
BEGIN
SELECT Customers.CustomerID AS 'Customers.CustomerID',
Customers.FirstName AS 'Customers.FirstName',
Customers.LastName AS 'Customers.LastName',
Orders.OrderID AS 'Orders.OrderID',
Orders.CustomerID AS 'Orders.CustomerID',
Orders.OrderDate AS 'Orders.OrderDate',
OrderDetails.OrderDetailID AS 'OrderDetails.OrderDetailID',
OrderDetails.OrderID AS 'OrderDetails.OrderID',
OrderDetails.Cost AS 'OrderDetails.Cost'
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID
WHERE Customers.CustomerId = @CustomerId or @CustomerId is null
END";
db.ExecuteNonQuery(sql);
//Insert some Dummy Data
sql = @"INSERT INTO Customers (FirstName, LastName)
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 < 3; ++i)
{
sql = @"INSERT INTO Orders (CustomerId, OrderDate)
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 < 3; ++j)
{
sql = @"INSERT INTO OrderDetails (OrderId, Cost)
VALUES (@OrderId, @Cost)";
db.ExecuteNonQuery(sql,
new Dictionary<string, object>
{
{ "@OrderId", orderId },
{ "@Cost", i + (decimal)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);