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.
Posted by devio 
Posted by devio 
Posted by devio 
