Tip 13: Modifying the DAL to Pump in a Custom Object
As I mentioned in Tip 6, in the Sept/Oct 2006 issue of CoDe Magazine I presented code in the Baker's Dozen that utilized .NET generics to populate a typed DataSet directly from a stored procedure, without using either a TableAdapter or a DataSet
Merge function.
Design from the outside-in, and then build from the inside-out.
|
|
To give "equal time" to custom collections, I've added a base method using .NET generics to my data access layer. The method
ReadIntoCollection (see
Listing 5) executes a stored procedure and pumps the results directly into any custom collection. For example, if you want to use a custom collection instead of a typed DataSet for the example in the previous tips:
List<SqlParameter> oSQLParms = new List<SqlParameter>();
oSQLParms.Add(new SqlParameter("@LastName", LastName));
List<CustomerClass> oCustomers = new List<CustomerClass>();
// pass an instance of the list, SP name/parms,
// and a type reference to the class
this.ReadIntoCollection(oCustomers, "[dbo].[LookupEmployees]",
oSQLParms, typeof(CustomerClass));
So what happens inside this method?
To start, let's look at the parameters inside the method. While the calling function passes an instance of a list of CustomerClass items to the base method, the base method knows nothing about this specific class. Here is where developers can use .NET generics in the parameter declaration—by specifying
List oCollection as the first parameter, you can pass any type of valid List. The second and third parameters (stored procedure name and list of SQL parameters) are the same as in Tip 6. The final parameter is a type reference to the class itself—the CustomerClass. The base method will need this to iterate through the properties of the class when populating the list.
public void ReadIntoCollection<T>List<T> oCollection,
string cStoredProc, List<SqlParameter> oParmList,
Type oCollectionType)
Next, the base method opens a connection, defines a command object for a stored procedure, and establishes any SQL parameters-again, very similar to the DataSet method.
SqlConnection oSqlConn = this.GetConnection();
SqlCommand oCmd = new SqlCommand(cStoredProc, oSqlConn);
oCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter oParm in oParmList)
oCmd.Parameters.Add(oParm);
But this time, instead of using the
Fill method of the .NET DataAdapter, the method opens the connection and executes a DataReader. The goal will be to iterate through the reader and populate the custom list.
oSqlConn.Open();
SqlDataReader oDR = oCmd.ExecuteReader();
The next series of steps go through the reader, create an instance of the class (using the Type parameter), determine all the properties of the class, and read the actual values of those property names from the reader into the class instance to populate the list. Sounds pretty involved! Well, there's the old line about eating an elephant one bite at a time…
First, the code sets up a loop with the reader object and creates an instance of the class type. Because the parameters utilized .NET generics to define the class parameter, the code can specify an instance of the class with the T placeholder. If you examine this code in the debugger, you'll see that
oItem is a class of type CustomerClass.
while(oDR.Read()) {
T oItem = (T)Activator.CreateInstance(
oCollectionType);
Second, the code has to use a little bit of .NET reflection to discover all of the properties of the class. The code reads all of the properties of
oItem into an array of type PropertyInfo, using
GetProperties. The code will call the array
oCollectionProps.
// get all the properties of the class
PropertyInfo[] oCollectionProps = (
(Type) oItem.GetType()).GetProperties();
Now that the code has an array of properties for the class (
oCollectionProps), you can iterate through that array, grab the name of the properties, and use the reflection method
SetValue to set the value of the specific property in
oItem, from the DataReader.
for (int n=0; n<oCollectionProps.Length; n++)
{
string cPropName = CollectionProps[n].Name;
oCollectionProps[n].SetValue
(oItem, oDR[cPropName], null);
}
// Add the item to the collection
oCollection.Add(oItem);
// Now get the next row in the DataReader
If you're having difficulty following along, think of it this way—imagine the code if you weren't trying to write anything generic, and then study/compare it to the generic code.
while(oDR.Read()) {
CustomerClass oCustomer = new CustomerClass();
// no need to loop through properties, we
// know what they are
oCustomerClass.FirstName = oDR["FirstName"];
oCustomerClass.LastName = oDR["LastNName"];
}
oCollection.Add(oItem);
}
Previous Page
