一、基礎知識 1.1、Dapper簡介 Dapper是.NET下的一個micro ORM,它和Entity Framework或NHibnate不同,屬於輕量級並且是半自動的(實體類都要自己寫)。假如你喜歡原生的Sql語句,又喜歡ORM的簡單,那你一定會喜歡上Dapper這款ORM。 1.2、Dap ...
一、基礎知識
1.1、Dapper簡介
Dapper是.NET下的一個micro ORM,它和Entity Framework或NHibnate不同,屬於輕量級並且是半自動的(實體類都要自己寫)。假如你喜歡原生的Sql語句,又喜歡ORM的簡單,那你一定會喜歡上Dapper這款ORM。
1.2、Dapper優點
1)輕量。只有一個文件(SqlMapper.cs)。
2)速度快。Dapper的速度接近於IDataReader,取列表的數據超過了DataTable。
3)支持多種資料庫。包括SQLite、SqlCe、Firebird、Oracle、MySQL、PostgreSQL、SQL Server。
4)可以映射一對一、一對多、多對多等多種關係。
5)性能高。通過Emit反射IDataReader的序列隊列,來快速地得到和產生對象。
1.3、Dapper安裝
此處使用Dapper擴展庫Dapper.SimpleCRUD,它也會預設安裝Dapper(依賴項):
項目右鍵->管理 NuGet 程式包->Dapper.SimpleCRUD。
二、數據準備
2.1、數據表
在SQL Server中創建4個數據表,分別是:Student(學生表)、Teacher(教師表)、Course(課程表)、Record(成績表)。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--學生表 CREATE TABLE [dbo].[Student]( [StudentID] [INT] IDENTITY(1,1) NOT NULL, [Name] [NVARCHAR](50) NULL, [Age] [SMALLINT] NULL, [Gender] [NVARCHAR](10) NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [StudentID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --教師表 CREATE TABLE [dbo].[Teacher]( [TeacherID] [INT] IDENTITY(1,1) NOT NULL, [Name] [NVARCHAR](50) NULL, CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED ( [TeacherID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --課程表 CREATE TABLE [dbo].[Course]( [CourseID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL, [TeacherID] [int] NULL, CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ( [CourseID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --成績表 CREATE TABLE [dbo].[Record]( [StudentID] [INT] NOT NULL, [CourseID] [INT] NOT NULL, [Score] [NUMERIC](8, 2) NULL, CONSTRAINT [PK_Score] PRIMARY KEY CLUSTERED ( [StudentID] ASC, [CourseID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --學生表數據插入 INSERT INTO Student (Name,Age,Gender) SELECT N'劉一',18,N'female' UNION SELECT N'陳二',19,N'female' UNION SELECT N'張三',18,N'male' UNION SELECT N'李四',19,N'male' UNION SELECT N'王五',18,N'male' UNION SELECT N'趙六',19,N'male' UNION SELECT N'孫七',19,N'female' --教師表數據插入 INSERT INTO Teacher (Name) SELECT N'周八' UNION SELECT N'吳九' UNION SELECT N'鄭十' --課程表數據插入 INSERT INTO Course (Name,TeacherID) SELECT N'離散數學',1 UNION SELECT N'程式設計',2 UNION SELECT N'數據結構',3 --成績表數據插入 INSERT INTO Record (StudentID,CourseID,Score ) SELECT 1,1,90 UNION SELECT 2,1,91 UNION SELECT 3,1,89 UNION SELECT 4,1,75 UNION SELECT 5,1,96 UNION SELECT 6,1,78 UNION SELECT 7,1,83 UNION SELECT 1,2,86 UNION SELECT 2,2,92 UNION SELECT 3,2,77 UNION SELECT 4,2,71 UNION SELECT 5,2,66 UNION SELECT 6,2,87 UNION SELECT 7,2,93 UNION SELECT 1,3,81 UNION SELECT 2,3,90 UNION SELECT 3,3,88 UNION SELECT 4,3,82 UNION SELECT 5,3,93 UNION SELECT 6,3,91 UNION SELECT 7,3,84View Code
2.2、實體類
Dapper的實體映射:
1)屬性不編輯,用[Editable(false)]這個特性標記,預設是true。
2)類名到表名的映射,用[Table("TableName")]特性,TableName對應物理數據表名稱。
3)主鍵映射,如果您的實體類中有Id屬性,Dapper會預設此屬性為主鍵,否則要為作為主鍵的屬性添加[Key]特性。
由上可知,如Student表,其實體類應該生成下麵這個樣子:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System; using System.Collections.Generic; using System.Text; using Dapper; namespace LinkTo.Test.ConsoleDapper { [Table("Student")] [Serializable] public class Student { [Key] public int? StudentID {get; set;} public string Name {get; set;} public short? Age {get; set;} public string Gender {get; set;} } }View Code
2.3、使用T4模板生成實體類
2.3.1、T4Code文件夾的文本模板
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<#@ assembly name="System.Core" #> <#@ assembly name="System.Data" #> <#@ import namespace="System.Linq" #> <#@ import namespace="System.Text" #> <#@ import namespace="System.Collections.Generic" #> <#@ import namespace="System.Data"#> <#@ import namespace="System.Data.SqlClient"#> <#+ #region T4Code /// <summary> /// 資料庫架構介面 /// </summary> public interface IDBSchema : IDisposable { List<string> GetTableList(); DataTable GetTableMetadata(string tableName); } /// <summary> /// 資料庫架構工廠 /// </summary> public class DBSchemaFactory { static readonly string DatabaseType = "SqlServer"; public static IDBSchema GetDBSchema() { IDBSchema dbSchema; switch (DatabaseType) { case "SqlServer": { dbSchema =new SqlServerSchema(); break; } default: { throw new ArgumentException("The input argument of DatabaseType is invalid."); } } return dbSchema; } } /// <summary> /// SqlServer /// </summary> public class SqlServerSchema : IDBSchema { public string ConnectionString = "Server=.;Database=Test;Uid=sa;Pwd=********;"; public SqlConnection conn; public SqlServerSchema() { conn = new SqlConnection(ConnectionString); conn.Open(); } public List<string> GetTableList() { List<string> list = new List<string>(); string commandText = "SELECT NAME TABLE_NAME FROM SYSOBJECTS WHERE XTYPE='U' ORDER BY NAME"; using(SqlCommand cmd = new SqlCommand(commandText, conn)) { using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { list.Add(dr["TABLE_NAME"].ToString()); } } } return list; } public DataTable GetTableMetadata(string tableName) { string commandText=string.Format ( "SELECT A.NAME TABLE_NAME,B.NAME FIELD_NAME,C.NAME DATATYPE,ISNULL(B.PREC,0) LENGTH, "+ "CONVERT(BIT,CASE WHEN NOT F.ID IS NULL THEN 1 ELSE 0 END) ISKEY, "+ "CONVERT(BIT,CASE WHEN COLUMNPROPERTY(B.ID,B.NAME,'ISIDENTITY') = 1 THEN 1 ELSE 0 END) AS ISIDENTITY, "+ "CONVERT(BIT,B.ISNULLABLE) ISNULLABLE "+ "FROM SYSOBJECTS A INNER JOIN SYSCOLUMNS B ON A.ID=B.ID INNER JOIN SYSTYPES C ON B.XTYPE=C.XUSERTYPE "+ "LEFT JOIN SYSOBJECTS D ON B.ID=D.PARENT_OBJ AND D.XTYPE='PK' "+ "LEFT JOIN SYSINDEXES E ON B.ID=E.ID AND D.NAME=E.NAME "+ "LEFT JOIN SYSINDEXKEYS F ON B.ID=F.ID AND B.COLID=F.COLID AND E.INDID=F.INDID "+ "WHERE A.XTYPE='U' AND A.NAME='{0}' "+ "ORDER BY A.NAME,B.COLORDER", tableName ); using(SqlCommand cmd = new SqlCommand(commandText, conn)) { SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds,"Schema"); return ds.Tables[0]; } } public void Dispose() { if (conn != null) { conn.Close(); } } } #endregion #>DBSchema.ttinclude
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<#@ assembly name="System.Core" #> <#@ assembly name="System.Data" #> <#@ assembly name="EnvDTE" #> <#@ import namespace="System.Linq" #> <#@ import namespace="System.Text" #> <#@ import namespace="System.Collections.Generic" #> <#@ import namespace="System.Data"#> <#@ import namespace="System.IO"#> <#@ import namespace="Microsoft.VisualStudio.TextTemplating"#> <#+ // T4 Template Block manager for handling multiple file outputs more easily. // Copyright (c) Microsoft Corporation.All rights reserved. // This source code is made available under the terms of the Microsoft Public License (MS-PL) // Manager class records the various blocks so it can split them up class Manager { public struct Block { public string Name; public int Start, Length; } public List<Block> blocks = new List<Block>(); public Block currentBlock; public Block footerBlock = new Block(); public Block headerBlock = new Block(); public ITextTemplatingEngineHost host; public ManagementStrategy strategy; public StringBuilder template; public string OutputPath { get; set; } public Manager(ITextTemplatingEngineHost host, StringBuilder template, bool commonHeader) { this.host = host; this.template = template; OutputPath = string.Empty; strategy = ManagementStrategy.Create(host); } public void StartBlock(string name) { currentBlock = new Block { Name = name, Start = template.Length }; } public void StartFooter() { footerBlock.Start = template.Length; } public void EndFooter() { footerBlock.Length = template.Length - footerBlock.Start; } public void StartHeader() { headerBlock.Start = template.Length; } public void EndHeader() { headerBlock.Length = template.Length - headerBlock.Start; } public void EndBlock() { currentBlock.Length = template.Length - currentBlock.Start; blocks.Add(currentBlock); } public void Process(bool split) { string header = template.ToString(headerBlock.Start, headerBlock.Length); string footer = template.ToString(footerBlock.Start, footerBlock.Length); blocks.Reverse(); foreach(Block block in blocks) { string fileName = Path.Combine(OutputPath, block.Name); if (split) { string content = header + template.ToString(block.Start, block.Length) + footer; strategy.CreateFile(fileName, content); template.Remove(block.Start, block.Length); } else { strategy.DeleteFile(fileName); } } } } class ManagementStrategy { internal static ManagementStrategy Create(ITextTemplatingEngineHost host) { return (host is IServiceProvider) ? new VSManagementStrategy(host) : new ManagementStrategy(host); } internal ManagementStrategy(ITextTemplatingEngineHost host) { } internal virtual void CreateFile(string fileName, string content) { File.WriteAllText(fileName, content); } internal virtual void DeleteFile(string fileName) { if (File.Exists(fileName)) File.Delete(fileName); } } class VSManagementStrategy : ManagementStrategy { private EnvDTE.ProjectItem templateProjectItem; internal VSManagementStrategy(ITextTemplatingEngineHost host) : base(host) { IServiceProvider hostServiceProvider = (IServiceProvider)host; if (hostServiceProvider == null) throw new ArgumentNullException("Could not obtain hostServiceProvider"); EnvDTE.DTE dte = (EnvDTE.DTE)hostServiceProvider.GetService(typeof(EnvDTE.DTE)); if (dte == null) throw new ArgumentNullException("Could not obtain DTE from host"); templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile); } internal override void CreateFile(string fileName, string content) { base.CreateFile(fileName, content); ((EventHandler)delegate { templateProjectItem.ProjectItems.AddFromFile(fileName); }).BeginInvoke(null, null, null, null); } internal override void DeleteFile(string fileName) { ((EventHandler)delegate { FindAndDeleteFile(fileName); }).BeginInvoke(null, null, null, null); } private void FindAndDeleteFile(string fileName) { foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems) { if (projectItem.get_FileNames(0) == fileName) { projectItem.Delete(); return; } } } } #>MultiDocument.ttinclude
DBSchema.ttinclude主要實現了資料庫工廠的功能。註:請將資料庫連接字元串改成您自己的。
MultiDocument.ttinclude主要實現了多文檔的功能。
2.3.2、生成實體類的文本模板
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<#@ template debug="true" hostspecific="true" language="C#" #> <#@ assembly name="System.Core" #> <#@ import namespace="System.Linq" #> <#@ import namespace="System.Text" #> <#@ import namespace="System.Collections.Generic" #> <#@ output extension=".cs" #> <#@ include file="T4Code/DBSchema.ttinclude"#> <#@ include file="T4Code/MultiDocument.ttinclude"#> <# var manager = new Manager(Host, GenerationEnvironment, true) { OutputPath = Path.GetDirectoryName(Host.TemplateFile)}; #> <# //System.Diagnostics.Debugger.Launch();//調試 var dbSchema = DBSchemaFactory.GetDBSchema(); List<string> tableList = dbSchema.GetTableList(); foreach (string tableName in tableList) { manager.StartBlock(tableName+".cs"); DataTable table = dbSchema.GetTableMetadata(tableName); //獲取主鍵 string strKey = string.Empty; foreach (DataRow dataRow in table.Rows) { if ((bool)dataRow["ISKEY"] == true) { strKey = dataRow["FIELD_NAME"].ToString(); break; } } #> //------------------------------------------------------------------------------- // 此代碼由T4模板MultiModelAuto自動生成 // 生成時間 <#= DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") #> // 對此文件的更改可能會導致不正確的行為,並且如果重新生成代碼,這些更改將會丟失。 //------------------------------------------------------------------------------- using System; using System.Collections.Generic; using System.Text; using Dapper; namespace LinkTo.Test.ConsoleDapper { [Table("<#= tableName #>")] [Serializable] public class <#= tableName #> { <# foreach (DataRow dataRow in table.Rows) { //獲取數據類型 string dbDataType = dataRow["DATATYPE"].ToString(); string dataType = string.Empty; switch (dbDataType) { case "decimal": case "numeric": case "money": case "smallmoney": dataType = "decimal?"; break; case "char": case "nchar": case "varchar": case "nvarchar": case "text": case "ntext": dataType = "string"; break; case "uniqueidentifier": dataType = "Guid?"; break; case "bit": dataType = "bool?"; break; case "real": dataType = "Single?"; break; case "bigint": dataType = "long?"; break; case "int": dataType