如何从Microsoft SQL Server生成C#类

目录

背景

使用代码

---

背景

如果您没有使用任何ORM工具从数据库生成类，则此过程会消耗大量时间和资源。

此SQL代码使您免于在此类数据操作上浪费时间，并让您专注于代码开发。

现在，您可以轻松地从常见的SQL数据库类型生成C#对象类。

使用代码

要使用此代码，只需突出显示您要使用的数据库并打开一个新查询。粘贴代码片段，执行生成的类并将其复制到Visual Studio或VS Code中。

// SET NOCOUNT ON;  
  
DECLARE @table_name NVARCHAR(250), @message VARCHAR(80);  
  
DECLARE table_cursor CURSOR FOR   
select distinct tab.name as table_name
from sys.tables as tab
    inner join sys.columns as col on tab.object_id = col.object_id
    left join sys.types as t on col.user_type_id = t.user_type_id
order by table_name;  
  
OPEN table_cursor  
  
FETCH NEXT FROM table_cursor   
INTO @table_name  
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT ' '  
    --SELECT @message = 'Table Name: ' +   
    --    @table_name  
  
    --PRINT @message  
  
    --declare @TableName sysname = 'TableName'
    declare @Result varchar(max) = 'public class ' + @table_name + '
    {'

    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(@table_name)
    ) t
    order by ColumnId

    set @Result = @Result  + '
    }'

    print @Result
  
    FETCH NEXT FROM table_cursor   
    INTO @table_name  
END   
CLOSE table_cursor;  
DEALLOCATE table_cursor;  
//

https://www.codeproject.com/Tips/5305142/How-to-Generate-Csharp-Classes-from-Microsoft-SQL