ADO.NET 2.0 Object Mapper To DataBase Tables And Stored Procedures

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
  Download C# Source Code
By popular demand, the NullSkull ADO.NET Object Mapper has been upgraded to include support for .NET Framework 2.0 and SQL Server 2005.  If you need the old version that supports .NET Framework 1.1, SQL Server 2000 (this new version still supports SQL Server 2000), or Microsoft Access, it is still available here:  ADO.NET Object Mapper 1.0.  If you aren't familiar with our object mapper, in a sense, I've tried to map the database to .NET class properties and methods to better enable you to implement an multi-layered object oriented environment for your data access layer.  Thus, you should rarely ever have to type the words SqlCommand, SqlDataAdapter, or SqlConnection again.
Unforunately, I don't have immediate access to other enterprise relational databases such as Oracle, Sybase, Informix, etc.  If you need a tool for these, download my code and make the necessary adjustments.  I've already done most of the hard work.
 
Summary
1.Reviews an entire SQL Server database and generates a class and class properties (with the appropriate .net data types) for every user created table.  The output of this is in the \GeneratedDataClasses folder in a folder for interfaces and tables.  Every class and class property is set to be public.  All of the data classes have been setup with .NET CustomAttributes to allow auto-population from DataTable classes.  They've also been enabled for serialization to xml just in case you want to implement solutions similar to XmlSerializer to Serialize Class to Xml and Bulk Load Data.  I've also added a few standard columns to all of my data classes to support sorting of class properites via Generic Lists.  This article will explain more:  NET Generics - Sorting IList By Class Properties
 
2.Creates an individual class for every stored procedure.  Each procedure has 4 methods: PrepareCommand, Execute, ToDataTable, and ToDataSet.  Each of these 4 methods has the stored procedure's parameters converted to .NET input/output parameters automatically.  Plus, the PrepareCommand method creates all of the SqlParameter objects and prepopulates their settings and values.
In the Execute method, any output parameters are set as ref parameters in the method and are prepopulated with the return values from the stored procedure.  If the stored procedure is found to have at least one output parameter, the ToDataTable and ToDataSet methods are not generated.
By default, all stored procedures generated are set to be internal classes.  This enables you to put them in a separate assembly from your UI and/or business logic code and hide them from these developers.  Typically, you would write a wrapper layer for each of these stored procedures and expose this to the UI and/or business layer.
However, if you don't want to utilize this extra layer, change the ProcedureClassVisibility parameter to public instead of internal in the SqlServer class constructor.  This will expose the generated stored procedures directly to your UI/business layer.
Either way, if you change the input/output parameters of an existed stored procedure and regenerate the code, your regular application will not compile until you've made the necessary changes.  No more guess work as to where the procedure is being called by the UI/Business layer.
 
3.Creates standard wrapper classes for the generated stored procedures.  The wrapper class is set to be publicly accessible to the UI/business layer.
Why bother with an additional layer?
The wrapper classes offer you a nice shell to map your generated data classes to the input or output of your stored procedure.  In fact, for stored procedures that return results of select statements, the wrapper class can pre-populate your data class properties with the results.  No hard coding this class property = this column value.
Here's how the custom wrapper works.  By default, it has 6 methods (Execute, ToList, ToRecord, Load, ToTable, and ToDataSet).  All of the methods have their input/output parameters prepopulated.  As above, if the procedure has at least one output parameter, ToRecord, ToList, ToTable, ToDataSet, and Load are not generated.
The ToTable and ToDataSet methods are initially written with a private modifier.  So, by default, you'll only be exposing methods that work best in an OOP environment.  If you want to expost either of these methods, just change their modifiers.
The ToList method returns a .NET Generic List<T> of the desired class.  The generator spits out List along with a where T: constraint with unknown as the classname and the new() constraint.  The developer is required to replace unknown with the appropriate class name.  Returning the results in a .NET Generics List will empower the business and/or user interface layers to work with the data in a much more intuitive way than data tables.
The Load() method will disregard any columns from the DataTable or properties in the class that aren't a match leaving your class properties populated with their default values.
You can adjust the wrapper class after generation any way you see fit.  Feel free to remove any of the 6 methods that aren't applicable or that you don't want exposed to the UI/business layer.
The generator will not overwrite an existing wrapper class even if you check to regenerate these classes.  I wanted to be sure the tool never eliminated code you actually wrote.
Let's take a look at the code below to get an idea of what the tool generates.


 
Partial Northwind Customers Tabled Mapped To .NET
 
public class Customers : DataBase.Tables.ColumnInfo
{
 

     private string CustomersCustomerID = "";
 
     [ColumnAttributes("CustomerID")]
     public string CustomerID
     {
       get { return CustomersCustomerID; }
       set 
       {	 
          if (value != CustomersCustomerID)
          { 
            CustomersCustomerID = value; 
            this.IsDirty = true; 
          }
       }
     }
 
 }
 
 
Northwind Stored Procedure CustOrderHist

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

CustOrderHist Stored Procedure Mapped To .NET
 
private static SqlCommand PrepareCommand(string CustomerID)
{
   SqlCommand oCmd = new SqlCommand(); 
   SqlParameter oPrm; 
 
    try
    {

     oCmd.CommandType=CommandType.StoredProcedure;
     oCmd.CommandText="CustOrderHist";

     oPrm = new SqlParameter();
     oPrm.ParameterName = "@RETURN_VALUE";
     oPrm.SqlDbType = SqlDbType.Int;
     oPrm.Precision = (byte)int.Parse("0");
     oPrm.Size = int.Parse("0");
     oPrm.Scale  = (byte)int.Parse("0");
     oPrm.SourceColumn = null; 
     oPrm.Direction = ParameterDirection.ReturnValue;
     oCmd.Parameters.Add(oPrm); 
 
     oPrm = new SqlParameter();
     oPrm.ParameterName = "@CustomerID";
     oPrm.SqlDbType = SqlDbType.NChar;
     oPrm.Precision = (byte)int.Parse("0");
     oPrm.Size = int.Parse("5");
     oPrm.Scale  = (byte)int.Parse("0");
     oPrm.SourceColumn = null; 
     oPrm.Direction = ParameterDirection.Input;
     oPrm.Value = CustomerID; 
     oCmd.Parameters.Add(oPrm); 
 
    }
    catch (Exception) { throw; }
    return oCmd;
}

  public static int Execute(string ConnectionString,string CustomerID)
  {
    int Ret=0; 
 
    try
    {
 
      SqlCommand oCmd = PrepareCommand(CustomerID);
 
      using (SqlConnection oConn = new SqlConnection())
      {
 
        oConn.ConnectionString = ConnectionString;
        oConn.Open(); 
        oCmd.Connection = oConn; 
        oCmd.ExecuteNonQuery();
 
        Ret = (int)oCmd.Parameters["@RETURN_VALUE"].Value;
 
        oConn.Close();
 
      }
 
    }
    catch (Exception) { throw; }
    return Ret;
  }

  public static DataTable ToDataTable(string ConnectionString,string CustomerID)
  {
    DataTable dt = new DataTable(); 
 
    try
    {
 
      SqlCommand oCmd = PrepareCommand(CustomerID);
 
      using (SqlConnection oConn = new SqlConnection())
      {
 
        oConn.ConnectionString = ConnectionString;
        oConn.Open(); 
        oCmd.Connection = oConn; 
 
        using(SqlDataAdapter da = new SqlDataAdapter(oCmd))
        {
          da.Fill(dt);
          oCmd.Dispose();
        }

      }
 
    }
    catch (Exception) { throw; }
    return dt;
  }

  public static DataSet ToDataSet(string ConnectionString,string CustomerID)
  {
    DataSet ds = new DataSet(); 
 
    try
    {
 
      SqlCommand oCmd = PrepareCommand(CustomerID);
 
      using (SqlConnection oConn = new SqlConnection())
      {
 
        oConn.ConnectionString = ConnectionString;
        oConn.Open(); 
        oCmd.Connection = oConn; 
 
        using(SqlDataAdapter da = new SqlDataAdapter(oCmd))
        {
          da.Fill(ds);
          oCmd.Dispose();
        }

      }
 
    }
    catch (Exception) { throw; }
    return ds;
  }
 
Partial Custom Wrapper Class For CustOrderHist
  
  // We'll keep a static array of the properties of our
  // class.  This enables us to only use Reflection the
  // very first time the procedure is called.  Nice
  // speed enhancement!

 private static object[,] OutputFields = null;
 
 
 #region Execute
 public static int Execute(string connectionString, string customerID)
 {
 
    int returnValue = 0;
 
    try
    {
      returnValue = Procedures.DeleteGridColumn.Execute(connectionString,
                                                        customerID);  
    }
    catch (Exception) { throw; }
    return returnValue;
 }
 #endregion

 
 public static List<T> ToList<T>(string connectionString,
	                                            string customerID) where T: Customers,new()
 { 
 
    List<T> recordList = new List<T>(); 
 
    #region Generated Code 
    T record = null; 
    DataTable table = null; 
 
    try 
    { 
 
       table = ToTable(connectionString,customerID);
 
       if (table == null) 
       {
          return null; 
       } 
 
       if (table.Rows.Count <1) 
       {
          return null; 
       } 
 
       if (OutputFields == null) 
       {
         record = new T(); 
         OutputFields = record.GetFields(typeof(T)); 
       } 
 
       for (int i = 0; i < table.Rows.Count; i++) 
       { 
         record = new T(); 
         record.SetFields(OutputFields, table.Rows[i]); 
         record.IsDirty = false; 
         recordList.Add(record); 
       } 
    } 
    catch (Exception) { recordList = null; throw; } 
    #endregion 
 
    return recordList; 
 }


 
 
 #region ToDataSet
 private static DataSet ToDataSet(string connectionString,string languageID)
 {
 
    DataSet returnValue = null;
 
    try
    {
       returnValue = Procedures.CustOrderHist.ToDataSet(connectionString,customerID);  
    }
    catch (Exception) { throw; } 
    return returnValue;
 }
 #endregion
 
 #region To Table
 private static DataTable ToTable(string connectionString,string customerID)
 {
 
   DataTable returnValue = null;
 
   try
   {
     returnValue = Procedures.CustOrderHist.ToDataTable(connectionString,customerID);  
   }
   catch (Exception) { throw; } 
 return returnValue;
 }
 #endregion
 
 
	 

Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.