When we work with a database we need to transform our tables into classes, then we need a SQL script to do it.

How it work

• Create a switch where we map every type of SQL in .NET type

• Check if the columns are nullable

• The result is varrchar, which we can copy and paste into our .NET file

Create the script

1. Create a variable that represents our table, the type is a 'sysname' that is used to reference database object names (it's similar to a nvarchar(128) but it's not nullable)

    DECLARE @TableName sysname = 'MyTable'
   

2. Create another variable, 'varchar(max)', the first assign is the header of class. We'll add every column of our table

    DECLARE @Result VARCHAR(MAX) = 'public class ' + @TableName + '{'
   

3. Now, we create the property. In this step, we create a SELECT that defines the property of our class, every property it'll contact with the @Result (it contains the header of the class).
   We set dynamically the type, and name of the property and if it's nullable

    SELECT @Result = 
                @Result + 'public ' + ColumnType + NullableSign + ' ' 
                        + ColumnName + ' { get; set; }'
   

4. In the FROM, a new query returns the type of the column and if it's nullable.

   To do this, we need a create an inner join between two system tables, columns and types.

   Sys.Columns contain information about the column, in our script, we need the name, object_id (the id of the table), user_type_id (id of the type) and is_nullable

   Sys.Types contain information about the type, in this case, we need only the name

    FROM (
        SELECT
            REPLACE(col.name, ' ', '_') ColumnName,
            ColumnType, -- I explain how generate in step 5
            NullableSign -- I explain how generate in step 6
        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 -- It's optional the order
   

5. In the previous step, we see what we do.

   Now we generate the property that represents the column. For every type we create a case, it returns the C# type. If a type doesn't exist in the script, it returns the name of the type (or if you prefer you can return Object type)

    CASE typ.name
                WHEN 'bigint' THEN 'long'
                WHEN 'date' THEN 'DateTime'
                WHEN 'decimal' THEN 'decimal'
                WHEN 'float' THEN 'double'
                WHEN 'image' THEN 'byte[]'
                WHEN 'int' THEN 'int'
                WHEN 'time' THEN 'TimeSpan'
                WHEN 'timestamp' THEN 'long'
                WHEN 'uniqueidentifier' THEN 'Guid'
                WHEN 'varchar' THEN 'string'
                WHEN 'xml' THEN 'string'
                -- Other type you can see in the complete script
                ELSE 'UNKNOWN_' + typ.name
    END ColumnType,
   

6. If the column is nullable, we create the sign of nullable (?)

    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
   

7. In the last step, we close the class with } and print the result

    SET @Result = @Result  + '}'
   
    PRINT @Result
   

Script

DECLARE @TableName SYSNAME = 'myTable'

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'
            WHEN 'xml' 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

Conclusions

This is a simple script that I use to generate a class in c# from an MSSQL table.
If you understand the logic behind this script, you can generate a clone of this script for other SQL or programming languages.