Search in Help for developer site.

Sunday 8 October 2017

How to generate Model Class or Data Object of a Database Table

1. Generate Model Class or Data Object of a Database table


While developing a application, we need to create model classes or data objects and generally those Model classes are the replica of our database tables.
So we create them manually by writing code. 
Today I'm gonna  show you how to auto generate those model classes and use them directly in c#.
you can customize the below code for JAVA classes also.
This article is for generate c# model class from sql server database table.

2. Model Class Generator code.



DECLARE @TableName VARCHAR(MAX) = 'Product' -- Replace 'Product' with your table name

DECLARE @TableSchema VARCHAR(MAX) = 'Production' -- Replace 'Production' with your schema name example-dbo
DECLARE @result varchar(max) = ''

SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13)
END

SET @result = @result + CHAR(9)+ 'public class ' + @TableName + CHAR(13) +CHAR(9)+ '{' + CHAR(13)

SELECT @result = @result + CHAR(13)
    +CHAR(9)+ ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)
FROM
(
SELECT  c.COLUMN_NAME   AS ColumnName
    ,CASE c.DATA_TYPE  
    WHEN 'bigint' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'long?' ELSE 'long' END
    WHEN 'binary' THEN 'Byte[]'
    WHEN 'bit' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'bool?' ELSE 'bool' END           
    WHEN 'char' THEN 'string'
    WHEN 'date' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
    WHEN 'datetime' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
    WHEN 'datetime2' THEN 
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
    WHEN 'datetimeoffset' THEN
        CASE C.IS_NULLABLE
    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                   
    WHEN 'decimal' THEN 
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                   
    WHEN 'float' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'single?' ELSE 'single' END                                    
    WHEN 'image' THEN 'Byte[]'
    WHEN 'int' THEN 
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'int?' ELSE 'int' END
    WHEN 'money' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                               
    WHEN 'nchar' THEN 'string'
    WHEN 'ntext' THEN 'string'
    WHEN 'numeric' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                                           
    WHEN 'nvarchar' THEN 'string'
    WHEN 'real' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'double?' ELSE 'double' END                                                                        
    WHEN 'smalldatetime' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   
    WHEN 'smallint' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'short?' ELSE 'short'END           
    WHEN 'smallmoney' THEN 
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                                                       
    WHEN 'text' THEN 'string'
    WHEN 'time' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                                   
    WHEN 'timestamp' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   
    WHEN 'tinyint' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'byte?' ELSE 'byte' END                                               
    WHEN 'uniqueidentifier' THEN 'Guid'
    WHEN 'varbinary' THEN 'byte[]'
    WHEN 'varchar' THEN 'string'
    ELSE 'Object'
    END AS ColumnType
    , c.ORDINAL_POSITION
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA 
) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result +CHAR(9) + '}' + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
    SET @result = @result + CHAR(13) + '}'
END

PRINT @result


The above one is a SQL Code which generates output similar to model class, so that you can copy that and paste it in your C# classes.

Model Class generator code explanation.


It gets the schema information and all the column names using INFORMATION_SCHEMA.COLUMNS of a particular table.

Then there are some switch cases to replace sql data types to C# data types.

So basically the above sql query generates a string similar to Model class.

3. How to use the Model Class Generator


One simple way would to just copy the above and paste it into a new query window and give the table name and schema name.
In my case i have AdventureWorkDB and i want a Model class for Product table.
so i have given as below:

DECLARE @TableName VARCHAR(MAX) = 'Product' -- Replace 'Product' with your table name
DECLARE @TableSchema VARCHAR(MAX) = 'Production' -- Replace 'Production' with your schema name example-dbo

Another best way would be to create a store procedure with the name of "USP_GenerateModelClass" and make table name and schema name as parameter like below:

Create Procedure USP_GenerateModelClass
(
     @tblName varchar(50),
     @schemaName varchar(50)='dbo'
)
as
Begin

DECLARE @TableName VARCHAR(MAX) = @tblName -- Replace 'Product' with your table name
DECLARE @TableSchema VARCHAR(MAX) = @schemaName -- Replace 'Production' with your schema name
DECLARE @result varchar(max) = ''

SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13)
END

SET @result = @result + CHAR(9)+ 'public class ' + @TableName + CHAR(13) +CHAR(9)+ '{' + CHAR(13)

SELECT @result = @result + CHAR(13)
    +CHAR(9)+ ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)
FROM
(
SELECT  c.COLUMN_NAME   AS ColumnName
    ,CASE c.DATA_TYPE  
   
    WHEN 'bigint' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'long?' ELSE 'long' END
    WHEN 'binary' THEN 'Byte[]'
    WHEN 'bit' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'bool?' ELSE 'bool' END           
    WHEN 'char' THEN 'string'
    WHEN 'date' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
    WHEN 'datetime' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
    WHEN 'datetime2' THEN 
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
    WHEN 'datetimeoffset' THEN
        CASE C.IS_NULLABLE
    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                   
    WHEN 'decimal' THEN 
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                   
    WHEN 'float' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'single?' ELSE 'single' END                                    
    WHEN 'image' THEN 'Byte[]'
    WHEN 'int' THEN 
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'int?' ELSE 'int' END
    WHEN 'money' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                               
    WHEN 'nchar' THEN 'string'
    WHEN 'ntext' THEN 'string'
    WHEN 'numeric' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                                           
    WHEN 'nvarchar' THEN 'string'
    WHEN 'real' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'double?' ELSE 'double' END                                                                        
    WHEN 'smalldatetime' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   
    WHEN 'smallint' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'short?' ELSE 'short'END           
    WHEN 'smallmoney' THEN 
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?' ELSE 'decimal' END                                                                       
    WHEN 'text' THEN 'string'
    WHEN 'time' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                                   
    WHEN 'timestamp' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   
    WHEN 'tinyint' THEN
        CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'byte?' ELSE 'byte' END                                               
    WHEN 'uniqueidentifier' THEN 'Guid'
    WHEN 'varbinary' THEN 'byte[]'
    WHEN 'varchar' THEN 'string'
    ELSE 'Object'
    END AS ColumnType
    , c.ORDINAL_POSITION
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA 
) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result +CHAR(9) + '}' + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
    SET @result = @result + CHAR(13) + '}'
END

PRINT @result

END



Now execute the store Procedure like below.

EXEC USP_GenerateModelClass 'Product','Production'
OR 
EXEC USP_GenerateModelClass 'Employee'  //default schema is 'dbo'

And it will return the output as below:
using System;
namespace Production
{
    public class Product
    {
        public int ProductID { get; set; }

        public string Name { get; set; }

        public string ProductNumber { get; set; }

        public int MakeFlag { get; set; }

        public int FinishedGoodsFlag { get; set; }

        public string Color { get; set; }

        public short SafetyStockLevel { get; set; }

        public short ReorderPoint { get; set; }

        public decimal StandardCost { get; set; }

        public decimal ListPrice { get; set; }

        public string Size { get; set; }

        public string SizeUnitMeasureCode { get; set; }

        public string WeightUnitMeasureCode { get; set; }

        public decimal? Weight { get; set; }

        public int DaysToManufacture { get; set; }

        public string ProductLine { get; set; }

        public string Class { get; set; }

        public string Style { get; set; }

        public int? ProductSubcategoryID { get; set; }

        public int? ProductModelID { get; set; }

        public DateTime SellStartDate { get; set; }

        public DateTime? SellEndDate { get; set; }

        public DateTime? DiscontinuedDate { get; set; }

        public Guid rowguid { get; set; }

        public DateTime ModifiedDate { get; set; }
    }
}


That's great, Isn't it. It gave me the output as i wanted.

4. Any Question?

Hope you guys liked my article, Please do write your comments in the comment section.




Posted by at
Categorie ,

3 comments:

  1. JpsOctober 08, 2017 2:25 pm

    Nice article bro.But I have one question here, if I want to generate a model class which has relationship with other class(i.e, 1 to 1 or 1 to many etc),how do I configure a class with virtual properties.

    ReplyDelete
    Replies
    1. Sushil SutharOctober 08, 2017 8:01 pm

      I will try to enhance the above query.
      As of now i can you a hint, We can check any dependency object for that table and we can create that property.
      Keep checking the blog.

      Delete
  2. selimMay 24, 2023 3:11 pm

    steroid satın al
    heets
    8O81

    ReplyDelete

Subscribe to: Post Comments (Atom)