vb批量导入数据到mysql_批量插入数据, 将DataTable里的数据批量写入数据库的方法...

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingETLUtilityDAL.Interfaces;usingETLUtilityDAL.Enums;usingETLUtilityDAL.Common;usingMySql.Data.MySqlClient;usingSystem.Data;usingSystem.Collections.Specialized;usingSystem.IO;namespaceETLUtilityDAL.Implement

{public classMySqlDBHelper : IDBHelper

{private DatabaseType _dBVender =DatabaseType.MySql;private readonly string _tmpBasePath =AppDomain.CurrentDomain.BaseDirectory;private readonly string _tmpCSVFilePattern = "Temp\\{0}.csv"; //0表示文件名称

private string_dbName;publicMySqlDBHelper()

{this._dbName = "";

}public MySqlDBHelper(stringdbName)

{this._dbName =dbName;

}#region IDBHelper 成员

public int ExecNonQuery(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues)

{int result = 0;using (MySqlConnection mySqlCon = GetConnection ())

{

MySqlCommand mySqlCmd= newMySqlCommand(sqlText, mySqlCon);

mySqlCmd.CommandType=cmdType;try{

fillParameters(mySqlCmd, paramNames, paramValues);

mySqlCon.Open();

result=mySqlCmd.ExecuteNonQuery();

}catch(MySqlException mse)

{throwmse;

}

}return 0;

}public T ExecScalar(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues)

{

T result= default(T);using (MySqlConnection mySqlCon = GetConnection())

{

MySqlCommand mySqlCmd= newMySqlCommand(sqlText, mySqlCon);

mySqlCmd.CommandType=cmdType;try{

fillParameters(mySqlCmd, paramNames, paramValues);

mySqlCon.Open();

result= (T)Convert.ChangeType(mySqlCmd.ExecuteScalar(), typeof(T));

}catch(MySqlException mse)

{throwmse;

}

}returnresult;

}public T ExecReader(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues)

{

T result= default(T);using (MySqlConnection mySqlCon = GetConnection())

{

MySqlCommand mySqlCmd= newMySqlCommand(sqlText, mySqlCon);

mySqlCmd.CommandType=cmdType;try{

fillParameters(mySqlCmd, paramNames, paramValues);

mySqlCon.Open();

result= (T)Convert.ChangeType(mySqlCmd.ExecuteReader(CommandBehavior.CloseConnection), typeof(T));

}catch(MySqlException mse)

{throwmse;

}

}returnresult;

}public DataTable ExecDataTable(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues)

{

DataTable result= newDataTable();using (MySqlConnection mySqlCon = GetConnection())

{

MySqlCommand mySqlCmd= newMySqlCommand(sqlText, mySqlCon);

mySqlCmd.CommandType=cmdType;try{

fillParameters(mySqlCmd, paramNames, paramValues);

MySqlDataAdapter mySqlDA= newMySqlDataAdapter(mySqlCmd);

mySqlDA.Fill(result);

}catch(MySqlException mse)

{throwmse;

}

}returnresult;

}public DataSet ExecDataSet(string sqlText, CommandType cmdType, string[] paramNames, object[] paramValues)

{

DataSet result= newDataSet();using (MySqlConnection mySqlCon = GetConnection())

{

MySqlCommand sqlCmd= newMySqlCommand(sqlText, mySqlCon);

sqlCmd.CommandType=cmdType;try{

fillParameters(sqlCmd, paramNames, paramValues);

MySqlDataAdapter mySqlDA= newMySqlDataAdapter(sqlCmd);

mySqlDA.Fill(result);

}catch(MySqlException mse)

{throwmse;

}

}returnresult;

}public int TxtExecuteNonQuery(stringsqlText)

{return ExecNonQuery(sqlText, CommandType.Text, null, null);

}public int TxtExecuteNonQuery(string sqlText, string[] paramNames, object[] paramValues)

{returnExecNonQuery(sqlText, CommandType.Text, paramNames, paramValues);

}public T TxtExecuteScalar(string sqlText, string[] paramNames, object[] paramValues)

{return ExecScalar(sqlText, CommandType.Text, paramNames, paramValues);

}public T TxtExecuteReader(string sqlText, string[] paramNames, object[] paramValues)

{return ExecReader(sqlText, CommandType.Text, paramNames, paramValues);

}public DataTable TxtExecuteDataTable(string sqlText, string[] paramNames, object[] paramValues)

{returnExecDataTable(sqlText, CommandType.Text, paramNames, paramValues);

}public DataSet TxtExecuteDataSet(string sqlText, string[] paramNames, object[] paramValues)

{returnExecDataSet(sqlText, CommandType.Text, paramNames, paramValues);

}public int SpExecuteNonQuery(stringsqlText)

{return ExecNonQuery("USP_RunSql", CommandType.StoredProcedure, null, null);

}public int SpExecuteNonQuery(string spName, string[] paramNames, object[] paramValues)

{returnExecNonQuery(spName, CommandType.StoredProcedure, paramNames, paramValues);

}public T SpExecuteScalar(string spName, string[] paramNames, object[] paramValues)

{return ExecScalar(spName, CommandType.StoredProcedure, paramNames, paramValues);

}public T SpExecuteReader(string spName, string[] paramNames, object[] paramValues)

{return ExecReader(spName, CommandType.StoredProcedure, paramNames, paramValues);

}public DataTable SpExecuteDataTable(string spName, string[] paramNames, object[] paramValues)

{returnExecDataTable(spName, CommandType.StoredProcedure, paramNames, paramValues);

}public DataSet SpExecuteDataSet(string spName, string[] paramNames, object[] paramValues)

{returnExecDataSet(spName, CommandType.StoredProcedure, paramNames, paramValues);

}public bool BulkInsert(T sqlBulkCopy, DataTable dataTable, T1 sqlTrasaction)

{bool result = false;string tmpCsvPath = this._tmpBasePath + string.Format(this._tmpCSVFilePattern, DateTime.Now.Ticks.ToString());string tmpFolder = tmpCsvPath.Remove(tmpCsvPath.LastIndexOf("\\"));if (!Directory.Exists(tmpFolder))

Directory.CreateDirectory(tmpFolder);

FileHelper.WriteDataTableToCSVFile(dataTable, tmpCsvPath);//Write to csv File

MySqlBulkLoader sqlBC= (MySqlBulkLoader)Convert.ChangeType(sqlBulkCopy, typeof(MySqlBulkLoader));

MySqlTransaction sqlTran= (MySqlTransaction)Convert.ChangeType(sqlTrasaction, typeof(MySqlTransaction));try{

sqlBC.TableName=dataTable.TableName;

sqlBC.FieldTerminator= "|";

sqlBC.LineTerminator= "\r\n";

sqlBC.FileName=tmpCsvPath;

sqlBC.NumberOfLinesToSkip= 0;

StringCollection strCollection= newStringCollection();//Mapping Destination Field of Database Table

for (int i = 0; i < dataTable.Columns.Count; i++)

{

strCollection.Add(dataTable.Columns[i].ColumnName);

}

sqlBC.Columns=strCollection;//Write DataTable

sqlBC.Load();

sqlTran.Commit();

result= true;

}catch(MySqlException mse)

{

result= false;

sqlTran.Rollback();throwmse;

}finally{//T、T1给默认值为Null, 由系统调用GC

sqlBC = null;

sqlBulkCopy= default(T);

sqlTrasaction= default(T1);

}

File.Delete(tmpCsvPath);returnresult;

}public boolBulkInsert(DataTable dataTable)

{bool result = false;if (dataTable != null && dataTable.Rows.Count > 0)

{using (MySqlConnection mySqlCon = GetConnection())

{

mySqlCon.Open();

MySqlTransaction sqlTran=mySqlCon.BeginTransaction(IsolationLevel.ReadCommitted);

MySqlBulkLoader sqlBulkCopy= newMySqlBulkLoader(mySqlCon);

sqlBulkCopy.Timeout= 60;

result=BulkInsert(sqlBulkCopy, dataTable, sqlTran);

}

}returnresult;

}public boolBulkInsert(DataSet dataSet)

{bool result = false;if (dataSet != null && dataSet.Tables.Count > 0)

{using (MySqlConnection mySqlCon = GetConnection())

{

mySqlCon.Open();

MySqlTransaction sqlTran=mySqlCon.BeginTransaction(IsolationLevel.ReadCommitted);

MySqlBulkLoader sqlBulkCopy= newMySqlBulkLoader(mySqlCon);

sqlBulkCopy.Timeout= 60;if (dataSet.Tables.Count == 1)

result= BulkInsert(sqlBulkCopy, dataSet.Tables[0], sqlTran);else{foreach (DataTable dt indataSet.Tables)

{

result=BulkInsert(sqlBulkCopy, dt, sqlTran);if (!result)break;

}

}

}

}returnresult;

}public stringDBName

{get { return this._dbName; }

}public T GetConnection()

{

T result= default(T);if (string.IsNullOrEmpty(this._dbName))

result= DALFactory.GetDatabaseConnection(this._dBVender);elseresult= DALFactory.GetDatabaseConnection(this._dBVender, this._dbName);returnresult;

}#endregion

private void fillParameters(MySqlCommand mySqlCmd, string[] paramNames, object[] paramValues)

{if (paramNames == null || paramNames.Length == 0)return;if (paramValues == null || paramValues.Length == 0)return;if (paramNames.Length !=paramValues.Length)throw new ArgumentException("The Name Count of parameters does not match its Value Count!");stringname;objectvalue;for (int i = 0; i < paramNames.Length; i++)

{

name=paramNames[i];

value=paramValues[i];if (value != null)

mySqlCmd.Parameters.AddWithValue(name, value);elsemySqlCmd.Parameters.AddWithValue(name, DBNull.Value);

}

}

}

}