C# ORM学习笔记：Dapper基本用法
一、基础知识
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（成绩表）。
--学生表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'UNIONSELECT N'陈二',19,N'female'UNIONSELECT N'张三',18,N'male'UNIONSELECT N'李四',19,N'male'UNIONSELECT N'王五',18,N'male'UNIONSELECT N'赵六',19,N'male'UNIONSELECT N'孙七',19,N'female'--教师表数据插入INSERT INTO Teacher (Name)SELECT N'周八'UNIONSELECT N'吴九'UNIONSELECT N'郑十'--课程表数据插入INSERT INTO Course (Name,TeacherID)SELECT N'离散数学',1UNIONSELECT N'程序设计',2UNIONSELECT N'数据结构',3--成绩表数据插入INSERT INTO Record (StudentID,CourseID,Score )SELECT 1,1,90UNIONSELECT 2,1,91UNIONSELECT 3,1,89UNIONSELECT 4,1,75UNIONSELECT 5,1,96UNIONSELECT 6,1,78UNIONSELECT 7,1,83UNIONSELECT 1,2,86UNIONSELECT 2,2,92UNIONSELECT 3,2,77UNIONSELECT 4,2,71UNIONSELECT 5,2,66UNIONSELECT 6,2,87UNIONSELECT 7,2,93UNIONSELECT 1,3,81UNIONSELECT 2,3,90UNIONSELECT 3,3,88UNIONSELECT 4,3,82UNIONSELECT 5,3,93UNIONSELECT 6,3,91UNIONSELECT 7,3,84View Code
2.2、实体类
Dapper的实体映射：
1）属性不编辑，用[Editable(false)]这个特性标记，默认是true。
2）类名到表名的映射，用[Table("TableName")]特性，TableName对应物理数据表名称。
3）主键映射，如果您的实体类中有Id属性，Dapper会默认此属性为主键，否则要为作为主键的属性添加[Key]特性。
由上可知，如Student表，其实体类应该生成下面这个样子：
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 Code2.3、使用T4模板生成实体类
2.3.1、T4Code文件夹的文本模板
<#@ 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(); } } }
