How to Convert a Table Column Into a C# Model Class

4 Min Read 222 Views

In this blog, I will demonstrate how to convert a table column into a C# model class using stored procedures. This is a very useful tip for any C# programmer.

SQL Code

Create tables and columns as you need, like given below:

CREATE TABLE [dbo].[EmployeeMaster](
 
    [RowId] [bigint] NULL,
 
    [EmpFirstName] [varchar](50) NULL,
 
    [EmpLastName] [varchar](50) NULL,
 
    [PhoneNo] [bigint] NULL,
 
    [City] [bigint] NULL,
 
    [Address] [varchar](500) NULL,
 
    [DateOfBirth] [datetime] NULL,
 
    [Gender] [int] NULL,
 
    [MaritalStatus] [bit] NULL,
 
    [EmpStatus] [bit] NULL
 
) ON [PRIMARY]
 
GO

Usually, you can create a class model with the same name as table columns. Often, you look at the table structure and then create properties. But now, you just have to pass the table name and the types of classes.

Now check out this SQL code:

CREATE PROCEDURE CREATEMODEL
 
(
 
     @TableName SYSNAME ,
 
     @CLASSNAME VARCHAR(500)
)
AS
BEGIN
    DECLARE @Result VARCHAR(MAX)
    SET @Result = @CLASSNAME + @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 '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 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
 
END

After running this procedure, execute the stored procedure with parameters. The result will be loaded as a model class:

exec CREATEMODEL 'EmployeeMaster', 'public class '

Output

public class EmployeeMaster
 
{
 
	public long? RowId { get; set; }
 
	public string EmpFirstName { get; set; }
 
	public string EmpLastName { get; set; }
 
	public long? PhoneNo { get; set; }
 
	public long? City { get; set; }
 
	public string Address { get; set; }
 
	public DateTime? DateOfBirth { get; set; }
 
	public int? Gender { get; set; }
 
	publicbool?  MaritalStatus { get; set; }
 
	publicbool?  EmpStatus { get; set; }
 
}

And that’s it!

Tags: