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

Part 1: Retrieve information on tables and columns

Part 2: SELECT record, access record fields

To select multiple records and create their DAL objects, we pass an IDataReader object or a combination of connection and SELECT statement to a static method GetRecords() and receive a typed List<> of records:

protected static List<T> GetRecords<T>(IDataReader dr)
    where T : BaseTable, new()
{
  List<T> li = new List<T>();
  while (dr.Read())
  {
    T record = new T();
    record.GetRecord(dr);
    li.Add(record);
  }
  dr.Close();
  return li;
}

protected static List<T> GetRecords<T>(SqlConnection conn, string sSelect)
    where T : BaseTable, new()
{
  SqlCommand cmd = new SqlCommand(sSelect, conn);
  return GetRecords<T>(cmd.ExecuteReader());
}

The Save() method writes all values of dictNew as INSERT or UPDATE statement:

public void Save(SqlConnection conn)
{
  if (dictNew == null)
    return;

  if (dictRecord == null)
    Insert(conn);
  else
    Update(conn);
}

This is a sample implementation of the Insert() command. Note that in the simplest version, the values of dictNew are INSERTed, and only the IDENTITY column (if defined) is retrieved after the insert.

In more complex scenarios, you would want to retrieve all the values of the newly generated record to handle defaults and computed columns. If the table is lacking an IDENTITY column, the record needs to be selected using the columns of a UNIQUE constraint. These scenarios are not covered right now.

private void Insert(SqlConnection conn)
{
  StringBuilder sb = new StringBuilder();
  sb.Append("INSERT INTO " + TableName + " (");

  bool bFirst = true;
  foreach (string s in dictNew.Keys)
  {
    if (!bFirst)
      sb.Append(", ");
    sb.Append(s);
    bFirst = false;
  }

  sb.Append(") VALUES (");
  bFirst = true;
  foreach (string s in dictNew.Keys)
  {
    if (!bFirst)
      sb.Append(", ");
    sb.Append("@" + s);
    bFirst = false;
  }
  sb.Append(")");

  SqlCommand cmd = new SqlCommand();
  cmd.Connection = conn;

  foreach (string s in dictNew.Keys)
  {
    if (dictNew[s] == null)
      cmd.Parameters.AddWithValue("@" + s, DBNull.Value);
    else
      cmd.Parameters.AddWithValue("@" + s, dictNew[s]);
  }

  if (IdentityColumn != null)
  {
    sb.Append("; SELECT " + IdentityColumn + " FROM " + TableName +
      " WHERE " + IdentityColumn + " = SCOPE_IDENTITY()");
    cmd.CommandText = sb.ToString();
    object oIdentity = cmd.ExecuteScalar();
    dictNew.Add(IdentityColumn, oIdentity);
  }
  else
  {
    cmd.CommandText = sb.ToString();
    cmd.ExecuteNonQuery();
  }
}