Generating C Sharp Classes from Tables in SQL Server

Hello to everyone,

In this article, I will share information about creating C Sharp classes from Tables in SQL Server.

Using this kind of stuff in the programming world can get you up to speed.

Some may not need such codes. He may think he’ll code quickly without SQL Server.

You can easily create the C Sharp class of the table you want using the code below.

DECLARE @TableName sysname = 'Products';
DECLARE @Result VARCHAR(MAX) = 'public class ' + @TableName + '
{';
SELECT @Result = @Result + '
    public '     + (CASE
                        WHEN ColumnName = 'RowVersion' THEN
                            'byte[]'
                        ELSE
                            ColumnType
                    END
                   ) + 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
                   '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
                   'timestamp'
               WHEN 'rowversion' THEN
                   'byte[]'
               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;

I made my example using the Categories table on the Northwind database, you can try it in your own database.

When you run the above code block, you will see the following result.

As you can see, he created the Categories table as a class. You can see it below.

public class Products
{
    public int ProductID { get; set; }

    public string ProductName { get; set; }

    public int? SupplierID { get; set; }

    public int? CategoryID { get; set; }

    public string QuantityPerUnit { get; set; }

    public decimal? UnitPrice { get; set; }

    public short? UnitsInStock { get; set; }

    public short? UnitsOnOrder { get; set; }

    public short? ReorderLevel { get; set; }

    public bool Discontinued { get; set; }

}

Good luck to everyone in business and life.