时间:2018年05月28日 | 作者 : aaronyang | 分类 : C#开发 | 浏览: 1124次 | 评论 0 人
====================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#,”的文章
合肥科大智能常年招聘C# .NET CORE,vue前端,JAVA开发,有想换工作的私聊我和AY交流
抖音号:wpfui,可以看到我的很多作品效果,私活合作请qq联系我
AYUI8社区版Github地址:前往获取
作者:杨洋(AaronYang简称AY,安徽六安人)目前是个人,还没公司和AY交流
高中学历,2015年1月17日开始,兴趣学习研究WPF,目前工作繁忙,不接任何活
声明:AYUI7个人与商用免费,源码可购买。部分DEMO不免费.AY主要靠卖技术服务挣钱
不是从我处购买的ayui7源码,我不提供任何技术服务,如果你举报从哪里买的,我可以帮你转正为我的客户,并送demo
查看捐赠AYUI7.X MVC教程 更新如下:
第一课 第二课 程序加密教程vs2015 企业版密钥HM6NR-QXX7C-DFW2Y-8B82K-WTYJV
vs2017 企业版密钥NJVYC-BMHX2-G77MM-4XJMR-6Q8QF
额 本文暂时没人评论 来添加一个吧
发表评论