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

Part 1: Retrieve information on tables and columns

Part 2: SELECT record, access record fields

Part 3: SELECT records, INSERT

Part 4: Generating C# classes

Part 5: Generated C# classes and polymorphism

The code presented in the previous articles generates a C# class definition (based on dbscript‘s Object table) like this:

public partial class Object : DAL.BaseTable
{
  public Object() { }
  public Object(IDataReader dr) : base(dr) { }
  protected override string TableName { get { return "Object"; } }
  public int OID
  { get { return GetColumnValue<int>(Columns.OID); } }
  public int PV_OID
  {
    get { return GetColumnValue<int>(Columns.PV_OID); }
    set { SetColumnValue(Columns.PV_OID, value); }
  }
  ....
  protected override string IdentityColumn { get { return "OID"; } }
  public static class Columns
  {
    public static string OID = "OID";
    public static string PV_OID = "PV_OID";
    ....
  }
  public static List<Object> GetRecords(IDataReader dr)
  { return GetRecords<Object>(dr); }
  public static List<Object> GetRecords(SqlConnection conn, string sSelect)
  { return GetRecords<Object>(conn, sSelect); }
}

Note that the class is generated as a partial class, so you are free to extend it in your own code.

In the dbscript database, all information about schema objects is stored in the Object table, and of course, not all columns of this table are used by the different object types, such as tables, views, etc. If we take into account different database engines (a concept introduced in dbscript 0.96), then tables, views, etc in MSSQL have different properties than their counterparts in Oracle or PostgreSQL.

Time to create some class which are derived from DAL.BaseTable to inherit the DAL mechanisms, but reference the generated DAL.Object columns:

public class SchemaObject : dbscriptlib.DAL.BaseTable
{
  protected override string TableName { get { return "Object"; } }
  protected override string IdentityColumn { get { return "OID"; } }
  public int OID
  { get { return GetColumnValue<int>(DAL.Object.Columns.OID); } }
  public string OwnerName
  {
    get { return GetColumnValue<string>(DAL.Object.Columns.OwnerName); }
    set { SetColumnValue(DAL.Object.Columns.OwnerName, value); }
  }
  public string ID
  {
    get { return GetColumnValue<string>(DAL.Object.Columns.ID); }
    set { SetColumnValue(DAL.Object.Columns.ID, value); }
  }
  ....
}
public class Table : SchemaObject
{
  public string FileGroupName 
  {
    get { return GetColumnValue<string>(DAL.Object.Columns.FileGroupName); }
    set { SetColumnValue(DAL.Object.Columns.FileGroupName, value); }
  }
  ....
}

Note that the FileGroupName is defined only for tables, rather than for all DAL classes. In Oracle, there is no FileGroupName, but a TablespaceName, which we map to the original column using this definition:

public class OracleTable : SchemaObject
{
  public string TablespaceName
  {
    get { return GetColumnValue<string>(DAL.Object.Columns.FileGroupName); }
    set { SetColumnValue(DAL.Object.Columns.FileGroupName, value); }
  }
  ....
}

With this simple mechanism, a sort of polymorphic table can easily be implemented, and only the accessing classes “know” how about the mapping of the table columns.