dapper mysql 批量新增修改

dapper是C#程序员比较喜欢用的轻量级ORM，简单易学，只是没有批量新增以及修改（收费版有），写了如下扩展
  1  /// <summary>
  2     /// dapper批量新增修改扩展
  3     /// </summary>
  4     public static class DapperExtensions
  5     {
  6         /// <summary>
  7         /// 批量插入
  8         /// </summary>
  9         /// <typeparam name="T"></typeparam>
 10         /// <param name="connection"></param>
 11         /// <param name="tableName">表名</param>
 12         /// <param name="items">列表</param>
 13         /// <param name="dataFunc"></param>
 14         /// <param name="duplicateData">相同主键覆盖字段</param>
 15         /// <param name="dbTransaction">事务</param>
 16         /// <param name="insert">insert,replace,insert ignore</param>
 17         /// <returns></returns>
 18         public static async Task BulkInsert<T>(
 19             this IDbConnection connection,
 20             string tableName,
 21             IReadOnlyCollection<T> items,
 22             Dictionary<string, Func<T, object>> dataFunc, IEnumerable<string>? duplicateData = null, IDbTransaction? dbTransaction = null, string insert = "INSERT")
 23         {
 24             const int MaxBatchSize = 5000;
 25             const int MaxParameterSize = 10000;
 26 
 27             var batchSize = Math.Min((int)Math.Ceiling((double)MaxParameterSize / dataFunc.Keys.Count), MaxBatchSize);
 28             var numberOfBatches = (int)Math.Ceiling((double)items.Count / batchSize);
 29             var columnNames = dataFunc.Keys;
 30             var insertSql = $"{insert} INTO {tableName} ({string.Join(",", columnNames.Select(e => $"`{e}`"))}) VALUES";
 31             var sqlToExecute = new List<Tuple<string, DynamicParameters>>();
 32 
 33             for (var i = 0; i < numberOfBatches; i++)
 34             {
 35                 var dataToInsert = items.Skip(i * batchSize)
 36                     .Take(batchSize);
 37                 var valueSql = GetQueries(dataToInsert, dataFunc);
 38 
 39                 sqlToExecute.Add(Tuple.Create($"{insertSql}{string.Join(",", valueSql.Item1)}", valueSql.Item2));
 40             }
 41 
 42             var duplicate = string.Empty;
 43             if (duplicateData != null)
 44             {
 45                 duplicate = $" ON DUPLICATE KEY UPDATE {string.Join(',', duplicateData.Select(d => $"`{d}`=VALUES(`{d}`)"))}";
 46             }
 47 
 48             foreach (var sql in sqlToExecute)
 49             {
 50                 await connection.ExecuteAsync(sql.Item1 + duplicate, sql.Item2, commandTimeout: int.MaxValue, transaction: dbTransaction);
 51             }
 52         }
 53 
 54         private static Tuple<IEnumerable<string>, DynamicParameters> GetQueries<T>(
 55             IEnumerable<T> dataToInsert,
 56             Dictionary<string, Func<T, object>> dataFunc)
 57         {
 58             var parameters = new DynamicParameters();
 59 
 60             return Tuple.Create(
 61                 dataToInsert.Select(e => $"({string.Join(",", GenerateQueryAndParameters(e, parameters, dataFunc))})"),
 62                 parameters);
 63         }
 64 
 65         private static IEnumerable<string> GenerateQueryAndParameters<T>(
 66             T entity,
 67             DynamicParameters parameters,
 68             Dictionary<string, Func<T, object>> dataFunc)
 69         {
 70             var paramTemplateFunc = new Func<Guid, string>(guid => $"@p{guid:N}");
 71             var paramList = new List<string>();
 72 
 73             foreach (var key in dataFunc)
 74             {
 75                 var paramName = paramTemplateFunc(Guid.NewGuid());
 76                 parameters.Add(paramName, key.Value(entity));
 77                 paramList.Add(paramName);
 78             }
 79 
 80             return paramList;
 81         }
 82 
 83         /// <summary>
 84         /// 批量更新
 85         /// </summary>
 86         /// <typeparam name="T"></typeparam>
 87         /// <param name="connection"></param>
 88         /// <param name="tableName">表名</param>
 89         /// <param name="items">列表</param>
 90         /// <param name="dataFunc"></param>
 91         /// <param name="primaryFunc"></param>
 92         /// <param name="primaryKey">主键</param>
 93         /// <param name="isIntKey">主键是否数字类型</param>
 94         /// <param name="dbTransaction">事务</param>
 95         /// <returns></returns>
 96         public static async Task BulkUpdate<T>(
 97             this IDbConnection connection,
 98             string tableName,
 99             IReadOnlyCollection<T> items,
100             Dictionary<string, Func<T, object>> dataFunc, Func<T, object> primaryFunc, string primaryKey, bool isIntKey = true, IDbTransaction? dbTransaction = null)
101         {
102             const int MaxBatchSize = 5000;
103             const int MaxParameterSize = 10000;
104 
105             var batchSize = Math.Min((int)Math.Ceiling((double)MaxParameterSize / dataFunc.Keys.Count), MaxBatchSize);
106             var numberOfBatches = (int)Math.Ceiling((double)items.Count / batchSize);
107             var columnNames = dataFunc.Keys;
108             var updateSql = $"UPDATE {tableName} SET";
109             var sqlToExecute = new List<Tuple<string, DynamicParameters>>();
110 
111             for (var i = 0; i < numberOfBatches; i++)
112             {
113                 var dataToUpdate = items.Skip(i * batchSize)
114                     .Take(batchSize);
115                 var valueSql = GetUpdateQueries(dataToUpdate, dataFunc, primaryFunc, primaryKey, isIntKey);
116 
117                 sqlToExecute.Add(Tuple.Create($"{updateSql}{valueSql.Item1}", valueSql.Item2));
118             }
119 
120 
121             foreach (var sql in sqlToExecute)
122             {
123                 await connection.ExecuteAsync(sql.Item1, sql.Item2, commandTimeout: int.MaxValue, transaction: dbTransaction);
124             }
125         }
126         private static Tuple<string, DynamicParameters> GetUpdateQueries<T>(
127             IEnumerable<T> dataToUpdate,
128             Dictionary<string, Func<T, object>> dataFunc, Func<T, object> primaryFunc, string primaryKey, bool isIntKey)
129         {
130             var paramTemplateFunc = new Func<Guid, T, (string param, string sql)>((guid, entity) =>
131             {
132                 var keyValue = primaryFunc(entity);
133                 if (!isIntKey)
134                 {
135                     keyValue = $"'{keyValue}'";
136                 }
137                 var param = $"@p{guid:N}";
138                 var sql = $"WHEN {keyValue} THEN {param}";
139 
140                 return (param, sql);
141             }
142             );
143             var parameters = new DynamicParameters();
144             List<string> sqlList = new();
145             foreach (var key in dataFunc)
146             {
147                 var paramList = new List<string>();
148                 foreach (var e in dataToUpdate)
149                 {
150                     var (param, sql) = paramTemplateFunc(Guid.NewGuid(), e);
151                     parameters.Add(param, key.Value(e));
152                     paramList.Add(sql);
153                 }
154                 sqlList.Add($"`{key.Key}`=CASE `{primaryKey}` {string.Join(" ", paramList)} END");
155             }
156             object idFunc(T p)
157             {
158                 return primaryFunc(p);
159             }
160             parameters.Add("@ids", dataToUpdate.Select(idFunc));
161             return Tuple.Create(
162                 $"{string.Join(",", sqlList)} WHERE `{primaryKey}` IN @ids",
163                 parameters);
164         }
165     }

使用方法：
新增：

1 await conn.BulkInsert(
2                    "userInfo",    //表名    
3                     userinfoList,    //列表
4                     new Dictionary<string, Func<UserInfo, object>>
5                         {
6                             {"Name", u => u.Name },
7                             {"Age", u => u.Age },
8                             {"Sex", u => u.Sex },
9                         });

修改：

1 await conn.BulkUpdate("userInfo", userInfoList, new Dictionary<string, Func<UserInfo, object>>
2                        {
3                         　　{"Name", u => u.Name }
4                             {"Age", u => u.Age },
5                             {"Sex", u => u.Sex },
6                        　　}, new Func<UserInfo, object>(u => u.ID), "ID");