ADO.NET Object Mapper To DataBase Tables And Stored Procedures

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
  Download C# Source Code
Article Update:   ADO.NET Source Code Generator - Map Microsoft Access DataBase Objects To .NET Classes
Do we really need another ADO.NET code generator?  There are several out there but I couldn't locate a decent one that also provided the source code for free.  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.  This code generator works for SQL Server and Microsoft Access.
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 \DataClasses folder in Columns.cs.  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.
 
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 5 methods (Execute, ToArray, Load, ToTable, and ToDataSet) and one subclass named Output.  All of the methods have their input/output parameters prepopulated.  As above, if the procedure has at least one output parameter, ToArray, ToTable, ToDataSet, and Load are not generated.
The Output subclass is a place for you to manually create the desired properties to be returned to the UI/business layer in the form of an array.  You would typically have the Output class inherit one of your generated data classes and then manually add any additional properties not included in that class.  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 anyway you see fit.  Feel free to remove the Output subclass altogether if you always want to return a DataTable/DataSet.  Feel free to remove any of the 5 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.


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

     private string mCustomerID = "";
 
     [ColumnAttributes("CustomerID")]
     public virtual string CustomerID
     {
       get { return mCustomerID; }
       set { mCustomerID = value; }
     }
 
 
     private string mCompanyName = "";
 
    [ColumnAttributes("CompanyName")]
    public virtual string CompanyName
    {
      get { return mCompanyName; }
      set { mCompanyName = value; }
    }
 
    private string mContactName = "";
 
    [ColumnAttributes("ContactName")]
    public virtual string ContactName
    {
      get { return mContactName; }
      set { mContactName = value; }
    }
 
    private string mContactTitle = "";
 
    [ColumnAttributes("ContactTitle")]
    public virtual string ContactTitle
    {
      get { return mContactTitle; }
      set { mContactTitle = value; }
    }
 
    private string mAddress = "";
 
    [ColumnAttributes("Address")]
    public virtual string Address
    {
      get { return mAddress; }
      set { mAddress = value; }
    }
 
    private string mCity = "";
 
    [ColumnAttributes("City")]
    public virtual string City
    {
      get { return mCity; }
      set { mCity = value; }
    }
 
    private string mRegion = "";
 
    [ColumnAttributes("Region")]
    public virtual string Region
    {
      get { return mRegion; }
      set { mRegion = value; }
    }

    private string mPostalCode = "";
 
    [ColumnAttributes("PostalCode")]
    public virtual string PostalCode
    {
      get { return mPostalCode; }
      set { mPostalCode = value; }
    }

    private string mCountry = "";
 
    [ColumnAttributes("Country")]
    public virtual string Country
    {
      get { return mCountry; }
      set { mCountry = value; }
    }

    private string mPhone = "";
 
    [ColumnAttributes("Phone")]
    public virtual string Phone
    {
      get { return mPhone; }
      set { mPhone = value; }
    }

    private string mFax = "";
 
    [ColumnAttributes("Fax")]
    public virtual string Fax
    {
      get { return mFax; }
      set { mFax = value; }
    }
	 
 }
 
 
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;
  }
 
Custom Wrapper Class For CustOrderHist
  
 private static object[,] OutputFields = null;

 public class Output : DataBase.Tables.Customers
 {
 
   private string mProductName = "";
 
   [DataBase.Tables.ColumnAttributes("ProductName" )] 
   public string ProductName
   { 
     get { return mProductName; }  
     set { mProductName = value; }  
   }
 
 }
 
 public static int Execute(string ConnectionString,string CustomerID)
 {
 
   int Ret = 0;
 
   try
   {
     Ret = Procedures.CustOrderHist.Execute(ConnectionString,CustomerID);  
   }
   catch (Exception) { throw; } 
   return Ret;
 }
 
 public static DataTable ToTable(string ConnectionString,string CustomerID)
 {
 
   DataTable Ret  = null;
 
   try
   {
     Ret = Procedures.CustOrderHist.ToDataTable(ConnectionString,CustomerID);  
   }
   catch (Exception) { throw; } 
   return Ret;
 }
 
 public static DataSet ToDataSet(string ConnectionString,string CustomerID)
 {
 
  DataSet Ret  = null;
 
  try
  {
    Ret = Procedures.CustOrderHist.ToDataSet(ConnectionString,CustomerID);  
  }
  catch (Exception) { throw; } 
  return Ret;
 }
 
 public static Output[] ToArray(string ConnectionString,string CustomerID)
 { 
 
   Output[] Ret = null; 
 
   try 
   { 
     Ret =  Load(ToTable(ConnectionString,CustomerID));
   }
   catch (Exception) { throw; } 
   return Ret; 
 }
 
 private static Output[] Load(DataTable oTable)
 { 
 
   Output[] Ret = null;
   Output Record = new Output();
 
   try 
   {
 
     if (oTable == null) { return Ret; } 
 
     Ret = new  Output[oTable.Rows.Count]; 

     if (OutputFields == null) 
     {
       OutputFields = Record.GetFields(typeof(Output));  
     }
  
     for(int i=0;i<oTable.Rows.Count;i++) 
     { 
       Record = new Output();
       Record.SetFields(OutputFields,oTable.Rows[i]);   
       Ret[i] = Record;
     }
 
  }
  catch (Exception) { throw; }
  return Ret;
 } 
	 

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.