Creating a light-weight Data Access Layer in C# (2)

The first part of this series dealt with the queries to retrieve the necessary information on tables and columns from the system catalog of a SQL Server 2000/2005 database.

Next we need a base class with minimum functionality to provide table name column names, and SELECT, INSERT and UPDATE statements.

namespace DAL
{
  public abstract class BaseTable
  {
    protected abstract string TableName { get; }
    protected virtual string IdentityColumn { get { return null; } }

    private Dictionary<string, object> dictRecord = null;
    private Dictionary<string, object> dictNew = null;

dictRecord holds the current (old) values as retrieved by SELECT, dictNew contains new values for INSERT or UPDATE.

The parameterless constructor creates a new record, whereas dictRecord is set by a constructor with IDataReader parameter:

protected BaseTable()
{
}

protected BaseTable(IDataReader dr)
{
  GetRecord(dr);
}

private void GetRecord(IDataReader dr)
{
  if (dictRecord == null)
  {
    dictRecord = new Dictionary<string, object>();
    for (int iField = 0; iField < dr.FieldCount; iField++)
      if (dr.IsDBNull(iField))
        dictRecord.Add(dr.GetName(iField), null);
      else
        dictRecord.Add(dr.GetName(iField), dr.GetValue(iField));
  }
}

The generic method GetColumnValue retrieves a (typed) field value from one of the dictionaries, the method SetColumnValue sets a field value in dictNew:

protected CT GetColumnValue<CT>(string sColumnName)
{
  if (dictNew != null && dictNew.ContainsKey(sColumnName))
    return (CT)dictNew[sColumnName];

  if (dictRecord != null && dictRecord.ContainsKey(sColumnName))
    return (CT)dictRecord[sColumnName];

  return default(CT);    
}

protected void SetColumnValue(string sColumnName, object oValue)
{
  if (oValue is string && (oValue as string) == "")
    oValue = null;

  if (dictNew == null)
    dictNew = new Dictionary<string, object>();
  if (dictNew.ContainsKey(sColumnName))
    dictNew[sColumnName] = oValue;
  else
    dictNew.Add(sColumnName, oValue);
}