Create Entity Class From SQL Table in SQL Server

I have been working in .NET for around 3+ years, with MSSQL as the Database engine. In the course of development many times I get myself dragged into a situation where I need to create custom Entity class objects for a SQL Table. 

Its not a big deal to create an Entity class from a SQL Table if you are using Entity Framework. But yes it might take few minutes to create an Entity class when without Entity Framework. 

But its not the point I am trying to figure out here, when you got a work to do then you have to but the thing is how can I make it simple and time saving, I know I am a lazy software developer so I always keep in search for some shortcut things to hack the trick. And here is the trick for creating a custom Entity class from a SQL Table.



Query to Create Entity Class From SQL Table


After searching for sometime in Google and trying some ways to figure it out, I finally got it, Here is the query to get it.

I have written a stored procedure that take "Table Name" as an input parameter and gives you a customized Entity Class as out put. What you have to do is just copy the result and Go to visual studio and past it in a blank Class file and name it accordingly.
-- =============================================
-- Author: Tapan Kumar
-- Create date: 06/02/2015
-- Description: Generates a custom Entity Class from the input table
-- EXEC GetEntityClass 'TableName'
-- =============================================
CREATE PROCEDURE GetEntityClass( 
@TableName SYSNAME 
)
AS
BEGIN

    DECLARE @Result VARCHAR(MAX) = 'public class ' + @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


This is what I got in the result window when I execute the above Procedure with a parameter "User Reports".
Query:
EXEC GetEntityClass 'UserReports'
Output:
public class UserReports
{
    public int Id { get; set; }

    public string ReportName { get; set; }

    public int? ReportTypeId { get; set; }

    public bool? IsDeleted { get; set; }

    public int? FkSiteId { get; set; }

    public string GroupBy { get; set; }

    public string PageSize { get; set; }

    public string PageMode { get; set; }

    public int? ReportColumns { get; set; }

    public string FillColumns { get; set; }

    public float? PageHeight { get; set; }

    public float? PageWidth { get; set; }

} 


After seeing this out put I am more than happy.

Credit: I could not stop myself from giving some credit to Mr. Deepak Jena for this awesome helpful query.

 Happy Coding...

7 comments:

  1. This is great - simple and exactly what I needed. Thank you for taking the time to post this!

    ReplyDelete
  2. Thanks Tapan, very handy script.

    Do you know about a script that also adds functions for adding and updating ?

    ReplyDelete
    Replies
    1. Not really, Never tried before, but yes that can be done..

      Delete