Loading By Sproc with Children VB.NET

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

Dim db As IDataHelper = 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") Then

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

End If

'Create a stored procedure.

'Notice the column names have the table name prefix

Dim sql As String = "CREATE PROCEDURE [GetCustomersWithChildren]" & ControlChars.CrLf & " @CustomerId Int = null" & ControlChars.CrLf & " AS" & ControlChars.CrLf & " BEGIN" & ControlChars.CrLf & " SELECT Customers.CustomerID AS 'Customers.CustomerID'," & ControlChars.CrLf & " Customers.FirstName AS 'Customers.FirstName'," & ControlChars.CrLf & " Customers.LastName AS 'Customers.LastName'," & ControlChars.CrLf & " Orders.OrderID AS 'Orders.OrderID'," & ControlChars.CrLf & " Orders.CustomerID AS 'Orders.CustomerID'," & ControlChars.CrLf & " Orders.OrderDate AS 'Orders.OrderDate'," & ControlChars.CrLf & " OrderDetails.OrderDetailID AS 'OrderDetails.OrderDetailID'," & ControlChars.CrLf & " OrderDetails.OrderID AS 'OrderDetails.OrderID'," & ControlChars.CrLf & " OrderDetails.Cost AS 'OrderDetails.Cost'" & ControlChars.CrLf & " FROM Customers" & ControlChars.CrLf & " INNER JOIN Orders" & ControlChars.CrLf & " ON Customers.CustomerID = Orders.CustomerID" & ControlChars.CrLf & " INNER JOIN OrderDetails" & ControlChars.CrLf & " ON Orders.OrderID = OrderDetails.OrderID" & ControlChars.CrLf & " WHERE Customers.CustomerId = @CustomerId or @CustomerId is null" & ControlChars.CrLf & " END"

db.ExecuteNonQuery(sql)

'Insert some Dummy Data

sql = "INSERT INTO Customers (FirstName, LastName)" & ControlChars.CrLf & " VALUES ('First', 'Last')"

db.ExecuteNonQuery(sql)

Dim customerId As Integer = Convert.ToInt32(db.GetIdentity("Customers"))

Dim ordersIdList As New List(Of Integer)()

Dim orderDetailsIdList As New List(Of Integer)()

For i As Integer = 0 To 2

sql = "INSERT INTO Orders (CustomerId, OrderDate)" & ControlChars.CrLf & " VALUES (@CustomerId, @OrderDate)"

db.ExecuteNonQuery(sql, New Dictionary(Of String, Object) From {{ "@CustomerId", customerId }, { "@OrderDate", Date.Today.AddDays(i) }})

Dim orderId As Integer = Convert.ToInt32(db.GetIdentity("Orders"))

For j As Integer = 0 To 2

sql = "INSERT INTO OrderDetails (OrderId, Cost)" & ControlChars.CrLf & " VALUES (@OrderId, @Cost)"

db.ExecuteNonQuery(sql, New Dictionary(Of String, Object) From {{ "@OrderId", orderId }, { "@Cost", i + CDec(j) / 10 }})

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

Next j

ordersIdList.Add(orderId)

Next i

' Set Parameters

Dim storedProcedure As String = "GetCustomersWithChildren"

Dim parameters As New Dictionary(Of String, Object)() From {{ "@CustomerId", customerId }}

'Load a customer with all orders and order details

Dim customers As List(Of Customer) = db.LoadFromSprocWithChildren(Of Customer)(storedProcedure, parameters)