Script to print VB.Net properties for the table

----------------------------------------
-- Vadim vadimg@sympatico.ca
-- prints VB.Net properties for the table
-- uses UDF fnTypeToVb
-----------------------------------------
 
CREATE PROCEDURE dbo.vbProp

@tbl varchar(100)

AS
SET NOCOUNT ON

DECLARE @var varchar(4000)
DECLARE cur CURSOR FOR
SELECT 
''' ' + column_name + ' from ' + @tbl + CHAR(13) + CHAR(10) +
' Private _' + column_name + ' As ' + dbo.fnTypeToVb(data_type)+ CHAR(13) + CHAR(10) +
' Public Property ' + Upper(Substring(column_name,1,1))+ Substring(column_name,2,100) + ' ()' +
' As ' + dbo.fnTypeToVb(data_type) + CHAR(13) + CHAR(10) +
' Get ' + CHAR(13) + CHAR(10) +
' Return _' + column_name + CHAR(13) + CHAR(10) +
' End Get' + CHAR(13) + CHAR(10) +
' Set(ByVal Value As ' + dbo.fnTypeToVb(data_type) + ')' + CHAR(13) + CHAR(10) +
' _' + column_name + ' = Value' + CHAR(13) + CHAR(10) +
' End Set' + CHAR(13) + CHAR(10) +
' End Property' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM  information_schema.columns WHERE table_name = @tbl

OPEN cur
FETCH NEXT FROM cur INTO @var
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @var
FETCH NEXT FROM cur INTO @var
END
CLOSE cur
DEALLOCATE cur
GO

----------------------------------------------------
-- Vadim
-- UDF: Sql data type -> VB.Net data type
----------------------------------------------------

CREATE FUNCTION fnTypeToVb (@tSql varchar(200))
RETURNS varchar(255) AS  
BEGIN 

DECLARE @tVB varchar(255)

SELECT @tVB = 
CASE WHEN @tSql = 'varchar' THEN 'String'
WHEN @tSql = 'char' then 'String'
WHEN @tSql = 'bit' then 'Boolean'
WHEN @tSql = 'int' then 'Integer'
WHEN @tSql = 'tinyint' then 'Byte'
WHEN @tSql = 'smallint' then 'Short'
WHEN @tSql = 'bigint' then 'Long'
WHEN @tSql = 'numeric' then 'Decimal'
WHEN @tSql = 'decimal' then 'Decimal'
WHEN @tSql = 'float' then 'Double'
WHEN @tSql = 'real' then 'Double'
WHEN @tSql = 'money' then 'Decimal'
WHEN @tSql = 'smallmoney' then 'Decimal'
WHEN @tSql = 'text' then 'String'
WHEN @tSql = 'ntext' then 'String'
WHEN @tSql = 'nchar' then 'String'
WHEN @tSql = 'nvarchar' then 'String'
WHEN @tSql = 'datetime' then 'Date'
WHEN @tSql = 'smalldatetime' then 'Date'
ELSE 'Unknown'
END

RETURN @tVB

END