Monday, June 28, 2010

Quick Script to Create Constants from All Columns Names

One thing I like to do in all my apps is create a static class that holds constants for all Column/Property names. This just avoids messing up names in places where I need to use strings. This is particularly useful when checking that attributes have been applied.

select 
     'public const string ' + COLUMN_NAME + ' = "' + COLUMN_NAME + '";'
from INFORMATION_SCHEMA.COLUMNS
where table_Name in ('Agencies', 'Customers')
group by COLUMN_NAME
order by COLUMN_NAME

The code produced looks like this:

public static class ColumnNames
{
    public const string AgencyName = "AgencyName";
    public const string CustomerName = "CustomerName";
}

I can then refer to the name using something like

VerifyPropertyHasAttribute(ColumnName.AgencyName, typeof(RequiredAttribute));

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