Tuesday, June 30, 2009

Create class variables and properties from database table

Use the below given query to create variables and properties from your schema.

Just replace the @tableName variable with the table name for which you want to generate the variables and properties.


This makes use of systypes and syscolumns system defined views to get the name of columns from the table and their type. Also add more types as per your requirement.

-----------------------------------------------------------------------------

declare @tableName varchar(50)

set @tableName = 'tbTest'

SELECT 'private ' + (

case systypes.name

when 'varchar' then 'string'

when 'decimal' then 'decimal'

when 'datetime' then 'DateTime'

when 'int' then 'int'

else systypes.name end) + ' _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + ';'

FROM syscolumns inner join systypes on

syscolumns.xtype = systypes.xtype

where id = Object_ID(@tableName)

order by colorder

SELECT 'public ' + (

case systypes.name

when 'varchar' then 'string'

when 'decimal' then 'decimal'

when 'datetime' then 'DateTime'

when 'int' then 'int'

else systypes.name end)+ ' ' + syscolumns.name + char(10) +

'{' + char(10) + 'get { return' +

' _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + '; }'

+ char(10) + 'set { _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + ' = value; }' + char(10) + '}'

FROM syscolumns inner join systypes on

syscolumns.xtype = systypes.xtype

where id = Object_ID(@tableName)

order by colorder


-----------------------------------------------------------------------------