SQL Table to VB Class (SQL 2000)

SQL Table to CSharp.sql

declare @TableName sysname;
set @TableName  = 'TABLENAME';
declare @Namespace varchar(50);
set @Namespace = 'NAMESPACE';
declare @prop varchar(8000);
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT 'using System; '
PRINT 'using NPoco; '
PRINT ''
PRINT 'namespace ' + @Namespace + ' {'
begin
PRINT '	[TableName("' + @TableName + '")] '
end

PRINT '	public class ' + @TableName
print '	{'
declare props cursor for
  select distinct 
				  '		public ' + ColumnType + ' ' + ColumnName + ' { get; set; }' as prop
	from ( 
	select  column_name as ColumnName,           
			case col.data_type  
				when 'bigint' then 'long' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'binary' then 'byte[]'             when 'bit' then 'bool' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'char' then 'string'             when 'date' then 'DateTime' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'datetime' then 'DateTime' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'datetime2' then 'DateTime' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'datetimeoffset' then 'DateTimeOffset'             when 'decimal' then 'decimal' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'float' then 'float' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'image' then 'byte[]'             when 'int' then 'int' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'money' then 'decimal' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'nchar' then 'char'             when 'ntext' then 'string'             when 'numeric' then 'decimal' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'nvarchar' then 'string'             when 'real' then 'double' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'smalldatetime' then 'DateTime'             when 'smallint' then 'short'             when 'smallmoney' then 'decimal' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'text' then 'string'             when 'time' then 'TimeSpan'             when 'timestamp' then 'DateTime' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'tinyint' then 'byte'             when 'uniqueidentifier' then 'Guid'             when 'varbinary' then 'byte[]'             when 'varchar' then 'string'             ELSE 'asdf'
			end ColumnType
		from information_schema.columns col join systypes typ on col.data_type = typ.name
	    
		where table_name = @TableName
	) t 
--order by prop
open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN
    print @prop
    FETCH NEXT FROM props INTO @prop
END
close props
DEALLOCATE props
PRINT '	}'
PRINT '}'

SQL Table to VB Class.sql

declare @TableName sysname;
set @TableName  = 'TABLENAME';
declare @Namespace varchar(50);
set @Namespace = 'NAMESPACE';
declare @prop varchar(8000);
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @PreAutoProperties bit = 'FALSE'; --Use this if you are working with VB9 and prior
PRINT 'Imports NPoco '
PRINT ''
PRINT 'Namespace ' + @Namespace
IF @PreAutoProperties = 1
begin
PRINT '<TableName("' + @TableName + '")> _'
end
else 
begin
PRINT '<TableName("' + @TableName + '")> '
end

PRINT 'Public Class ' + @TableName
declare props cursor for
  select distinct
  CASE WHEN @PreAutoProperties = 1 then 
				  'Private _' + ColumnName + ' as ' + ColumnType +  @NewLineChar + 
				  'Public Property ' + ColumnName + ' as ' + ColumnType +  @NewLineChar +
				  '	Get ' +  @NewLineChar +
				  '		Return _' + ColumnName +  @NewLineChar +
				  '	End Get ' +  @NewLineChar +
				  '	Set(ByVal value as ' + ColumnType + ')' + @NewLineChar +
				  '		_' + ColumnName + ' = value' +  @NewLineChar +
				  '	End Set ' +  @NewLineChar +
				  'End Property ' +  @NewLineChar 
				  else  
				  'Public Property ' + ColumnName + ' as ' + ColumnType end as prop
	from ( 
	select  column_name as ColumnName,           
			case col.data_type  
				when 'bigint' then 'long' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'binary' then 'byte[]'             when 'bit' then 'boolean' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'char' then 'String'             when 'date' then 'DateTime' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'datetime' then 'DateTime' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'datetime2' then 'DateTime' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'datetimeoffset' then 'DateTimeOffset'             when 'decimal' then 'decimal' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'float' then 'float' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'image' then 'byte[]'             when 'int' then 'Integer' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'money' then 'decimal' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'nchar' then 'char'             when 'ntext' then 'String'             when 'numeric' then 'decimal' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'nvarchar' then 'String'             when 'real' then 'double' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'smalldatetime' then 'DateTime'             when 'smallint' then 'short'             when 'smallmoney' then 'decimal' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'text' then 'String'             when 'time' then 'TimeSpan'             when 'timestamp' then 'DateTime' + COALESCE((CASE col.is_nullable WHEN 'YES' THEN '?' end), '')             when 'tinyint' then 'byte'             when 'uniqueidentifier' then 'Guid'             when 'varbinary' then 'byte[]'             when 'varchar' then 'String'             ELSE 'asdf'
			end ColumnType
		from information_schema.columns col join systypes typ on col.data_type = typ.name
	    
		where table_name = @TableName
	) t 
--order by prop
open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN
    print @prop
    FETCH NEXT FROM props INTO @prop
END
close props
DEALLOCATE props
PRINT 'End Class'
PRINT 'End Namespace'