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

Comments

Unknown said…

Hello there I am so delighted I found your blog, I really found you by mistake, while I was searching on Askjeeve for something else, Anyways I am here now and would just like to say many thanks for a incredible post and a all round entertaining blog (I also love the theme/design), I don't have time to look over it all at the moment but I have book-marked it and also included your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the awesome work. gmail log in

Popular posts from this blog

Migrating Legacy Apps to the New SimpleMembership Provider

Get Asp.Net Profile properties from Sql

JavaScript function to automatically add slashes to date