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

当前位置:

首页 > temp > 简明python教程 >

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'
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,84

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;}
    }
}

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();
            }
        }
    }
    #endregion
#>

<#@ 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
      



  

   共4页: 
上一页
1
2
3
4
下一页

  

栏目列表