詳解MySqlBulkLoader的使用
mysql數據庫：最近要寫一個服務，跨庫數據同步，目前數據量大約一萬，以後會越來越多，考慮到擴展性，數據的插入操作就采用瞭MySqlBulkLoader。本文分兩部分來寫，第一部分寫一下MySqlBulkLoader的使用，第二部分記錄使用過程中出現的問題。
一、MySqlBulkLoader的使用
我們先來定義個數據表student，表結構如下：
創建一個core控制臺項目，相關代碼如下：
入口代碼：
using System;
using System.Collections.Generic;
namespace MySqlBulkLoaderDemo
{
class Program
{
static void Main(string[] args)
{
//裝載30個數據
List<Student> stuList = new List<Student>();
for (int i = 0; i < 30; i++)
{
stuList.Add(
new Student
{
Guid = Guid.NewGuid().ToString(),
Name = "QXH",
Age = new Random().Next(1, 30)
});
}
//調用MySqlBulkLoader，往student表中插入stuList
int insertCount = MySqlBulkLoaderHelper.BulkInsert<Student>(stuList, "student");
Console.WriteLine($"成功插入{insertCount}條數據");
Console.ReadKey();
}
}
}
定義一個Student映射類：
using System;
using System.Collections.Generic;
using System.Text;
namespace MySqlBulkLoaderDemo
{
public class Student
{
public string Guid { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
}
定義一個MySqlBulkLoaderHelper類，用於存放相關方法：
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
namespace MySqlBulkLoaderDemo
{
public class MySqlBulkLoaderHelper
{
const string ConnectionString = "server=localhost;port=3306;user=root;password=123456;database=mysql;SslMode = none;AllowLoadLocalInfile=true";
public static int BulkInsert<T>(List<T> entities, string tableName)
{
DataTable dt = entities.ToDataTable();
using (MySqlConnection conn = new MySqlConnection())
{
conn.ConnectionString = ConnectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (tableName.IsNullOrEmpty())
{
var tableAttribute = typeof(T).GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault();
if (tableAttribute != null)
tableName = ((TableAttribute)tableAttribute).Name;
else
tableName = typeof(T).Name;
}
int insertCount = 0;
string tmpPath = Path.Combine(Path.GetTempPath(), DateTime.Now.Ticks.ToString() + "_" + Guid.NewGuid().ToString() + ".tmp");
string csv = dt.ToCsvStr();
File.WriteAllText(tmpPath, csv, Encoding.UTF8);
using (MySqlTransaction tran = conn.BeginTransaction())
{
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
Local = true,
NumberOfLinesToSkip = 0,
TableName = tableName,
CharacterSet = "utf8"
};
try
{
bulk.Columns.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
insertCount = bulk.Load();
tran.Commit();
}
catch (MySqlException ex)
{
if (tran != null)
tran.Rollback();
throw ex;
}
}
File.Delete(tmpPath);
return insertCount;
}
}
}
}
定義一個幫助類ExtentionHelper，主要是擴展方法：
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
namespace MySqlBulkLoaderDemo
{
public static class ExtentionHelper
{
/// <summary>
/// 將對象序列化成Json字符串
/// </summary>
/// <param name="obj">需要序列化的對象</param>
/// <returns></returns>
public static string ToJson(this object obj)
{
return JsonConvert.SerializeObject(obj);
}
/// <summary>
/// 將Json字符串轉為DataTable
/// </summary>
/// <param name="jsonStr">Json字符串</param>
/// <returns></returns>
public static DataTable ToDataTable(this string jsonStr)
{
return jsonStr == null ? null : JsonConvert.DeserializeObject<DataTable>(jsonStr);
}
/// <summary>
/// 將IEnumerable'T'轉為對應的DataTable
/// </summary>
/// <typeparam name="T">數據模型</typeparam>
/// <param name="iEnumberable">數據源</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable<T>(this IEnumerable<T> iEnumberable)
{
return iEnumberable.ToJson().ToDataTable();
}
/// <summary>
/// 判斷是否為Null或者空
/// </summary>
/// <param name="obj">對象</param>
/// <returns></returns>
public static bool IsNullOrEmpty(this object obj)
{
if (obj == null)
return true;
else
{
string objStr = obj.ToString();
return string.IsNullOrEmpty(objStr);
}
}
/// <summary>
///將DataTable轉換為標準的CSV字符串
/// </summary>
/// <param name="dt">數據表</param>
/// <returns>返回標準的CSV</returns>
public static string ToCsvStr(this DataTable dt)
{
//以半角逗號（即,）作分隔符，列為空也要表達其存在。
//列內容如存在半角逗號（即,）則用半角引號（即""）將該字段值包含起來。
//列內容如存在半角引號（即"）則應替換成半角雙引號（""）轉義，並用半角引號（即""）將該字段值包含起來。
StringBuilder sb = new StringBuilder();
DataColumn colum;
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
colum = dt.Columns[i];
if (i != 0) sb.Append(",");
if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
{
sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
}
else sb.Append(row[colum].ToString());
}
sb.AppendLine();
}
return sb.ToString();
}
}
}
完整項目：MySqlBulkLoaderDemo
運行結果如下：
二、MySqlBulkLoader使用過程中出現的問題
上邊已經完整瞭介紹瞭MySqlBulkLoader的使用，但是在使用過程中出現瞭很多問題，主要集中在兩方面，第一個方面是Mysql數據庫不支持加載本地文件數據；第二個方面是我的數據庫在阿裡雲服務器上，而代碼在本地，換句話說數據庫和項目是分別放在不同服務器上的。
1、Mysql數據庫不支持加載本地文件數據
（1）MySQLBulkLoader原理？
我們結合SQLBulkCopy來說，用過SqlServer數據庫的都熟悉SQLBulkCopy，很方便，可以直接將datatable中的數據批量導入到數據庫。與SQLBulkCopy不同，MySQLBulkLoader也稱為LOAD DATA INFILE，他要從文件讀取數據，所以我們需要將我們的數據集（如上邊的List<Student>）保存到文件，然後再從文件裡面讀取。而對於Mysql來說，為瞭數據庫的安全，本地導入文件的配置沒有開啟，所以使用MySQLBulkLoader批量導入數據庫，就需要mysql數據庫支持本地導入文件。否則會出現以下錯誤：
The used command is not allowed with this MySQL version
（2）解決方案
mysql數據庫開啟允許本地導入數據的配置，命令如下：
SET GLOBAL local_infile=1；//1表示開啟，0表示關閉
查看該配置的狀態命令如下：
SHOW VARIABLES LIKE '%local%';
在項目裡面的數據庫連接字符串做設置
數據庫連接字符串要加上”AllowLoadLocalInfile=true“，如下：
const string ConnectionString = "server=localhost;port=3306;user=root;password=123456;database=mysql;SslMode = none;AllowLoadLocalInfile=true";
2、數據庫和項目是分別放在不同服務器上
（1）問題描述
數據庫和項目是分別放在不同服務器上，會造成以下問題：
System.NotSupportedException HResult=0x80131515 Message=To use MySqlBulkLoader.Local=true, set AllowLoadLocalInfile=true in the connection string. See https://fl.vu/mysql-load-data
（2）原因
因為項目中將數據集生成的文件保存在瞭項目所在的服務器，另一個服務器上的數據庫在插入數據操作時，找不到數據集文件，導致的錯誤
（3）解決方法
方法很簡單，因為數據庫並不在項目所在的服務器，所以MySqlBulkLoader中要設置Local = true讀取本地文件，進行導入。具體代碼如下：
（4）總結
如果你的項目和數據庫在一臺服務器上，那麼就不會出現該問題。
推薦閱讀：
- C# 遍歷datatable字段名和value的案例
- C# 創建MDB數據庫、並存放表格數據的案例
- DataTable的AcceptChanges()和RejectChanges()方法介紹並實現DataGridView數據增、刪、改
- C#中對象與JSON字符串互相轉換的三種方式
- C# 獲取數據庫中所有表名、列名的示例代碼