一区二区三区日韩精品-日韩经典一区二区三区-五月激情综合丁香婷婷-欧美精品中文字幕专区

分享

C# ORM學(xué)習(xí)筆記:Dapper基本用法

 頭號碼甲 2021-04-01

    一、基礎(chǔ)知識

    1.1、Dapper簡介

    Dapper是.NET下的一個micro ORM,它和Entity Framework或NHibnate不同,屬于輕量級并且是半自動的(實體類都要自己寫)。假如你喜歡原生的Sql語句,又喜歡ORM的簡單,那你一定會喜歡上Dapper這款ORM。

    1.2、Dapper優(yōu)點

    1)輕量。只有一個文件(SqlMapper.cs)。

    2)速度快。Dapper的速度接近于IDataReader,取列表的數(shù)據(jù)超過了DataTable。

    3)支持多種數(shù)據(jù)庫。包括SQLite、SqlCe、Firebird、Oracle、MySQL、PostgreSQL、SQL Server。

    4)可以映射一對一、一對多、多對多等多種關(guān)系。

    5)性能高。通過Emit反射IDataReader的序列隊列,來快速地得到和產(chǎn)生對象。

    1.3、Dapper安裝

    此處使用Dapper擴(kuò)展庫Dapper.SimpleCRUD,它也會默認(rèn)安裝Dapper(依賴項):

    項目右鍵->管理 NuGet 程序包->Dapper.SimpleCRUD。

    二、數(shù)據(jù)準(zhǔn)備

    2.1、數(shù)據(jù)表

    在SQL Server中創(chuàng)建4個數(shù)據(jù)表,分別是:Student(學(xué)生表)、Teacher(教師表)、Course(課程表)、Record(成績表)。

--學(xué)生表
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]

--學(xué)生表數(shù)據(jù)插入
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'

--教師表數(shù)據(jù)插入
INSERT INTO Teacher (Name)
SELECT N'周八'
UNION
SELECT N'吳九'
UNION
SELECT N'鄭十'

--課程表數(shù)據(jù)插入
INSERT INTO Course (Name,TeacherID)
SELECT N'離散數(shù)學(xué)',1
UNION
SELECT N'程序設(shè)計',2
UNION
SELECT N'數(shù)據(jù)結(jié)構(gòu)',3

--成績表數(shù)據(jù)插入
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
View Code

    2.2、實體類

    Dapper的實體映射:

    1)屬性不編輯,用[Editable(false)]這個特性標(biāo)記,默認(rèn)是true。

    2)類名到表名的映射,用[Table("TableName")]特性,TableName對應(yīng)物理數(shù)據(jù)表名稱。

    3)主鍵映射,如果您的實體類中有Id屬性,Dapper會默認(rèn)此屬性為主鍵,否則要為作為主鍵的屬性添加[Key]特性。

    由上可知,如Student表,其實體類應(yīng)該生成下面這個樣子:

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>
    /// 數(shù)據(jù)庫架構(gòu)接口
    /// </summary>
    public interface IDBSchema : IDisposable
    {
        List<string> GetTableList();
        DataTable GetTableMetadata(string tableName);
    }

    /// <summary>
    /// 數(shù)據(jù)庫架構(gòu)工廠
    /// </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
<#@ 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主要實現(xiàn)了數(shù)據(jù)庫工廠的功能。注:請將數(shù)據(jù)庫連接字符串改成您自己的。

    MultiDocument.ttinclude主要實現(xiàn)了多文檔的功能。

    2.3.2、生成實體類的文本模板

<#@ 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();//調(diào)試
    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") #>
// 對此文件的更改可能會導(dǎo)致不正確的行為,并且如果重新生成代碼,這些更改將會丟失。
//-------------------------------------------------------------------------------

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)
        {
            //獲取數(shù)據(jù)類型
            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 = "int?";
                    break;
                case "tinyint":
                case "smallint":
                    dataType = "short?";
                    break;
                case "float":
                    dataType = "float?";
                    break;
                case "date":
                case "datetime":
                case "datetime2":
                case "smalldatetime":
                    dataType = "DateTime?";
                    break;
                case "datetimeoffset ":
                    dataType = "DateTimeOffset?";
                    break;
                case "timeSpan ":
                    dataType = "TimeSpan?";
                    break;
                case "image":
                case "binary":
                case "varbinary":
                    dataType = "byte[]";
                    break;
                default:
                    break;
            }
            if (dataRow["FIELD_NAME"].ToString() == strKey)
            {
#>
        [Key]
        public <#= dataType #> <#= dataRow["FIELD_NAME"].ToString() #> {get; set;}
<#
            }
            else
            {
#>

        public <#= dataType #> <#= dataRow["FIELD_NAME"].ToString() #> {get; set;}
<# 
            }
        }
#>
    }
}
<#
        manager.EndBlock();
    }
    dbSchema.Dispose();
    manager.Process(true);
#>
MultiModelAuto.tt

    三、CRUD

    3.1、connectionStrings

    在App.config中添加數(shù)據(jù)庫連接字符串:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
  <connectionStrings>
    <add name="connString" connectionString="Server=.;Database=Test;Uid=sa;Pwd=********;" />
  </connectionStrings>
</configuration>
View Code

    添加一個DapperHelper類,實現(xiàn)數(shù)據(jù)庫連接及后續(xù)的CRUD。

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace LinkTo.Test.ConsoleDapper
{
    public class DapperHelper
    {
        public IDbConnection Connection = null;
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

        public DapperHelper()
        { }

        private IDbConnection GetCon()
        {
            if (Connection == null)
            {
                Connection = new SqlConnection(ConnectionString);
            }
            else if (Connection.State == ConnectionState.Closed)
            {
                Connection.ConnectionString = ConnectionString;
            }
            else if (Connection.State == ConnectionState.Broken)
            {
                Connection.Close();
                Connection.ConnectionString = ConnectionString;
            }
            return Connection;
        }
    }
}
View Code

    3.2、Create

    a1)通過SQL插入單條數(shù)據(jù)(帶參數(shù)),返回結(jié)果是影響行數(shù)。

        /// <summary>
        /// 通過SQL插入單條數(shù)據(jù)(帶參數(shù)),返回結(jié)果是影響行數(shù)。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithSqlA()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "INSERT INTO Student (Name,Age,Gender) VALUES (@Name,@Age,@Gender)";
                return conn.Execute(strSql, new { Name = "Hello", Age = 18, Gender = "male" });
            }
        }
View Code

    a2)通過SQL插入單條數(shù)據(jù)(帶實體),返回結(jié)果是影響行數(shù)。

        /// <summary>
        /// 通過SQL插入單條數(shù)據(jù)(帶實體),返回結(jié)果是影響行數(shù)。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithSqlB()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "INSERT INTO Student (Name,Age,Gender) VALUES (@Name,@Age,@Gender)";
                Student student = new Student
                {
                    Name = "Hello",
                    Age = 18,
                    Gender = "male"
                };
                return conn.Execute(strSql, student);
            }
        }
View Code

    a3)通過SQL插入單條數(shù)據(jù)(帶實體),返回主鍵值。

        /// <summary>
        /// 通過SQL插入單條數(shù)據(jù)(帶實體),返回主鍵值。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithSqlC()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "INSERT INTO Student (Name,Age,Gender) VALUES (@Name,@Age,@Gender)";
                Student student = new Student
                {
                    Name = "Hello",
                    Age = 18,
                    Gender = "male"
                };
                strSql += " SELECT SCOPE_IDENTITY()";
                return conn.QueryFirstOrDefault<int>(strSql, student);
            }
        }
View Code

    a4)通過SQL插入多條數(shù)據(jù)(帶實體),返回結(jié)果是影響行數(shù)。

        /// <summary>
        /// 通過SQL插入多條數(shù)據(jù)(帶實體),返回結(jié)果是影響行數(shù)。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithSqlD()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "INSERT INTO Student (Name,Age,Gender) VALUES (@Name,@Age,@Gender)";
                List<Student> list = new List<Student>();
                for (int i = 0; i < 3; i++)
                {
                    Student student = new Student
                    {
                        Name = "World" + i.ToString(),
                        Age = 18,
                        Gender = "male"
                    };
                    list.Add(student);
                }
                return conn.Execute(strSql, list);
            }
        }
View Code

    b)通過實體插入數(shù)據(jù),返回結(jié)果是主鍵值。

        /// <summary>
        /// 通過實體插入數(shù)據(jù),返回結(jié)果是主鍵值。
        /// </summary>
        /// <returns></returns>
        public int? InsertWithEntity()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                var entity = new Student { Name = "World", Age = 18, Gender = "male" };
                return conn.Insert(entity);
            }
        }
View Code

    3.3、Read

    a1)通過SQL查詢數(shù)據(jù)(查詢所有數(shù)據(jù))

        /// <summary>
        /// 通過SQL查詢數(shù)據(jù)(查詢所有數(shù)據(jù))
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Student> GetStudentList1()
        {
            string strSql = "SELECT * FROM Student";
            using (var conn = GetCon())
            {
                conn.Open();
                return conn.Query<Student>(strSql);
            }
        }
View Code

    a2)通過SQL查詢數(shù)據(jù)(帶參數(shù))

        /// <summary>
        /// 通過SQL查詢數(shù)據(jù)(帶參數(shù))
        /// </summary>
        /// <param name="studentID"></param>
        /// <returns></returns>
        public Student GetStudentList1A(int studentID)
        {
            string strSql = "SELECT * FROM Student WHERE StudentID=@StudentID";
            using (var conn = GetCon())
            {
                conn.Open();
                return conn.Query<Student>(strSql, new { StudentID = studentID }).FirstOrDefault();
            }
        }
View Code

    a3)通過SQL查詢數(shù)據(jù)(IN)

        /// <summary>
        /// 通過SQL查詢數(shù)據(jù)(IN)
        /// </summary>
        /// <param name="studentID"></param>
        /// <returns></returns>
        public IEnumerable<Student> GetStudentList1B(string studentID)
        {
            string strSql = "SELECT * FROM Student WHERE StudentID IN @StudentID";
            var idArr = studentID.Split(',');
            using (var conn = GetCon())
            {
                conn.Open();
                return conn.Query<Student>(strSql, new { StudentID = idArr });
            }
        }
View Code

    b1)通過實體查詢數(shù)據(jù)(查詢所有數(shù)據(jù))

        /// <summary>
        /// 通過實體詢數(shù)據(jù)(查詢所有數(shù)據(jù))
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Student> GetStudentList2()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                return conn.GetList<Student>();
            }
        }
View Code

    b2)通過實體查詢數(shù)據(jù)(指定ID)

        /// <summary>
        /// 通過實體詢數(shù)據(jù)(指定ID)
        /// </summary>
        /// <param name="studentID"></param>
        /// <returns></returns>
        public Student GetStudentList2A(int studentID)
        {
            using (var conn = GetCon())
            {
                conn.Open();
                return conn.Get<Student>(studentID);
            }
        }
View Code

    b3)通過實體查詢數(shù)據(jù)(帶參數(shù))

        /// <summary>
        /// 通過實體詢數(shù)據(jù)(帶參數(shù))
        /// </summary>
        /// <param name="studentID"></param>
        /// <returns></returns>
        public Student GetStudentList2B(int studentID)
        {
            using (var conn = GetCon())
            {
                conn.Open();
                return conn.GetList<Student>(new { StudentID = studentID }).FirstOrDefault();
            }
        }
View Code

    c1)多表查詢(QueryMultiple),主要操作:通過QueryMultiple方法,返回查詢中每條SQL語句的數(shù)據(jù)集合。

        /// <summary>
        /// 多表查詢(QueryMultiple)
        /// </summary>
        /// <returns></returns>
        public string GetMultiEntityA()
        {
            string strSql = "SELECT * FROM Student AS A;SELECT * FROM Teacher AS A";
            StringBuilder sbStudent = new StringBuilder();
            StringBuilder sbTeacher = new StringBuilder();
            using (var conn = GetCon())
            {
                conn.Open();
                var grid = conn.QueryMultiple(strSql);
                var students = grid.Read<Student>();
                var teachers = grid.Read<Teacher>();
                foreach (var item in students)
                {
                    sbStudent.Append($"StudentID={item.StudentID} Name={item.Name} Age={item.Age} Gender={item.Gender}\n");
                }
                foreach (var item in teachers)
                {
                    sbTeacher.Append($"TeacherID={item.TeacherID} Name={item.Name}\n");
                }
                return sbStudent.ToString() + sbTeacher.ToString();
            }
        }
View Code

    c2)多表查詢(Query),主要操作:通過SQL進(jìn)行多表關(guān)聯(lián)查詢,返回查詢結(jié)果的數(shù)據(jù)集合。

        /// <summary>
        /// 多表查詢(Query)
        /// </summary>
        /// <returns></returns>
        public string GetMultiEntityB()
        {
            string strSql = "SELECT A.Name CourseName,B.Name TeacherName FROM Course A INNER JOIN Teacher B ON A.TeacherID=B.TeacherID";
            StringBuilder sbResult = new StringBuilder();
            using (var conn = GetCon())
            {
                conn.Open();
                var query = conn.Query(strSql);
                query.AsList().ForEach(q =>
                {
                    sbResult.Append($"CourseName={q.CourseName} TeacherName={q.TeacherName}\n");
                });
                return sbResult.ToString();
            }
        }
View Code

    3.4、Update

    a1)通過SQL更新數(shù)據(jù)(帶參數(shù)),返回結(jié)果是影響行數(shù)。

        /// <summary>
        /// 通過SQL更新數(shù)據(jù)(帶參數(shù)),返回結(jié)果是影響行數(shù)。
        /// </summary>
        /// <returns></returns>
        public int? UpdateWithSqlA()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "UPDATE Student SET Name=@Name,Age=@Age,Gender=@Gender WHERE StudentID=@StudentID";
                return conn.Execute(strSql, new { Name = "World3", Age = 19, Gender = "female", StudentID = 17 });
            }
        }
View Code

    a2)通過SQL插入單條數(shù)據(jù)(帶實體),返回結(jié)果是影響行數(shù)。

        /// <summary>
        /// 通過SQL更新數(shù)據(jù)(帶實體),返回結(jié)果是影響行數(shù)。
        /// </summary>
        /// <returns></returns>
        public int? UpdateWithSqlB()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "UPDATE Student SET Name=@Name,Age=@Age,Gender=@Gender WHERE StudentID=@StudentID";
                Student student = new Student
                {
                    StudentID = 17,
                    Name = "World3",
                    Age = 18,
                    Gender = "male"
                };
                return conn.Execute(strSql, student);
            }
        }
View Code

    b)通過實體更新數(shù)據(jù),返回結(jié)果是影響行數(shù)。

        /// <summary>
        /// 通過實體更新數(shù)據(jù),返回結(jié)果是影響行數(shù)。
        /// </summary>
        /// <returns></returns>
        public int? UpdateWithEntity()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                var entity = new Student { StudentID = 17, Name = "World4", Age = 18, Gender = "male" };
                return conn.Update(entity);
            }
        }
View Code

    3.5、Delete

    a)通過SQL刪除數(shù)據(jù)(帶參數(shù)),返回結(jié)果是影響行數(shù)。

        /// <summary>
        /// 通過SQL刪除數(shù)據(jù)(帶參數(shù)),返回結(jié)果是影響行數(shù)。
        /// </summary>
        /// <returns></returns>
        public int? DeleteWithSql()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                string strSql = "DELETE FROM Student WHERE StudentID=@StudentID";
                return conn.Execute(strSql, new { StudentID = 16 });
            }
        }
View Code

    b)通過實體刪除數(shù)據(jù),返回結(jié)果是影響行數(shù)。

        /// <summary>
        /// 通過實體刪除數(shù)據(jù),返回結(jié)果是影響行數(shù)。
        /// </summary>
        /// <returns></returns>
        public int? DeleteWithEntity()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                var entity = new Student { StudentID = 17 };
                return conn.Delete(entity);
            }
        }
View Code

    四、Procedure

    4.1、帶輸出參數(shù)的存儲過程

CREATE PROCEDURE [dbo].[GetStudentAge]
    @StudentID INT,
    @Name NVARCHAR(50) OUTPUT
AS
BEGIN
    DECLARE @Age SMALLINT
    SELECT @Name=Name,@Age=Age FROM Student WHERE StudentID=@StudentID
    SELECT @Age
END
View Code
        /// <summary>
        /// 帶輸出參數(shù)的存儲過程
        /// </summary>
        /// <param name="studentID"></param>
        /// <returns></returns>
        public Tuple<string, int> GetStudentAge(int studentID)
        {
            int age = 0;
            var para = new DynamicParameters();
            para.Add("StudentID", 1);
            para.Add("Name", string.Empty, DbType.String, ParameterDirection.Output);

            using (var conn = GetCon())
            {
                conn.Open();
                age = conn.Query<int>("GetStudentAge", para, commandType: CommandType.StoredProcedure).FirstOrDefault();
            }

            return Tuple.Create(para.Get<string>("Name"), age);
        }
View Code  

   五、Transaction

    5.1、在IDbConnection下事務(wù),主要操作:在執(zhí)行Insert方法時傳入Transaction;在正常情況下Commit事務(wù);在異常時回滾事務(wù)。

        /// <summary>
        /// 在IDbConnection下事務(wù)
        /// </summary>
        /// <returns></returns>
        public bool InsertWithTran()
        {
            using (var conn = GetCon())
            {
                conn.Open();
                int studentID = 0, teacherID = 0, result = 0;
                var student = new Student { Name = "Sandy", Age = 18, Gender = "female" };
                var teacher = new Teacher { Name = "Luci" };
                var tran = conn.BeginTransaction();
                try
                {
                    studentID = conn.Insert(student, tran).Value;
                    result++;
                    teacherID = conn.Insert(teacher, tran).Value;
                    result++;
                    tran.Commit();
                }
                catch
                {
                    result = 0;
                    tran.Rollback();
                }
                return result > 0;
            }
        }
View Code

    5.2、在存儲過程下事務(wù),主要操作:在存儲過程中進(jìn)行事務(wù);通過DynamicParameters傳遞參數(shù)給存儲過程;通過Query調(diào)用存儲過程。

CREATE PROCEDURE [dbo].[InsertData]
    --Student
    @StudentName NVARCHAR(50),
    @Age SMALLINT,
    @Gender NVARCHAR(10),
    --Teacher
    @TeacherName NVARCHAR(50)
AS
BEGIN
    --變量定義
    DECLARE @Result BIT=1    --結(jié)果標(biāo)識
    
    --事務(wù)開始
    BEGIN TRANSACTION

    --數(shù)據(jù)插入
    INSERT INTO Student (Name,Age,Gender) VALUES (@StudentName,@Age,@Gender)
    INSERT INTO Teacher (Name) VALUES (@TeacherName)
    
    --事務(wù)執(zhí)行
    IF @@ERROR=0
    BEGIN
        COMMIT TRANSACTION
    END
    ELSE
    BEGIN
        SET @Result=0
        ROLLBACK TRANSACTION
    END

    --結(jié)果返回
    SELECT @Result
END
View Code
        /// <summary>
        /// 在存儲過程下事務(wù)
        /// </summary>
        /// <returns></returns>
        public bool InsertWithProcTran()
        {
            var para = new DynamicParameters();
            para.Add("StudentName", "Hanmeimei");
            para.Add("Age", 18);
            para.Add("Gender", "female");
            para.Add("TeacherName", "Angel");

            using (var conn = GetCon())
            {
                conn.Open();
                return conn.Query<bool>("InsertData", para, commandType: CommandType.StoredProcedure).FirstOrDefault();
            }
        }
View Code

    六、Paging

    6.1、簡單分頁

        /// <summary>
        /// 簡單分頁
        /// </summary>
        /// <param name="beginRowNum"></param>
        /// <param name="endRowNum"></param>
        /// <returns></returns>
        public IEnumerable<Student> GetPaging(int beginRowNum = 1, int endRowNum = 5)
        {
            string strSql =
                    "SELECT * FROM " +
                        "( " +
                            "SELECT A.*, ROW_NUMBER() OVER(ORDER BY A.StudentID) RowNum " +
                            "FROM Student AS A " +
                        ") B " +
                    "WHERE B.RowNum BETWEEN @BeginRowNum AND @EndRowNum " +
                    "ORDER BY B.RowNum ";

            using (var conn = GetCon())
            {
                return conn.Query<Student>(strSql, new { BeginRowNum = beginRowNum, EndRowNum = endRowNum });
            }
        }
View Code

    6.2、通用分頁

CREATE PROCEDURE [dbo].[PageList]
     @TableName VARCHAR(200),       --表名
     @FieldName VARCHAR(500) = '*', --字段名
     @Where VARCHAR(100) = NULL,    --條件語句
     @GroupBy VARCHAR(100) = NULL,  --分組字段
     @OrderBy VARCHAR(100),         --排序字段
     @PageIndex INT = 1,            --當(dāng)前頁數(shù)
     @PageSize INT = 20,            --每頁顯示記錄數(shù)
     @TotalCount INT = 0 OUTPUT     --總記錄數(shù)
AS
BEGIN
    --SQL拼接語句
    DECLARE @SQL NVARCHAR(4000)

    --總記錄數(shù)
    SET @SQL='SELECT @RecordCount=COUNT(1) FROM ' + @TableName
    IF (ISNULL(@Where,'')<>'')
        SET @SQL=@SQL+' WHERE '+@Where
    ELSE IF (ISNULL(@GroupBy,'')<>'')
        SET @SQL=@SQL+' GROUP BY '+@GroupBy

    EXEC SP_EXECUTESQL @SQL,N'@RecordCount INT OUTPUT',@TotalCount OUTPUT

    --總頁數(shù)
    DECLARE @PageCount INT
    SELECT @PageCount=CEILING((@TotalCount+0.0)/@PageSize)
    
    --簡單分頁
    SET @SQL='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderBy+') AS RowNum,' + @FieldName + ' FROM '+@TableName+' AS A'
    IF (ISNULL(@Where,'')<>'')
        SET @SQL=@SQL+' WHERE '+@Where
    ELSE IF (ISNULL(@GroupBy,'')<>'')
        SET @SQL=@SQL+' GROUP BY '+@GroupBy

    IF (@PageIndex<=0)
        SET @PageIndex=1
    IF @PageIndex>@PageCount
        SET @PageIndex=@PageCount
     
    DECLARE @BeginRowNum INT,@EndRowNum INT  
    SET @BeginRowNum=(@PageIndex-1)*@PageSize+1
    SET @EndRowNum=@BeginRowNum+@PageSize-1

    SET @SQL=@SQL + ') AS B WHERE B.RowNum BETWEEN '+CONVERT(VARCHAR(32),@BeginRowNum)+' AND '+CONVERT(VARCHAR(32),@EndRowNum)
    EXEC(@SQL)
END
View Code
        /// <summary>
        /// 通用分頁
        /// </summary>
        /// <returns></returns>
        public IEnumerable<T> GetCommonPaging<T>(string tableName, string fieldName, string where, string groupby, string orderby, int pageIndex, int pageSize)
        {
            var para = new DynamicParameters();
            para.Add("TableName", tableName);
            para.Add("FieldName", fieldName);
            para.Add("Where", where);
            para.Add("GroupBy", groupby);
            para.Add("OrderBy", orderby);
            para.Add("PageIndex", pageIndex);
            para.Add("PageSize", pageSize);
            para.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

            using (var conn = GetCon())
            {
                conn.Open();
                return conn.Query<T>("PageList", para, commandType: CommandType.StoredProcedure);
            }
        }
View Code

 

    本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間,所有內(nèi)容均由用戶發(fā)布,不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息,謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,請點擊一鍵舉報。
    轉(zhuǎn)藏 分享 獻(xiàn)花(0

    0條評論

    發(fā)表

    請遵守用戶 評論公約

    類似文章 更多

    在线播放欧美精品一区| 亚洲国产精品久久琪琪| 国产亚洲精品一二三区| 亚洲专区一区中文字幕| 色综合久久超碰色婷婷| 日本一本在线免费福利| 国产成人精品在线一区二区三区| 亚洲成人久久精品国产| 国产高清精品福利私拍| 日本加勒比在线观看一区| 国产成人高清精品尤物| 搡老熟女老女人一区二区| 午夜福利视频六七十路熟女| 91一区国产中文字幕| 国产精品偷拍视频一区| 日韩欧美中文字幕av| 国产又大又猛又粗又长又爽| 一区二区福利在线视频| 国产亚洲午夜高清国产拍精品| 色婷婷久久五月中文字幕| 熟妇久久人妻中文字幕| 欧美精品一区久久精品| 色一情一伦一区二区三| 国产亚洲视频香蕉一区| 日韩欧美亚洲综合在线| 久久精视频免费视频观看| 国产成人在线一区二区三区| 成人精品一区二区三区在线| 免费午夜福利不卡片在线 视频| 亚洲av日韩一区二区三区四区| 日本欧美视频在线观看免费| 国产欧美日韩一级小黄片| 精品推荐久久久国产av| 亚洲性日韩精品一区二区| 国产精品午夜小视频观看| 精品午夜福利无人区乱码| 欧美一区日韩二区亚洲三区| 四季精品人妻av一区二区三区| 五月的丁香婷婷综合网| 久久热麻豆国产精品视频| 国产又黄又猛又粗又爽的片|