從資料表產生 C# 類別 (Generate C# POCO Class From SQL Server Table)

2022-12-04

筆記如何從資料表產生類別 Class，讓使用 ADO.NET 或者是 Dapper 等資料存取方式，不用再手動對照資料表格逐筆 prop 🙂

說明

使用 dotnet ef

第一種方式其實與 EntityFramework Database First 無異，就是利用 EntityFramework 能夠自動產生 Table 與 Class 對應來產生類別 Class。

第一次使用 add package 需要部份的下載時間，整個過程可以在 5 分鐘內完成。

產生出來的類別 Class 或包含 Table 以及 View 的對應，開發人員再根據需求從 Model 資料夾剪取需要的類別到專案即可。

dotnet tool install --global dotnet-ef

dotnet new console

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 6.0.11
dotnet add package Microsoft.EntityFrameworkCore.Design --version 6.0.11

dotnet ef dbcontext scaffold "Server=.;Database=Northwind;Trusted_Connection=True;" 
  Microsoft.EntityFrameworkCore.SqlServer -o Model --force

ASP.NET MVC EntityFramework Model (Database First)

使用 TSQL 組合類別字串

參考 Brian Pedersen 於Create C# Class from SQL 的範例程式碼，結合 sp_MSforeachtable 一次取得所有資料表的類別 Class。

使用上的缺點是仍需逐各個類別去建立檔案，外加上剪貼藉由 TSQL 產生的程式碼，略不方便。

建立 temp Stored Procedures 用以處理資料表對照為類別。

DROP PROC IF EXISTS #GenerateClassFromTable
GO

CREATE PROC #GenerateClassFromTable 
@tableName nvarchar(1024)
AS
DECLARE @Schema VARCHAR(MAX) = replace(replace(substring(@tableName, 0, charindex('.', @tableName)), '[', ''), ']', '')
DECLARE @Table VARCHAR(MAX) = replace(replace(substring(@tableName, charindex('.', @tableName) + 1, 128), '[', ''), ']', '')
 
DECLARE @result varchar(max) = ''
SET    @result = @result + 'namespace ' + @Schema  + CHAR(13) + '{' + CHAR(13) 
SET    @result = @result + '    public class ' + @Table + CHAR(13) + '    {' + CHAR(13) 
SELECT @result = @result + '        public ' + DataType + ' ' + PropertyName + ' { get; set; } ' + CHAR(13)
FROM (SELECT
    UPPER(left(c.COLUMN_NAME,1))+SUBSTRING(c.COLUMN_NAME,2,LEN(c.COLUMN_NAME)) AS PropertyName,
    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 'double?' ELSE 'double' 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 'Byte[]'
        WHEN 'tinyint'          THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END
        WHEN 'uniqueidentifier' THEN CASE C.IS_NULLABLE WHEN 'YES' THEN 'Guid?' ELSE 'Guid' END
        WHEN 'varbinary'        THEN 'Byte[]'
        WHEN 'varchar'          THEN 'string'
        ELSE 'Object'
    END AS DataType, c.ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_NAME = @Table
    AND ISNULL(@Schema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA) t
ORDER BY t.ORDINAL_POSITION
 
SET @result = @result  + '    }' + CHAR(13)
SET @result = @result + '}'
 
PRINT @result

執行指令取得類別。

SELECT * FROM sys.tables

EXEC sp_MSforeachtable 'EXEC #GenerateClassFromTable "?"'
GO

範例成果：

namespace Dbo
{
    public class Products
    {
        public int ProductID { get; set; } 
        public string ProductName { get; set; } 
        public int? SupplierID { get; set; } 
        public int? CategoryID { get; set; } 
        public string QuantityPerUnit { get; set; } 
        public decimal? UnitPrice { get; set; } 
        public short? UnitsInStock { get; set; } 
        public short? UnitsOnOrder { get; set; } 
        public short? ReorderLevel { get; set; } 
        public bool Discontinued { get; set; } 
    }
}

Online Tools

sql2object 利用 Data 以及 Header 推測對應的類別 Class，畢竟是推測的可能發現不正確的型別對照。

CodVerter 藉由提供 SQL Create 指令，對照產生類別 Class。