最新冠状新型病毒最新消息/网站seo优化网站
ORM框架之Dapper，项目测试环境：.NET Core2.0 ConsoleApp应用；
新建ConsoleApp应用（项目命名DapperApp），添加NuGet引用，如下图所示：
项目添加=》新建文件夹=》Model添加实体类：DETAILS和TB_TYPE，数据库对应表模型。
public class DETAILS{public string Id { get; set; }public string Title { get; set; }public string Mark { get; set; }public string Content { get; set; }public DateTime Date { get; set; }public int ReadCount { get; set; }public string Writer { get; set; }public short TypeId { get; set; }public string PartContent { get; set; }public short SubId { get; set; }}public class TB_TYPE
{public short Id { get; set; }public string TypeName { get; set; }
}在Program的Main方法中添加测试：
using System;
using Dapper;
using System.Data;
using System.Data.SqlClient;
using DapperApp.Model;
using System.Collections.Generic;
using Newtonsoft.Json;
using System.Threading.Tasks;namespace DapperApp
{class Program{private static string sqlconnStr = "Server=.;Database=Test;uid=sa;pwd=123456";static void Main(string[] args){Console.WriteLine("Hello Dapper!");//Query();//QueryAsync();QueryJoin();//QueryInData();//QueryMultiTable();//QueryMultiTableAsync();//Insert();//Update();//UpdateKeyVal();//Delete();Proc();}/// <summary>/// T-SQL查询，返回单一数据集/// </summary>private static void Query(){using (var db = new SqlConnection(sqlconnStr)){string sql = "SELECT [ID],[TITLE],[MARK],[CONTENT],[DATE],[READCOUNT],[WRITER],[TYPEID],[PARTCONTENT],[SUBID] FROM [dbo].[TB_DETAILS]";var query = db.Query<DETAILS>(sql);foreach (var item in query){var str = $"{item.Id} {item.Title} {item.Mark} {item.Content} {item.Date} {item.ReadCount} {item.Writer} {item.TypeId} {item.PartContent} {item.SubId}";Console.WriteLine(str);}}}/// <summary>/// 异步T-SQL查询，返回单一数据集/// </summary>/// <returns></returns>private async static Task QueryAsync(){using (var db = new SqlConnection(sqlconnStr)){string sql = "SELECT [ID],[TITLE],[MARK],[CONTENT],[DATE],[READCOUNT],[WRITER],[TYPEID],[PARTCONTENT],[SUBID] FROM [dbo].[TB_DETAILS]";var query = await db.QueryAsync<DETAILS>(sql);foreach (var item in query){var str = $"{item.Id} {item.Title} {item.Mark} {item.Content} {item.Date} {item.ReadCount} {item.Writer} {item.TypeId} {item.PartContent} {item.SubId}";Console.WriteLine(str);}}}/// <summary>/// T-SQL-IN查询，返回单一数据集/// </summary>private static void QueryInData(){using (var db = new SqlConnection(sqlconnStr)){///注意sql语句底层会包装T-SQL-IN语句string sql = "SELECT [TITLE],[MARK],[CONTENT],[DATE] FROM [dbo].[TB_DETAILS] WHERE [MARK] IN @MARK";///匿名类参数var query = db.Query<DETAILS>(sql, new { mark = new string[3] { "李四测试", "李四测试2", "afd" } });foreach (var item in query){var str = $"{item.Title} {item.Mark} {item.Content} {item.Date}";Console.WriteLine(str);}}}/// <summary>/// 多表查询，返回多数据集/// </summary>private static void QueryMultiTable(){using (var db = new SqlConnection(sqlconnStr)){///多sql语句执行，返回多数据集string sql = "SELECT * FROM [dbo].[TB_DETAILS];SELECT * FROM [dbo].[TB_TYPE]";///匿名类参数var queryMulti = db.QueryMultiple(sql);var details = queryMulti.Read<DETAILS>();var type = queryMulti.Read<TB_TYPE>();//处理键值对模型对象List<KeyValuePair<string, object>> list = new List<KeyValuePair<string, object>>();list.Add(new KeyValuePair<string, object>("details", details));list.Add(new KeyValuePair<string, object>("type", type));AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = list, Sum = list.Count };//string jsonStr = JsonConvert.SerializeObject(ajaxResult);Console.WriteLine($"{JsonConvert.SerializeObject(ajaxResult)}");}}/// <summary>/// 异步多表查询，返回多数据集/// </summary>private async static Task QueryMultiTableAsync(){using (var db = new SqlConnection(sqlconnStr)){///多sql语句执行，返回多数据集string sql = "SELECT * FROM [dbo].[TB_DETAILS];SELECT * FROM [dbo].[TB_TYPE]";///匿名类参数var queryMulti = await db.QueryMultipleAsync(sql);var details = queryMulti.ReadAsync<DETAILS>();var type = queryMulti.ReadAsync<TB_TYPE>();List<KeyValuePair<string, object>> list = new List<KeyValuePair<string, object>>();list.Add(new KeyValuePair<string, object>("details", details));list.Add(new KeyValuePair<string, object>("type", type));AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = list, Sum = list.Count };//string jsonStr = JsonConvert.SerializeObject(ajaxResult);Console.WriteLine($"{JsonConvert.SerializeObject(ajaxResult)}");}}/// <summary>/// T-SQL-JOIN语句/// </summary>private static void QueryJoin(){using (var db = new SqlConnection(sqlconnStr)){///注意sql语句底层会包装T-SQL-JOIN语句string sql = "SELECT A.TITLE,A.MARK,A.[DATE],B.TYPENAME FROM [dbo].[TB_DETAILS] AS A JOIN [dbo].[TB_TYPE] AS B ON A.TYPEID = B.ID WHERE B.TYPENAME = '设计'";//DETAILS：实体模型，TB_TYPE：实体模型，DETAILS：返回实体模型var result = db.Query<DETAILS, TB_TYPE, DETAILS>(sql,(details,users) => {//对应参数details-》DETAILS，users -》TB_TYPEdetails.TypeId = users.Id;return details; //返回DETAILS},splitOn:"TypeName"); //以TypeName分割AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = result, Sum = 0 };//string jsonStr = JsonConvert.SerializeObject(ajaxResult);Console.WriteLine($"{JsonConvert.SerializeObject(ajaxResult)}");}}/// <summary>/// 单语句sql添加操作。返回受影响行数/// </summary>private static void Insert(){using (var db = new SqlConnection(sqlconnStr)){string sql = "INSERT INTO [dbo].[TB_DETAILS](ID,TITLE,MARK) VALUES(@ID,@TITLE,@MARK)";///匿名类模型int count = db.Execute(sql, new{ID = "1a33ed8f501b475dac40cc5ecf728306",TITLE = "Test",MARK = "afd"});AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = count, Sum = count };}}/// <summary>/// 单条或多条数据添加/// </summary>private static void InsertBulk(){using (var db = new SqlConnection(sqlconnStr)){string sql = "INSERT INTO [dbo].[TB_DETAILS]([ID],[TITLE],[MARK],[CONTENT],[DATE],[READCOUNT],[WRITER],[TYPEID],[PARTCONTENT] ,[SUBID]) VALUES(@ID,@TITLE,@MARK,@CONTENT,@DATE,@READCOUNT,@WRITER,@TYPEID,@PARTCONTENT,@SUBID)";//实体类模型对象DETAILS details = new DETAILS(){Id = "1a33ed8f501b475dac40cc5ecf728308",Title = "Test",Mark = "afd",Content = "sf",Date = DateTime.Now,ReadCount = 3,Writer = "zuzhe",TypeId = 5,PartContent = "sdfds",SubId = 2};var c = db.Execute(sql,details); //单条数据添加List<DETAILS> list = new List<DETAILS>() {new DETAILS{Id = "1a33ed8f501b475dac40cc5ecf728309",Title ="Test", Mark = "afd",Content ="sf",Date = DateTime.Now,ReadCount = 3,Writer = "zuzhe",TypeId = 5,PartContent = "sdfds",SubId = 2},new DETAILS{Id = "1a33ed8f501b475dac40cc5ecf728310",Title ="Test", Mark = "afd",Content ="sf",Date = DateTime.Now,ReadCount = 3,Writer = "zuzhe",TypeId = 5,PartContent = "sdfds",SubId = 2},new DETAILS{Id = "1a33ed8f501b475dac40cc5ecf728311",Title ="Test", Mark = "afd",Content ="sf",Date = DateTime.Now,ReadCount = 3,Writer = "zuzhe",TypeId = 5,PartContent = "sdfds",SubId = 2}};int count = db.Execute(sql, list); //多条数据添加AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = count, Sum = count };}}/// <summary>/// 单条语句更新/// </summary>private static void Update(){using (var db = new SqlConnection(sqlconnStr)){string sql = "UPDATE [dbo].[TB_DETAILS] SET [MARK] = @MARK WHERE [ID] = @ID";//匿名对象var count = db.Execute(sql, new {MARK= "张三", //参数占位符映射ID = "1a33ed8f501b475dac40cc5ecf728305"});AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = count, Sum = count };}}/// <summary>/// 键值对操作更新/// </summary>private static void UpdateKeyVal(){using (var db = new SqlConnection(sqlconnStr)){string sql = "UPDATE [dbo].[TB_DETAILS] SET [MARK] = @MARK WHERE [ID] = @ID";List<KeyValuePair<string, object>> keys = new List<KeyValuePair<string, object>>();keys.Add(new KeyValuePair<string, object>("@MARK","李四测试"));keys.Add(new KeyValuePair<string, object>("@ID", "1a33ed8f501b475dac40cc5ecf728305"));///注意，键值对操作更新 会执行覆盖，以最后一组键值对更新//keys.Add(new KeyValuePair<string, object>("@MARK", "李四测试2"));//keys.Add(new KeyValuePair<string, object>("@ID", "1a33ed8f501b475dac40cc5ecf728306"));var count = db.Execute(sql, keys);AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = count, Sum = count };}}/// <summary>/// 删除操作/// </summary>private static void Delete(){using (var db = new SqlConnection(sqlconnStr)){string sql = "delete [dbo].[TB_DETAILS] where [ID] = @ID";///匿名对象var count = db.Execute(sql,new { Id= "sdfsd" });AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = count, Sum = count };}}/// <summary>/// 存储过程操作/// </summary>private static void Proc(){using (var db = new SqlConnection(sqlconnStr)){string sql = "sp_details"; //存储过程名称///匿名对象,使用存储过程时，查询显示指明存储过程操作var deails = db.Query<DETAILS>(sql,new { typeid = 2 },commandType: CommandType.StoredProcedure);AjaxResult ajaxResult = new AjaxResult() { Code = "0", Msg = "OK", Data = deails, Sum = 0 };//string jsonStr = JsonConvert.SerializeObject(ajaxResult);Console.WriteLine($"{JsonConvert.SerializeObject(ajaxResult)}");}}private static void Test(){using (var db = new SqlConnection(sqlconnStr)){//测试连接}}}public class AjaxResult{public string Code { get; set; }public string Msg { get; set; }public object Data { get; set; }public int Sum { get; set; }}
}