数据的更新操作包括:Insert、Delete、Update
在Dapper中,我们使用提供的重载的扩展方法:
//
// 摘要:
// Execute parameterized SQL.
//
// 参数:
// cnn:
// The connection to query on.
//
// sql:
// The SQL to execute for this query.
//
// param:
// The parameters to use for this query.
//
// transaction:
// The transaction to use for this query.
//
// commandTimeout:
// Number of seconds before command execution timeout.
//
// commandType:
// Is it a stored proc or a batch?
//
// 返回结果:
// The number of rows affected.
public static int Execute(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
其方法返回值:受影响行数
/// <summary>
/// 批量处理
/// 执行更新操作(增、删、改),不带事务处理
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="sql">SQL语句</param>
/// <param name="t">泛型对象</param>
public static void TestDapperUpdate<T>(string sql,List<T> t) {
using (SqlConnection connect = new SqlConnection(ConnectStr)) {
int line=connect.Execute(sql,t);//调用Dapper的扩展方法Execute()执行更新操作,提交到数据库
if (line>0) {
Console.WriteLine("数据成功更新!");
}
}
}
我这里封装的是一个泛型方法:为了整个程序的扩展性,并且第二个参数为List
插入单条记录:
//插入单条记录
string sql = @"INSERT INTO ProductType(TypeName) VALUES(@TypeName)";
List<ProductType> ptList = new List<ProductType>() {
new ProductType(){TypeName="男装"}
};
TestDapperUpdate(sql,ptList);
Line Value:
批量插入数据:
//批量插入多条数据
string sql = @"INSERT INTO ProductType(TypeName) VALUES(@TypeName)";
List<ProductType> productTypeList = new List<ProductType>() {
new ProductType(){ TypeName="家具"},
new ProductType(){ TypeName="珠宝"},
new ProductType(){ TypeName="特产"},
new ProductType(){ TypeName="礼品鲜花"}
};
TestDapperUpdate(sql, productTypeList);
Result:
修改记录:
测试代码:
//修改单条记录
string sqlUpdated = @"UPDATE ProductType SET TypeName=@TypeName
WHERE TypeId=@TypeId";
List<ProductType> productTypeUpdated = new List<ProductType>() {
new ProductType(){ TypeId=4, TypeName="运动户外"}
};
Result:
批量更新数据:
//批量更新数据
string sqlUpdated = @"UPDATE ProductType SET TypeName=@TypeName
WHERE TypeId=@TypeId";
List<ProductType> productTypeUpdatedList = new List<ProductType>() {
new ProductType(){ TypeId=11, TypeName="个护清洁"},
new ProductType(){ TypeId=12, TypeName="宠物"},
new ProductType(){ TypeId=13, TypeName="汽车用品"},
new ProductType(){ TypeId=14, TypeName="母婴"},
new ProductType(){ TypeId=15, TypeName="玩具器乐"}
};
TestDapperUpdate(sqlUpdated, productTypeUpdatedList);
Result:
记录的删除:
删除单条记录:
string sqlDeleted = @"DELETE FROM ProductType WHERE TypeId=@TypeId";
//删除单条记录
List<ProductType> productTypeDeleted = new List<ProductType>() {
new ProductType(){ TypeId=5}
};
TestDapperUpdate(sqlDeleted, productTypeDeleted);
Result:记录已被删除
批量删除记录:
//批量删除数据
string sqlDeleted = @"DELETE FROM ProductType WHERE TypeId=@TypeId";
List<ProductType> productTypeDeletedList = new List<ProductType>() {
new ProductType(){ TypeId=11},
new ProductType(){ TypeId=12},
new ProductType(){ TypeId=13},
new ProductType(){ TypeId=14}
};
TestDapperUpdate(sqlDeleted, productTypeDeletedList);
Result:记录都已被删除掉
至此,关于更新的操作大概在这里了,还有一些请看其他章节