SQL helper script to generate properties for an Entity Framework model

Posted on October 16, 2013 by Michael Roma

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
comments powered by Disqus