Knight Data Access Layer
LoadFromSprocWithChildren<T>(String) Method
Example 




KellermanSoftware.NetDataAccessLayer Namespace > DataHelper Class > LoadFromSprocWithChildren Method : LoadFromSprocWithChildren<T>(String) Method
Generic class type
The name of the stored procedure
Load a list of objects from a stored procedure with all children
Syntax
Public Overloads Function LoadFromSprocWithChildren(Of T As {Class, New})( _
   ByVal storedProcedureName As String _
) As List(Of T)
Dim instance As DataHelper
Dim storedProcedureName As String
Dim value As List(Of T)
 
value = instance.LoadFromSprocWithChildren(Of T)(storedProcedureName)
public List<T> LoadFromSprocWithChildren<T>( 
   string storedProcedureName
)
where T: class, new()
public: List<T*>* LoadFromSprocWithChildren<T>( 
   string* storedProcedureName
) 
where T: ref class, gcnew()
public:
List<T^>^ LoadFromSprocWithChildrengeneric<typename T>
( 
   String^ storedProcedureName
) 
where T: ref class, gcnew()

Parameters

storedProcedureName
The name of the stored procedure

Type Parameters

T
Generic class type

Return Value

A list of objects
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]
          @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);
'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)
Requirements

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

See Also

Reference

DataHelper Class
DataHelper Members
Overload List