Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

If you're a C# programmer, you need to know how to convert a table column into a C# model class using stored procedures. And if you don't, you've come to the right place.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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; }  
	public bool? MaritalStatus { get; set; }  
	public bool? EmpStatus { get; set; }  
}  

And that's it!

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
ms sql ,c# ,database ,stored procedures ,tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}