ADO.NET Source Code Generator - Map Microsoft Access DataBase Objects To .NET Classes

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
  Download C# Source Code
This is the second part in the effort to create an object mapper for certain databases to ADO.NET.  The first, ADO.NET Code Generator - Map SQL Server DataBase Objects To .NET Classes , was incredibly successful and easy to use.  The code sample now includes a class for mapping Microsoft Access database tables, columns, and stored queries to .NET classes and methods in the exact same fashion as the SQL Server version does.


The purpose of this excerise is not to demonstrate how to write a tool like this but simply to provide you with the source code to a fairly comprehensive tool that you can adjust to your liking.  So, what does the tool do?  In a sense, I've tried to map the database to .NET class properties and methods to better enable you to implement an object oriented environment for your data access layer.  Thus, you should rarely ever have to type the words OleDbCommand, OleDbDataAdapter, or OleDbConnection again.
The Microsoft Access code generation class has a couple of hacks in it.  Namely around getting the proper parameters and data types for stored queries.  The OLEDB provider does not return parameters for stored queries via the standard .DeriveParameters method.  So, I went way back to DAO to collect these.  Thus, the translation of data types from DAO to OLEDB to .NET could be considered a little hokey.  The code generator works pretty well against a basic Microsoft Access database.  If you run it against one with forms or macros in it, you are likely to get some extraneous stored procedure classes generated for these.
I also had a problem converting the binary data in the standard northwind database that comes with Microsoft Access.  You'll notice that the sample console application for access loads elvis.jpg (my son Caeden at Halloween) into the nwind.mdb file first and then retrieves it.  If nothing else, you got a free sample of how to save images to an Access database as well as how to retrieve them.
 
Summary
1.Reviews an entire Microsoft Access 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 query.  Each query has 4 methods: PrepareCommand, Execute, ToDataTable, and ToDataSet.  Each of these 4 methods has the query's parameters converted to .NET input/output parameters automatically.  Plus, the PrepareCommand method creates all of the OleDbParameter 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 queries 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 MicrosoftAccess 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 queries.  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 queries.  In fact, for stored queries 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 : db.Tables.ColumnInfo
 {
 
   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 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 mCountry = "";
 
   [ColumnAttributes("Country")]
   public virtual string Country
   {
     get { return mCountry; }
     set { mCountry = value; }
   }
 
   private string mCustomerID = "";
 
   [ColumnAttributes("CustomerID")]
   public virtual string CustomerID
   {
     get { return mCustomerID; }
     set { mCustomerID = value; }
   }
 
   private string mFax = "";
 
   [ColumnAttributes("Fax")]
   public virtual string Fax
   {
     get { return mFax; }
     set { mFax = value; }
   }
 
   private string mPhone = "";
 
   [ColumnAttributes("Phone")]
   public virtual string Phone
   {
     get { return mPhone; }
     set { mPhone = value; }
   }
 
   private string mPostalCode = "";
 
   [ColumnAttributes("PostalCode")]
   public virtual string PostalCode
   {
     get { return mPostalCode; }
     set { mPostalCode = value; }
   }
 
   private string mRegion = "";
 
   [ColumnAttributes("Region")]
   public virtual string Region
   {
     get { return mRegion; }
     set { mRegion = value; }
   }
 
 }
	
Northwind Stored Query GetCustomerByID

PARAMETERS [CustomerID] Text ( 255 );
SELECT *
FROM Customers
WHERE Customers.CustomerID=[CustomerID];


GetCustomerByID Stored Query Mapped To .NET

internal class GetCustomerByID
{
 
  public static OleDbCommand PrepareCommand(string customerID)
  {

     OleDbCommand cmd = new OleDbCommand(); 
     OleDbParameter prm; 
 
     try
     {
 
        cmd.CommandType=CommandType.Text; 
        cmd.CommandText="EXECUTE GetCustomerByID";
 
        prm = new OleDbParameter();
        prm.ParameterName = "CustomerID";
        prm.OleDbType = OleDbType.VarChar;
        prm.Precision = (byte)int.Parse("0");
        prm.Size = int.Parse("0");
        prm.Scale  = (byte)int.Parse("0");
        prm.SourceColumn = null; 
        prm.Direction = ParameterDirection.Input;
        prm.Value = customerID;
        cmd.Parameters.Add(prm); 
 
     }
     catch (Exception) { throw; }
     return cmd;
 }
 
 public static int Execute(string connectionString,string customerID)
 {
    int Ret=0; 
 
    try
    {
 
      OleDbCommand cmd = PrepareCommand(customerID);
 
      using (OleDbConnection conn = new OleDbConnection())
      {
 
        conn.ConnectionString = connectionString;
        conn.Open(); 
        cmd.Connection = conn; 
        cmd.ExecuteNonQuery();
 
        conn.Close();
 
      }
 
    }
    catch (Exception) { throw; }
    return Ret;
 }
 
 public static DataTable ToDataTable(string connectionString,string customerID)
 {

    DataTable dt = new DataTable(); 
 
    try
    {
 
      OleDbCommand cmd = PrepareCommand(customerID);
 
      using (OleDbConnection conn = new OleDbConnection())
      {
 
        conn.ConnectionString = connectionString;
        conn.Open(); 
        cmd.Connection = conn; 
 
        using(OleDbDataAdapter da = new OleDbDataAdapter(cmd))
        {
           da.Fill(dt);
           cmd.Dispose();
        }

      }
 
    }
    catch (Exception) { throw; }
    return dt;
 }
 
 public static DataSet ToDataSet(string connectionString,string customerID)
 {
    DataSet ds = new DataSet(); 
 
    try
    {
 
      OleDbCommand cmd = PrepareCommand(customerID);
 
      using (OleDbConnection conn = new OleDbConnection())
      {
 
         conn.ConnectionString = connectionString;
         conn.Open(); 
         cmd.Connection = conn; 
 
         using(OleDbDataAdapter da = new OleDbDataAdapter(cmd))
         {
           da.Fill(ds);
           cmd.Dispose();
         }

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

 
}      
 
Custom Wrapper Class For GetCustomerByID

public class GetCustomerByID
{
 
  private static object[,] OutputFields = null;

  public class Output : db.Tables.Customers 
  {
  
  }
	
  public static int Execute(string connectionString,string customerID)
  {
 
     int Ret = 0;
 
     try
     {
        Ret = Procedures.GetCustomerByID.Execute(connectionString,customerID);  
     }
     catch (Exception) { throw; } 
     return Ret;
  }
 
  public static DataTable ToTable(string connectionString,string customerID)
  {
 
     DataTable Ret  = null;
 
     try
     {
        Ret = Procedures.GetCustomerByID.ToDataTable(connectionString,customerID);  
     }
     catch (Exception) { throw; } 
     return Ret;
  }
 
  public static DataSet ToDataSet(string connectionString,string customerID)
  {
 
    DataSet Ret  = null;
 
    try
    {
       Ret = Procedures.GetCustomerByID.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 table)
  { 
 
     Output[] Ret = null;
     Output Record = new Output();
 
     try 
     {
 
       if (table == null) { return Ret; } 
 
       Ret = new  Output[table.Rows.Count]; 
 
       if (OutputFields == null) 
       {
         OutputFields = Record.GetFields(typeof(Output));  
       }  
 
       for(int i=0;i<table.Rows.Count;i++) 
       { 
          Record = new Output();
          Record.SetFields(OutputFields ,table.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.