how-to generate C# classes with stored procedure in sql server 2008

/****** 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 ) 

select @DbName = DB_NAME()               

declare @strCode nvarchar(max) 

set @strCode = ''

BEGIN TRY        --begin try                            

set @strCode = @strCode +  'namespace ' + @DbName + '.Gen  {' + CHAR(13)  

set @strCode = @strCode +  'public class ' + @TableName + CHAR(11) +  '{ ' + CHAR(13)  + CHAR(13)

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

--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 = '_' + @ColName 

-- GENERATE THE START REGION PART 

SET @strCode = @strCode + CHAR(11)+ CHAR(11) + '#region ' + @ColName + CHAR(13) + CHAR(13) 

--GENERATE THE PRIVATE MEMEBER 

SET @StrCode = @strCode +   'private ' + @DataType + ' ' +  @varPrivate + ' ;' + CHAR(13) 

-- GENERATE THE PUBLIC MEMBER 

SET @StrCode = @strCode +   'public ' + @DataType +  ' ' + @ColName + CHAR(13) + '{' + CHAR(13)

SET @StrCode = @strCode + CHAR(11) + CHAR(11) + 'get { return ' + @varPrivate + ' } ' 

SET @strCode = @strCode + CHAR(11) + CHAR(11) + 'set { ' + @varPrivate +' = value ; }' + CHAR(13) 

SET @strCode = @strCode + CHAR(11) + '} //eof prop ' + @ColName + CHAR(13) + CHAR(13)

-- GENERATE THE START REGION PART 

SET @strCode = @strCode + CHAR(11) + CHAR(11) + '#endregion ' + @ColName  + CHAR(13)

if @CurrentDelimiterPositionVar != @Count         

SET @StrCode = @StrCode + ''        

IF @DataType != 'timestamp'        

--PRINT @StrCode         

--DEBUGGING        

--PRINT '@ColName - ' + @ColName         

--PRINT '@DataType - ' + @DataType                  

--PRINT '@IS_NULLABLE - ' + @IS_NULLABLE         

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

set @strCode = @strCode + char(13)     

SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                                

END                               

set @strCode = + @strCode + char(13) + ' } //eof class ' + @TableName + CHAR(13)

set @strCode = + @strCode + char(13) + ' } //eof namespace  ' + CHAR(13)

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(11)) +                         

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

SELECT NAME FROM SYS.tables where name like '%feature%'          

EXEC [dbo].[procUtils_GenerateClass] @TableName = N'Requirement'         

*/