How to create Data model from Sql Server

Hey everyone wishing you a good day!!

I hope last session was really helped somebody.
Today i have got new thing while i searched in google. so i would like to share this thinks to you. If somebody got help it will make me more happy. My friend, also my colleague he found this code. So without any delay let me start...

If we follow Data model concept in our project instead of entity framework (C#), then we have to create each and every models from dB. if the table is big then you will irritate and spoil whole day. No worries we can do it in easy way. 

• 1 If table consist value then how we make data model?
• Open Sql Server 
• Take new query under your dB
• Copy, paste, execute  this code

             DECLARE @Jsondata varchar(MAX)=(

             select * from [dbo].County

             FOR JSON PATH,INCLUDE_NULL_VALUES)

              SELECT @Jsondata

This will return a Json string like,

[{"CountyID":1,"StateID":1,"FipsCode":"01001","Name":"Autauga","IsHidden":false,"CreatedBy":"INITLOAD","CreatedDate":"2019-06-06T10:56:03.683","ModifiedBy":"INITLOAD","ModifiedDate":"2019-06-06T10:56:03.683"}]

Next we have to copy this string and paste on http://json2csharp.com/ . This will helps to create a data model using Json.

As like above picture we will get the data model and just change the name of class and implement easily. Cool isn't it.. 

• 2 If the table has no data then how we create data model?

Some times we don't have values in table but we have to generate model. use query and execute you will get data model directly. Check this store procedure below :

declare @TableName sysname = '[table name]'

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 'double'

            when 'image' then 'byte[]'

            when 'int' then 'int'

            when 'money' then 'decimal'

            when 'nchar' then 'string'

            when 'ntext' then 'string'

            when 'numeric' then 'decimal'

            when 'nvarchar' then 'string'

            when 'real' then 'float'

            when 'smalldatetime' then 'DateTime'

            when 'smallint' then 'short'

            when 'smallmoney' then 'decimal'

            when 'text' then 'string'

            when 'time' then 'TimeSpan'

            when 'timestamp' then 'long'

            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

Here just change table name on first line other things don't care and execute.

table name should give like this " [dbo].[EmployeeTable] " in between single quotes.

Simply change the class name and enjoy!!