Dapper是.NET下的一個(gè)micro ORM，它和Entity Framework或NHibnate不同，屬于輕量級并且是半自動(dòng)的（實(shí)體類(lèi)都要自己寫(xiě)）。假如你喜歡原生的Sql語(yǔ)句，又喜歡ORM的簡(jiǎn)單，那你一定會(huì )喜歡上Dapper這款ORM。
1）輕量。只有一個(gè)文件（SqlMapper.cs）。
2）速度快。Dapper的速度接近于IDataReader，取列表的數據超過(guò)了DataTable。
3）支持多種數據庫。包括SQLite、SqlCe、Firebird、Oracle、MySQL、PostgreSQL、SQL Server。
4）可以映射一對一、一對多、多對多等多種關(guān)系。
5）性能高。通過(guò)Emit反射IDataReader的序列隊列，來(lái)快速地得到和產(chǎn)生對象。
此處使用Dapper擴展庫Dapper.SimpleCRUD，它也會(huì )默認安裝Dapper（依賴(lài)項）：
項目右鍵->管理 NuGet 程序包->Dapper.SimpleCRUD。
在SQL Server中創(chuàng )建4個(gè)數據表，分別是：Student（學(xué)生表）、Teacher（教師表）、Course（課程表）、Record（成績(jì)表）。
--學(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]--成績(jì)表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é)生表數據插入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'離散數學(xué)',1UNIONSELECT N'程序設計',2UNIONSELECT N'數據結構',3--成績(jì)表數據插入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
Dapper的實(shí)體映射：
1）屬性不編輯，用[Editable(false)]這個(gè)特性標記，默認是true。
2）類(lèi)名到表名的映射，用[Table("TableName")]特性，TableName對應物理數據表名稱(chēng)。
3）主鍵映射，如果您的實(shí)體類(lèi)中有Id屬性，Dapper會(huì )默認此屬性為主鍵，否則要為作為主鍵的屬性添加[Key]特性。
由上可知，如Student表，其實(shí)體類(lèi)應該生成下面這個(gè)樣子：
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.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> /// 數據庫架構工廠(chǎng) /// </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 upclass 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; } } }}#>
DBSchema.ttinclude主要實(shí)現了數據庫工廠(chǎng)的功能。注：請將數據庫連接字符串改成您自己的。
MultiDocument.ttinclude主要實(shí)現了多文檔的功能。
2.3.2、生成實(shí)體類(lèi)的文本模板
<#@ 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自動(dòng)生成// 生成時(shí)間 <#= DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") #>// 對此文件的更改可能會(huì )導致不正確的行為，并且如果重新生成代碼，這些更改將會(huì )丟失。//-------------------------------------------------------------------------------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) { //獲取數據類(lèi)型 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);#>
在A(yíng)pp.config中添加數據庫連接字符串：
<?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>
添加一個(gè)DapperHelper類(lèi)，實(shí)現數據庫連接及后續的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; } }}
a1）通過(guò)SQL插入單條數據(帶參數)，返回結果是影響行數。
/// <summary> /// 通過(guò)SQL插入單條數據(帶參數)，返回結果是影響行數。 /// </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" }); } }
a2）通過(guò)SQL插入單條數據(帶實(shí)體)，返回結果是影響行數。
/// <summary> /// 通過(guò)SQL插入單條數據(帶實(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); } }
a3）通過(guò)SQL插入單條數據(帶實(shí)體)，返回主鍵值。
/// <summary> /// 通過(guò)SQL插入單條數據(帶實(shí)體)，返回主鍵值。 /// </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); } }
a4）通過(guò)SQL插入多條數據(帶實(shí)體)，返回結果是影響行數。
/// <summary> /// 通過(guò)SQL插入多條數據(帶實(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); } }
b）通過(guò)實(shí)體插入數據，返回結果是主鍵值。
/// <summary> /// 通過(guò)實(shí)體插入數據，返回結果是主鍵值。 /// </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); } }
a1）通過(guò)SQL查詢(xún)數據（查詢(xún)所有數據）
/// <summary> /// 通過(guò)SQL查詢(xún)數據(查詢(xún)所有數據) /// </summary> /// <returns></returns> public IEnumerable<Student> GetStudentList1() { string strSql = "SELECT * FROM Student"; using (var conn = GetCon()) { conn.Open(); return conn.Query<Student>(strSql); } }
a2）通過(guò)SQL查詢(xún)數據（帶參數）
/// <summary> /// 通過(guò)SQL查詢(xún)數據(帶參數) /// </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(); } }
a3）通過(guò)SQL查詢(xún)數據（IN）
/// <summary> /// 通過(guò)SQL查詢(xún)數據(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 }); } }
b1）通過(guò)實(shí)體查詢(xún)數據（查詢(xún)所有數據）
/// <summary> /// 通過(guò)實(shí)體詢(xún)數據(查詢(xún)所有數據) /// </summary> /// <returns></returns> public IEnumerable<Student> GetStudentList2() { using (var conn = GetCon()) { conn.Open(); return conn.GetList<Student>(); } }
b2）通過(guò)實(shí)體查詢(xún)數據（指定ID）
/// <summary> /// 通過(guò)實(shí)體詢(xún)數據(指定ID) /// </summary> /// <param name="studentID"></param> /// <returns></returns> public Student GetStudentList2A(int studentID) { using (var conn = GetCon()) { conn.Open(); return conn.Get<Student>(studentID); } }
b3）通過(guò)實(shí)體查詢(xún)數據（帶參數）
/// <summary> /// 通過(guò)實(shí)體詢(xún)數據(帶參數) /// </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(); } }
c1）多表查詢(xún)(QueryMultiple)，主要操作：通過(guò)QueryMultiple方法，返回查詢(xún)中每條SQL語(yǔ)句的數據集合。
/// <summary> /// 多表查詢(xún)(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(); } }
c2）多表查詢(xún)(Query)，主要操作：通過(guò)SQL進(jìn)行多表關(guān)聯(lián)查詢(xún)，返回查詢(xún)結果的數據集合。
/// <summary> /// 多表查詢(xún)(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(); } }
a1）通過(guò)SQL更新數據(帶參數)，返回結果是影響行數。
/// <summary> /// 通過(guò)SQL更新數據(帶參數)，返回結果是影響行數。 /// </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 }); } }
a2）通過(guò)SQL插入單條數據(帶實(shí)體)，返回結果是影響行數。
/// <summary> /// 通過(guò)SQL更新數據(帶實(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); } }
b）通過(guò)實(shí)體更新數據，返回結果是影響行數。
/// <summary> /// 通過(guò)實(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); } }
a）通過(guò)SQL刪除數據(帶參數)，返回結果是影響行數。
/// <summary> /// 通過(guò)SQL刪除數據(帶參數)，返回結果是影響行數。 /// </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 }); } }
b）通過(guò)實(shí)體刪除數據，返回結果是影響行數。
/// <summary> /// 通過(guò)實(shí)體刪除數據，返回結果是影響行數。 /// </summary> /// <returns></returns> public int? DeleteWithEntity() { using (var conn = GetCon()) { conn.Open(); var entity = new Student { StudentID = 17 }; return conn.Delete(entity); } }
4.1、帶輸出參數的存儲過(guò)程
CREATE PROCEDURE [dbo].[GetStudentAge] @StudentID INT, @Name NVARCHAR(50) OUTPUTASBEGIN DECLARE @Age SMALLINT SELECT @Name=Name,@Age=Age FROM Student WHERE StudentID=@StudentID SELECT @AgeEND
/// <summary> /// 帶輸出參數的存儲過(guò)程 /// </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); }
5.1、在IDbConnection下事務(wù)，主要操作：在執行Insert方法時(shí)傳入Transaction；在正常情況下Commit事務(wù)；在異常時(shí)回滾事務(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; } }
5.2、在存儲過(guò)程下事務(wù)，主要操作：在存儲過(guò)程中進(jìn)行事務(wù)；通過(guò)DynamicParameters傳遞參數給存儲過(guò)程；通過(guò)Query調用存儲過(guò)程。
CREATE PROCEDURE [dbo].[InsertData] --Student @StudentName NVARCHAR(50), @Age SMALLINT, @Gender NVARCHAR(10), --Teacher @TeacherName NVARCHAR(50)ASBEGIN --變量定義 DECLARE @Result BIT=1 --結果標識 --事務(wù)開(kāi)始 BEGIN TRANSACTION --數據插入 INSERT INTO Student (Name,Age,Gender) VALUES (@StudentName,@Age,@Gender) INSERT INTO Teacher (Name) VALUES (@TeacherName) --事務(wù)執行 IF @@ERROR=0 BEGIN COMMIT TRANSACTION END ELSE BEGIN SET @Result=0 ROLLBACK TRANSACTION END --結果返回 SELECT @ResultEND
/// <summary> /// 在存儲過(guò)程下事務(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(); } }
6.1、簡(jiǎn)單分頁(yè)
/// <summary> /// 簡(jiǎn)單分頁(yè) /// </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 }); } }
6.2、通用分頁(yè)
CREATE PROCEDURE [dbo].[PageList] @TableName VARCHAR(200), --表名 @FieldName VARCHAR(500) = '*', --字段名 @Where VARCHAR(100) = NULL, --條件語(yǔ)句 @GroupBy VARCHAR(100) = NULL, --分組字段 @OrderBy VARCHAR(100), --排序字段 @PageIndex INT = 1, --當前頁(yè)數 @PageSize INT = 20, --每頁(yè)顯示記錄數 @TotalCount INT = 0 OUTPUT --總記錄數ASBEGIN --SQL拼接語(yǔ)句 DECLARE @SQL NVARCHAR(4000) --總記錄數 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 --總頁(yè)數 DECLARE @PageCount INT SELECT @PageCount=CEILING((@TotalCount+0.0)/@PageSize) --簡(jiǎn)單分頁(yè) 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
/// <summary> /// 通用分頁(yè) /// </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); } }
聯(lián)系客服
