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

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

Part 6: UPDATE record

Our DAL object has two collections, with dictNew holding new values, and dictRecord for previously inserted or retrieved values. To implement an Update() method, the Insert() method has to be fixed to save the inserted values:

   dictRecord = dictNew;
   dictNew = null;

Now we are ready to implement UPDATE. The parameters are collected similarly as described in part 3, which describes the INSERT operation:

private void Update(SqlConnection conn)
{
    if (IdentityColumn != null)
    {
        if (!dictRecord.ContainsKey(IdentityColumn))
            throw new Exception("UPDATE " + this.TableName + " without IDENTITY column set");

        StringBuilder sb = new StringBuilder();
        sb.Append("UPDATE " + TableName + " SET ");

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

        sb.Append(" WHERE " + IdentityColumn + " = @" + IdentityColumn);

        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]);
        }

        cmd.Parameters.AddWithValue("@" + IdentityColumn, dictRecord[IdentityColumn]);
        cmd.CommandText = sb.ToString();

        foreach (string s in dictNew.Keys)
            if (dictRecord.ContainsKey(s))
                dictRecord[s] = dictNew[s];
            else
                dictRecord.Add(s, dictNew[s]);

        dictNew = null;
    }
    else
        throw new Exception("UPDATE " + this.TableName + " without IDENTITY column is not implemented.");
}

If the table does not have an IDENTITY column, the WHERE condition for the UPDATE is unknown and an exception is raised. If the identity column of the existing record is not set, an exception is raised, too.

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.

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

Part 1: Retrieve information on tables and columns

Part 2: SELECT record, access record fields

Part 3: SELECT records, INSERT

As we have the base class of our DAL almost complete, it’s time to generate the actual table access classes.

CREATE PROCEDURE Generate_DAL AS
PRINT
'using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
/*
 Class Definition for Data Access Layer
 automatically generated by Generate_DAL
 ' + CONVERT(VARCHAR, GETDATE(), 120) + '
*/
namespace DAL
{'
DECLARE @OID INT, @ID VARCHAR(50)
DECLARE cTable CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR

(insert the table SELECT statement here). First we generate the access class constructors:

OPEN cTable
FETCH cTable INTO @OID, @ID
WHILE @@FETCH_STATUS=0 BEGIN
  IF UPPER(@ID) = 'COLUMNS' SET @ID = SUBSTRING(@ID, 1, LEN(@ID)-1)
  PRINT
'    public partial class ' + @ID + ' : DAL.BaseTable
 {
 public ' + @ID + '() { }
 public ' + @ID + '(IDataReader dr)  : base(dr)  { }
 protected override string TableName { get { return "' + @ID + '"; } }'

DECLARE @colID VARCHAR(50), @colDataType VARCHAR(50), @colLength INT
DECLARE @colNullable BIT, @colIdentity BIT, @colComputed BIT
DECLARE @colNetType VARCHAR(50), @colIdentityName VARCHAR(50)
SET @colIdentityName = NULL
DECLARE cCol CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR

(insert the column SELECT statement here). We need to convert SQL data types to C# data types, and translate NULLable to Nullable<>:

OPEN cCol
FETCH cCol INTO @colID, @colDataType, @colLength, @colNullable, @colIdentity, @colComputed
WHILE @@FETCH_STATUS=0 BEGIN
  IF @colNullable = 1
    SELECT @colNetType = CASE @colDataType
      WHEN 'int' THEN 'int?'
      WHEN 'bit' THEN 'bool?'
      WHEN 'nvarchar' THEN 'string'
      WHEN 'varchar' THEN 'string'
      WHEN 'ntext' THEN 'string'
      WHEN 'text' THEN 'string'
      WHEN 'datetime' THEN 'DateTime?'
      ELSE @colDataType
    END
  ELSE
    SELECT @colNetType = CASE @colDataType
      WHEN 'int' THEN 'int'
      WHEN 'bit' THEN 'bool'
      WHEN 'nvarchar' THEN 'string'
      WHEN 'varchar' THEN 'string'
      WHEN 'ntext' THEN 'string'
      WHEN 'text' THEN 'string'
      WHEN 'datetime' THEN 'DateTime'
      ELSE @colDataType
    END

  IF @colIdentity = 1 SET @colIdentityName = @colID
  PRINT ' public ' + @colNetType + ' ' + @colID + '{
get { return GetColumnValue<' + @colNetType + '>(Columns.' + @colID + '); }'
  IF @colIdentity = 0 AND @colComputed = 0
    PRINT ' set { SetColumnValue(Columns.' + @colID + ', value); }'
  PRINT ' }'
  FETCH cCol INTO @colID, @colDataType, @colLength, @colNullable, @colIdentity, @colComputed
END
CLOSE cCol

IF @colIdentityName IS NOT NULL
  PRINT '        protected override string IdentityColumn { get { return "' + @colIdentityName + '"; } }'
PRINT '        public static class Columns {'
OPEN cCol
FETCH cCol INTO @colID, @colDataType, @colLength, @colNullable, @colIdentity, @colComputed
WHILE @@FETCH_STATUS=0 BEGIN
  PRINT    '            public static string ' + @colID + ' = "' + @colID + '";';
  FETCH cCol INTO @colID, @colDataType, @colLength, @colNullable, @colIdentity, @colComputed
END
CLOSE cCol
DEALLOCATE cCol
PRINT    '        }'
PRINT    '        public List<' + @ID + '> GetRecords(IDataReader dr) {
    return GetRecords<' + @ID + '>(dr);
  }
public List<' + @ID + '> GetRecords(SqlConnection conn, string sSelect) {
    return GetRecords<' + @ID + '>(conn, sSelect);
  }
}'

FETCH cTable INTO @OID, @ID
END
PRINT    '}'

The resulting class definition has typed getter/setter properties for each column of the base table, and a static string array containing the names of the table columns.

Thus changes the data model (column type, column name, dropped table or column) are immediately reflected in the generated code, and automatically raise to compilation warnings or errors in your code which depends on the model.

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();
  }
}

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);
}

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

I wrote about generating Stored Procedure wrappers a couple of months ago, and experimented with Subsonic which is a powerful framework for several databases.

However Subsonic requires you to have a special Subsonic section in your app/web.config file. So I thought: what is necessary to create a lightweight data access layer if I don’t need the full frameworks.

First, get the list of tables and columns you need in your DAL.

In SQL Server 2000, this is done with the two queries:

SELECT sysobjects.id, sysobjects.name
FROM sysobjects
WHERE    xtype='U' AND name<>'dtproperties'
AND    name in ('list', 'of', 'table', 'names')
ORDER BY name

SELECT dbo.syscolumns.name AS ID, dbo.systypes.name AS DataType,
    CASE WHEN systypes.status = 0 THEN NULL
         WHEN dbo.systypes.name = 'nvarchar' THEN dbo.syscolumns.length / 2
         ELSE dbo.syscolumns.length END AS Length,
    syscolumns.isnullable AS IsNullable,
    COLUMNPROPERTY (syscolumns.id, syscolumns.name, 'IsIdentity') as IsIdentity,
    COLUMNPROPERTY (syscolumns.id, syscolumns.name, 'IsComputed') as IsComputed
FROM dbo.syscolumns
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE     syscolumns.id = @OID AND systypes.name <> 'sysname'
ORDER BY syscolumns.colid

The queries can be run in SQL 2005 too, but if you don’t need to support 2000, use the new sys.* schema:

SELECT sys.objects.object_id, sys.objects.name
FROM sys.objects
WHERE type='U' AND name<>'dtproperties'
AND name in ('list', 'of', 'table', 'names')
ORDER BY name

SELECT sys.columns.name AS ID, sys.types.name AS DataType,
    CASE WHEN sys.types.name = 'nvarchar' THEN sys.columns.max_length / 2
         ELSE sys.columns.max_length END AS Length,
    sys.columns.is_nullable AS IsNullable,
    sys.columns.is_identity as IsIdentity,
    sys.columns.is_computed as IsComputed
FROM sys.columns
INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
WHERE sys.columns.object_id = @OID AND sys.types.name <> 'sysname'
ORDER BY sys.columns.column_id

The parameter @OID in the column query denotes the table’s id column.

C# T-SQL Stored Procedure Wrapper with dbscript

As my article on generating a C# wrapper for T-SQL Stored Procedures has recently become one of the most popular posts, I thought of providing an implementation within dbscript.

From the various different possibilities (generate from a Project Versions’ schema information, define an XSL on the Project Version’s XML) I chose the simplest solution was to create two Code Snippets.

The Code Snippet “Stored Procedure Wrapper (2000)” queries sysobjects, syscolumns and systypes, as outlined in the post.

The second Code Snippet, “Stored Procedure Wrapper”, queries the INFORMATION_SCHEMA views ROUTINES and PARAMETERS to achieve the same goal.

The generated C# code can be copied directly from the browser window into Visual Studio.

The Code Snippets can be downloaded here along with version 0.95 of dbscript, and will be included in future versions.

Generating a C# Stored Procedure Wrapper in TSQL

I love compiling languages. Whenever you change something that consequently does not fit the rest of the program, something breaks and you get a compiler error. Great!

If you develop database applications, this nice change-it-break-it mechanism stops to work, as the relation between database objects and their usage by a C# application is in no way hard-coded.

Therefore I developed a small piece of T-SQL code which will generate a static C# class which encapsulates all stored procedures in a given database in the form:

public static class MyStoredProc {
    public static SqlCommand MyProc1(SqlConnection conn)
    {
        SqlCommand cmd = new SqlCommand("MyProc1", conn);
        ...
        return cmd;
    }
    ...
}

The strategy is simple: loop through all desired procedures, and for each procedure loop through its parameters twice: once for the declaration, second time for the parameter values.

Create file and class header:

PRINT 'using System;
using System.Data;
using System.Data.SqlClient;
'

PRINT    'namespace ' + @NameSpace
PRINT    '{'
PRINT    'public static class ' + @Class + '
{'

Now we declare the loop for the procedure names (the code uses SQL2000 dictionary objects, which are also present on SQL2005; adaption should be simple):

DECLARE cP CURSOR FOR
SELECT  dbo.sysobjects.name
FROM     dbo.sysobjects
WHERE    (dbo.sysobjects.name NOT LIKE 'dt[_]%')
AND    ... other conditions ...
AND     (dbo.sysobjects.xtype = 'P')
ORDER BY dbo.sysobjects.name

OPEN cP
FETCH cP INTO @Proc

WHILE @@FETCH_STATUS = 0 BEGIN

PRINT '    public static SqlCommand ' + @Proc + '(SqlConnection conn'

Next we declare the cursor for the procedure parameters in the correct order:

DECLARE c CURSOR FOR
SELECT  dbo.syscolumns.name, dbo.systypes.name, dbo.syscolumns.length
FROM     dbo.sysobjects
INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE    (dbo.sysobjects.name = @Proc) AND (dbo.systypes.name <> N'sysname')
ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid

OPEN c
FETCH c INTO @Column, @Type, @Length

WHILE @@FETCH_STATUS = 0 BEGIN

For parameter declaration, we need to translate the SQL datatypes into C# datatypes. In this example, we use the following mapping:

int -> int?
datetime -> DateTime?
n/varchar -> string
bit -> bool?

PRINT  '        , ' +
    CASE @Type
    WHEN 'int' THEN 'int?'
    WHEN 'datetime' THEN 'DateTime?'
    WHEN 'nvarchar' THEN 'string'
    WHEN 'varchar' THEN 'string'
    WHEN 'bit' THEN 'bool?'
    ELSE '**datatype-not-supported**' END +
    ' ' + SUBSTRING(@Column, 2, LEN(@Column)-1)

All the wrapping method does is to create a SqlCommand using the SqlConnection:

PRINT ' )'
PRINT ' {'
PRINT '   SqlCommand cmd = new SqlCommand("' + @Proc + '", conn);'
PRINT '   cmd.CommandType = CommandType.StoredProcedure;'
PRINT '   cmd.CommandTimeout = 0;'

Then we loop through the parameters again to set the SqlCommand’s Parameters’ values. As shown above, parameter types are mapped to C# Nullable<T> types (question-mark notation), except for strings, as they can have the value null. Thus we have to treat string parameters differently from nullable (in C#) parameters:

OPEN c
FETCH c INTO @Column, @Type, @Length

WHILE @@FETCH_STATUS = 0 BEGIN

    IF @Type='nvarchar' OR @Type='varchar'
        PRINT ' cmd.Parameters.AddWithValue("' + @Column + '", ' +
            SUBSTRING(@Column, 2, LEN(@Column)-1) + ' != null ? (object)' +
            SUBSTRING(@Column, 2, LEN(@Column)-1) + ' : DBNull.Value);'
    ELSE
        PRINT ' cmd.Parameters.AddWithValue("' + @Column + '", ' +
            SUBSTRING(@Column, 2, LEN(@Column)-1) + '.HasValue ? (object)' +
            SUBSTRING(@Column, 2, LEN(@Column)-1) + '.Value : DBNull.Value);'

Finally, return the new SqlCommand:

PRINT '        return cmd;'
PRINT '    }'

Run this and paste the result into your C# application