如何使用Dapper从数据库生成模型？

Question

我是从PetaPoco训练营来的。PetaPoco有一个从数据库生成模型的T4模板。Dapper有没有类似的东西？

我使用NuGet安装了Dapper并添加了SqlHelper.cs，但我没有找到任何可以从数据库生成模型的东西。

Answer 1

Dapper本身为connection对象提供了很少的扩展方法(查询、执行)，并且没有“模型生成器”。也许可以使用一些其他框架来基于db模式生成POCO。

更新：

Database tables to C# POCO classes T4 template

<#@ template language="C#“debug="True”#> <#@程序集name="System“#> <#@程序集name="System.Data”#> <#@程序集name="System.Core“#> <#@程序集name=”“assembly”“assembly”“assembly”“@ import namespace="System“#> <#@ import namespace="System.Text”#> <#@ import namespace="System.Xml“#> <#@ import Namespace #> <#@ import namespace="System.Data.SqlClient”#> <#@ import namespace Namespace Namespace{ var =“”；var serverConnection =新SqlConnection( @"Data Source=.\SQLEXPRESS；集成Security=true；初始Catalog=“+ databaseName)；var svrConnection =新服务器(ServerConnection)；服务器源=新服务器(SvrConnection)；foreach (srv.DatabasesdatabaseName.Tables中的表格){ #> <#= table.Name #> { <# foreach (table.Columns中的列){ #> public <#= GetNetDataType(col.DataType.Name) #> <#= col.Name #> { get；set；} <# } #> } <# } #> } <#+公共静态字符串GetNetDataType(string sqlDataTypeName) {<#+ (sqlDataTypeName.ToLower()) { case "bigint"：返回"Int64"；case "binary"：返回"Byte[]"；case "bit"：返回"bool"；case "char"：返回"char"；case "cursor"：返回string.Empty；case "datetime"：返回"DateTime"；case "decimal"：返回"Decimal"；case "float"：返回"Double"；case "int"：返回"int"；case "money"：return "Decimal"；case "nchar"：return "string"；case "numeric"：return "Decimal"；case "nvarchar"：return "string"；case "real"：返回"single"；case "smallint"：返回"Int16"；case "text"：返回"string"；case "tinyint"：返回"Byte"；case "varbinary"：返回"Byte[]"；case "xml"：返回"string"；case "varchar"：返回"string"；case "smalldatetime"：返回"DateTime"；case "image"：返回"byte[]"；默认:返回string.Empty；}} #>

Answer 2

我最近刚刚编写了一个sql查询来完成这项工作。在我需要的时候用额外的类型更新它。只需将表名替换为@即可。

为了创建大量的表，我创建了一个临时存储过程来调用。例如：exec createTablePOCO(@tableName)

SELECT 
    'public ' + a1.NewType + ' ' + a1.COLUMN_NAME + ' {get;set;}'
    ,*
FROM (
    /*using top because i'm putting an order by ordinal_position on it. 
    putting a top on it is the only way for a subquery to be ordered*/
    SELECT TOP 100 PERCENT
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    CASE 
        WHEN DATA_TYPE = 'varchar' THEN 'string'
        WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
        WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
        WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'YES' THEN 'int?'
        WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'NO' THEN 'int'
        WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'NO' THEN 'Int16'
        WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'Int16?'
        WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'NO' THEN 'decimal'
        WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'YES' THEN 'decimal?'
        WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'NO' THEN 'long'
        WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'YES' THEN 'long?'
        WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' THEN 'byte'
        WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
        WHEN DATA_TYPE = 'char' THEN 'string'
        WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
        WHEN DATA_TYPE = 'varbinary' THEN 'byte[]'
        WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'NO' THEN 'bool'
        WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES' THEN 'bool?'
        WHEN DATA_TYPE = 'xml' THEN 'string'
    END AS NewType
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = '@@@@'
    ORDER BY ORDINAL_POSITION
) as a1

Answer 3

从游标调用存储过程

如果您组合了前面提到的sp mattritchies (参见上面的答案)并从游标调用它，那么您可以为数据库中的每个表生成poco类。

USE YourDataBaseName
GO 
    DECLARE @field1 nvarchar(400)
    DECLARE cur CURSOR LOCAL for

    SELECT TABLE_NAME FROM information_schema.tables
    OPEN cur
    FETCH NEXT FROM cur INTO @field1 --, @field2
    WHILE @@FETCH_STATUS = 0 BEGIN          
        exec Helper_CreatePocoFromTableName @field1 -- , @field2            
        fetch next from cur into @field1 -- , @field2
    END

close cur
deallocate cur

提到的存储过程问题

我从mattritchies answer中获取了sql (参见上文)，创建了他提到的存储过程，并对其进行了一些修改，以便它也添加了类名。如果将Management Studio置于Text- output -Mode模式并删除列名的输出，则会得到copy paste text for all classes：

CREATE PROCEDURE [dbo].[Helper_CreatePocoFromTableName]    
    @tableName varchar(100)
AS
BEGIN
SET NOCOUNT ON;

-- Subquery to return only the copy paste text
Select PropertyColumn from (
    SELECT 1 as rowNr, 'public class ' + @tableName + ' {' as PropertyColumn
    UNION
    SELECT 2 as rowNr, 'public ' + a1.NewType + ' ' + a1.COLUMN_NAME + ' {get;set;}' as PropertyColumn
    -- ,* comment added so that i get copy pasteable output
     FROM 
    (
        /*using top because i'm putting an order by ordinal_position on it. 
        putting a top on it is the only way for a subquery to be ordered*/
        SELECT TOP 100 PERCENT
        COLUMN_NAME,
        DATA_TYPE,
        IS_NULLABLE,
        CASE 
            WHEN DATA_TYPE = 'varchar' THEN 'string'
            WHEN DATA_TYPE = 'nvarchar' THEN 'string' 
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'YES' THEN 'int?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'NO' THEN 'int'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'NO' THEN 'Int16'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'Int16?'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'NO' THEN 'long'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'YES' THEN 'long?'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' THEN 'byte'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
            WHEN DATA_TYPE = 'char' THEN 'string'                       
            WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
            WHEN DATA_TYPE = 'varbinary' THEN 'byte[]'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'NO' THEN 'bool'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES' THEN 'bool?'
            WHEN DATA_TYPE = 'xml' THEN 'string'
        END AS NewType
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @tableName
        ORDER BY ORDINAL_POSITION
        ) AS a1 
    UNION 
    SELECT 3 as rowNr,  '} // class ' + @tableName
    ) as t Order By rowNr asc
END

附言:我会把它作为对他的答案的编辑建议，但我的经验是，编辑建议经常被拒绝。

更新

用户chris-w-mclean建议了以下更改(请参阅his suggested-edit)，我自己还没有尝试过：

• 将SELECT 1 as rowNr, 'public class '替换为SELECT 1.0 as rowNr, 'public class '
• 将SELECT 2 as rowNr, 'public '替换为SELECT 2 + a1.ORDINAL_POSITION/1000 as rowNr, 'public '

将D14替换为D15 H117添加到SELECT 3.0 as的D15和H117之间