Posts Tagged ‘T-SQL’
How to create vb.net class script from table in T-SQL
Posted by: admin in .NET, Database, MS SQL Server, VB.Net on March 6th, 2009
/*
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
How to create a vb.net structure from table using T-SQL script
Posted by: admin in MS SQL Server, VB.Net on March 6th, 2009
/*
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 @struct VARCHAR(MAX)
DECLARE @tablename VARCHAR(20)
SET @tablename = ‘tblExchangeDocument’
SET @newType = ”
SET @struct = ‘Public Class ‘ + RIGHT(@tablename, LEN(@tablename) – 3)
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’
SET @newType = ‘String’
IF @dbType = ‘bigint’
SET @newType = ‘long’
IF @dbType = ‘int’
SET @newType = ‘Integer’
IF @dbType = ‘datetime’
SET @newType = ‘date’
SET @struct = @struct + ”
SET @struct = @struct + char(13) + char(9) + ‘ Public ‘ + @fldname + ‘ AS ‘ + @newType
FETCH NEXT FROM rs
INTO @fldName, @dbType, @len, @precision, @scale
END
CLOSE rs
DEALLOCATE rs
SET @struct = @struct + char(13) + ‘ End Class’
print @Struct
How to Print ASCII characters in SQL Server (T-SQL)
Posted by: admin in Database, MS SQL Server on March 6th, 2009
Use char(intValue)
Param: intValue = Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.
| Control character | Value |
|
Tab |
char(9) |
|
Line feed |
char(10) |
|
Carriage return |
char(13) |