AY的Dapper研究学习-基本入门-C#开发-aaronyang技术分享

原文: AY的Dapper研究学习-基本入门-C#开发-aaronyang技术分享

====================www.ayjs.net       杨洋    wpfui.com        ayui      ay  aaronyang=======请不要转载谢谢了。=========

本文，是AY 看github源码，和网上一些博客，文章 整理，写出来的。目前还没找到系统级别的教程

先建立.net framework4.6的控制台项目,添加nuget引用

新建了 Sqlserver的数据库TestDapper  

CREATE TABLE [dbo].[Users]( [UserID] [int] IDENTITY(1,1) NOT NULL, [UserName] [varchar](50) NULL, [Email] [varchar](100) NULL, [Address] [varchar](100) NULL, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserID] 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].[Product]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [ProductName] [varchar](220) NULL, [ProductDesc] [varchar](220) NULL, [UserID] [int] NULL, [CreateTime] [datetime] NULL, CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

连接：

 var con = new SqlConnection(ConfigurationManager.ConnectionStrings["sql"].ConnectionString);

新增后，返回新增ID-  MsSql：

 var id = con.QueryFirst<int>("insert into users values('ay', 'ay2015@qq.com', '合肥市润安大厦');select @@IDENTITY;");

新增后，返回新增ID-  MySql：

var id = con.QueryFirst<int>("insert into users values(null, 'ay', 'ay2015@qq.com', '合肥市润安大厦');select last_insert_id();");

参数赋值，Query返回一个列表，这里Users这个Model是自己声明的：

var u = con.Query<Users>("select * from Users where UserID = @UserID", new { UserID = 1 }); Console.WriteLine(u.First().UserName);

使用Execute执行 更删改sql，返回int，应该是受影响的行数

通过Execute执行update

 con.Execute("update users set UserName = '李四川' where UserId = @UserId", new { UserId = 3 }); var u = con.Query<Users>("select * from Users where UserId = @UserId", new { UserId = 3}); Console.WriteLine(u.First().UserName);

删除

 con.Execute("delete from users where UserId = @UserId", new { UserId = 3 }); var us = con.Query<Users>("select * from Users"); foreach (var item in us) { Console.WriteLine(item.UserName); }

增加数据

 var id = con.QueryFirst<int>("insert into users values('ay163', 'ay163@163.com', '合肥市润安大厦11');select @@IDENTITY;");

IN操作的处理

 var uss = con.Query<Users>("select * from Users where UserId in @UserIds", new { UserIds = new List<int> {1,2 } }); foreach (var item in uss) { Console.WriteLine(item.UserName); }

插入一条数据

 var id = con.Execute($"insert into Product values('WCF编程宝典', 'WCF基本入门的一本书籍', 1,'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')");

多条sql查询反馈，我感觉这个设计真的好

 //多条sql执行 var sql = "select * from Product; select * from Users"; using (var multiReader = con.QueryMultiple(sql)) { var productList = multiReader.Read<Product>(); var userList = multiReader.Read<Users>(); foreach (var item in productList) { Console.WriteLine(item.ProductName); } foreach (var item in userList) { Console.WriteLine(item.UserName); } }

关于多表查询的，假如1个表有个导航属性

Product加一个Users类型的User属性，使用 splitOn

 var sql = @"select p.ProductName,p.CreateTime,u.UserName from Product as p join Users as u on p.UserID = u.UserID where p.CreateTime > '2015-12-12'; "; var productList = con.Query<Product, Users, Product>(sql, (product, users) => { product.User = users; return product; }, splitOn: "UserName"); foreach (var item in productList) { Console.WriteLine(item.ProductName+" "+item.User.UserName); }

我把sql语句的createtime和u.username位置换了下

效果，日期没了，

如果位置换回来，又有了，说明是从右往左  分割复制的，只要列 不要是由2张表以上的列成员组成的就

如果你觉得很难理解，你可以新建一个类，把你的要返回的列，写成对应属性，效果一样的。

 var sql = @"select p.ProductName,p.CreateTime,u.UserName from Product as p join Users as u on p.UserID = u.UserID where p.CreateTime > '2015-12-12'; "; var productList = con.Query<TAY>(sql); foreach (var item in productList) { Console.WriteLine(item.ProductName + " " + item.CreateTime + " " + item.UserName); }

 public class TAY { public string ProductName { get; set; } public DateTime CreateTime { get; set; } public string UserName { get; set; } }

执行存储过程，根据ID查询用户

USE TestDapper GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create proc [dbo].[sp_GetUsers] @id int as begin select * from Users where UserID = @id ; end

代码简单，没的讲

 var userList = con.Query<Users>("sp_GetUsers", new { id = 1 }, commandType: CommandType.StoredProcedure); foreach (var item in userList) { Console.WriteLine(item.UserName); }

批量插入怎么写？

 List<Users> user = new List<Users>(); for (int i = 1; i <= 1000; i++) { Users u = new Users(); u.UserName = "AY " + i; u.Email = "EM " + i; u.Address = "AD " + i; user.Add(u); } con.Execute("insert users(UserName,Email,Address) values(@UserName,@Email,@Address)", user); var userList = con.Query<Users>("select * from users"); foreach (var item in userList) { Console.WriteLine(item.UserName); }

我个人觉得不太喜欢，查询如下

 var userList = con.Query<Users>("select * from users where UserName like '%测试%'"); foreach (var item in userList) { Console.WriteLine(item.UserName); }

 System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); StringBuilder sb = new StringBuilder("insert users(UserName,Email,Address) "); for (int i = 1; i <= 1000; i++) { if (i == 1000) sb.Append(string.Format("select '{0}','{1}','{2}' ", "用户" + i, "邮箱" + i, "地址" + i)); else { sb.Append(string.Format("select '{0}','{1}','{2}' UNION ", "用户" + i, "邮箱" + i, "地址" + i)); } } sw.Stop(); Console.WriteLine(sw.ElapsedMilliseconds.ToString()); sw.Start(); var _resultCount = con.Execute(sb.ToString()); Console.WriteLine(_resultCount); sw.Stop(); Console.WriteLine(sw.ElapsedMilliseconds.ToString());

当然肯定是不推荐以上两种，推荐SqlServer的BulkCopy

 public bool SqlServerBulkInsert(DataTable table, string connectionString) { try { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlTransaction trans = conn.BeginTransaction(); SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans); // 设置源表名称 sqlbulkCopy.DestinationTableName = table.TableName; //分几次拷贝 //sqlbulkCopy.BatchSize = 10; // 设置超时限制 sqlbulkCopy.BulkCopyTimeout = CommandTimeOut; foreach (DataColumn dtColumn in table.Columns) { sqlbulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName); } try { // 写入 sqlbulkCopy.WriteToServer(table); // 提交事务 trans.Commit(); return true; } catch(Exception ex) { trans.Rollback(); sqlbulkCopy.Close(); return false; } finally { conn.Close(); conn.Dispose(); sqlbulkCopy.Close(); } } } catch (Exception e) { return false; } }

====================www.ayjs.net       杨洋    wpfui.com        ayui      ay  aaronyang=======请不要转载谢谢了。=========

我先写这么多吧，下篇博客继续

推荐您阅读更多有关于“C#，”的文章