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; }