dapper mysql 批量_Dapper批量更新

本次示例项目使用Dappe1.50.5和Dapper.Contrib1.50.5

数据库执行的脚本检测工具是SQL Server Prifiler

1.使用Where In 实现批量更新

1.1代码如下：

///

/// 把多个用户的isvalid置为0

///

public void UpdateUsersByWhereInTest()

{

var sql = @"UPDATE dbo.[user] SET isvalid=0 WHERE UserId IN @UserId ;";

var userIdArr = new int[] { 3013, 3012, 3011, 3010 };

var res = Repository.Execute(sql, new { UserId = userIdArr });

Assert.True(res > 0);

}

///查询多条记录

List vs = new List();

string sql = " select * from Special_attributes where SattrId in @vs";

arrtModels = SqlConnectionFactory.Connection.Query(sql, new { vs } ).ToList();

1.2检测到数据库执行的脚本

exec sp_executesql N'UPDATE dbo.[user]

SET isvalid=0

WHERE UserId IN (@UserId1,@UserId2,@UserId3,@UserId4) ;',

N'@UserId1 int,@UserId2 int,

@UserId3 int,@UserId4 int',

@UserId1=3013,@UserId2=3012,

@UserId3=3011,@UserId4=3010

2.更新多条数据，每一条数据更新的内容可不相同

2.1代码如下

///

/// 分别把用户Id是3013的用户名称更新为张三，用户Id是3012的用户名称更新为李四

///

[Fact]

public void UpdateUsersTest()

{

var sql = @"UPDATE dbo.[user] SET UserName=@UserName WHERE UserId = @UserId ;";

var users = new List {

new User { UserId=3013, UserName = "张三", },

new User { UserId =3012, UserName = "李四", },

};

var res = Repository.Execute(sql,users);

Assert.True(res > 0);

}

// 6、批量删除

public int Delete_Shopping(List CommodityID)

{

string sql = string.Empty;

sql += " delete ShoppingCommodity where CommodityID in @CommodityIDs";

int j = SqlConnectionFactory.Connection.Execute(sql,new { CommodityIDs = CommodityID.ToArray() });

return j;

}

// 7、 用where 的in 查询    参数得是数组类型  并且参数不加括号

// 8 、批量添加  List 当作参数  list里某个属性的值只能有一个

2.2检测到数据库执行的脚本

生成两条Update脚本执行

exec sp_executesql N'UPDATE dbo.[user]

SET UserName=@UserName

WHERE UserId = @UserId ;',

N'@UserId int,@UserName nvarchar(4000)',

@UserId=3013,@UserName=N'张三'

exec sp_executesql N'UPDATE dbo.[user]

SET UserName=@UserName

WHERE UserId = @UserId ;',

N'@UserId int,@UserName nvarchar(4000)',

@UserId=3012,@UserName=N'李四'