/// <summary>
/// 按数据库使用事务删除多个表的数据
/// </summary>
/// <param name="list">
/// Hashtable格式：
/// hs["id"] 键值
/// hs["table"] 表名
/// hs["mainKey"] 列名
/// </param>
/// <param name="DBName">数据库名称</param>
/// <param name="mainKey">默认列名</param>
/// <param name="ErrorIsRollBack">是否需要匹配传入的列表行数，当行数与变更行数不等时回滚，默认不需要</param>
/// <returns></returns>
public static int Delete(List <Hashtable> list, DBConfigEnum DBName, string mainKey = "id", bool ErrorIsRollBack = false)
{
int res = 0;
using (var conn = new DBConfig().InitConn(DBName.ToString()))
{
IDbTransaction tran = conn.BeginTransaction();
try
{
foreach (var hs in list)
{
if (string.IsNullOrWhiteSpace(Convert.ToString(hs["id"])) || string.IsNullOrWhiteSpace(Convert.ToString(hs["table"])))
{
tran.Rollback();
return(0);
}
if (string.IsNullOrWhiteSpace(Convert.ToString(hs["mainKey"])))
{
hs["mainKey"] = mainKey;
}
string sql = $" Delete {Convert.ToString(hs["table"])} where {Convert.ToString(hs["mainKey"])} = @{Convert.ToString(hs["mainKey"])}";
DynamicParameters param = new DynamicParameters();
param.Add("@" + Convert.ToString(hs["mainKey"]), Convert.ToString(hs["id"]));
res += conn.Execute(sql, param, tran);
}
if (ErrorIsRollBack)
{
if (res < list.Count || res > list.Count)
{
tran.Rollback();
return(0);
}
}
tran.Commit();
return(res);
}
catch
{
tran.Rollback();
return(0);
}
}
}
/// <summary>
/// 按数据库使用事务删除多条数据
/// </summary>
/// <param name="ids">键值</param>
/// <param name="DBName">数据库名称</param>
/// <param name="tableName">表名</param>
/// <param name="mainKey">主键</param>
/// <param name="ErrorIsRollBack">是否需要匹配传入的列表行数，当行数与变更行数不等时回滚，默认不需要</param>
/// <returns></returns>
public static int Delete(List <string> ids, DBConfigEnum DBName, string tableName, string mainKey = "id", bool ErrorIsRollBack = false)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("无效的表名");
}
int res = 0;
using (var conn = new DBConfig().InitConn(DBName.ToString())) {
IDbTransaction tran = conn.BeginTransaction();
try
{
foreach (var id in ids)
{
if (string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(mainKey))
{
tran.Rollback();
return(0);
}
string sql = $" Delete {tableName} where {mainKey} = @{mainKey}";
DynamicParameters param = new DynamicParameters();
param.Add("@" + mainKey, id);
res += conn.Execute(sql, param, tran);
}
if (ErrorIsRollBack)
{
if (res < ids.Count || res > ids.Count)
{
tran.Rollback();
return(0);
}
}
tran.Commit();
return(res);
}
catch {
tran.Rollback();
return(0);
}
}
}
/// <summary>
/// 按数据库更新实体列表
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="models">实体列表</param>
/// <param name="DBName">数据库名称</param>
/// <param name="tableName">表名，可空，默认为实体名称</param>
/// <param name="mainKey">主键列名，默认id</param>
/// <param name="ErrorIsRollBack">错误是否回滚</param>
/// <returns></returns>
public static int Update <T>(List <T> models, DBConfigEnum DBName, string tableName = "", string mainKey = "id", bool ErrorIsRollBack = false)
{
int res = 0;
if (string.IsNullOrWhiteSpace(tableName))
{
tableName = typeof(T).Name;
}
List <StringBuilder> sqls = new List <StringBuilder>();
List <DynamicParameters> paramList = new List <DynamicParameters>();
foreach (var model in models)
{
StringBuilder sql = new StringBuilder();
string setStr = "";
DynamicParameters param = new DynamicParameters();
sql.Append($"update {tableName} set ");
foreach (var a in model.GetType().GetProperties())
{
if (string.IsNullOrWhiteSpace(Convert.ToString(a.GetValue(model))))
{
continue;
}
if (a.Name.ToLower() == mainKey.ToLower())
{
if (string.IsNullOrWhiteSpace(Convert.ToString(a.GetValue(model))))
{
return(0);
}
param.Add("@" + mainKey, a.GetValue(model).ToString());
continue;
}
setStr += $"{a.Name} = @{a.Name},";
param.Add("@" + a.Name, a.GetValue(model).ToString());
}
if (!string.IsNullOrWhiteSpace(setStr))
{
setStr = setStr.Substring(0, setStr.Length - 1);
sql.Append(setStr);
sql.Append($" where {mainKey} = @{mainKey} ");
sqls.Add(sql);
paramList.Add(param);
}
}
using (var conn = new DBConfig().InitConn(DBName.ToString())) {
IDbTransaction tran = conn.BeginTransaction();
for (int i = 0; i < sqls.Count; i++)
{
res += conn.Execute(sqls[i].ToString(), paramList[i], tran);
}
if (ErrorIsRollBack)
{
if (res < models.Count || res > models.Count)
{
tran.Rollback();
return(0);
}
}
tran.Commit();
return(res);
}
}
