/****** 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 + @WinNewLineset @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 + '{' + @WinNewLineELSE
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'
*/
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!