/*
params:
@dbType - holds the data type defined in the table structure
@fldName - field/column name
@len - storage length of the column if data type is varchar/char etc.
@precision - numeric
@scale - numeric
@newType - equivalent data type in vb.net; ex. varchar -> string
@struct - script
@tablename - table name
*/
DECLARE @dbType VARCHAR(50)
DECLARE @fldName VARCHAR(50)
DECLARe @len smallint
DECLARE @precision smallint
DECLARE @scale smallint
DECLARE @newType VARCHAR(20)
DECLARE @class VARCHAR(MAX)
DECLARE @tablename VARCHAR(20)
DECLARE @tmpVar VARCHAR(MAX)
DECLARE @tmpVarpfx VARCHAR(3)
SET @tablename = ‘tblExchangeDocument’
SET @newType = ”
SET @tmpVar = ”
SET @class = ”–’Public Class ‘ + RIGHT(@tablename, LEN(@tablename) - 3)
SET @tmpVarpfx = ”
DECLARE rs CURSOR FOR
SELECT sys.columns.[name] as fldname, sys.types.name as dbtype, sys.columns.[max_length], sys.columns.[precision], sys.columns.[scale]
FROM sys.columns INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
WHERE object_id = OBJECT_ID(@tablename)
OPEN rs
FETCH NEXT FROM rs
INTO @fldname, @dbType, @len, @precision, @scale
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dbType = ‘varchar’ or @dbType = ‘char’
BEGIN
SET @tmpVarpfx = ’str’
SET @newType = ‘String’
END
IF @dbType = ‘bigint’
BEGIN
SET @tmpVarpfx = ‘lng’
SET @newType = ‘long’
END
IF @dbType = ‘int’
BEGIN
SET @tmpVarpfx = ‘int’
SET @newType = ‘Integer’
END
IF @dbType = ‘datetime’
BEGIN
SET @tmpVarpfx = ‘dat’
SET @newType = ‘date’
END
SET @tmpVar = @tmpVar + char(13)+ char(9) + ‘Private ‘+ @tmpVarpfx + @fldname + ‘ AS ‘ + @newType
SET @class = @class + char(13) + char(9) + ‘Public Property ‘ + @fldname + ‘() AS ‘ + @newType
SET @class = @class + char(13) + char(9) + char(9) +’GET’
SET @class = @class + char(13) + char(9) + char(9) + char(9) +’return ‘ + @tmpVarpfx + @fldname
SET @class = @class + char(13) + char(9) + char(9) +’END GET’
SET @class = @class + char(13) + char(9) + char(9) +’SET (value AS ‘+ @newType +’)’
SET @class = @class + char(13) + char(9) + char(9) + char(9) + @tmpVarpfx + @fldname + ‘ = ‘ + ‘value’
SET @class = @class + char(13) + char(9) + char(9) +’END SET’
SET @class = @class + char(13) + char(9) + ‘END Property’
FETCH NEXT FROM rs
INTO @fldName, @dbType, @len, @precision, @scale
END
CLOSE rs
DEALLOCATE rs
SET @class = @tmpVar + char(13) + @class
SET @class = ‘Public Class ‘ + RIGHT(@tablename, LEN(@tablename) - 3) + @class
SET @class = @class + char(13) + ‘ End Class’
print @class