Map DataTable / Stored Procedure Output To Class Properties

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
  Download C# Source Code
  ADO.NET Code Generator - Map DataBase Objects To .NET Classes And Stored Procedures
 
This article is a culmination of two previous articles: XmlSerializer to Serialize Class to Xml and Bulk Load Data and Map DataTable DataColumn To Class Properties Using Custom Attributes .  Plus, I wanted to provide a more complete code design alternative to strongly typed datasets for mapping ADO.NET resultsets (DataSet and DataTable) returned from stored procedures.  Another goal of this design is to better structure source code to make it easier for the database layer to be worked on by multiple developers without conflicts with checking files in and out of Visual Source Safe (or other source code repository).
 
The code sample shown today has the following specific goals in mind:
1.Each set of code for its corresponding stored procedure should be able to be checked out without tieing up code for other stored procedures.
2.The database layer developer should be able to add .NET code to call the stored procedure and return an object array who's properties would should in intellisense with very little effort.
3.Wherever possible, the output returned from stored procedures should utilize existing business classes in conjunction with derived columns not currently defined in these same classes.
4.The database layer developer should never have to hard code class properties from the DataRows returned from the stored procedures.  This should be handled automatically.
5.All classes returned from the method calls should be enabled for serialization to Xml.
6.Enable UI developers to easily determine the specific items returned from a stored procedure.  The methods to call a stored procedure should be consistent in offering an option to work with the raw DataTable or return an array of classes with properties that show in intellisense without using strongly typed datasets.


Steps taken to accomplish these goals
1.Every stored procedure gets its own class.  If the database layer developer wanted to get fancy, it wouldn't be particularly difficult to write a simple code generator to auto created these classes from the database itself.
2.A template class named aaProcTemplate.cs was added for convenience.  Whenever a new stored procedure class is needed, create the new class and paste the template code in as a base to work from.
3.Each stored procedure class contains a subclass named Output.  The database developer will create properties based on the expected results from the stored procedured.  In some cases, these properties will be defined entirely in the Output class.  In other cases, the database developer can designate the Output class to inherit other DataBase.Tables classes and / or interfaces.
4.In each stored procedure class, the private method .Load() is called which will populate the Output subclass properties with the values from the returned DataTable.  By using reflection, we can using the Output class custom attributes to automatically map the corresponding class property to the DataTable.Column.  If the DataTable or the Output class contains columns or properties not defined in both objects, they'll simply be disregarded.
5.As a convenience to the UI and business logic developers, we'll enable each class returned to be easily serialized to Xml by having the DataBase.ColumnInfo class inherit our Serialization class that exposes a .ToXml() method.
6.As previously mentioned, every stored procedure class has a subclass Output which is returned as an array of Output.  It also exposes a method to return the DataTable directly just in case the UI developer needs it for ease of databinding.  The UI or business logic developer can simply define the Output array and then check intellisense for available properties.  ex:
DataBase.CustOrdersDetail.Output[] Ret = DataBase.CustOrdersDetail.ToArray("10248",ConStr);
Then type: Ret[i]. and see the dropdown of available properties.
 
In the end, this design doesn't attempt to overly complicate the data access layer or bind it too closely to the database.  It is entirely up to the database layer developer to decide how tight or loose the object mapping should be on a stored procedure by stored procedure basis while still providing a consistent interface.  This article is not intended as a complete database access layer solution.  It is merely to demonstrate how you might want to approach exposing your stored procedures that return query results.  I hope you find this helpful.  As always, if you have questions, please post them to our forums.
 
Class1.cs    ( What the UI developer sees )
using System;
using System.Diagnostics; 
using DataBase;

namespace ConsoleApplication1
{
 
  class Class1
  {
 
    [STAThread]
    static void Main(string[] args)
    {
 
       string ConStr="Data Source=localhost;User ID=sa;Password=;Initial Catalog=Northwind";

       try
       {

         DataBase.CustOrderHist.Output[] Ret = DataBase.CustOrderHist.ToArray("ALFKI",ConStr);
         DataBase.CustOrdersDetail.Output[] Ret2 = DataBase.CustOrdersDetail.ToArray("10248",ConStr);
            
         if ((Ret ==null ) || (Ret2 == null)) { return; }
         
         for(int i=0;i<Ret.Length;i++)
         {
           Console.WriteLine(Ret[i].ProductName); 
         }
         
         Console.WriteLine("");
 
         for(int i=0;i<Ret2.Length;i++)
         {
           Console.WriteLine(Ret2[i].ProductName + "   " + Ret2[i].ExtendedPrice.ToString()); 
           Console.WriteLine(Ret2[i].ToXml());
         }
    
       }
       catch (Exception e) { Console.WriteLine(e.Message); }
       Console.WriteLine("done");
       Console.ReadLine();
    }
  }
}

Stored Procedure Class
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;   
using System.Collections; 
using System.Reflection;
using System.Diagnostics; 
using DataBase;
 

namespace DataBase
{
 
    public class CustOrderHist
    {
        private const string ProcedureName = "CustOrderHist ";

        public class Output : DataBase.Tables.Customers  
        {

            [ColumnAttributes( "ProductName" )]
            public string ProductName = "";

            [ColumnAttributes( "Total" )]
            public double Total = 0;
            
        }
 
        public static DataTable ToTable(string CustomerID,string ConStr)
        {
             string Sql = "dbo." + ProcedureName + " '" +  CustomerID + "'";

            DataTable Ret  = null;
 
            try
            {
                Ret = Query.GetDataTable(Sql,ConStr); 
            }
            catch (Exception e) { throw e; }
          
            return Ret;
        }
 
        public static Output[]  ToArray(string CustomerID,string ConStr)
        {
            
            Output[] Ret = null;
 
            try
            {
                Ret =  Load(ToTable(CustomerID,ConStr));
            }
            catch (Exception e) { throw e; }
          
            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];

                object[,] oFields = Record.GetFields(typeof(Output)); 

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

    }
}  

DataBase General Class
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;   
using System.Collections; 
using System.Reflection;
using System.Diagnostics; 
using System.Xml.Serialization;
using System.Xml; 
using System.IO;
using System.Text;

namespace DataBase
{
  
 
    public class Query
    {
 
        public static DataTable GetDataTable(string Sql,string ConnectionString)
        {

            // The following code is open to SQL Injection attacks.  It is shown
            // here for demo purposes only.  Typically, you would incorporate your
            // own methods for data retrieval or utilize the SqlHelper.cs class from
            // MSDN

            SqlConnection oConn = new SqlConnection(); 
            DataTable oTable = new DataTable(); 
         
            try 
            {   
                oConn.ConnectionString =  ConnectionString;
                oConn.Open();       
                SqlDataAdapter oDA = new SqlDataAdapter(Sql,oConn); 
                oDA.Fill(oTable); 
                oConn.Close(); 
            } 
            catch (Exception) { throw;   }   
            finally{ if (oConn.State == ConnectionState.Open) { oConn.Close(); } } 
            return oTable;
        }
    
    }
 
    public class ColumnInfo : DataBase.Serialization 
    { 
 
 
        public object[,] GetFields(Type t)
        {
              
            FieldInfo[] oFields = t.GetFields();
            FieldInfo oField;
            Attribute[] attributes;
            object[,]  StructureInfo = new object[oFields.Length,2]; 

            try
            {
                for(int i =0;i<oFields.Length;i++)
                {      
                    oField = oFields[i];
                    attributes =  Attribute.GetCustomAttributes(oField,typeof(DataBase.ColumnAttributes),false);
                    StructureInfo[i,0] = oField;
                    StructureInfo[i,1] = attributes;
                }
            }
            catch (Exception e) { throw e; }
            return StructureInfo;

        }
 
        public void SetFields(object[,] StructureInfo,DataRow oRow)
        {
            FieldInfo oField;
            Attribute[] attributes;

            try
            {

                for(int i =0;i<=StructureInfo.GetUpperBound(0);i++)
                {
                       
                    oField = (FieldInfo)StructureInfo[i,0];
                    attributes = (Attribute[])StructureInfo[i,1];

                    foreach(Attribute attribute in attributes)
                    {
                        ColumnAttributes oColumnAttributeName = (ColumnAttributes)attribute;
                        if (!oRow.Table.Columns.Contains(oColumnAttributeName.ColumnName)) { continue; } 
                        if (oRow[oColumnAttributeName.ColumnName] == System.DBNull.Value)  { continue; }
                        oField.SetValue(this,oRow[oColumnAttributeName.ColumnName]);
                        break;
                    }
               
                }
              
            } 
            catch(Exception e) { Debug.WriteLine(e.Message); throw e; }
            return;
        }
 

    }

 
        public class Serialization
        {
            public string ToXml()
            {

                StringWriter Output = new StringWriter(new StringBuilder());
                string Ret="";

                try
                {
                    XmlSerializer s = new XmlSerializer(this.GetType());
                    s.Serialize(Output,this);

                    Ret = Output.ToString().Replace("xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"","");
                    Ret = Ret.Replace("xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"",""); 
                    Ret = Ret.Replace("<?xml version=\"1.0\" encoding=\"utf-16\"?>","").Trim();
                }
                catch (Exception) {  throw; }
            
                return Ret;
            }
      }
 
    [AttributeUsage(AttributeTargets.Field,AllowMultiple = true)]
    public class ColumnAttributes : System.Attribute 
    {
        public string ColumnName="";

        public ColumnAttributes(string DataBaseColumnName)
        {
            this.ColumnName = DataBaseColumnName;
        }
    }
 
}  

Tables
using System;

namespace DataBase
{
 
  public class Tables
  {
  
        public class Customers : DataBase.ColumnInfo
        {
            
            [ColumnAttributes( "CustomerID" )]
            public string CustomerID = "";

            [ColumnAttributes( "CompanyName" )]
            public string CompanyName = "";
            
            [ColumnAttributes( "ContactName" )]
            public string ContactName = "";
            
            [ColumnAttributes( "ContactTitle" )]
            public string ContactTitle = "";
            
            [ColumnAttributes( "Address" )]
            public string Address = "";
            
            [ColumnAttributes( "City" )]
            public string City = "";
            
            [ColumnAttributes( "Region" )]
            public string Region = "";
            
            [ColumnAttributes( "PostalCode" )]
            public string PostalCode = "";
            
            [ColumnAttributes( "Country" )]
            public string Country = "";

            [ColumnAttributes( "Phone" )]
            public string Fax = "";
           
        }
      

   }
}   


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.