Updated Code To Get Class Definition From Table Structure

My previous post on Script to Create MetadataType classes was to work with LINQ-To-SQL and partial classes. EF 4.1 introduces Code First which uses POCOs and here is the code to create a class definition from a table’s structure.

Enjoy.

SET NOCOUNT ON
declare @TableName varchar(256) = 'BusinessTypes'
declare @EntityName varchar(256) = 'BusinessType'
declare @TableSchema varchar(256) = 'dbo'

declare @ColumnName varchar(256)
    , @DataType varchar(256)
    , @NewDataType varchar(256)
    , @MaxLength int
    , @Nullable varchar(5)
    
declare @Lines table (Line varchar(1000))

insert into @Lines select 'public class ' + @EntityName
insert into @Lines select '{'

declare @DataTypes table (SqlDataType varchar(1000), DataType varchar(1000))

insert into @DataTypes (SqlDataType, DataType) values ('bit', 'bool')
insert into @DataTypes (SqlDataType, DataType) values ('char', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('datetime', 'DateTime')
insert into @DataTypes (SqlDataType, DataType) values ('decimal', 'decimal')
insert into @DataTypes (SqlDataType, DataType) values ('int', 'int')
insert into @DataTypes (SqlDataType, DataType) values ('money', 'decimal')
insert into @DataTypes (SqlDataType, DataType) values ('ntext', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('nvarchar', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('smalldatetime', 'DateTime')
insert into @DataTypes (SqlDataType, DataType) values ('timestamp', 'byte[]')
insert into @DataTypes (SqlDataType, DataType) values ('uniqueidentifier', 'Guid')
insert into @DataTypes (SqlDataType, DataType) values ('varchar', 'string')

declare cols cursor for 
    select COLUMN_NAME, Data_type, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
    from INFORMATION_SCHEMA.COLUMNS
    where table_Name = @TableName and table_schema = @Tableschema
    order by ORDINAL_POSITION

open cols

fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
while @@FETCH_STATUS = 0
begin
    select @NewDataType = DataType from @DataTypes where SqlDataType = @DataType
    
    if @DataType in ('varchar', 'char') and @Nullable = 'NO'
        insert into @Lines select char(9) + '[Required]'
        
    if @DataType in ('varchar', 'char')
        insert into @Lines select char(9) + '[StringLength(' + CAST(@MaxLength as varchar) + ')]'
    
    insert into @Lines select char(9) + 'public ' + @NewDataType + ' ' + @ColumnName + ' { get; set; }'
    
    insert into @Lines select char(9) + ''
    
    fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
end

close cols
deallocate cols
insert into @Lines select '}'

select * FROM @Lines

SET NOCOUNT OFF