我来自 PetaPoco 阵营。PetaPoco 有一个 T4 模板,可以从数据库生成模型。Dapper 有类似的功能吗?
我使用 NuGet 安装了 Dapper 并添加了 SqlHelper.cs,但是我没有找到任何能够从数据库生成模型的工具。
我来自 PetaPoco 阵营。PetaPoco 有一个 T4 模板,可以从数据库生成模型。Dapper 有类似的功能吗?
我使用 NuGet 安装了 Dapper 并添加了 SqlHelper.cs,但是我没有找到任何能够从数据库生成模型的工具。
我最近刚写了一条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
Dapper本身为连接对象提供了少量的扩展方法(Query,Execute),并且没有“模型生成器”。也许可以使用其他框架根据数据库模式生成POCO。
更新:
<#@ template language="C#" debug="True" #>
<#@ assembly name="System" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="Microsoft.SqlServer.Management.Common" #>
namespace Namespace
{
<#
var databaseName = "testDb";
var serverConnection = new SqlConnection(
@"Data Source=.\SQLEXPRESS; Integrated Security=true; Initial Catalog=" + databaseName);
var svrConnection = new ServerConnection(serverConnection);
Server srv = new Server(svrConnection);
foreach (Table table in srv.Databases[databaseName].Tables)
{
#>
class <#= table.Name #>
{
<#
foreach (Column col in table.Columns)
{
#>
public <#= GetNetDataType(col.DataType.Name) #> <#= col.Name #> { get; set; }
<#
}
#>
}
<# }
#>
}
<#+
public static string GetNetDataType(string sqlDataTypeName)
{
switch (sqlDataTypeName.ToLower())
{
case "bigint":
return "Int64";
case "binary":
return "Byte[]";
case "bit":
return "bool";
case "char":
return "char";
case "cursor":
return string.Empty;
case "datetime":
return "DateTime";
case "decimal":
return "Decimal";
case "float":
return "Double";
case "int":
return "int";
case "money":
return "Decimal";
case "nchar":
return "string";
case "numeric":
return "Decimal";
case "nvarchar":
return "string";
case "real":
return "single";
case "smallint":
return "Int16";
case "text":
return "string";
case "tinyint":
return "Byte";
case "varbinary":
return "Byte[]";
case "xml":
return "string";
case "varchar":
return "string";
case "smalldatetime":
return "DateTime";
case "image":
return "byte[]";
default:
return string.Empty;
}
}
#>
如果您将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上面提供的sql,并创建了他提到的存储过程,在此基础上进行了一些修改,使其添加了类名。如果您将Management Studio设置为文本输出模式并删除列名输出,则可以获得所有类的复制粘贴文本:
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
P.S.:我本来想把它作为对他回答的修改建议提交,但我的经验是修改建议通常会被拒绝。
用户chris-w-mclean提出了以下更改建议(请参见他的建议编辑),但我自己没有尝试:
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 '
SELECT TOP 100 PERCENT COLUMN_NAME,
替换为SELECT COLUMN_NAME,
IS_NULLABLE,CASE
之间添加这一行:cast(ORDINAL_POSITION as float) as ORDINAL_POSITION,
ORDER BY ORDINAL_POSITION
SELECT 3 as
更改为SELECT 3.0 as
ORDER BY rowNr, pos
进行排序。然后 "TOP 100 PERCENT" 的修补也可以去掉了。 - user2864740SET NOCOUNT ON
DECLARE @tbl as varchar(255)
SET @tbl = '@@@@'
DECLARE @flds as varchar(8000)
SET @flds=''
SELECT -1 as f0, 'public class ' + @tbl + ' {' as f1 into #tmp
INSERT #tmp
SELECT
ORDINAL_POSITION,
' public ' +
CASE
WHEN DATA_TYPE = 'varchar' THEN 'string'
WHEN DATA_TYPE = 'nvarchar' THEN 'string'
WHEN DATA_TYPE = 'text' THEN 'string'
WHEN DATA_TYPE = 'ntext' THEN 'string'
WHEN DATA_TYPE = 'char' THEN 'string'
WHEN DATA_TYPE = 'xml' 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 = '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?'
END + ' ' + COLUMN_NAME + ' {get;set;}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tbl
INSERT #tmp SELECT 999, '}'
SELECT @flds=@flds + f1 +'
' from #tmp order by f0
DROP TABLE #tmp
PRINT @flds
<dynamic>
获取一些未分类的行var persons = connection.Query<dynamic>("SELECT * FROM Persons");
var serializedPerson = JsonConvert.Serialize(persons.First());
Console.WriteLine(serializedPerson);
这个是给Oracle的。可能还不完整,但到目前为止对我来说都有效。
SELECT
'public ' || A.NewType || ' ' || REPLACE(INITCAP(REPLACE(A.COLUMN_NAME, '_', ' ')), ' ', '') || ' {get;set;}' GET_SET
, A.*
FROM
(
SELECT
COLUMN_NAME,
DATA_TYPE,
NULLABLE,
CASE
WHEN DATA_TYPE = 'VARCHAR2' THEN 'string'
WHEN DATA_TYPE = 'VARCHAR' THEN 'string'
WHEN DATA_TYPE = 'DATE' AND NULLABLE = 'N' THEN 'DateTime'
WHEN DATA_TYPE = 'DATE' AND NULLABLE = 'Y' THEN 'DateTime?'
WHEN DATA_TYPE = 'INT' AND NULLABLE = 'N' THEN 'int?'
WHEN DATA_TYPE = 'INT' AND NULLABLE = 'Y' THEN 'int'
WHEN DATA_TYPE = 'DECIMAL' AND NULLABLE = 'N' THEN 'decimal'
WHEN DATA_TYPE = 'DECIMAL' AND NULLABLE = 'Y' THEN 'decimal?'
WHEN DATA_TYPE = 'NUMBER' AND NULLABLE = 'N' THEN 'decimal'
WHEN DATA_TYPE = 'NUMBER' AND NULLABLE = 'Y' THEN 'decimal?'
WHEN DATA_TYPE = 'NUMBER2' AND NULLABLE = 'N' THEN 'decimal'
WHEN DATA_TYPE = 'NUMBER2' AND NULLABLE = 'Y' THEN 'decimal?'
WHEN DATA_TYPE = 'CHAR' THEN 'string'
WHEN DATA_TYPE = 'CHAR2' THEN 'string'
WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
WHEN DATA_TYPE = 'CLOB' THEN 'byte[]'
ELSE '??'
END AS NewType
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = '<<TABLENAME>>'
ORDER BY COLUMN_ID
) A
sqlserver,oracle,mysql,postgresql
第一步:将PocoClassGenerator.cs代码复制并粘贴到您的项目或LINQPad中。
或者从NuGet安装。
PM> install-package PocoClassGenerator
GenerateAllTables
方法,然后打印输出结果。using (var connection = Connection)
{
Console.WriteLine(connection.GenerateAllTables());
}
GeneratorBehavior.DapperContrib
调用方法。using (var conn = GetConnection())
{
var result = conn.GenerateAllTables(GeneratorBehavior.DapperContrib);
Console.WriteLine(result);
}
using (var conn = GetConnection())
{
var result = conn.GenerateAllTables(GeneratorBehavior.Comment);
Console.WriteLine(result);
}
using (var conn = GetConnection())
{
var result = conn.GenerateAllTables(GeneratorBehavior.View);
Console.WriteLine(result);
}
using (var conn = GetConnection())
{
var result = conn.GenerateAllTables(GeneratorBehavior.View | GeneratorBehavior.Comment | GeneratorBehavior.DapperContrib);
Console.WriteLine(result);
}
using (var connection = Connection)
{
var classCode = connection.GenerateClass("select * from Table");
Console.WriteLine(classCode);
}
using (var connection = Connection)
{
var classCode = connection.GenerateClass("with EMP as (select 1 ID,'WeiHan' Name,25 Age) select * from EMP", className: "EMP");
Console.WriteLine(classCode);
}
var dt = new DataTable();
dt.TableName = "TestTable";
dt.Columns.Add(new DataColumn() { ColumnName = "ID", DataType = typeof(string) });
var result = dt.GenerateClass();
var expect =
@"public class TestTable
{
public string ID { get; set; }
}";
Assert.Equal(expect, result);
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using Dapper;
namespace Pocos
{
public class TAB {
public string TABLE_NAME { get; set; }
}
public class COL {
public string COLUMN_NAME { get; set; }
public int? ORIDINAL_POSITIONS { set; get; }
public string DATA_TYPE { get; set; }
public string CHARACTER_MAXIMUM_LENGTH { get; set; }
public string NUMERIC_PRECISION { get; set; }
public string NUMERIC_SCALE { get; set; }
}
class Program {
static void Main(string[] args) {
string sConnect = "Server=LT6-MARKL;Database=PKDEM815;UID=PKDEM815;Password=PKDEM815";
IEnumerable tables;
IEnumerable columns;
List lines;
using ( var conn = new SqlConnection(sConnect))
tables = conn.Query("SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME");
// Roll through each table of the database and generate an .cs file, as a POCO
foreach (TAB t in tables.OrderBy(t => t.TABLE_NAME)) {
lines = new List();
lines.Add("using System;");
lines.Add("using System.Collections.Generic;");
lines.Add("using System.Configuration;");
lines.Add("using System.Data.SqlClient;");
lines.Add("using Dapper;");
lines.Add("using Dapper.Contrib.Extensions;");
lines.Add("");
lines.Add("namespace PKDataLayer.Models {");
lines.Add("");
lines.Add("\t[Table(\"" + t.TABLE_NAME + "\")]");
lines.Add("\tpublic class " + t.TABLE_NAME + " {");
lines.Add("");
using (var conn2 = new SqlConnection(sConnect)) {
columns = conn2.Query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '"+ t.TABLE_NAME +"' ORDER BY ORDINAL_POSITION");
foreach( COL c in columns) {
if (t.TABLE_NAME + "_KEY" == c.COLUMN_NAME || t.TABLE_NAME + "_SEQNUM" == c.COLUMN_NAME)
lines.Add("\t\t[Key]");
// SELECT DISTINCT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES )
if (c.DATA_TYPE == "char" || c.DATA_TYPE == "varchar")
lines.Add("\t\tpublic string " + c.COLUMN_NAME + " { get; set; }");
if (c.DATA_TYPE == "int")
lines.Add("\t\tpublic int " + c.COLUMN_NAME + " { get; set; }");
if (c.DATA_TYPE == "datetime")
lines.Add("\t\tpublic DateTime? " + c.COLUMN_NAME + " { get; set; }");
if (c.DATA_TYPE == "decimal" || c.DATA_TYPE == "numeric")
lines.Add("\t\tpublic decimal? " + c.COLUMN_NAME + " { get; set; }");
}
}
lines.Add("\t}");
lines.Add("}");
Console.WriteLine("Creating POCO for " + t.TABLE_NAME);
using (TextWriter tw = new StreamWriter( t.TABLE_NAME + ".cs" ))
foreach (String s in lines)
tw.WriteLine(s);
}
}
}
}