Script to Create MetadataType classes

UPDATE: Here's the same thing for EF 4.1 and Code First.

I’ve been using the DataAnnotations for a validation with MVC2 and Silverlight 4. They both rely on metadata (buddy) classes. The Entity Framework now will create metaclasses for you but LinqToSql didn’t get this feature.

This short script will create a partial class and a metaclass for the specified table.

 

declare @TableName varchar(256) = 'Agencies'
declare @EntityName varchar(256) = 'Agency'
declare @TableSchema varchar(256) = 'dbo'

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

insert into @Lines select 'partial class ' + @EntityName
insert into @Lines select '{'
insert into @Lines select '}'
insert into @Lines select 'public class ' + @EntityName + 'MetaClass'
insert into @Lines select '{'

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
    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 object ' + @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