convert your sql table in a .net class

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

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'
```
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 + '{'
```
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; }'
```

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

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,

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

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.

Convert your SQL Table in a Class

Table of contents

How it work

Create the script

Script

Conclusions