.NET MySQL 批量操作
.NET MySQL 数据库批量操作
在开发中，我们经常需要在数据库中执行批量操作，以便高效地插入和更新大量数据。这篇文章将介绍如何使用 .NET 和 MySqlConnector 类库来实现针对 MySQL 数据库的批量操作，包括批量插入和更新数据。
准备工作：启用本地文件加载功能
在开始之前，我们需要确保 MySQL 服务器已启用本地文件加载功能，以便能够执行批量操作。以下是启用该功能的步骤：
-
打开 MySQL 服务器的配置文件，通常是 my.cnf 或 my.ini。
-
添加或修改以下行，使 MySQL 服务器启用本地文件加载功能：
[mysqld] local-infile=1 -
重启 MySQL 服务器：
在修改配置文件后，重启 MySQL 服务器，以使配置更改生效。 -
授权用户（可选）：
如果你想允许某个用户使用 LOAD DATA LOCAL INFILE 语句，你需要确保该用户拥有相应的权限。在 MySQL 中，使用以下命令来为用户授权：GRANT FILE ON *.* TO 'username'@'localhost'; FLUSH PRIVILEGES;在上面的命令中，将 ‘username’ 替换为你要授权的用户名，‘localhost’ 替换为数据库连接地址。FILE 权限允许用户从本地文件加载数据。
批量操作代码示例
下面是使用 .NET 和 MySqlConnector 类库实现批量插入和更新操作的示例代码：
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
namespace Helper
{
public class MySqlHelper
{
private readonly static string config = "这里改成你的mysql地址";
/// <summary>
/// List转DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <returns></returns>
public static DataTable ConvertToDataTable<T>(IEnumerable<T> data)
{
DataTable table = new DataTable();
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
foreach (PropertyDescriptor prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
return table;
}
#region MySql 批量操作
/// <summary>
/// 批量添加
/// </summary>
/// <param name="table">DataTable数据集</param>
/// <param name="tableName">要新增数据的表名</param>
/// <exception cref="ArgumentException"></exception>
public static void BatchInsertData(DataTable table, string tableName)
{
if (table == null || string.IsNullOrEmpty(tableName))
{
throw new ArgumentException("BatchInsertData:输入参数无效");
}
MySqlConnection sqlConnection = new MySqlConnection(config);
//允许从本地文件加载数据
var connectionStringBuilder = new MySqlConnectionStringBuilder(sqlConnection.ConnectionString)
{
AllowLoadLocalInfile = true
};
sqlConnection.ConnectionString = connectionStringBuilder.ConnectionString;
using (sqlConnection)
{
sqlConnection.Open();
var bulkCopy = new MySqlBulkCopy(sqlConnection);
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(table);
}
}
/// <summary>
/// 批量添加 List集合数据添加到表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">集合数据</param>
/// <param name="dt">DataTable表结构</param>
/// <param name="tableName">表名</param>
/// <exception cref="ArgumentException"></exception>
public static void BatchInsertData<T>(List<T> list, DataTable dt, string tableName)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentException("BatchInsertData: tableName参数不能为空");
}
if (list.Count == 0 || dt == null)
{
return;
}
DataTable table = ConvertToDataTable(list);
BatchInsertData(table, tableName);
}
/// <summary>
/// 批量更新 List集合数据更新到表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">集合数据</param>
/// <param name="dt">DataTable表结构</param>
/// <param name="keys">更新条件 比如说ID</param>
/// <param name="TableName">需要更新数据的表名</param>
/// <param name="UpdateKeys">需要更新值的字段</param>
/// <exception cref="ArgumentException"></exception>
/// <exception cref="Exception"></exception>
public static void BatchUpdateData<T>(List<T> list, DataTable dt, List<string> keys, string TableName, List<string> UpdateKeys = null)
{
if (list.Count == 0 || dt == null || keys.Count == 0 || string.IsNullOrEmpty(TableName))
{
throw new ArgumentException("BatchUpdateData: 参数不能为空");
}
MySqlConnection sqlConnection = new MySqlConnection(config);
//允许从本地文件加载数据
var connectionStringBuilder = new MySqlConnectionStringBuilder(sqlConnection.ConnectionString)
{
AllowLoadLocalInfile = true
};
sqlConnection.ConnectionString = connectionStringBuilder.ConnectionString;
using (sqlConnection)
{
sqlConnection.Open();
using (var sqlTrans = sqlConnection.BeginTransaction())
{
try
{
DataTable table = ConvertToDataTable(list);
if (table.Rows.Count == 0) return;
table.TableName = TableName;
var tempTable = "Tmp_" + TableName;
using (var command = new MySqlCommand(sqlConnection, sqlTrans))
{
command.CommandText = "CREATE TEMPORARY TABLE " + tempTable + " LIKE " + TableName;
command.ExecuteNonQuery();
var bulkcopy = new MySqlBulkCopy(sqlConnection, sqlTrans)
{
BulkCopyTimeout = 600,
DestinationTableName = tempTable
};
//创建临时表
bulkcopy.WriteToServer(table);
// 更新内容：排除唯一键
var contentSqlList = new List<string>();
if (UpdateKeys != null && UpdateKeys.Count > 0)
{
// 只更新特殊字段
foreach (var item in UpdateKeys)
{
contentSqlList.Add("Prim." + item + " = Temp." + item);
}
}
else
{
// 全表更新
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
foreach (PropertyDescriptor prop in properties)
{
if (!keys.Contains(prop.Name) && prop.Name != "RowVersion")
{
contentSqlList.Add("Prim." + prop.Name + " = Temp." + prop.Name);
}
}
}
var contentStr = string.Join(",", contentSqlList);
// 更新条件：唯一键
var keySqlList = new List<string>();
foreach (var item in keys)
{
keySqlList.Add("Prim." + item + " = Temp." + item);
}
var keyStr = string.Join(" AND ", keySqlList);
if (!string.IsNullOrEmpty(contentStr) && !string.IsNullOrEmpty(keyStr))
{
var updateStr = @"UPDATE {0} Prim
INNER JOIN {1} Temp ON ({2})
SET {3};";
var updateSql = string.Format(updateStr, TableName, tempTable, keyStr, contentStr);
command.CommandTimeout = 600;
command.CommandText = updateSql;
command.ExecuteNonQuery();
}
// 提交事务
sqlTrans.Commit();
}
}
catch (Exception ex)
{
// 发生异常，回滚事务
sqlTrans.Rollback();
throw new Exception("BatchUpdateData批量更新异常: " + ex.Message, ex);
}
}
}
}
#endregion
}
}
上述代码提供了三个主要的批量操作方法：BatchInsertData 用于批量插入数据，BatchInsertData 用于将 List 集合数据批量插入数据库，BatchUpdateData 用于批量更新数据，以及如何将 List 转换为 DataTable 的方法。
扫一扫
900
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
