原文:
AY的Dapper研究学习-基本入门-C#开发-aaronyang技术分享
====================www.ayjs.net 杨洋 wpfui.com ayui ay aaronyang=======请不要转载谢谢了。=========
本文，是AY 看github源码，和网上一些博客，文章 整理，写出来的。目前还没找到系统级别的教程
先建立.net framework4.6的控制台项目,添加nuget引用
新建了 Sqlserver的数据库TestDapper
SQL
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]
连接：
C#
var con = new SqlConnection(ConfigurationManager.ConnectionStrings["sql"].ConnectionString);新增后，返回新增ID- MsSql：
C#
var id = con.QueryFirst<int>("insert into users values('ay', 'ay2015@qq.com', '合肥市润安大厦');select @@IDENTITY;");新增后，返回新增ID- MySql：
C#
var id = con.QueryFirst<int>("insert into users values(null, 'ay', 'ay2015@qq.com', '合肥市润安大厦');select last_insert_id();");参数赋值，Query返回一个列表，这里Users这个Model是自己声明的：
C#
var u = con.Query<Users>("select * from Users where UserID = @UserID", new { UserID = 1 });
Console.WriteLine(u.First().UserName);
使用Execute执行 更删改sql，返回int，应该是受影响的行数
通过Execute执行update
C#
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);
删除
C#
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);
}
增加数据
C#
var id = con.QueryFirst<int>("insert into users values('ay163', 'ay163@163.com', '合肥市润安大厦11');select @@IDENTITY;");IN操作的处理
C#
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);
}插入一条数据
C#
var id = con.Execute($"insert into Product values('WCF编程宝典', 'WCF基本入门的一本书籍', 1,'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')");多条sql查询反馈，我感觉这个设计真的好
C#
//多条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
C#
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张表以上的列成员组成的就
如果你觉得很难理解，你可以新建一个类，把你的要返回的列，写成对应属性，效果一样的。
C#
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);
}
C#
public class TAY
{
public string ProductName { get; set; }
public DateTime CreateTime { get; set; }
public string UserName { get; set; }
}执行存储过程，根据ID查询用户
SQL
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
代码简单，没的讲
C#
var userList = con.Query<Users>("sp_GetUsers", new { id = 1 },
commandType: CommandType.StoredProcedure);
foreach (var item in userList)
{
Console.WriteLine(item.UserName);
}批量插入怎么写？
C#
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);
}我个人觉得不太喜欢，查询如下
C#
var userList = con.Query<Users>("select * from users where UserName like '%测试%'");
foreach (var item in userList)
{
Console.WriteLine(item.UserName);
}
C#
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
C#
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#，”的文章
