Procedure to Create Property from Tables and Views in SQL Server

Hello everyone. In this article, I will try to give information about the procedure that creates Property from Tables and Views in SQL Server.

If you’re programming on the CSharp side, you need Properties.

It can take a lot of time to create a table or view and then create a Property in SQL Server.

Using the procedure below, you can easily create the class structure of your table.

CREATE  PROCEDURE [dbo].[usp_GenerateClassFromViewAndTable]
  @TabloViewAdi NVARCHAR(100),
  @ClassAdi NVARCHAR(100)
AS
BEGIN
declare @Sonuc varchar(max) = 'public class ' + @ClassAdi + '
{'

select @Sonuc = @Sonuc + '
    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(@TabloViewAdi)
) t
order by ColumnId

set @Sonuc = @Sonuc  + '
}'

PRINT  @Sonuc

END
GO

--Use of the procedure

EXEC usp_GenerateClassFromViewAndTable 
@TabloViewAdi = N'TableViewName' 
,@ClassAdi = N'ClassNameYouWanttoCreate'

Good luck to everyone in business and life.