Monday, November 14, 2011

Generating a C# Class Based on the Underlying SQL Server Database Table

If a class structure is based on the underlying database, it may be useful to be able to automatically generate a class "stub" based on the SQL Server table. I looked up the ways to do it without too much upfront time investment, and decided to follow one of the approaches.

First, a table have to be created to define the types in SQL Server and corresponding types in the language of choice - C# for me. The table is created by the following script:

/****** Object:  Table [dbo].[DbVsCSharpTypes]    Script Date: 03/20/2010 03:07:56 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DbVsCSharpTypes]')
AND type in (N'U'))
DROP TABLE [dbo].[DbVsCSharpTypes]
GO

/****** Object: Table [dbo].[DbVsCSharpTypes] Script Date: 03/20/2010 03:07:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DbVsCSharpTypes](
[DbVsCSharpTypesId] [int] IDENTITY(1,1) NOT NULL,
[Sql2008DataType] [varchar](200) NULL,
[CSharpDataType] [varchar](200) NULL,
[CLRDataType] [varchar](200) NULL,
[CLRDataTypeSqlServer] [varchar](2000) NULL,

CONSTRAINT [PK_DbVsCSharpTypes] PRIMARY KEY CLUSTERED
(
[DbVsCSharpTypesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

SET IDENTITY_INSERT [dbo].[DbVsCSharpTypes] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[DbVsCSharpTypes]([DbVsCSharpTypesId], [Sql2008DataType], [CSharpDataType], [CLRDataType], [CLRDataTypeSqlServer])
SELECT 1, N'bigint', N'short', N'Int64, Nullable', N'SqlInt64' UNION ALL
SELECT 2, N'binary', N'byte[]', N'Byte[]', N'SqlBytes, SqlBinary' UNION ALL
SELECT 3, N'bit', N'bool', N'Boolean, Nullable', N'SqlBoolean' UNION ALL
SELECT 4, N'char', N'char', NULL, NULL UNION ALL
SELECT 5, N'cursor', NULL, NULL, NULL UNION ALL
SELECT 6, N'date', N'DateTime', N'DateTime, Nullable', N'SqlDateTime' UNION ALL
SELECT 7, N'datetime', N'DateTime', N'DateTime, Nullable', N'SqlDateTime' UNION ALL
SELECT 8, N'datetime2', N'DateTime', N'DateTime, Nullable', N'SqlDateTime' UNION ALL
SELECT 9, N'DATETIMEOFFSET', N'DateTimeOffset', N'DateTimeOffset', N'DateTimeOffset, Nullable' UNION ALL
SELECT 10, N'decimal', N'decimal', N'Decimal, Nullable', N'SqlDecimal' UNION ALL
SELECT 11, N'float', N'double', N'Double, Nullable', N'SqlDouble' UNION ALL
SELECT 12, N'geography', NULL, NULL, N'SqlGeography is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 13, N'geometry', NULL, NULL, N'SqlGeometry is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 14, N'hierarchyid', NULL, NULL, N'SqlHierarchyId is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 15, N'image', NULL, NULL, NULL UNION ALL
SELECT 16, N'int', N'int', N'Int32, Nullable', N'SqlInt32' UNION ALL
SELECT 17, N'money', N'decimal', N'Decimal, Nullable', N'SqlMoney' UNION ALL
SELECT 18, N'nchar', N'string', N'String, Char[]', N'SqlChars, SqlString' UNION ALL
SELECT 19, N'ntext', NULL, NULL, NULL UNION ALL
SELECT 20, N'numeric', N'decimal', N'Decimal, Nullable', N'SqlDecimal' UNION ALL
SELECT 21, N'nvarchar', N'string', N'String, Char[]', N'SqlChars, SqlStrinG SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.' UNION ALL
SELECT 22, N'nvarchar(1), nchar(1)', N'string', N'Char, String, Char[], Nullable', N'SqlChars, SqlString' UNION ALL
SELECT 23, N'real', N'single', N'Single, Nullable', N'SqlSingle' UNION ALL
SELECT 24, N'rowversion', N'byte[]', N'Byte[]', NULL UNION ALL
SELECT 25, N'smallint', N'smallint', N'Int16, Nullable', N'SqlInt16' UNION ALL
SELECT 26, N'smallmoney', N'decimal', N'Decimal, Nullable', N'SqlMoney' UNION ALL
SELECT 27, N'sql_variant', N'object', N'Object', NULL UNION ALL
SELECT 28, N'table', NULL, NULL, NULL UNION ALL
SELECT 29, N'text', N'string', NULL, NULL UNION ALL
SELECT 30, N'time', N'TimeSpan', N'TimeSpan, Nullable', N'TimeSpan' UNION ALL
SELECT 31, N'timestamp', NULL, NULL, NULL UNION ALL
SELECT 32, N'tinyint', N'byte', N'Byte, Nullable', N'SqlByte' UNION ALL
SELECT 33, N'uniqueidentifier', N'Guid', N'Guid, Nullable', N'SqlGuidUser-defined type(UDT)The same class that is bound to the user-defined type in the same assembly or a dependent assembly.' UNION ALL
SELECT 34, N'varbinary ', N'byte[]', N'Byte[]', N'SqlBytes, SqlBinary' UNION ALL
SELECT 35, N'varbinary(1), binary(1)', N'byte', N'byte, Byte[], Nullable', N'SqlBytes, SqlBinary' UNION ALL
SELECT 36, N'varchar', N'string', N'String, Char[]', N'SqlChars, SqlStrinG SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.' UNION ALL
SELECT 37, N'xml', NULL, NULL, N'SqlXml'
COMMIT;
RAISERROR (N'[dbo].[DbVsCSharpTypes]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[DbVsCSharpTypes] OFF;

Here is what results from the script:

Next, a function that will return the C# type when the SQL Server type is passed to it will be required. It will take it from that table that was just created. This is the script for the function:

/****** Object:  UserDefinedFunction [dbo].[funcGetCLRTypeBySqlType]    
Script Date: 03/23/2010 15:25:09 ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[funcGetCLRTypeBySqlType]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[funcGetCLRTypeBySqlType]
GO
/****** Object: UserDefinedFunction [dbo].[funcGetCLRTypeBySqlType]
Script Date: 03/23/2010 15:25:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[funcGetCLRTypeBySqlType]
(@SqlType [nvarchar] (200))
RETURNS [varchar](200)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @ClrType varchar(200)
SET @ClrType = ( SELECT TOP 1 CSharpDataType FROM DbVsCSharpTypes
WHERE Sql2008DataType= @SqlType)
-- Return the result of the function
RETURN @ClrType END
/*Used for automatic conversation between tsql and C# types */
GO

Sample of the usage - nothing hard yet.

A small function just because I want my private variable start from a lower case character

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[lowerCaseFirstCharacter]
(@Input [nvarchar] (200))
RETURNS [varchar](200)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @Result varchar(200)
declare @Len int
SET @Len = LEN(@Input)
SET @Result = LOWER(SUBSTRING(@Input, 1, 1)) + SUBSTRING(@Input, 2, @Len-1)

RETURN @Result
END

And, finally, the stored procedure that generates some C# code:

IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_GenerateClass]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[procUtils_GenerateClass]
GO
/****** Object: StoredProcedure [dbo].[procUtils_GenerateClass]
Script Date: 03/20/2010 13:10:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procUtils_GenerateClass]
@TableName [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
DECLARE @DbName nvarchar(200 )
select @DbName = DB_NAME()
declare @strCode nvarchar(max)
set @strCode = ''

BEGIN TRY --begin try
set @strCode = @strCode + 'namespace ' + @DbName + '.Gen' + CHAR(13) + '{' + CHAR(13)
set @strCode = @strCode + CHAR(9) + 'public class ' + @TableName + CHAR(13) + CHAR(9) + '{ ' + CHAR(13)

DECLARE @ColNames TABLE
(
Number [int] IDENTITY(1,1), --Auto incrementing Identity column
ColName [varchar](300) , --The string value ,
DataType varchar(50) , --the datatype
IS_NULLABLE nvarchar(5) , --should we add =null in front
CHARACTER_MAXIMUM_LENGTH INT
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
DECLARE @PkColName varchar(200)
set @PkColName = ''
declare @ColumnName varchar(200)
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT

INSERT INTO @ColNames
SELECT column_name , Data_type , IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@TableName
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @ColName varchar(300);
DECLARE @DataType varchar(50)
DECLARE @IS_NULLABLE VARCHAR(5)
DECLARE @CHARACTER_MAXIMUM_LENGTH INT
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count --1st loop
BEGIN
--Load current value from the Table
SELECT @ColName = ColName FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @IS_NULLABLE = IS_NULLABLE FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
SELECT @CHARACTER_MAXIMUM_LENGTH = CHARACTER_MAXIMUM_LENGTH FROM @ColNames
WHERE Number = @CurrentDelimiterPositionVar
-- get the C# type based on the passed sqlType, )( needs the DbVsCSharpTypes table )
set @DataType =( SELECT dbo.funcGetCLRTypeBySqlType(@DataType) )
IF @IS_NULLABLE = 'YES'
set @DataType = @DataType + '?'
DECLARE @varPrivate nvarchar(200)
set @varPrivate = '_' + dbo.lowerCaseFirstCharacter(@ColName)

--GENERATE THE PRIVATE MEMBER
SET @StrCode = @strCode + CHAR(9)+ CHAR(9) + 'private ' + @DataType + ' ' + @varPrivate + ';' + CHAR(13) + CHAR(13)
-- GENERATE THE PUBLIC MEMBER
SET @StrCode = @strCode + CHAR(9)+ CHAR(9) + 'public ' + @DataType + ' ' + @ColName + CHAR(13) + CHAR(9)+ CHAR(9) + '{' + CHAR(13)
SET @StrCode = @strCode + CHAR(9) + CHAR(9) + CHAR(9) + 'get { return ' + @varPrivate + ' } ' + CHAR(13)
SET @strCode = @strCode + CHAR(9) + CHAR(9) + CHAR(9) + 'set { ' + @varPrivate +' = value ; }' + CHAR(13)
SET @strCode = @strCode + CHAR(9) + CHAR(9) + '}' + CHAR(13)

if @CurrentDelimiterPositionVar != @Count
SET @StrCode = @StrCode + ''
IF @DataType != 'timestamp'

set @strCode = @strCode + char(13)
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
set @strCode = + @strCode + char(9) + ' } //class ' + @TableName + CHAR(13)
set @strCode = + @strCode + ' } //namespace ' + CHAR(13)

PRINT @strCode
END TRY
BEGIN CATCH
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varCHAR(9)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))
END CATCH
END --procedure end
/* Generates a C# class base on DataType conversion*/
GO

Here's a test table I used to check the results:

Here's the stored procedure output sample:

References

How can I programatically convert SQL data-types to .Net data-types?

how-to generate classes based on tables in ms sql 2008

Function to get the C# type based on the tsql type

by . Also posted on my website

No comments: