how-to generate classes based on tables in ms sql 2008 ( see previous posts )

/****** Object:  StoredProcedure [dbo].[procUtils_GenerateClass]    

Script Date: 03/20/2010 13:10:40 ******/

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 ) 

DECLARE @CSharpDataType nvarchar(200)

select @DbName = DB_NAME()               

declare @strCode nvarchar(max) 

set @strCode = ''

-- use this variable while generating code 

DECLARE @WinNewLine nvarchar(2)

set @WinNewLine = CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10))

-- this is a horizontal tab

DECLARE @Tab nvarchar(1)

set @Tab = CONVERT(NVARCHAR(1) , CHAR(9))

SET @DbName = 'Cas'

BEGIN TRY        --begin try                            

set @strCode = @strCode +  'using System ;  ' + @WinNewLine + @WinNewLine

set @strCode = @strCode +  'namespace ' + @DbName + '.Model  {' + @WinNewLine  

set @strCode = @strCode +  'public class ' + @TableName + @Tab +  '{ ' + @WinNewLine  + @WinNewLine

set @strCode = @strCode +  @WinNewLine + '#region FieldsAndProps ' + @WinNewLine + @WinNewLine

--CODE SNIPPET TO LIST TABLE COLUMNS           

-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE          

--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name           

--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'          

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'              

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                                

--Populate the TABLE variable using some logic                                

-- SELECT * from INFORMATION_SCHEMA.COLUMNS                             

--SET IDENTITY_INSERT ON ; 

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        

-- START GENERATING PROPERTIES   

--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                    

IF @DataType = 'timestamp'        

begin 

SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                                

continue ; 

end 

-- get the C# type based on the passed sqlType, )( needs the DbVsCSharpTypes table ) 

set @CSharpDataType=( SELECT  dbo.funcGetCLRTypeBySqlType(@DataType) )

DECLARE @varPrivate nvarchar(200) 

set @varPrivate = '_' + @ColName 

-- GENERATE THE START REGION PART 

SET @strCode = @strCode + @WinNewLine + @Tab+ @Tab + '#region ' + @ColName + @WinNewLine + @WinNewLine 

-- set the nullable 

IF @IS_NULLABLE = 'YES'  and @CSharpDataType <> 'string'      

SET @StrCode = @strCode +   'private ' + @CSharpDataType + '? ' +  @varPrivate + ' ;' + @WinNewLine 

ELSE 

SET @StrCode = @strCode +   'private ' + @CSharpDataType + ' ' +  @varPrivate + ' ;' + @WinNewLine 

-- GENERATE THE PUBLIC MEMBER 

IF @IS_NULLABLE = 'YES'  and @CSharpDataType <> 'string'

SET @StrCode = @strCode +   'public ' + @CSharpDataType +  '? ' + @ColName + @WinNewLine + '{' + @WinNewLine

ELSE 

SET @StrCode = @strCode +   'public ' + @CSharpDataType +  ' ' + @ColName + @WinNewLine + '{' + @WinNewLine

SET @StrCode = @strCode + @Tab + @Tab + 'get { return ' + @varPrivate + '; } ' 

SET @strCode = @strCode + @Tab + @Tab + 'set { ' + @varPrivate +' = value ; }' + @WinNewLine 

SET @strCode = @strCode + @Tab + '} //eof prop ' + @ColName + @WinNewLine 

-- GENERATE THE ENDREGION PART FOR THE PROPERTY

SET @strCode = @strCode + @WinNewLine + @Tab + @Tab + '#endregion ' + @ColName  + @WinNewLine

--if @CurrentDelimiterPositionVar != @Count         

--SET @StrCode = @StrCode + ''        

--PRINT @StrCode         

--DEBUGGING        

--PRINT '@ColName - ' + @ColName         

--PRINT '@CSharpDataType - ' + @CSharpDataType                  

--PRINT '@IS_NULLABLE - ' + @IS_NULLABLE         

--PRINT '@CHARACTER_MAXIMUM_LENGTH - ' +  CONVERT ( VARCHAR , @CHARACTER_MAXIMUM_LENGTH )   

set @strCode = @strCode + @WinNewLine     

SELECT @strCode 

SET @strCode = ''

SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                                

END   --eof while 1-st loop

SELECT @strCode --IF WE ARE GENERATING MORE THAN 4000 CHARS 

SET @strCode = ''                  

set @strCode = @strCode + @WinNewLine+ @WinNewLine + @Tab + @Tab +  '#endregion FieldsAndProps ' + @WinNewLine + @WinNewLine

-- END GENERATION PROPERTIES 

-- START CONSTRUCTOR 

SET @CurrentDelimiterPositionVar = 1 --RESTART THE COUNTER

set @strCode = @strCode +  @WinNewLine + '#region Constructor' + @WinNewLine + @WinNewLine

set @strCode = @strCode + 'public ' + @TableName + '(System.Data.DataRow dr ) ' + @WinNewLine + '{'

WHILE @CurrentDelimiterPositionVar <= @Count --2nd 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                    

IF @DataType = 'timestamp'        

begin 

SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                                

continue ; 

end 

-- get the C# type based on the passed sqlType, )( needs the DbVsCSharpTypes table ) 

set @CSharpDataType =( SELECT  dbo.funcGetCLRTypeBySqlType(@DataType) )

set @varPrivate = '_' + @ColName 

-- GENERATE THE START REGION PART 

SET @strCode = @strCode + @WinNewLine + @Tab+ @Tab + '#region ' + @ColName + @WinNewLine  + @WinNewLine

---- set the nullable 

--IF @IS_NULLABLE = 'YES'  and @Data <> 'string'      

--    SET @StrCode = @strCode +   'private ' + @CSharpDataType + '? ' +  @varPrivate + ' ;' + @WinNewLine 

--ELSE 

--    SET @StrCode = @strCode +   'private ' + @CSharpDataType + ' ' +  @varPrivate + ' ;' + @WinNewLine 

DECLARE @strConvertToCode nvarchar(200) 

set @strConvertToCode  =( SELECT  dbo.funcGetCLRConvertToCodeBySqlType(@DataType) )

-- THE DBNULL

--IF @IS_NULLABLE = 'YES'  and @DataType <> 'string'

--    BEGIN

SET @StrCode = @strCode + @Tab + @Tab + @Tab + 

'if (dr["' + @ColName + '"] != null && !(dr["' + @ColName + '"] is DBNull))

this.' + @ColName + ' = ' + @strConvertToCode  + '(dr["' + @ColName + '"]);' + @WinNewLine

--    END --EOF IF @IS_NULLABLE = 'YES'  and @DataType <> 'string'

--ELSE 

--    BEGIN

--    SET @StrCode = @strCode +   'public ' + @DataType +  ' ' + @ColName + @WinNewLine + '{' + @WinNewLine

--    END -- EOF ELSE IF @IS_NULLABLE = 'YES'  and @DataType <> 'string'

-- GENERATE THE END REGION PART FOR THE IF DR

SET @strCode = @strCode + @WinNewLine + @Tab+ @Tab + '#endregion ' + @ColName  + @WinNewLine + @WinNewLine

--??

--if @CurrentDelimiterPositionVar != @Count         

--SET @StrCode = @StrCode + ''        

--PRINT @StrCode         

--DEBUGGING        

--PRINT '@ColName - ' + @ColName         

--PRINT '@DataType - ' + @DataType                  

--PRINT '@IS_NULLABLE - ' + @IS_NULLABLE         

--PRINT '@CHARACTER_MAXIMUM_LENGTH - ' +  CONVERT ( VARCHAR , @CHARACTER_MAXIMUM_LENGTH )   

SELECT @strCode --IF WE ARE GENERATING MORE THAN 4000 CHARS 

set @strCode = ''

SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                                

END --eof while 1-st loop 

SET @strCode = ''

set @strCode = @strCode + @Tab + '} //eof const for'+ @TableName 

set @strCode = @strCode + @WinNewLine + @WinNewLine + @Tab + '#endregion Constructor' + @WinNewLine + @WinNewLine

-- END CONSTRUCTOR 

-- BEGIN PARAMETERLESS CONST

set @strCode = @strCode +  @WinNewLine + '#region Parameterless Constructor' + @WinNewLine + @WinNewLine

set @strCode = @strCode + 'public ' + @TableName + '() ' + @WinNewLine + '{'

-- NOTHING HAPPENS HERE

set @strCode = @strCode + @Tab + '} //eof Parameterless const for'+ @TableName 

set @strCode = @strCode + @WinNewLine + @WinNewLine + @Tab + '#endregion Parameterless Constructor' + @WinNewLine + @WinNewLine

-- END PARAMETERLESS CONST

set @strCode = + @strCode + @WinNewLine + ' } //eof class ' + @TableName + @WinNewLine

set @strCode = + @strCode + @WinNewLine + ' } //eof namespace  ' + @WinNewLine

SELECT @strCode 

END TRY        --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                                       

/* 

<doc> Generates a C# class base on DataType conversion</doc>

*/

GO                            

/*           

Example usage:         

exec procUtils_GenerateClass 'PageType'

*/