当前位置：首页 > news >正文

为C#的PetaPoco组件增加一个批量更新功能(临时表模式)

news 来源：原创 2024/9/23 13:48:21

总有一些数据是需要批量更新的，并且更新的字段，每个数据都不一样。

为了实现这样一个功能，写了这样一个方法：

using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using NetRube.Data;
using PetaPoco;namespace NetRube.Utils
{/// <summary>/// 临时表工具类/// </summary>public class TempTableUtil{/// <summary>/// 获取字段名/// </summary>/// <typeparam name="T">数据表类</typeparam>/// <param name="colExp">表字段</param>/// <returns></returns>private static string GetColName<T>(Expression<Func<T, dynamic>> colExp) where T : IModel{var colName = "";if (colExp.Body is MemberExpression memberExpression)colName = memberExpression.Member.Name;else if (colExp.Body is UnaryExpression unaryExpression && unaryExpression.Operand is MemberExpression operandMemberExpression)colName = operandMemberExpression.Member.Name;return colName;}/// <summary>/// 获取表名与主键字段名/// </summary>/// <typeparam name="T">数据表类</typeparam>/// <param name="priColExpression">获取主键的Lambda表达式</param>/// <returns></returns>/// <exception cref="ArgumentException"></exception>private static (string, string) GetTableAndPrimaryColName<T>(Expression<Func<T, dynamic>> priColExpression) where T : IModel{T table = Activator.CreateInstance<T>();var type = table.GetType();var tableAttr = (TableNameAttribute)Attribute.GetCustomAttribute(type, typeof(TableNameAttribute));if (tableAttr == null)throw new ArgumentException("无效对象");string tableName = tableAttr.Value; //得到表名var priColName = GetColName(priColExpression);  //得到主键PropertyInfo[] proInfos = table.GetType().GetProperties(); //获取所有字段foreach (var pro in proInfos){if (pro.Name.ToLower() == priColName.ToLower())priColName = pro.Name;if (!string.IsNullOrEmpty(priColName))break;  //已经拿到，跳出}if (string.IsNullOrEmpty(priColName))throw new ArgumentException("无效主键字段名");return (tableName, priColName);}/// <summary>/// 获取表名与字段名/// </summary>/// <typeparam name="T">数据表类</typeparam>/// <param name="modCols">获取待修改字段的Lambda表达式，可多个</param>/// <returns></returns>/// <exception cref="ArgumentException"></exception>private static Dictionary<string, string> GetModifyColNames<T>(Dictionary<Expression<Func<T, dynamic>>, string> modCols) where T : IModel{T table = Activator.CreateInstance<T>();var type = table.GetType();var tableAttr = (TableNameAttribute)Attribute.GetCustomAttribute(type, typeof(TableNameAttribute));if (tableAttr == null)throw new ArgumentException("无效对象");string tableName = tableAttr.Value; //得到表名PropertyInfo[] proInfos = table.GetType().GetProperties(); //获取所有字段var result = new Dictionary<string, string>();foreach (var col in modCols){var modColName = GetColName(col.Key);var modColType = col.Value;foreach (var pro in proInfos){if (pro.Name.ToLower() == modColName.ToLower()){modColName = pro.Name;result.Add(modColName, modColType);break;  //已经拿到，跳出}}if (string.IsNullOrEmpty(modColName))throw new ArgumentException("无效的修改字段名");}return result;}/// <summary>/// 批量更新多列字段(临时表模式)/// </summary>/// <typeparam name="T">数据表类</typeparam>/// <param name="priColExpression">主键的Lambda表达式</param>/// <param name="priColType">主键在数据表的数据类型</param>/// <param name="modColExpressions">待修改字段的Lambda表达式，可多个</param>/// <param name="db">数据库对象</param>/// <param name="dataList">用于修改的数据，必须有主键数据和待修改字段的数据</param>/// <returns></returns>public static bool BatchUpdateMutiColData<T>(Expression<Func<T, dynamic>> priColExpression, string priColType,Dictionary<Expression<Func<T, dynamic>>, string> modColExpressions,Database db, List<T> dataList) where T : IModel{//如果字典为空则直接返回if (dataList == null || dataList.Count == 0)return false;//获取表名与字段名var (tableName, priColName) = GetTableAndPrimaryColName(priColExpression);var modColNames = GetModifyColNames(modColExpressions);//创建临时表sqlvar tmpTableName = $"TempUpdateTable_{DateTime.Now.ToString("yyyyMMddHHmmss")}";var modNameTypeSql = string.Join(", ", modColNames.Select(m => m.Key + " " + m.Value));var createTempTableSql = @$"CREATE TEMPORARY TABLE {tmpTableName} ({priColName} {priColType}, {modNameTypeSql} );";//数据插入到临时表sqlvar insertTempTableSql = new StringBuilder();var modNameSql = string.Join(", ", modColNames.Select(m => m.Key));insertTempTableSql.Append($"INSERT INTO {tmpTableName} ({priColName}, {modNameSql}) VALUES ");var parameters = new List<object>();int idx = 0;foreach (var data in dataList){PropertyInfo[] proInfos = data.GetType().GetProperties(); //获取所有属性insertTempTableSql.Append($"(");foreach (var pro in proInfos){if (priColName.ToLower() == pro.Name.ToLower())  //获取主键值{var val = pro.GetValue(data);insertTempTableSql.Append($"@{idx},");parameters.Add(val);       // 添加对应修改值idx++;break;  //已经拿到，跳出}}foreach (var modName in modColNames){foreach (var pro in proInfos){if (modName.Key.ToLower() == pro.Name.ToLower())  //获取修改字段{var val = pro.GetValue(data);insertTempTableSql.Append($"@{idx},");parameters.Add(val);       // 添加对应修改值idx++;break;  //已经拿到，跳出}}}insertTempTableSql.Length--;  // 移除最后的逗号insertTempTableSql.Append($"),");}insertTempTableSql.Length--;insertTempTableSql.Append(";");  // 移除最后的逗号//通过JOIN批量更新更新到主表sqlvar modNameMapSql = string.Join(", ", modColNames.Select(m => "a." + m.Key + " = t." + m.Key));var updateSql = @$"UPDATE {tableName} a JOIN {tmpTableName} t ON a.{priColName} = t.{priColName} SET {modNameMapSql};";//删除临时表sql（MySQL在会话结束时会自动删除，手动删除以确保无残留）var deleteTempTableSql = $"DROP TEMPORARY TABLE IF EXISTS {tmpTableName};";//开始执行脚本db.Execute(createTempTableSql);db.Execute(insertTempTableSql.ToString(), parameters.ToArray());db.Execute(updateSql);db.Execute(deleteTempTableSql);return true;}}
}

使用方式：

//待更新的字段
var Database = new Database();   //来自PetaPoco
var modCols = new Dictionary<Expression<Func<ProductInfo, dynamic>>, string>();
modCols.Add(a => a.SalePrice, "decimal(10,2)");
modCols.Add(a => a.Stock, "int");
//测试数据
var dataList = new List<ProductInfo>();
dataList.Add(new ProductInfo { Id = "4347_429_3150_0", SalePrice = 90.8M, Stock = 8 });
dataList.Add(new ProductInfo { Id = "4347_429_3151_0", SalePrice = 90.9M, Stock = 9 });
//更新
TempTableUtil.BatchUpdateMutiColData<ProductInfo>(a => a.Id, "varchar(50)", modCols, Database, dataList);