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
