Generate C# Model Class From Table In MSSQL

In this article, we are going to create a C# Model Class From The Table In MSSQL Database.

We are going to create the Stored Procedure name which will take table name as input and give C# Model Class as output.

Generally, we are creating a model class with the same name as columns in the table, and we are looking at the table structure and then creating property accordingly. but using this we just have to pass the table name and types of class which we want. it will be useful for reducing programmer work for this kind of mapping thing.

following are steps for it.

1. Create Stored Procedure In MSSQL, as below

CREATE PROCEDURE GenerateModelClass
(  
     @TableName SYSNAME,  
     @ClassName VARCHAR(500)   
)  
AS  
BEGIN  
    DECLARE @Result VARCHAR(MAX)  
  
    SET @Result = @ClassName + @TableName + '  
{'  
  
SELECT @Result = @Result + '  
    public ' + ColumnType + NullSign + ' ' + 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 NullSign  
    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  
  
END

above store procedure will take table name and class type as parameters and it will return/print model class.

2. Execute Stored Procedure, as below

exec GenerateModelClass '[dbo].[TblExpense]', 'public class '

this way we can execute the stored procedure, in the parameter, we have passed the table name which class we have to generate, and in the second parameter, we have passed class type so it will append that text before our class.

Below are the outputs.

public class [dbo].[TblExpense]  
{  
    public int ExpenseId { get; set; }  
    public int UserId { get; set; }  
    public int ProjectId { get; set; }  
    public int ExpenseCategoryId { get; set; }  
    public DateTime ExpenseDate { get; set; }  
    public string Description { get; set; }  
    public decimal Amount { get; set; }  
    public int StatusId { get; set; }  
    public int InsertedBy { get; set; }  
    public DateTime InsertedDateTime { get; set; }  
    public int? UpdatedBy { get; set; }  
    public DateTime? UpdatedDateTime { get; set; }  
}

 

Hope you like it and find something useful which reduce our some small amount of time 🙂 Thank You.

Also, check Generate Dynamic XML File In .NET Core