Geeks With Blogs
christopher w davis

Using Reflection and ADO.NET to create a dynamic Data Layer

 

In this article I will discuss using object oriented techniques and built-in ADO.NET functions to create a data layer which will essentially figure out what values to put into stored procedure parameters, dynamically assign types in ADO.NET and c#, get values from a database and assign them to object properties, and write the code in such a way that you won't have to rewrite it anytime soon; even if you add new objects into your solution or modify your database design.

 

A very common architecture in the web world today is the n-tiered architecture.  This breaks up the application into distinct sections or tiers, each with its own specific and expendable purpose.  We will be working within the n-tier architecture throughout this article.  We will be using a business layer with well defined objects, a SQL Server database, and of course the crux of this is the data layer.  To give a quick overview of what we are about to undertake the business layer will contain several objects, each will inherit a base class that contains the functionality to get and set data in the database.  This functionality consists of calling methods in the data layer which will accept a parameter of type object, match the properties to the parameters of a stored procedure, then execute the procedure against the database.  If we have data coming back we then set it into the properties of the object.  Sounds like fun!  Let's go:

 

Business Layer Overview

 

In this pattern we will have to set some ground rules:

1)      Object structures must match data structures.

2)      If an object has a collection of child objects, it must be a List.

3)      Each object will have to inherit a base object which contains the data methods and a few common properties.

 

That's not so bad, right?  The first thing we'll have to create is our base object; this will need the methods we'll be using to access the data.  These methods are Fill, Delete, and Save.  Since we will be using reflection in these examples we will pass our objects around as type object.  These methods will rely on the data layer to create the ADO, execute the SqlCommand, and populate the corresponding data into our object.  Let's jump over to that and come back to the business layer to fill those in later.

 

The Data Layer

 

The first key to the data layer is the SqlCommandBuilder class in ADO.NET.  This handy class allows us to derive the parameters of a stored procedure, thus giving us the ability to figure them out at run time and match them up with the properties of our objects.  The following example is referencing a stored procedure with strSProc and the SQL Connection with conn.

 

            SqlCommand cmd = new SqlCommand(strSProc, conn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandTimeout = 1800;

 

            SqlCommandBuilder.DeriveParameters(cmd);

 

The code above fills up our parameter collection strait from the database, no gigantic blocks of ADO, no matching the parameter types with the property, types, it's all done for us.  Once you have enough of this fleshed out you can step through the code as is executes and note that the parameter collection is complete; all of the parameters have types and the lengths are set properly.  Whatever the parameters are defined as in your stored procedure is what will populate this list.  So speaking of matching parameter types with property types, let's use some refection to get that done with a simple loop:

 

foreach (SqlParameter parm in cmd.Parameters)

{

    parm.Value = DBNull.Value;

    parm.IsNullable = true;

 

   foreach (PropertyInfo p in o.GetType().GetProperties())

   {

       if (parm.ParameterName.Replace("@", "").ToUpper() == p.Name.ToUpper())

       {

           parm.Value = p.GetValue(o, null);

       }

   }

}

 

Since we have a complete parameter collection we can loop through it and compare the names of the parameters with the names of the properties of our object.  Note that since the parameters in our stored procedure start with an “@”, I am stripping that off the parameter name as we're doing the comparison.  Also since string comparisons are case sensitive we'll convert both strings to upper case to take care of any inconsistencies in casing between the database and the business objects.  Another thing I’m doing to avoid any trouble is making sure I can set the parameters to null and setting the value to DBNull by default.

 

Remember rule #1 from before: “Object structures must match data structures”.  By looking at the above chunk of code it become clear why this is rule #1, as long as the parameter name is the same as the property name we can match the 2 together, get the value from the property, and assign it to the value of the parameter.  Now we have a complete SQL command ready to fetch us some data.

 

The above is great for building some simple commands that are used to simply get data.  But what if we want to write data back to the DB?  Things will start to get complicated pretty quickly.  Note in the code below:

 

foreach (SqlParameter param in cmd.Parameters)

{

    param.Value = DBNull.Value;

    param.IsNullable = true;

    if (param.Direction != ParameterDirection.Output)

    {

        foreach (PropertyInfo p in o.GetType().GetProperties())

        {

            //check if this is a list

            if (p.PropertyType.Name.IndexOf("List") > -1)

            {

                System.Collections.IList propertyObject = (System.Collections.IList)p.GetValue(o, null);

                InsertUpdateChild(propertyObject, conn);

            }

 

            if (param.ParameterName.Replace("@", "").ToUpper() == p.Name.ToUpper())

            {

                if (p.GetValue(o, null) != null)

                {

                    if (p.PropertyType.Name == "DateTime")

                    {

                        if ((DateTime)p.GetValue(o, null) == DateTime.MinValue)

                            param.Value = DBNull.Value;

                        else

                            param.Value = p.GetValue(o, null);

                    }

                    else

                        param.Value = p.GetValue(o, null);

                }

            }

        }

    }

 

Notice how we’re checking the type; remember rule #2: “If an object has a collection of child objects, it must be a List”.  If this is the case then we are going to call a method specific to saving all of the data from the objects in the child list.  Check it out, first we loop through the list and get the values we need to call our function that will build the command:

 

private void InsertUpdateChild(System.Collections.IList childObjectList, SqlConnection conn)

{

    for (int i = 0; i < childObjectList.Count; i++)

    {

        string spName = childObjectList[i].GetType().GetProperty("InsertUpdateStoredProcedure").GetValue(childObjectList[i], null).ToString();

        CreateInsertCommand(childObjectList[i], spName, conn);

    }

}

 

Here we are looping through list of objects, creating and executing the SQL command as we go.  In this case we can dynamically save entire lists of objects dynamically; here we are creating the command: 

 

public void CreateInsertCommand(Object o, string strSProc, SqlConnection conn)

{

    SqlCommand cmd = InsertUpdateCMD(o, strSProc, conn);

    int intReturn = cmd.ExecuteNonQuery();

    foreach (SqlParameter param in cmd.Parameters)

    {

        if (param.Direction == ParameterDirection.InputOutput)

        {

            foreach (PropertyInfo p in o.GetType().GetProperties())

            {

                if (param.ParameterName.Replace("@", "").ToUpper() == p.Name.ToUpper())

                {

                    p.SetValue(o, param.Value, null);

                }

            }

        }

    }

}

 

In my code I decided to write the insert and update statements in one stored procedure.  If the ID of the table I'm putting data into is less than 1, then I will perform an insert – otherwise I'll perform an update.  This makes writing my data layer easier since there’s less stores procedures to manage.  This will also make migrating to SQL Server 2008 easier as one of the new features in SQL Server 2008 is the combination of the insert and update into a single SQL statement, the MERGE statement.

 

Once we get the data we want to set the values from the database back to the properties of the objects.  Here we will have to look at the columns of the dataset returned and set the values of the columns to the properties of our object like this:

 

foreach (DataColumn c in DS.Tables[0].Columns)

{

    if (p.Name == c.ColumnName)

    {

        // if there are encrypted properties

        if (dr[c] != DBNull.Value)

        {

            p.SetValue(oNewItemInstance, dr[c], null);

        }

    }

}

 

That wasn’t so bad, was it?  Now we can set parameters to properties and vice versa, we can also set the returned data back to the properties of our object.

 

Type (in)Consistency Between SQL and c#

 

There are a few things to consider here when thinking about types.  When passing variables between .net and the SQL server we have to ensure that types are handled properly in each place.  For example DBNull is different than null in c# and the minimum DateTime in c# is very different than the minimum date time in SQL Server.  Also in my object I want new objects to have an identity value of less than 1, but that will have to translate to NULL in the database.  To accommodate the different typing note the following code:

 

if (p.PropertyType.Name != "DateTime" &&

    p.PropertyType.Name != "Int32" &&

    p.PropertyType.Name != "String" &&

    p.PropertyType.Name != "DataSet" &&

    p.PropertyType.Name != "Boolean")

{

    //check if this is a list

    if (p.PropertyType.Name.IndexOf("List") > -1)

    {

        System.Collections.IList propertyObject = (System.Collections.IList)p.GetValue(o, null);

        FillChildList(propertyObject, o, conn);

    }

}

 

As you can see I am checking they type of the variable here to determine whether it should be null or not.  Based on the type I can handle it properly; NULL gets converted to DBNULL, a min value DateTime will also get converted to a DBNULL.  The chunk of code above is actually being used in the method that fills up the child list.  To make sure we can accommodate lists of properties which have lists as properties we should be calling this function recursively to ensure all data gets populated.

 

Back To the Business Layer

 

Our base class not only has some common methods, but there are a few common properties that make our life easier.  First of all the base class has a property called DS; this property holds the raw data returned from the database as a DataSet.  In the original design of this code I out this in for testing but also realized this is very handy if I need to transform this data with XSLT somewhere as I can just call the GetXml method from the DS property.  The other common properties which are integral in this example are the GetStoredProcedure and InsertUpdateStoredProcedure properties.  As you can see in the code above these properties hold the names of the stored procedures that perform the select and insert/update functionality against the database.  By adding them as properties of our objects we can set defaults in our constructor, or we can change the property somewhere else if we ever need to override the stored procedure being used for some reason.  Here’s what one our constructors might look like:

 

public NiceClass()

{

    _getStoredProcedure = "sp_GetNiceClass";

    _insertUpdateStoredProcedure = "sp_InsertUpdateNiceClass";

}

 

By setting the procedures in the object we can use reflection again to get the procedure from the data layer like this:

 

PropertyInfo pStoredProcedure = o.GetType().GetProperty("GetStoredProcedure");

if (pStoredProcedure != null)

{

    strSProc = pStoredProcedure.GetValue(o, null).ToString();

}

 

The final common property is the Identifier property.  Every class represents data, and each row of data has a unique ID which identifies it.  Perhaps NiceClass has an identity column called NiceID and CoolClass has an identifier called CoolID.  Though both of these serve the same purpose as unique identifiers, they have different names so to accommodate this we can simply have an Identifier property in the base class which will contain the same value as the identifier for each class.  Using this method we’re able to always get the unique identification using reflection, regardless of the naming convention for the class.

 

Methods in the Base Class

 

The methods in the base class will simply call the methods from the data layer.  Fill, Save, and Delete all do things slightly different so each gets its own method.  All of these methods are in the base class and each of them accepts an object and the stored procedure name to execute as parameters.  By simply passing an object polymorphism figures out the actual type, thus allowing us to pass in any of our business objects without having to worry about what type they are.  By passing in the stored procedure name we round out our flexibility by giving us the ability to change the stored procedure at any time if need be.  Here are the Fill and Save methods:

 

/// <summary>

/// Fills an object

/// </summary> 

public void Fill()

{

    GetConnectionString();

    MSSQL ms = new MSSQL(_connectionString);

    ms.Fill(this);

}

 

/// <summary>

/// Saves an object

/// </summary>

public void Save()

{

    GetConnectionString();

    MSSQL ms = new MSSQL(_connectionString);

    ms.InsertUpdate(this);

}

 

MSSQL is the name of the data access class.  I decided to call this MSSQL so we can have a data access class for each type of database; for example perhaps we will want to create a version of this for Oracle.  In that case most of the code is the same but we will be using Oracle connections and objects in ADO instead of SQL objects.  Perhaps we will want to create a version of this for SharePoint, where the data access is getting data from Lists instead of using ADO.NET, in that case the class will e very different.  By breaking out data access up into different classes in this way we can use the same business logic and patters no matter what we decide to connect to.  This can prove quite handy when migrating from one data based system to another.

 

Lists of Child Objects

 

When I first designed this pattern some of the objects I was using required a collection of child objects as a property.  In the initial design I had a bit of a challenge adding Lists into the mix here and due to time constraints ended up working with arrays.  However once the project was complete and I had some time to reflect (haha) on this project I figured out how to make Lists work in this context.  The key to realize how lists work with reflection: if you have a list of objects and try to get the type, it will not recognize the list as the base type.  To cast these properly you have to make sure you type the list property as a System.Collections.IList like this: (System.Collections.IList)p.GetValue(o, null);.  Another thing to keep in mind when working with generic lists with reflection would be instantiating an object whose type is that of the basis of the list.  It’s not quite as straightforward as creating an object from the type, here’s what you have to do:

Object o = Activator.CreateInstance(childList.GetType().GetGenericArguments()[0], false);

 

You still use Activator.CreateInstance, however you need to get the generic arguments from the type of the list.  If you try to get the type of the list you will never actually get the underlying type of the objects that make up the list.  Remember that a list is in itself an object, so to instantiate an object from a list of that type of object, you must first get the type of the list, then check the GenericArguments to see what is inside this list.

 

Stored Procedures

 

This example uses stored procedures for data access.  Below is an example of a few of the procedures used here just to round out this example.  I don't think I'm doing anything groundbreaking here, however I've included the code to accommodate the insert/update in SQL Server 2008 as well as 2005.

 

2005 Insert/Update Code

IF @QuestionID is null or @QuestionID = 0

      BEGIN

            INSERT INTO Question

                     ([QuestionText]

                     ,[QuestionTypeID]

                     ,[isProbCalc]

                     ,[QuestionMandatory]

             VALUES

                     (@QuestionText

                     ,@QuestionTypeID

                     ,@ProbCalc

                     ,@QuestionMandatory

            END

ELSE

      BEGIN

            UPDATE Question

                  SET [QuestionText] = @QuestionText

               ,[QuestionTypeID] = @QuestionTypeID

               ,[isProbCalc] = @ProbCalc

               ,[QuestionMandatory] = @QuestionMandatory

            WHERE [QuestionID] = @QuestionID

      END

 

Conclusion

 

As you can see when a language is strongly typed language it's not completely inflexible.  Using object oriented techniques and the built in power of ADO.NET we can create a data layer that will figure itself out at runtime.  This technique is an excellent way to get a project up and running without having to write tons of ADO.NET code to accommodate for stored procedures parameters, figure out types as you're coding, and thus takes out a lot of possibility for human error which is very handy in many situations.  Thanks for reading, hope you enjoyed it and I'll see you next time :^)

 

NOTE: this blog is taken from the presentation by christopher davis @ the CMAP Code Camp on October 25th, 2008. 

Posted on Tuesday, October 28, 2008 5:52 PM .NET | Back to top


Comments on this post: Using Reflection and ADO.NET to create a dynamic Data Layer

# re: Using Reflection and ADO.NET to create a dynamic Data Layer
Requesting Gravatar...
May you post the all code or a link where I mau download it?
Left by Riccardo on Nov 02, 2008 6:16 PM

# re: Using Reflection and ADO.NET to create a dynamic Data Layer
Requesting Gravatar...
Thanks man
Left by Vivek on Dec 11, 2008 9:01 AM

Your comment:
 (will show your gravatar)


Copyright © cdavis | Powered by: GeeksWithBlogs.net