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# operators ‘is’, ‘as’, and type casts

Background information: starting with version 0.96, dbscript was capable of dealing with more than 1 database engine (i.e. other than MSSQL), and the question I had to solve was how the user interface would reflect the different capabilities of the database modules.

DBEngine
+-- MSSql.DBEngine
+-- Oracle.DBEngine
+.. other derived classes

For example, a database module might implement the “Import”, “Upload”, or “Generate Script” function, but how does the module let its capabilities be known to the rest of the code.

There are several possibilities:

• Boolean flags (e.g. CanImport) and an implementing method (DoImport())
• [Flags] enumeration and corresponding implementing methods
• Interfaces

Both bools and enumerations are easy to implement, but they don’t scale if you have a lot of features to take care of, and there is no strong link between the information and the implementation.

Using interfaces, the capabilities problem is solved by declaring and implementing interfaces (such as IDBEngineImport) and checking whether the module implements an interface. The only question remaining is: How does it perform?

Well, other people have asked that question before, and others have answered it:

C# ‘is’ operator performance (stackoverflow)

What are the performance characteristics of ‘is’ reflection in C#? (stackoverflow)

Prefix-casting versus as-casting in C#

Type casting impact over execution performance in C#

and the answer seems to be: It does not really matter.

However the answers behind above links are the same:

• If you just want to check for an interface implementation, use “is”.
• If you need the type-cast object, use “as” and check for null.
• Don’t use () type cast operators if you don’t know if the cast will always be successful, as exceptions have a performance penalty.

Analysis of the Unicode Han Database

The Unicode Han Database is part of the Unicode standard and contains data on all CJK Unified Ideographs encoded by the standard.

As of version 5.1, Unicode contains 71.234 CJK character and a total of 1.1 million character field values.

The Unihan database groups character fields into Field Types. For each field type below, the fields, the (assumed) language, and the number of characters having a value for that field are listed.

Definition

The English gloss

Definition

20627

Notepad++ not shown in Explorer context menu

I recently installed Notepad++ on 2 PCs, and noticed that on one of them the “Edit with Notepad++” context menu entry was missing in Explorer.

After some digging I found that Notepad++ and SciTE register the same CLSID under

HKEY_CLASSES_ROOT\*\shellex\ContextMenuHandlers.

Steps to register Notepad++ in the context menu:

• Uninstall SciTE
• Re-install Notepad++
• Kill and restart explorer.exe from Task Manager (or reboot)

Works

Multi-Value IComparer

In .Net, SortedList<>’s cannot contain multiple Key values. This may be caused by the fact that the SortedList index operator [] needs to uniquely identify a list entry. However the restriction is annoying if you just want to sort some arbitrary set of data.

I came across this post Advanced IComparer // Sorting on Multiple Values on creating custom comparers implementing IComparer<T>. I especially liked Simon’s source code he posted in his comment.

However I had to fix 2 problems to make it work in my code:

First was the mishandling of ASC/DESC: when you defined the properties to be compared, you needed to add “ASC” or “DESC” after the property name. “ASC” should be the default, as in SQL SELECTs, and should not be required. I changed args.Length>0 to args.Length>1 to avoid an out-of-range error.

The second problem was more difficult to solve, as it involved the reflection API. The original code supposed all comparable values of the objects to be Properties of the respective class. I wanted the comparison also to include member variables to avoid requiring to declare { get; set; } for every variable.

So I changed the property value handling from

object o1 = x.GetType().GetProperty(prop).GetValue(x, null);

to

pi = x.GetType().GetProperty(prop);
if (pi != null)
    o1 = pi.GetValue(x, null);
else
{
    fi = x.GetType().GetField(prop);
    if (fi != null)
        o1 = fi.GetValue(x);
    else
        throw new Exception("Field or Property " + prop + " not found in " + x.GetType().ToString());
}

which checks the declared names against property names and field (member variable) names.

So here’s the code:

Convert Unicode Hex Codepoint to Unicode Character in SQL Server

The Unicode standard uses the notation U+[x]xxxx to identify each Unicode character, i.e. “U+” followed by 4 or 5 hex digits.

If we remove the first 2 characters (as in the UCD), only hex digits will remain, and we need to convert them to an integer. Using this HexStrToVarBinary function, we get the binary representation of the hex number, which can than be CONVERTed to an int or bigint.

Next, we use the built-in NCHAR() function to create an NCHAR value of the int.

SELECT  UniHex,
        CONVERT(INT, dbo.HexStrToVarBinary(UniHex)) AS UniInt,
        NCHAR(CONVERT(INT, dbo.HexStrToVarBinary(UniHex))) AS UniNChar
FROM    UniHexTable

Note that the NCHAR() function only works with int arguments from 0 to 65535, so surrogate characters are not handled, and NCHAR() returns NULL.

SQL Server stores Unicode data encoded as UCS-2, and therefore does not support supplementary characters (surrogate pairs).

Unicode Versions supported in SQL Server

SQL Server	Unicode
2000	3.0 ?
2005	3.2
2008	5.0

(I did not find an explicit statement on SQL2000, so this is a guess based on the release of SQL Server 2000 and the release date of Unicode 3.0)

Managing Customers, Project Versions and Installations

If you develop software and you have a couple of customers, you will pretty soon lose track of which customer has which software version installed and when.

I now implemented a long over-due item on my todo list to manage customers, contacts, and their installation of software versions.

After you create a Customer record in dbscript, you can assign it Project Versions with shipping date and/or installation date. These Installations are visible from both the Customer record and the Project Version record.

The Project page gives you an overview of which Customer had software version installations, and which is the most current Project Version installed:

The latest version of dbscript can be downloaded here.

By the way, the current version of dbscript 0.97 also has an improved Data Diagram generator which performs better for data models with more than 100 tables.