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);
}
}
}
}