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(); } } } #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 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; } } }}#>
MultiDocument.ttinclude
DBSchema.ttinclude主要实现了数据库工厂的功能。注:请将数据库连接字符串改成您自己的。
MultiDocument.ttinclude主要实现了多文档的功能。
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();//调试 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") #>// 对此文件的更改可能会导致不正确的行为,并且如果重新生成代码,这些更改将会丢失。//-------------------------------------------------------------------------------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) { //获取数据类型 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中添加数据库连接字符串:
<?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类,实现数据库连接及后续的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插入单条数据(带参数),返回结果是影响行数。
/// <summary> /// 通过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" }); } }
View Code
a2)通过SQL插入单条数据(带实体),返回结果是影响行数。
/// <summary> /// 通过SQL插入单条数据(带实体),返回结果是影响行数。 /// </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插入单条数据(带实体),返回主键值。
/// <summary> /// 通过SQL插入单条数据(带实体),返回主键值。 /// </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插入多条数据(带实体),返回结果是影响行数。
/// <summary> /// 通过SQL插入多条数据(带实体),返回结果是影响行数。 /// </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)通过实体插入数据,返回结果是主键值。
/// <summary> /// 通过实体插入数据,返回结果是主键值。 /// </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查询数据(查询所有数据)
/// <summary> /// 通过SQL查询数据(查询所有数据) /// </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查询数据(带参数)
/// <summary> /// 通过SQL查询数据(带参数) /// </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查询数据(IN)
/// <summary> /// 通过SQL查询数据(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)通过实体查询数据(查询所有数据)
/// <summary> /// 通过实体询数据(查询所有数据) /// </summary> /// <returns></returns> public IEnumerable<Student> GetStudentList2() { using (var conn = GetCon()) { conn.Open(); return conn.GetList<Student>(); } }
View Code
b2)通过实体查询数据(指定ID)
/// <summary> /// 通过实体询数据(指定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)通过实体查询数据(带参数)
/// <summary> /// 通过实体询数据(带参数) /// </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语句的数据集合。
/// <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进行多表关联查询,返回查询结果的数据集合。
/// <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更新数据(带参数),返回结果是影响行数。
/// <summary> /// 通过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 }); } }
View Code
a2)通过SQL插入单条数据(带实体),返回结果是影响行数。
/// <summary> /// 通过SQL更新数据(带实体),返回结果是影响行数。 /// </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)通过实体更新数据,返回结果是影响行数。
/// <summary> /// 通过实体更新数据,返回结果是影响行数。 /// </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删除数据(带参数),返回结果是影响行数。
/// <summary> /// 通过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 }); } }
View Code
b)通过实体删除数据,返回结果是影响行数。
/// <summary> /// 通过实体删除数据,返回结果是影响行数。 /// </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、带输出参数的存储过程
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
View Code
/// <summary> /// 带输出参数的存储过程 /// </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下事务,主要操作:在执行Insert方法时传入Transaction;在正常情况下Commit事务;在异常时回滚事务。
/// <summary> /// 在IDbConnection下事务 /// </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、在存储过程下事务,主要操作:在存储过程中进行事务;通过DynamicParameters传递参数给存储过程;通过Query调用存储过程。
CREATE PROCEDURE [dbo].[InsertData] --Student @StudentName NVARCHAR(50), @Age SMALLINT, @Gender NVARCHAR(10), --Teacher @TeacherName NVARCHAR(50)ASBEGIN --变量定义 DECLARE @Result BIT=1 --结果标识 --事务开始 BEGIN TRANSACTION --数据插入 INSERT INTO Student (Name,Age,Gender) VALUES (@StudentName,@Age,@Gender) INSERT INTO Teacher (Name) VALUES (@TeacherName) --事务执行 IF @@ERROR=0 BEGIN COMMIT TRANSACTION END ELSE BEGIN SET @Result=0 ROLLBACK TRANSACTION END --结果返回 SELECT @ResultEND
View Code
/// <summary> /// 在存储过程下事务 /// </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, --当前页数 @PageSize INT = 20, --每页显示记录数 @TotalCount INT = 0 OUTPUT --总记录数ASBEGIN --SQL拼接语句 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 --总页数 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