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.