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)

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);
        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;
    dictNew.Add(sColumnName, oValue);

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

  1. […] Part 2: SELECT record, access record fields […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: