SQL helper script to generate properties for an Entity Framework model

Wed Oct 16 2013

This post shows a T-SQL script that can be used to generate the properties for a class that correspond to to a field in a database table. This can be used with Entity Framework.

T-SQL script to generate the properties:

-- define the table to return back
declare @t nvarchar(100); set @t = 'lookup_site_url'

-- return back the properties
    'public ' + t + ' ' + n + ' { get; set; }'    

from (
    select c.name as n, 

        -- convert t-sql type to .net c# type
        case c.user_type_id
            when 127 then 'long'
            when 167 then 'string'
            when 35 then 'string'
            when 231 then 'string'
            when 241 then 'string'    
            when 56 then 'int'
            when 61 then 'DateTime'
            when 104 then 'bool'
            when 62 then 'double'
            when 60 then 'decimal'
            when 106 then 'decimal'
            when 36 then 'Guid'
            else null
        end as t, user_type_id, max_length     

        sys.columns c, sys.tables t 
        c.object_id = t.object_id and t.name = @t
) d