这篇文章的类容,已经有很多博客介绍过了,仅做记录。
原理:将DataTable中的数据写道.csv文件中,然后再通过MySqlBulkLoader 写到数据库中。
注意,DataTable的TableName要与数据库中的表名对应,.csv文件的文件名倒是无所谓
MySql批量更新代码
public static int BulkLoad(DataTable dtInfo)
{
int count = 0;
string tmpPath = Directory.GetCurrentDirectory() + "\\UpTemp\\" + dtInfo.TableName + ".csv";
if (File.Exists(tmpPath))
File.Delete(tmpPath);
CSVEx.DataTableWriteToCsvFile(dtInfo, tmpPath);//CSVEx类看下文
using (MySqlConnection conn = new MySqlConnection(MySqlConnectString))
{
conn.Open();
MySqlTransaction transction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = Directory.GetCurrentDirectory() + "\\UpTemp\\" + dtInfo.TableName + ".csv",
NumberOfLinesToSkip = 0,
TableName = dtInfo.TableName,
CharacterSet = "UTF8",
};
stopwatch.Stop();
count = bulk.Load();
transction.Commit();
}
catch (Exception)
{
transction.Rollback();
throw;
}
}
File.Delete(tmpPath);
return count;
}
CSVEx类(用于将DataTable数据转为CSV文件存入mysql中),此类还可以优化(为什么判断DataTable数据的条数,因为自己写的程序处理的是地理空间数据,动不动就是上万,上十万的数据,都读到内存中不是不可以,但这样的习惯不好,何况还要将上万条的数据转化未字符串写道文件中去,最好不要这样做,想想如果DataTable的列很多,那估计是不小的量)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
namespace Chn.gzGISer.Util
{
public class CSVEx
{
public static bool DataTableWriteToCsvFile(DataTable table, string path)
{
//以半角逗号(即,)作分隔符,列为空也要表达其存在。
//列内容如存在半角逗号(即,)则用半角引号(即"")将该字段值包含起来。
//列内容如存在半角引号(即")则应替换成半角双引号("")转义,并用半角引号(即"")将该字段值包含起来。
try
{
StringBuilder sb = new StringBuilder();
DataColumn colum;
int rowCount = table.Rows.Count;
int writeSize = 500;
if (rowCount > 500)
{
int count = 0;
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
colum = table.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();
count++;
if (count == writeSize)
{
File.AppendAllText(path, sb.ToString());
sb.Clear();
count = 0;
}
}
if (count > 0)
{
File.AppendAllText(path, sb.ToString());
}
sb.Clear();
sb = null;
return true;
}
else
{
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
colum = table.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();
}
File.WriteAllText(path, sb.ToString());
sb.Clear();
sb = null;
return true;
}
}
catch (Exception)
{
throw;
}
}
}
}