SQL to Generate C# Properties from SQL Table Columns

2016/07/15

Below SQL will generate C# properties for all columns in a table in SQL server.

Notes:

  1. You will have to fix datatypes that are not interchangeable between SQL and C#. You will also have to polish property names if you follow a different naming convention for column names.
  2. The query below automatically renames some SQL types to corresponding C# types (e.g. tinyint, and int will be output as int). I only added types I came across while using this so feel free to add other types as you come across. And please share your updated queries for others.
  3. The output is ordered (in the outer query) by C# type name length to make the output more pleasant and readable.

select
'public ' + data_type + ' ' + column_name + ' { get; set; }'
from (
   select
      case when c.DATA_TYPE in ('char', 'varchar', 'nvarchar') then 'string'
           when c.DATA_TYPE in ('int', 'tinyint') then 'int'
           when c.DATA_TYPE in ('datetime') then 'DateTime'
           else c.DATA_TYPE 
      end as data_type 
   , c.column_name
   from information_schema.columns c
   where TABLE_CATALOG = 'DATABASE_NAME'
      and TABLE_SCHEMA = 'SCHEMA'
      and TABLE_NAME = 'TABLE_NAME'
) data
order by len(data.data_type)

 

e.g. output


public int CallReasonTypeIdent { get; set; }
public int CallLogTypeIdent { get; set; }
public int ProductIdent { get; set; }
public int FunctionIdent { get; set; }
public int CompanyIdent { get; set; }
public string FunctionName { get; set; }
public string ProductCode { get; set; }
public string ProductName { get; set; }
public string CompanyName { get; set; }
public DateTime CreatedDateTime { get; set; }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: