Generate class from database table

Asked 2011-05-03T17:15:49.300

309

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 |
+----+-------+----------------+

c# sql sql-server t-sql

Edit Report

2 Answers

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


                        
                        
                            answered 2012-11-17T13:02:41.880


            
            
                
                    
                        
                            
                            
                        
                        -4
                        
                            
                            
                        
                        
                    
                    
                        
                        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.

                        
                        
                            answered 2011-05-03T17:19:46.253



        
        
            Your Answer