Generate class from database table

Question

How can I generate a class from a SQL Server table object   I m not talking about using some ORM  I just need to create the entities  simple class   Something like       public class Person                public string Name   get set            public string Phone   get set            Given some table like    ---- ------- ----------------    ID   Name        Phone         ---- ------- ----------------     1   Alice    555  555-5550      2   Bob      555  555-5551      3   Cathy    555  555-5552    ---- ------- ----------------

User · Answer

If you have access to SQL Server 2016  you can use the FOR JSON  with INCLUDE NULL VALUES  option to get JSON output from a select statement  Copy the output  then in Visual Studio  paste special -  paste JSON as class   Kind of a budget solution  but might save some time

User · Answer

I packaged ideas from several SQL based answers here  mainly the root answer by Alex Aza  into klassify  a console application that generates all the classes for a specified database at once     For example  given a table Users that looks like this    ---- ------------------ ----------- ---------------------    Id         Name         Username           Email           ---- ------------------ ----------- ---------------------     1   Leanne Graham      Bret        Sincere april biz        2   Ervin Howell       Antonette   Shanna melissa tv        3   Clementine Bauch   Samantha    Nathan yesenia net     ---- ------------------ ----------- ---------------------    klassify will generate a file called Users cs that looks like this       public class User                public int Id  get  set            public string Name   get set            public string Username   get  set            public string Email   get  set            It will output one file for every table   Discard what you don t use   Usage   --out  -o          output directory      lt  lt  defaults to the current directory  gt  gt   --user  -u          sql server user id    lt  lt  required  gt  gt   --password  -p          sql server password   lt  lt  required  gt  gt   --server  -s          sql server            lt  lt  defaults to localhost  gt  gt   --database  -d          sql database          lt  lt  required  gt  gt   --timeout  -t          connection timeout    lt  lt  defaults to 30  gt  gt   --help  -h          show help

User · Answer

In appreciation to Alex s solution and Guilherme for asking I made this for MySQL to generate C  classes  set  schema     schema name   set  table     table name   SET group concat max len   2048  SELECT      concat  public class     table    n  n   GROUP CONCAT a property  SEPARATOR   n      n    class  FROM       select         CONCAT            tpublic            case              when DATA TYPE    bigint  then  long              when DATA TYPE    BINARY  then  byte                when DATA TYPE    bit  then  bool              when DATA TYPE    char  then  string              when DATA TYPE    date  then  DateTime              when DATA TYPE    datetime  then  DateTime              when DATA TYPE    datetime2  then  DateTime              when DATA TYPE    datetimeoffset  then  DateTimeOffset              when DATA TYPE    decimal  then  decimal              when DATA TYPE    double  then  double              when DATA TYPE    float  then  float              when DATA TYPE    image  then  byte                when DATA TYPE    int  then  int              when DATA TYPE    money  then  decimal              when DATA TYPE    nchar  then  char              when DATA TYPE    ntext  then  string              when DATA TYPE    numeric  then  decimal              when DATA TYPE    nvarchar  then  string              when DATA TYPE    real  then  double              when DATA TYPE    smalldatetime  then  DateTime              when DATA TYPE    smallint  then  short              when DATA TYPE    smallmoney  then  decimal              when DATA TYPE    text  then  string              when DATA TYPE    time  then  TimeSpan              when DATA TYPE    timestamp  then  DateTime              when DATA TYPE    tinyint  then  byte              when DATA TYPE    uniqueidentifier  then  Guid              when DATA TYPE    varbinary  then  byte                when DATA TYPE    varchar  then  string              else   UNKNOWN           end                COLUMN NAME     get  set     as property      FROM INFORMATION SCHEMA COLUMNS     WHERE table name    table AND table schema    schema  a      Thanks Alex and Guilherme

User · Answer

I like to setup my classes with private local members and public accessors   mutators  So I ve modified Alex s script above to do that as well for anyone that is intersted   declare  TableName sysname    TABLE NAME  declare  result varchar max     public class      TableName         SET  result    result          public      TableName                  select  result    result         private     ColumnType           m     stuff replace ColumnName            1  1  lower left ColumnName  1          from       select          replace col name            ColumnName          column id          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     where object id   object id  TableName    t order by column id  SET  result    result        select  result    result         public     ColumnType         ColumnName       get   return m     stuff replace ColumnName            1  1  lower left ColumnName  1          set  m     stuff replace ColumnName            1  1  lower left ColumnName  1          value      from       select          replace col name            ColumnName          column id          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     where object id   object id  TableName    t order by column id  set  result    result          print  result

User · Answer

Java class Generation declare  TableName varchar max     Restaurants  declare  Templete varchar max            public  ColumnType  ColumnName       ColumnDesc        public  ColumnType get ColumnName                  return this  ColumnName              public void set ColumnName  ColumnType  ColumnName                 this  ColumnName  ColumnName                 declare  before varchar max   public class   TableName          declare  after varchar max          declare  result varchar max   set  before  replace  before   TableName   TableName   set  result  before  select  result    result    replace replace replace replace replace  Templete   ColumnType  ColumnType     ColumnName  ColumnName     ColumnDesc  ColumnDesc    ISPK  ISPK    max length  max length   from         select      column id      replace col name            ColumnName      typ name as sqltype      typ max length      is identity      pkk ISPK           case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  String              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  String              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  String              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         END   CASE WHEN col is nullable 1 AND typ name NOT IN   binary    varbinary    image    text    ntext    varchar    nvarchar    char    nchar   THEN     ELSE    END ColumnType        isnull colDesc colDesc     AS ColumnDesc      from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id             left join                               SELECT c name  AS  ColumnName   CASE WHEN dd pk IS NULL THEN  false  ELSE  true  END ISPK                            FROM        sys columns c                     JOIN    sys tables  t   ON c object id   t object id                         LEFT JOIN  SELECT   K COLUMN NAME   C CONSTRAINT TYPE as pk                           FROM INFORMATION SCHEMA KEY COLUMN USAGE AS K                              LEFT JOIN INFORMATION SCHEMA TABLE CONSTRAINTS AS C                         ON K TABLE NAME   C TABLE NAME                             AND K CONSTRAINT NAME   C CONSTRAINT NAME                             AND K CONSTRAINT CATALOG   C CONSTRAINT CATALOG                             AND K CONSTRAINT SCHEMA   C CONSTRAINT SCHEMA                                     WHERE K TABLE NAME    TableName  as dd                      ON dd COLUMN NAME   c name                  WHERE       t name    TableName                      pkk  on ColumnName col name      OUTER APPLY       SELECT TOP 1 CAST value AS NVARCHAR max   AS colDesc     FROM        sys extended properties     WHERE        major id   col object id        AND        minor id   COLUMNPROPERTY major id  col name   ColumnId         colDesc           where object id   object id  TableName         t      set  result  result  after      select  result     --print  result

User · Answer

To print out NULLABLE properties WITH COMMENTS  Summary   use this  It is a slight modification of first answer  declare  TableName sysname    TableName  declare  result varchar max     public class      TableName        select  result    result    CASE WHEN ColumnDesc IS NOT NULL THEN            lt summary gt              ColumnDesc              lt  summary gt   ELSE    END         public     ColumnType         ColumnName       get  set     from       select          replace col name            ColumnName          column id          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  String              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  String              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  String              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         END   CASE WHEN col is nullable 1 AND typ name NOT IN   binary    varbinary    image    text    ntext    varchar    nvarchar    char    nchar   THEN     ELSE    END ColumnType          colDesc colDesc AS ColumnDesc     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     OUTER APPLY       SELECT TOP 1 CAST value AS NVARCHAR max   AS colDesc     FROM        sys extended properties     WHERE        major id   col object id        AND        minor id   COLUMNPROPERTY major id  col name   ColumnId         colDesc                 where object id   object id  TableName    t order by column id  set  result    result          print  result

User · Answer

I m confused as to what you want out of this  but here are the general options when designing what you want to design    Using the built-in ORM in your version Visual Studio  Write one yourself  similar to your code example   As usual  a tutorial is your best friend if you re not sure how  Use an alternative ORM such as NHibernate

User · Answer

The simplest way is EF  Reverse Engineer  http   msdn microsoft com en-US data jj593170

User · Answer

You just did  as long as your table contains two columns and is called something like  tblPeople    You can always write your own SQL wrappers  I actually prefer to do it that way  I HATE generated code  in any fashion   Maybe create a DAL class  and have a method called GetPerson int id   that queries the database for that person  and then creates your Person object from the result set

User · Answer

Commercial  but CodeSmith Generator does that  http   www codesmithtools com product generator

User · Answer

Visual Studio Magazine published this   Generating  NET POCO Classes for SQL Query Results  It has a downloadable project that you can build  give it your SQL info  and it will crank out the class for you   Now if that tool just created the SQL Commands for SELECT  INSERT  and UPDATE

User · Answer

Set  TableName to the name of your table   declare  TableName sysname    TableName  declare  Result varchar max     public class      TableName         select  Result    Result         public     ColumnType   NullableSign         ColumnName       get  set      from       select          replace col name            ColumnName          column id ColumnId          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  double              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  string              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  float              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  long              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType          case              when col is nullable   1 and typ name in   bigint    bit    date    datetime    datetime2    datetimeoffset    decimal    float    int    money    numeric    real    smalldatetime    smallint    smallmoney    time    tinyint    uniqueidentifier                then                  else             end NullableSign     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     where object id   object id  TableName    t order by ColumnId  set  Result    Result          print  Result

User · Answer

slightly modified from top reply   declare  TableName sysname    HistoricCommand   declare  Result varchar max      System Data Linq Mapping Table Name         TableName        public class Dbo     TableName         select  Result    Result          System Data Linq Mapping Column Name        t ColumnName       IsPrimaryKey       pkk ISPK           public     ColumnType   NullableSign         t ColumnName       get  set      from       select          replace col name            ColumnName          column id ColumnId          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  string              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType          case              when col is nullable   1 and typ name in   bigint    bit    date    datetime    datetime2    datetimeoffset    decimal    float    int    money    numeric    real    smalldatetime    smallint    smallmoney    time    tinyint    uniqueidentifier                then                  else             end NullableSign     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id              where object id   object id  TableName     t                     SELECT c name  AS  ColumnName   CASE WHEN dd pk IS NULL THEN  false  ELSE  true  END ISPK                            FROM        sys columns c                     JOIN    sys tables  t   ON c object id   t object id                         LEFT JOIN  SELECT   K COLUMN NAME   C CONSTRAINT TYPE as pk                           FROM INFORMATION SCHEMA KEY COLUMN USAGE AS K                              LEFT JOIN INFORMATION SCHEMA TABLE CONSTRAINTS AS C                         ON K TABLE NAME   C TABLE NAME                             AND K CONSTRAINT NAME   C CONSTRAINT NAME                             AND K CONSTRAINT CATALOG   C CONSTRAINT CATALOG                             AND K CONSTRAINT SCHEMA   C CONSTRAINT SCHEMA                                     WHERE K TABLE NAME    TableName  as dd                      ON dd COLUMN NAME   c name                  WHERE       t name    TableName                           pkk where pkk ColumnName   t ColumnName order by ColumnId  set  Result    Result          print  Result   which makes output needed for full LINQ in C  declaration    System Data Linq Mapping Table Name    HistoricCommand    public class DboHistoricCommand        System Data Linq Mapping Column Name    HistoricCommandId   IsPrimaryKey   true       public int HistoricCommandId   get  set          System Data Linq Mapping Column Name    PHCloudSoftwareInstanceId   IsPrimaryKey   true       public int PHCloudSoftwareInstanceId   get  set          System Data Linq Mapping Column Name    CommandType   IsPrimaryKey   false       public int CommandType   get  set          System Data Linq Mapping Column Name    InitiatedDateTime   IsPrimaryKey   false       public DateTime InitiatedDateTime   get  set          System Data Linq Mapping Column Name    CompletedDateTime   IsPrimaryKey   false       public DateTime CompletedDateTime   get  set          System Data Linq Mapping Column Name    WasSuccessful   IsPrimaryKey   false       public bool WasSuccessful   get  set          System Data Linq Mapping Column Name    Message   IsPrimaryKey   false       public string Message   get  set          System Data Linq Mapping Column Name    ResponseData   IsPrimaryKey   false       public string ResponseData   get  set          System Data Linq Mapping Column Name    Message orig   IsPrimaryKey   false       public string Message orig   get  set          System Data Linq Mapping Column Name    Message XX   IsPrimaryKey   false       public string Message XX   get  set

User · Answer

create PROCEDURE for create custom code using template  create PROCEDURE  dbo   createCode           TableName sysname           befor varchar max   public class   TableName           templet varchar max          public  ColumnType  ColumnName     get  set         ColumnDesc         after varchar max          AS BEGIN    declare  result varchar max   set  befor  replace  befor   TableName   TableName   set  result  befor  select  result    result    replace replace replace replace replace  templet   ColumnType  ColumnType     ColumnName  ColumnName     ColumnDesc  ColumnDesc    ISPK  ISPK    max length  max length   from         select      column id      replace col name            ColumnName      typ name as sqltype      typ max length      is identity      pkk ISPK           case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  String              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  String              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  String              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         END   CASE WHEN col is nullable 1 AND typ name NOT IN   binary    varbinary    image    text    ntext    varchar    nvarchar    char    nchar   THEN     ELSE    END ColumnType        isnull colDesc colDesc     AS ColumnDesc      from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id             left join                               SELECT c name  AS  ColumnName   CASE WHEN dd pk IS NULL THEN  false  ELSE  true  END ISPK                            FROM        sys columns c                     JOIN    sys tables  t   ON c object id   t object id                         LEFT JOIN  SELECT   K COLUMN NAME   C CONSTRAINT TYPE as pk                           FROM INFORMATION SCHEMA KEY COLUMN USAGE AS K                              LEFT JOIN INFORMATION SCHEMA TABLE CONSTRAINTS AS C                         ON K TABLE NAME   C TABLE NAME                             AND K CONSTRAINT NAME   C CONSTRAINT NAME                             AND K CONSTRAINT CATALOG   C CONSTRAINT CATALOG                             AND K CONSTRAINT SCHEMA   C CONSTRAINT SCHEMA                                     WHERE K TABLE NAME    TableName  as dd                      ON dd COLUMN NAME   c name                  WHERE       t name    TableName                      pkk  on ColumnName col name      OUTER APPLY       SELECT TOP 1 CAST value AS NVARCHAR max   AS colDesc     FROM        sys extended properties     WHERE        major id   col object id        AND        minor id   COLUMNPROPERTY major id  col name   ColumnId         colDesc           where object id   object id  TableName         t      set  result  result  after      select  result     --print  result  END     now create custom code  for example c  class  exec  createCode   TableName  book   templet          public  ColumnType  ColumnName     get  set         ColumnDesc      output is  public class  book           public long ID     get  set                 public String Title     get  set        Book Title       for LINQ    exec  createCode   TableName  book     befor     System Data Linq Mapping Table Name     TableName    public class  TableName          templet            System Data Linq Mapping Column Name     ColumnName   IsPrimaryKey    ISPK        public  ColumnType  ColumnName     get  set         ColumnDesc                 after          output is    System Data Linq Mapping Table Name    book    public class book          System Data Linq Mapping Column Name    ID   IsPrimaryKey   true        public long ID     get  set                  System Data Linq Mapping Column Name    Title   IsPrimaryKey   false        public String Title     get  set        Book Title        for java class  exec  createCode   TableName  book   templet          public  ColumnType  ColumnName       ColumnDesc        public  ColumnType get ColumnName                  return this  ColumnName              public void set ColumnName  ColumnType  ColumnName                 this  ColumnName  ColumnName                   output is  public class  book           public long ID              public long getID                  return this ID              public void setID long ID                 this ID ID                public String Title      Book Title        public String getTitle                  return this Title              public void setTitle String Title                 this Title Title              for android sugarOrm model  exec  createCode   TableName  book     befor     Table name     TableName   public class  TableName         templet            Column name     ColumnName        public  ColumnType  ColumnName      ColumnDesc                after          output is    Table name    book   public class book          Column name    ID        public long ID               Column name    Title        public String Title     Book Title

User · Answer

Just thought I d add my own variation of the top answer for anyone who s interested  The main features are    It will automatically generate classes for all the tables in the entire schema  Just specify the schema name  It will add System Data Linq Mapping attributes to the class and each property  Useful for anyone using Linq to SQL   declare  TableName sysname declare  Result varchar max  declare  schema varchar 20     dbo  DECLARE  Cursor CURSOR  SET  Cursor   CURSOR FAST FORWARD FOR SELECT DISTINCT tablename   rc1 TABLE NAME FROM INFORMATION SCHEMA Tables rc1 where rc1 TABLE SCHEMA    schema  OPEN  Cursor FETCH NEXT FROM  Cursor INTO  TableName  WHILE    FETCH STATUS   0  BEGIN set  Result     Table Name         schema          TableName        public class     Replace  TableName                    select  Result    Result          Column    PriKey         public     ColumnType   NullableSign         ColumnName       get  set      from       select          replace col name            ColumnName          col column id ColumnId          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  double              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  string              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  float              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  long              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType          case              when col is nullable   1 and typ name in   bigint    bit    date    datetime    datetime2    datetimeoffset    decimal    float    int    money    numeric    real    smalldatetime    smallint    smallmoney    time    tinyint    uniqueidentifier                then                  else             end NullableSign          case             when pk CONSTRAINT NAME is not null and ic column id is not null then   IsPrimaryKey   true  IsDbGenerated   true               when pk CONSTRAINT NAME is not null then   IsPrimaryKey   true               when ic column id is not null then   IsDbGenerated   true               else            end PriKey     from sys columns col     join sys types typ on col system type id   typ system type id AND col user type id   typ user type id     left outer join sys identity columns ic on ic column id   col column id and col object id   ic object id     left outer join           SELECT  K TABLE NAME               K COLUMN NAME               K CONSTRAINT NAME         FROM    INFORMATION SCHEMA TABLE CONSTRAINTS AS C                 JOIN INFORMATION SCHEMA KEY COLUMN USAGE AS K ON C TABLE NAME   K TABLE NAME                                                                  AND C CONSTRAINT CATALOG   K CONSTRAINT CATALOG                                                                  AND C CONSTRAINT SCHEMA   K CONSTRAINT SCHEMA                                                                  AND C CONSTRAINT NAME   K CONSTRAINT NAME         where C CONSTRAINT TYPE    PRIMARY KEY        pk on pk COLUMN NAME   col name and pk TABLE NAME    TableName     where col object id   object id  schema          TableName    t order by ColumnId  set  Result    Result            print  Result  FETCH NEXT FROM  Cursor INTO  TableName end  CLOSE  Cursor DEALLOCATE  Cursor GO

User · Answer

I tried to use the suggestions above and in the process improved upon the solutions in this thread   Let us say you use a base class  ObservableObject in this case  that implements the PropertyChanged Event  you would do something like this  I will probably write a blog post one day in my blog  sqljana wordpress com  Please do substitute the values for the first three variables       --These three things have to be substituted  when called from Powershell  they are replaced before execution  DECLARE  Schema VARCHAR MAX    N  amp Schema  DECLARE  TableName VARCHAR MAX    N  amp TableName  DECLARE  Namespace VARCHAR MAX    N  amp Namespace   DECLARE  CRLF VARCHAR 2    CHAR 13    CHAR 10   DECLARE  result VARCHAR max         DECLARE  PrivateProp VARCHAR 100     CRLF                    CHAR 9    CHAR 9     private  lt ColumnType gt    lt ColumnName gt     DECLARE  PublicProp VARCHAR 255     CRLF                    CHAR 9    CHAR 9     public  lt ColumnType gt   lt ColumnName gt        CRLF                   CHAR 9    CHAR 9            CRLF                   CHAR 9    CHAR 9        get   return   lt ColumnName gt          CRLF                   CHAR 9    CHAR 9        set      CRLF                   CHAR 9    CHAR 9               CRLF                   CHAR 9    CHAR 9              lt ColumnName gt    value      CRLF                   CHAR 9    CHAR 9            base RaisePropertyChanged        CRLF                   CHAR 9    CHAR 9               CRLF                   CHAR 9    CHAR 9           CRLF   DECLARE  RPCProc VARCHAR MAX     CRLF                            CHAR 9    CHAR 9     public event PropertyChangedEventHandler PropertyChanged       CRLF                   CHAR 9    CHAR 9     private void RaisePropertyChanged       CRLF                   CHAR 9    CHAR 9             CallerMemberName  string caller             CRLF                   CHAR 9    CHAR 9             CRLF                   CHAR 9    CHAR 9        if  PropertyChanged    null        CRLF                   CHAR 9    CHAR 9               CRLF                   CHAR 9    CHAR 9            PropertyChanged  this  new PropertyChangedEventArgs  caller            CRLF                   CHAR 9    CHAR 9               CRLF                   CHAR 9    CHAR 9          DECLARE  PropChanged VARCHAR 200      CRLF                               CHAR 9    CHAR 9     protected override void AfterPropertyChanged string propertyName       CRLF                   CHAR 9    CHAR 9            CRLF                   CHAR 9    CHAR 9        System Diagnostics Debug WriteLine       TableName     property changed      propertyName        CRLF                   CHAR 9    CHAR 9          SET  result    using System      CRLF    CRLF                    using MyCompany Business      CRLF    CRLF                    namespace      Namespace     CRLF          CRLF                       public class      TableName       ObservableObject     CRLF                              CRLF                        region Instance Properties     CRLF   SELECT  result    result                                     REPLACE                              REPLACE  PrivateProp                                 lt ColumnName gt    ColumnName                              lt ColumnType gt    ColumnType                                                               REPLACE                              REPLACE  PublicProp                                 lt ColumnName gt    ColumnName                              lt ColumnType gt    ColumnType                     FROM       SELECT  c COLUMN NAME   AS ColumnName            CASE c DATA TYPE                WHEN  bigint  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  Int64   ELSE  Int64  END             WHEN  binary  THEN  Byte                WHEN  bit  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Boolean   ELSE  Boolean  END                         WHEN  char  THEN  String              WHEN  date  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                     WHEN  datetime  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                     WHEN  datetime2  THEN                   CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                     WHEN  datetimeoffset  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  DateTimeOffset   ELSE  DateTimeOffset  END                                                 WHEN  decimal  THEN                   CASE C IS NULLABLE                     WHEN  YES  THEN  Decimal   ELSE  Decimal  END                                                 WHEN  float  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Single   ELSE  Single  END                                                 WHEN  image  THEN  Byte                WHEN  int  THEN                   CASE C IS NULLABLE                     WHEN  YES  THEN  Int32   ELSE  Int32  END             WHEN  money  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  Decimal   ELSE  Decimal  END                                                             WHEN  nchar  THEN  String              WHEN  ntext  THEN  String              WHEN  numeric  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  Decimal   ELSE  Decimal  END                                                                         WHEN  nvarchar  THEN  String              WHEN  real  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Double   ELSE  Double  END                                                                                     WHEN  smalldatetime  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                                 WHEN  smallint  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Int16   ELSE  Int16 END                         WHEN  smallmoney  THEN                   CASE C IS NULLABLE                     WHEN  YES  THEN  Decimal   ELSE  Decimal  END                                                                                     WHEN  text  THEN  String              WHEN  time  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  TimeSpan   ELSE  TimeSpan  END                                                                                                 WHEN  timestamp  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                                 WHEN  tinyint  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Byte   ELSE  Byte  END                                                             WHEN  uniqueidentifier  THEN  Guid              WHEN  varbinary  THEN  Byte                WHEN  varchar  THEN  String              ELSE  Object          END AS ColumnType           c ORDINAL POSITION  FROM    INFORMATION SCHEMA COLUMNS c WHERE   c TABLE NAME    TableName      AND ISNULL  Schema  c TABLE SCHEMA    c TABLE SCHEMA     t ORDER BY t ORDINAL POSITION  SELECT  result    result    CRLF                    CHAR 9      endregion Instance Properties     CRLF                   --CHAR 9     RPCProc    CRLF                   CHAR 9     PropChanged    CRLF                   CHAR 9           CRLF                    CRLF        --SELECT  result PRINT  result   The base class is based on Josh Smith s article here From http   joshsmithonwpf wordpress com 2007 08 29 a-base-class-which-implements-inotifypropertychanged   I did rename the class to be called ObservableObject and also took advantage of a c  5 feature using the CallerMemberName attribute    From http   joshsmithonwpf wordpress com 2007 08 29 a-base-class-which-implements-inotifypropertychanged       Jana s change  Used c  5 feature to bypass passing in the property name using  CallerMemberName       protected void RaisePropertyChanged  CallerMemberName  string propertyName        using System  using System Collections Generic  using System ComponentModel  using System Diagnostics  using System Reflection  using System Runtime CompilerServices   namespace MyCompany Business             lt summary gt          Implements the INotifyPropertyChanged interface and          exposes a RaisePropertyChanged method for derived          classes to raise the PropertyChange event   The event          arguments created by this class are cached to prevent          managed heap fragmentation           lt  summary gt       Serializable      public abstract class ObservableObject   INotifyPropertyChanged                region Data          private static readonly Dictionary lt string  PropertyChangedEventArgs gt  eventArgCache          private const string ERROR MSG     0  is not a public property of  1              endregion    Data           region Constructors          static ObservableObject                         eventArgCache   new Dictionary lt string  PropertyChangedEventArgs gt                        protected ObservableObject                                 endregion    Constructors           region Public Members               lt summary gt              Raised when a public property of this object is set               lt  summary gt           field  NonSerialized          public event PropertyChangedEventHandler PropertyChanged                lt summary gt              Returns an instance of PropertyChangedEventArgs for              the specified property name               lt  summary gt               lt param name  propertyName  gt              The name of the property to create event args for               lt  param gt                  public static PropertyChangedEventArgs             GetPropertyChangedEventArgs string propertyName                        if  String IsNullOrEmpty propertyName                   throw new ArgumentException                       propertyName cannot be null or empty                  PropertyChangedEventArgs args                  Get the event args from the cache  creating them                and adding to the cache if necessary              lock  typeof ObservableObject                                 bool isCached   eventArgCache ContainsKey propertyName                   if   isCached                                        eventArgCache Add                          propertyName                          new PropertyChangedEventArgs propertyName                                       args   eventArgCache propertyName                              return args                      endregion    Public Members           region Protected Members               lt summary gt              Derived classes can override this method to             execute logic after a property is set  The              base implementation does nothing               lt  summary gt               lt param name  propertyName  gt              The property which was changed               lt  param gt          protected virtual void AfterPropertyChanged string propertyName                                    lt summary gt              Attempts to raise the PropertyChanged event  and              invokes the virtual AfterPropertyChanged method               regardless of whether the event was raised or not               lt  summary gt               lt param name  propertyName  gt              The property which was changed               lt  param gt          protected void RaisePropertyChanged  CallerMemberName  string propertyName                             this VerifyProperty propertyName                PropertyChangedEventHandler handler   this PropertyChanged              if  handler    null                                   Get the cached event args                  PropertyChangedEventArgs args                       GetPropertyChangedEventArgs propertyName                       Raise the PropertyChanged event                  handler this  args                              this AfterPropertyChanged propertyName                       endregion    Protected Members           region Private Helpers           Conditional  DEBUG            private void VerifyProperty string propertyName                        Type type   this GetType                    Look for a public property with the specified name              PropertyInfo propInfo   type GetProperty propertyName                if  propInfo    null                                   The property could not be found                     so alert the developer of the problem                   string msg   string Format                      ERROR MSG                      propertyName                      type FullName                    Debug Fail msg                                     endregion    Private Helpers           Here is the part that you guys are going to like some more  I built a Powershell script to generate for all the tables in a SQL database  It is based on a Powershell guru named Chad Miller s Invoke-SQLCmd2 cmdlet which can be downloaded from here  http   gallery technet microsoft com ScriptCenter 7985b7ef-ed89-4dfd-b02a-433cc4e30894   Once you have that cmdlet  the Powershell script to generate for all tables becomes simple  do substitute the variables with your specific values      C  MyScripts Invoke-Sqlcmd2 ps1   serverInstance    MySQLInstance   databaseName    MyDb   generatorSQLFile    C  MyScripts ModelGen sql    tableListSQL    SELECT name FROM  databaseName sys tables   outputFolder    C  MyScripts Output    namespace    MyCompany Business    placeHolderSchema     amp Schema   placeHolderTableName     amp TableName   placeHolderNamespace     amp Namespace    Get the list of tables in the database to generate c  models for  tables   Invoke-Sqlcmd2 -ServerInstance  serverInstance -Database  databaseName -Query  tableListSQL -As DataRow -Verbose  foreach   table in  tables         table1    table 0       outputFile     outputFolder  table1 cs         Replace variables with values  returns an array that we convert to a string to use as query       generatorSQLFileWSubstitutions    Get-Content  generatorSQLFile                                               Replace  placeHolderSchema  dbo                                                Replace  placeHolderTableName   table1                                               Replace  placeHolderNamespace   namespace    Out-String       Ouputing for  table1 to  outputFile        The command generates  cs file content for model using  PRINT  statements which then gets written to verbose output  stream 4           capture the verbose output and redirect to a file      Invoke-Sqlcmd2 -ServerInstance  serverInstance -Database  databaseName -Query  generatorSQLFileWSubstitutions -Verbose  4 gt   outputFile

User · Answer

Grab QueryFirst  visual studio extension that generates wrapper classes from SQL queries  You not only get     public class MyClass      public string MyProp get set       public int MyNumberProp get set               And as a bonus  it ll throw in     public class MyQuery      public static IEnumerable lt MyClass gt Execute         public static MyClass GetOne                 Are you sure you want to base your classes directly on your tables  Tables are a static  normalized data storage notion that belongs in the DB  Classes are dynamic  fluid  disposable  context-specific  perhaps denormalized  Why not write real queries for the data you want for an operation  and let QueryFirst generate the classes from that

User · Answer

In case it s useful to anyone else  working on a Code-First approach using attribute mappings  I wanted something that just left me needing to bind an entity in the object model   So thanks to Carnotaurus  answer  I extended it as per their own suggestion and made a couple of tweaks   This relies therefore on this solution comprising TWO parts  both of which are SQL Scalar-Valued functions         An  Initial Caps  function  taken from    https   social msdn microsoft com Forums sqlserver en-US 8a58dbe1-7a4b-4287-afdc-bfecb4e69b23 similar-to-initcap-in-sql-server-tsql   and slightly modified to meet my needs       ALTER function  dbo   ProperCase    cStringToProper varchar 8000   returns varchar 8000  as begin    declare   Position int     select  cStringToProper   stuff lower  cStringToProper    1   1   upper left  cStringToProper   1               Position   patindex     a-zA-Z  a-z       cStringToProper collate Latin1 General Bin      while  Position  gt  0          select  cStringToProper   stuff  cStringToProper    Position   2   upper substring  cStringToProper    Position   2                     Position   patindex     a-zA-Z  a-z       cStringToProper collate Latin1 General Bin     select  cStringToProper   replace  cStringToProper              return  cStringToProper end         The output function itself  which extends Carnotaurus  solution by          Correctly outputting newline characters   Performing some basic tabulation   Writing out an appropriate a  Table  mapping  as suggested    Writing out an appropriate  Column  mapping  including the type name  as suggested    Allowing the Entity name to differ from the name of the table   Fixes the limitation of the Print  Result truncating when you have tables with a large number of columns         CREATE FUNCTION  dbo   GetEntityObject    NameSpace NVARCHAR MAX    TableName NVARCHAR MAX    EntityName NVARCHAR MAX    RETURNS NVARCHAR MAX  AS BEGIN  DECLARE  result NVARCHAR MAX   SET  result    result    using System     CHAR 13    CHAR 13    IF   NameSpace IS NOT NULL   BEGIN     SET  result    result    namespace      NameSpace    CHAR 13          CHAR 13   END  SET  result    result     Table name      CHAR 34     TableName   CHAR 34           CHAR 13  SET  result    result    public class      EntityName   CHAR 13          CHAR 13    SET  result    result     region Instance Properties    CHAR 13     SELECT  result    result   CHAR 13          Column name      CHAR 34    OriginalColumnName   CHAR 34       TypeName       CHAR 34    DataType   CHAR 34           CHAR 13         public     ColumnType         ColumnName       get  set        CHAR 13   FROM       SELECT dbo ProperCase  c COLUMN NAME    AS ColumnName            CASE c DATA TYPE                WHEN  bigint  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  Int64   ELSE  Int64  END             WHEN  binary  THEN  Byte                WHEN  bit  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Boolean   ELSE  Boolean  END                         WHEN  char  THEN  String              WHEN  date  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                     WHEN  datetime  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                     WHEN  datetime2  THEN                   CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                     WHEN  datetimeoffset  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  DateTimeOffset   ELSE  DateTimeOffset  END                                                 WHEN  decimal  THEN                   CASE C IS NULLABLE                     WHEN  YES  THEN  Decimal   ELSE  Decimal  END                                                 WHEN  float  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Single   ELSE  Single  END                                                 WHEN  image  THEN  Byte                WHEN  int  THEN                   CASE C IS NULLABLE                     WHEN  YES  THEN  Int32   ELSE  Int32  END             WHEN  money  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  Decimal   ELSE  Decimal  END                                                             WHEN  nchar  THEN  String              WHEN  ntext  THEN  String              WHEN  numeric  THEN                 CASE C IS NULLABLE                     WHEN  YES  THEN  Decimal   ELSE  Decimal  END                                                                         WHEN  nvarchar  THEN  String              WHEN  real  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Double   ELSE  Double  END                                                                                     WHEN  smalldatetime  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                                 WHEN  smallint  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Int16   ELSE  Int16 END                         WHEN  smallmoney  THEN                   CASE C IS NULLABLE                     WHEN  YES  THEN  Decimal   ELSE  Decimal  END                                                                                     WHEN  text  THEN  String              WHEN  time  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  TimeSpan   ELSE  TimeSpan  END                                                      WHEN  timestamp  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  DateTime   ELSE  DateTime  END                                                 WHEN  tinyint  THEN                  CASE C IS NULLABLE                     WHEN  YES  THEN  Byte   ELSE  Byte  END                                                             WHEN  uniqueidentifier  THEN  Guid              WHEN  varbinary  THEN  Byte                WHEN  varchar  THEN  String              ELSE  Object          END AS ColumnType           c ORDINAL POSITION          c COLUMN NAME as OriginalColumnName        c DATA TYPE as DataType  FROM    INFORMATION SCHEMA COLUMNS c WHERE   c TABLE NAME    TableName  t ORDER BY t ORDINAL POSITION  SET  result    result   CHAR 13      endregion Instance Properties    CHAR 13     SET  result    result          CHAR 13   IF   TableName IS NOT NULL   BEGIN     SET  result    result   CHAR 13         END  return  result END   Usage from within MS SQL Management Studio      SELECT dbo GetEntityObject  MyNameSpace    MyTableName    MyEntityName     will result in a column value you can copy and paste into Visual Studio   If it helps anyone  then great

User · Answer

Yea these are great if your using a simple ORM like Dapper   If your using  Net you can generate an XSD file at run time with any DataSet using the WriteXmlSchema method  http   msdn microsoft com en-us library xt7k72x8 v vs 110  aspx  Like this   using  SqlConnection cnn   new SqlConnection mConnStr     DataSet Data   new DataSet    cnn Open    string sql    SELECT   FROM Person    using  SqlDataAdapter Da   new SqlDataAdapter sql  cnn     try       Da Fill Data       Da TableMappings Add  Table    Person        Data WriteXmlSchema   C  Person xsd      catch  Exception ex    MessageBox Show ex Message       cnn Close      From there you can use xsd exe to create a class that s XML serializable from the Developer Command Prompt   http   msdn microsoft com en-us library x6c1kb0s v vs 110  aspx  like this   xsd C  Person xsd  classes  language CS

User · Answer

VB version  declare  TableName sysname    myTableName  declare  prop varchar max  PRINT  Public Class      TableName declare props cursor for select distinct   public property     ColumnName     AS     ColumnType AS prop from        select           replace col name            ColumnName   column id           case typ name               when  bigint  then  long               when  binary  then  byte                 when  bit  then  boolean               when  char  then  string               when  date  then  DateTime               when  datetime  then  DateTime               when  datetime2  then  DateTime               when  datetimeoffset  then  DateTimeOffset               when  decimal  then  decimal               when  float  then  float               when  image  then  byte                 when  int  then  integer               when  money  then  decimal               when  nchar  then  char               when  ntext  then  string               when  numeric  then  decimal               when  nvarchar  then  string               when  real  then  double               when  smalldatetime  then  DateTime               when  smallint  then  short               when  smallmoney  then  decimal               when  text  then  string               when  time  then  TimeSpan               when  timestamp  then  DateTime               when  tinyint  then  byte               when  uniqueidentifier  then  Guid               when  varbinary  then  byte                 when  varchar  then  string           end ColumnType      from sys columns col join sys types typ on col system type id   typ system type id      where object id   object id  TableName     t  order by prop open props FETCH NEXT FROM props INTO  prop WHILE   FETCH STATUS   0 BEGIN     print  prop     FETCH NEXT FROM props INTO  prop END close props DEALLOCATE props PRINT  End Class

User · Answer

I m trying to give my 2 cents  0  QueryFirst https   marketplace visualstudio com items itemName bbsimonbb QueryFirst  Query-first is a visual studio extension for working intelligently with SQL in C  projects  Use the provided  sql template to develop your queries  When you save the file  Query-first runs your query  retrieves the schema and generates two classes and an interface  a wrapper class with methods Execute    ExecuteScalar    ExecuteNonQuery   etc  its corresponding interface  and a POCO encapsulating a line of results   1  Sql2Objects  Creates the class starting from the result of a query  but not the DAL    2  https   docs microsoft com en-us ef ef6 resources tools   3  https   visualstudiomagazine com articles 2012 12 11 sqlqueryresults-code-generation aspx   4  http   www codesmithtools com product generator features

User · Answer

A small addition to the solutions before  object id  TableName  works only if you are in the default schema    Select id from sysobjects where name    TableName    works in any schema provided  tableName is unique

User · Answer

To print out NULLABLE properties  use this  It adds a slight modification to Alex Aza s script for the CASE statement block   declare  TableName sysname    TableName  declare  result varchar max     public class      TableName         select  result    result         public     ColumnType         ColumnName       get  set      from       select          replace col name            ColumnName          column id          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end            CASE             WHEN col is nullable 1 AND                  typ name NOT IN                         binary    varbinary    image                         text    ntext                         varchar    nvarchar    char    nchar               THEN                 ELSE    END AS  ColumnType      from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id      where object id   object id  TableName    t order by column id  set  result    result          print  result

User · Answer

I could not get Alex s answer to work on Sql Server 2008 R2  So  I rewrote it using the same basic principles  It now allows for schemas and several fixes have been made for column-property mappings  including mapping nullable date types to nullable C  value types   Here is the Sql      DECLARE  TableName VARCHAR MAX     NewsItem  -- Replace  NewsItem  with your table name     DECLARE  TableSchema VARCHAR MAX     Markets  -- Replace  Markets  with your schema name     DECLARE  result varchar max            SET  result    result    using System     CHAR 13    CHAR 13        IF   TableSchema IS NOT NULL       BEGIN         SET  result    result    namespace      TableSchema    CHAR 13          CHAR 13       END      SET  result    result    public class      TableName   CHAR 13          CHAR 13        SET  result    result     region Instance Properties    CHAR 13        SELECT        result    result   CHAR 13            public     ColumnType         ColumnName       get  set        CHAR 13      FROM  SELECT       c COLUMN NAME AS ColumnName        CASE c DATA TYPE         WHEN  bigint  THEN CASE C IS NULLABLE             WHEN  YES  THEN  Int64               ELSE  Int64            END         WHEN  binary  THEN  Byte            WHEN  bit  THEN CASE C IS NULLABLE             WHEN  YES  THEN  bool               ELSE  bool            END         WHEN  char  THEN  string          WHEN  date  THEN CASE C IS NULLABLE             WHEN  YES  THEN  DateTime               ELSE  DateTime            END         WHEN  datetime  THEN CASE C IS NULLABLE             WHEN  YES  THEN  DateTime               ELSE  DateTime            END         WHEN  datetime2  THEN CASE C IS NULLABLE             WHEN  YES  THEN  DateTime               ELSE  DateTime            END         WHEN  datetimeoffset  THEN CASE C IS NULLABLE             WHEN  YES  THEN  DateTimeOffset               ELSE  DateTimeOffset            END         WHEN  decimal  THEN CASE C IS NULLABLE             WHEN  YES  THEN  decimal               ELSE  decimal            END         WHEN  float  THEN CASE C IS NULLABLE             WHEN  YES  THEN  Single               ELSE  Single            END         WHEN  image  THEN  Byte            WHEN  int  THEN CASE C IS NULLABLE             WHEN  YES  THEN  int               ELSE  int            END         WHEN  money  THEN CASE C IS NULLABLE             WHEN  YES  THEN  decimal               ELSE  decimal            END         WHEN  nchar  THEN  string          WHEN  ntext  THEN  string          WHEN  numeric  THEN CASE C IS NULLABLE             WHEN  YES  THEN  decimal               ELSE  decimal            END         WHEN  nvarchar  THEN  string          WHEN  real  THEN CASE C IS NULLABLE             WHEN  YES  THEN  Double               ELSE  Double            END         WHEN  smalldatetime  THEN CASE C IS NULLABLE             WHEN  YES  THEN  DateTime               ELSE  DateTime            END         WHEN  smallint  THEN CASE C IS NULLABLE             WHEN  YES  THEN  Int16               ELSE  Int16            END         WHEN  smallmoney  THEN CASE C IS NULLABLE             WHEN  YES  THEN  decimal               ELSE  decimal            END         WHEN  text  THEN  string          WHEN  time  THEN CASE C IS NULLABLE             WHEN  YES  THEN  TimeSpan               ELSE  TimeSpan            END         WHEN  timestamp  THEN  Byte            WHEN  tinyint  THEN CASE C IS NULLABLE             WHEN  YES  THEN  Byte               ELSE  Byte            END         WHEN  uniqueidentifier  THEN CASE C IS NULLABLE             WHEN  YES  THEN  Guid               ELSE  Guid            END         WHEN  varbinary  THEN  Byte            WHEN  varchar  THEN  string          ELSE  Object        END AS ColumnType        c ORDINAL POSITION     FROM INFORMATION SCHEMA COLUMNS c     WHERE c TABLE NAME    TableName     AND ISNULL  TableSchema  c TABLE SCHEMA    c TABLE SCHEMA  t     ORDER BY t ORDINAL POSITION      SET  result    result   CHAR 13      endregion Instance Properties    CHAR 13         SET  result    result          CHAR 13       IF   TableSchema IS NOT NULL       BEGIN         SET  result    result   CHAR 13             END      PRINT  result   It produces C  like the following   using System   namespace Markets       public class NewsItem                   region Instance Properties          public Int32 NewsItemID   get  set             public Int32  TextID   get  set             public String Description   get  set              endregion Instance Properties            It may be an idea to use EF  Linq to Sql  or even Scaffolding  however  there are times when a piece of coding like this comes in handy  Frankly  I do not like using EF navigation properties where the code it generates made 19 200 separate database calls to populate a 1000 row grid  This could have been achieved in a single database call  Nonetheless  it could just be that your technical architect does not want you to use EF and the like  So  you have to revert to code like this    Incidentally  it may also be an idea to decorate each of the properties with attributes for DataAnnotations  etc   but I m keeping this strictly POCO   EDIT Fixed for TimeStamp and Guid

User · Answer

A bit late but I ve created a web tool to help create a C   or other  objects from SQL result  SQL Table and SQL SP   sql2object com   This can really safe you having to type all your properties and types   If the types are not recognised the default will be selected

User · Answer

This post has saved me several times  I just want to add my two cents  For those that dont like to use ORMs  and instead write their own DAL classes  when you have like 20 columns in a table  and 40 different tables with their respective CRUD operations  its painful and a waste of time  I repeated the above code  for generating CRUD methods based on the table entity and properties    declare  TableName sysname    Tablename  declare  Result varchar max     public class      TableName         select  Result    Result         public     ColumnType   NullableSign         ColumnName       get  set      from       select          replace col name            ColumnName          column id ColumnId          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType          case              when col is nullable   1 and typ name in   bigint    bit    date    datetime    datetime2    datetimeoffset    decimal    float    int    money    numeric    real    smalldatetime    smallint    smallmoney    time    tinyint    uniqueidentifier                then                  else             end NullableSign     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     where object id   object id  TableName    t order by ColumnId  set  Result    Result          print  Result  declare  InitDataAccess varchar max     public class     TableName   DataAccess       declare  ListStatement varchar max    public List lt    TableName   gt  Get   TableName  List      String conn   ConfigurationManager ConnectionStrings  ConnectionNameInWeb config   ConnectionString   var itemList   new List lt    TableName   gt               try                               using  var sqlCon   new SqlConnection conn                                         sqlCon Open                        var cmd   new SqlCommand                                               Connection   sqlCon                          CommandType   CommandType StoredProcedure                          CommandText    StoredProcedureSelectAll                                             SqlDataReader reader   cmd ExecuteReader                        while  reader Read                                                var item   new    TableName         select  ListStatement    ListStatement     item    ColumnName          ColumnType   NullableSign     reader    ColumnName        from       select          replace col name            ColumnName          column id ColumnId          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType          case              when col is nullable   1 and typ name in   bigint    bit    date    datetime    datetime2    datetimeoffset    decimal    float    int    money    numeric    real    smalldatetime    smallint    smallmoney    time    tinyint    uniqueidentifier                then                  else             end NullableSign     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     where object id   object id  TableName    t order by ColumnId  select  ListStatement    ListStatement                            itemList Add item                                                                      catch  Exception ex                                throw new Exception ex Message                             return itemList              declare  GetIndividual varchar max       public    TableName   Get   TableName        String conn   ConfigurationManager ConnectionStrings  ConnectionNameInWeb config   ConnectionString   var item   new    TableName                try                               using  var sqlCon   new SqlConnection conn                                         sqlCon Open                        var cmd   new SqlCommand                                               Connection   sqlCon                          CommandType   CommandType StoredProcedure                          CommandText    StoredProcedureSelectIndividual                                              cmd Parameters AddWithValue   ItemCriteria   item id                       SqlDataReader reader   cmd ExecuteReader                        if  reader Read                            select  GetIndividual    GetIndividual     item    ColumnName          ColumnType   NullableSign     reader    ColumnName        from       select          replace col name            ColumnName          column id ColumnId          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType          case              when col is nullable   1 and typ name in   bigint    bit    date    datetime    datetime2    datetimeoffset    decimal    float    int    money    numeric    real    smalldatetime    smallint    smallmoney    time    tinyint    uniqueidentifier                then                  else             end NullableSign     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     where object id   object id  TableName    t order by ColumnId  select  GetIndividual    GetIndividual                                                                        catch  Exception ex                                throw new Exception ex Message                             return item                declare  InsertStatement varchar max     public void  Insert   TableName      TableName   item     String conn   ConfigurationManager ConnectionStrings  ConnectionNameInWeb config   ConnectionString             try                               using  var sqlCon   new SqlConnection conn                                         sqlCon Open                        var cmd   new SqlCommand                                               Connection   sqlCon                          CommandType   CommandType StoredProcedure                          CommandText    StoredProcedureInsert                                                 select  InsertStatement    InsertStatement      cmd Parameters AddWithValue     ColumnName     item   ColumnName       from       select          replace col name            ColumnName          column id ColumnId          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType          case              when col is nullable   1 and typ name in   bigint    bit    date    datetime    datetime2    datetimeoffset    decimal    float    int    money    numeric    real    smalldatetime    smallint    smallmoney    time    tinyint    uniqueidentifier                then                  else             end NullableSign     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     where object id   object id  TableName    t order by ColumnId  select  InsertStatement    InsertStatement                         cmd ExecuteNonQuery                                                 catch  Exception ex                                throw new Exception ex Message                              declare  UpdateStatement varchar max     public void  Update   TableName      TableName   item     String conn   ConfigurationManager ConnectionStrings  ConnectionNameInWeb config   ConnectionString             try                               using  var sqlCon   new SqlConnection conn                                         sqlCon Open                        var cmd   new SqlCommand                                               Connection   sqlCon                          CommandType   CommandType StoredProcedure                          CommandText    StoredProcedureUpdate                                             cmd Parameters AddWithValue   UpdateCriteria   item Id                          select  UpdateStatement    UpdateStatement      cmd Parameters AddWithValue     ColumnName     item   ColumnName       from       select          replace col name            ColumnName          column id ColumnId          case typ name              when  bigint  then  long              when  binary  then  byte                when  bit  then  bool              when  char  then  string              when  date  then  DateTime              when  datetime  then  DateTime              when  datetime2  then  DateTime              when  datetimeoffset  then  DateTimeOffset              when  decimal  then  decimal              when  float  then  float              when  image  then  byte                when  int  then  int              when  money  then  decimal              when  nchar  then  char              when  ntext  then  string              when  numeric  then  decimal              when  nvarchar  then  string              when  real  then  double              when  smalldatetime  then  DateTime              when  smallint  then  short              when  smallmoney  then  decimal              when  text  then  string              when  time  then  TimeSpan              when  timestamp  then  DateTime              when  tinyint  then  byte              when  uniqueidentifier  then  Guid              when  varbinary  then  byte                when  varchar  then  string              else  UNKNOWN     typ name         end ColumnType          case              when col is nullable   1 and typ name in   bigint    bit    date    datetime    datetime2    datetimeoffset    decimal    float    int    money    numeric    real    smalldatetime    smallint    smallmoney    time    tinyint    uniqueidentifier                then                  else             end NullableSign     from sys columns col         join sys types typ on             col system type id   typ system type id AND col user type id   typ user type id     where object id   object id  TableName    t order by ColumnId  select  UpdateStatement    UpdateStatement                         cmd ExecuteNonQuery                                                 catch  Exception ex                                throw new Exception ex Message                              declare  EndDataAccess varchar max           print  InitDataAccess  print  GetIndividual print  InsertStatement print  UpdateStatement print  ListStatement print  EndDataAccess   Of course its not bulletproof code  and can be improved  Just wanted to contribute to this excelent solution

[c#] Generate class from database table

Examples related to c#

Examples related to sql

Examples related to sql-server

Examples related to tsql