2020年4月30日星期四

C# ORM学习笔记:Dapper基本用法

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,84
View 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 Code

    2.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();   }  } } 

没有评论:

发表评论