C# Winform下一個熱插拔的MIS/MRP/ERP框架12（資料處理基類）
摘要：
作為ERP等資料應用程式，資料庫的處理是重中之重。
在框架中，我封裝了一個數據庫的基類，在每個模組啟動或窗體啟動過程中，例項化一個基類即可呼叫CRUD操作（create 新增read讀取 update 修改delete刪除），當然，還包括基礎的SQL事務處理。
這個過程中，還考慮到對外...
在框架中，我封裝了一個數據庫的基類，在每個模組啟動或窗體啟動過程中，例項化一個基類即可呼叫CRUD操作（create 新增read讀取 update 修改delete刪除），當然，還包括基礎的SQL事務處理。
這個過程中，還考慮到對外掛的管控，利用反射增加了呼叫授權，避免未經授權的程式盜用資料連線。
看看完整的程式碼：
/// <summary>
/// 資料庫連線基類
/// </summary>
public class DBContext
{
/// <summary>
/// 預設的加密方法Key,用於使用者密碼加密等次要場合
/// </summary>
private readonly string DftAESKeyOfDlls = "James Wang";
/// <summary>
/// 資料庫連線字串和用於外掛DLL或EXE的註冊加密KEY,根據程式集的標題加密,寫入到說明中,後面對2個值進行比對.
/// </summary>
private readonly string PlugRegKey = "*^*James/Wang/";
//private string DftAESKeyOfDllsOfBase = "12345876543210";
/// <summary>
/// 本類內部明文連線串
/// </summary>
private readonly string ConnStr = null;
/// <summary>
/// 經測試後,資料庫是否可正常連線
/// </summary>
public bool IsConnected = false;
/// <summary>
/// 連接出錯後記錄的錯誤資訊
/// </summary>
public string ConnectErrorMessage = string.Empty;
/// <summary>
/// 加密後的資料庫連線字串,引用的地方要進行解密
/// </summary>
public string ConnStrShare = null;
#region 資料連線的初始化
/// <summary>
/// 以全域性資訊的ConnStrCurAct連線串為依據初始化一個數據庫連線.
/// </summary>
public DBContext()
{
//獲取呼叫者的Title
string tmpTitle = AssemblyInfos.GetAsbAttr(AssemblyInfos.AttrType.Title, Assembly.GetCallingAssembly());
//獲取呼叫者的說明
string tmpDesc = AssemblyInfos.GetAsbAttr(AssemblyInfos.AttrType.Description, Assembly.GetCallingAssembly());
if (tmpTitle != OCrypto.AES16Decrypt(tmpDesc, PlugRegKey))
{
MyMsg.Warning("呼叫者:"+tmpTitle+"未經過框架授權,無法引用資料連線.");
return;
}
//沒有指定連線則獲取當前操作帳套的連線字串;
ConnStrShare = GlbInfo.ConnStrCurAct;
ConnStr = OCrypto.AES16Decrypt(GlbInfo.ConnStrCurAct, PlugRegKey);
Initial();
}
/// <summary>
/// 以一個加密後的連線字串為依據初始化一個數據庫連線.
/// </summary>
/// <param name="connStrEncrypted">已使用系統預設加密的連線字串</param>
public DBContext(string connStrEncrypted)
{
//獲取呼叫者的Title
string tmpTitle = AssemblyInfos.GetAsbAttr(AssemblyInfos.AttrType.Title, Assembly.GetCallingAssembly());
//獲取呼叫者的說明
string tmpDesc = AssemblyInfos.GetAsbAttr(AssemblyInfos.AttrType.Description, Assembly.GetCallingAssembly());
if (tmpTitle != OCrypto.AES16Decrypt(tmpDesc, PlugRegKey))
{
MyMsg.Warning("呼叫者:" + tmpTitle + "未經過框架授權,無法引用資料連線.");
return;
}
ConnStrShare = connStrEncrypted;
ConnStr = OCrypto.AES16Decrypt(connStrEncrypted, PlugRegKey);
Initial();
}
private void Initial()
{
try
{
bool authorized = true;//這裡取消了管制直接為True
if (authorized)
{
if (ConnStr == null)
{
throw (new Exception("連線字串沒有提供."));
}
else
{
IsConnected = TestConnection();
}
}
else
{
return;
}
}
catch (Exception ex)
{
MyMsg.Asterisk("未授權的操作!");
throw ex;
}
}
/// <summary>
/// 測試資料庫連線是否正常
/// </summary>
/// <returns></returns>
private bool TestConnection()
{
try
{
ConnectErrorMessage = string.Empty;
using (SqlConnection con = new SqlConnection(ConnStr))
{
con.Open();
return true;
}
}
catch (Exception ex)
{
ConnectErrorMessage = ex.Message;
return false;
}
}
#endregion
#region SQL作業區域
/// <summary>
/// 執行UPDATE、INSERT 和 DELETE 語句，返回值為該命令所影響的行數。對於所有其他型別的語句，返回值為 -1。如果發生回滾，返回值也為 -1
/// </summary>
/// <param name="sqlText"></param>
/// <param name="paras">引數列表,如new SqlParameter ("@Age",100)</param>
public int RunSql(string sqlText, params SqlParameter[] paras)
{
try
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sqlText, con))
{
cmd.Parameters.AddRange(paras);
int rst = cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); //清除掉引數,如有OUTPUT則不能使用此句
return rst;
}
}
}
catch (Exception ex)
{
MyMsg.Exclamation(ex.Message);
throw ex;
}
}
/// <summary>
/// 批量執行SQL語句,傳入SQL語句+引數的字典,如果是同樣的語句重複執行,則使用"Lead"+i+"||||||"(6個|)的方法區分開每條語句避免傳入失敗.
/// 各語句成功執行返回0,失敗回滾返回-1
/// </summary>
/// <param name="dicSqls">SQL語句+引數字典</param>
/// <param name="cmdTimeOut">CommandTimeout,此引數大於30時才起作用</param>
/// <returns></returns>
public int RunSqlsInTran(Dictionary<string, SqlParameter[]> dicSqls, int cmdTimeOut = 0)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction()) //開始一個事務
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = tran;//必須指定事務物件
//設定一個超時時間,需時過長的應該放在儲存過程中處理
if (cmdTimeOut > 30) cmd.CommandTimeout = cmdTimeOut;
//開始
if (dicSqls != null && dicSqls.Count >= 0)
{
foreach (KeyValuePair<string, SqlParameter[]> sqls in dicSqls)
{
int m = sqls.Key.IndexOf("||||||");
if (m >= 0)
{
cmd.CommandText = sqls.Key.Substring(m + 6);
}
else cmd.CommandText = sqls.Key;
cmd.Parameters.Clear();
if (sqls.Value != null) cmd.Parameters.AddRange(sqls.Value);
cmd.ExecuteNonQuery();
}
}
//結束
cmd.Parameters.Clear();//清除掉引數,如有OUTPUT則不能使用此句
}
try
{
tran.Commit();//事務提交
return 0;
}
catch
{
tran.Rollback();//事務回滾
return -1;
}
finally
{
}
}//事務結束
}
}
catch (Exception ex)
{
MyMsg.Exclamation(ex.Message);
return -1;
}
}
/// <summary>
/// 按順序批量執行SQL語句.
/// 傳入SQL語句+引數的字典,如果是同樣的語句重複執行,則使用"Lead"+i+"||||||"的方法區分開每條語句避免傳入失敗.
/// 成功執行後返回"{success:ok}"字串,如果執行步驟出錯,則返回步驟Lead字串+"{error:..."的狀態字,事務過程中出錯返回"{exception-tran:..."
/// 傳入的所有語句都要有具有返回值(select要有記錄,update等要有影響的行數),沒有返回值則發生回滾。
/// </summary>
/// <param name="dicSqls"></param>
/// <param name="cmdTimeOut">CommandTimeout,此引數大於30時才起作用</param>
/// <returns></returns>
public string RunSeqSqlsInTran(Dictionary<string, SqlParameter[]> dicSqls, int cmdTimeOut = 0)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
string rtnValue = string.Empty;
using (SqlTransaction tran = conn.BeginTransaction()) //開始一個事務
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = tran;//必須指定事務物件
if (cmdTimeOut > 30) cmd.CommandTimeout = cmdTimeOut;//設定一個超時時間,需時過長的應該放在儲存過程中處理
//開始
if (dicSqls != null && dicSqls.Count >= 0)
{
object tmpO = null;
int runRstRows = 0;
foreach (KeyValuePair<string, SqlParameter[]> sqls in dicSqls)
{
int m = sqls.Key.IndexOf("||||||");
if (m >= 0)
{
rtnValue = sqls.Key.Substring(0, m);
cmd.CommandText = sqls.Key.Substring(m + 6);
}
else
{
rtnValue = "未指定的作業";
cmd.CommandText = sqls.Key;
}
cmd.Parameters.Clear();
if (sqls.Value != null) cmd.Parameters.AddRange(sqls.Value);
if (cmd.CommandText.Trim().Substring(0, 6) == "SELECT")
{
tmpO = cmd.ExecuteScalar();
if (tmpO == null)
{
tran.Rollback();//事務回滾
return "{error:" + rtnValue + "}";
}
}
else
{
runRstRows = cmd.ExecuteNonQuery();
if (runRstRows <= 0)
{
tran.Rollback();//事務回滾
return "{error:" + rtnValue + "}";
}
}
}
}
//結束
cmd.Parameters.Clear();//清除掉引數,如有OUTPUT則不能使用此句
}
try
{
tran.Commit();//事務提交
return "{success:ok}";
}
catch (Exception ex)
{
tran.Rollback();//事務回滾
return "{exception-tran:" + ex.Message + "}";
}
finally
{
}
}//事務結束
}
}
catch (Exception ex)
{
MyMsg.Exclamation(ex.Message);
return "{exception:" + ex.Message + "}";
}
}
/// <summary>
/// 執行SQL語句,並填充指定的Dataset中的表(在填充之前會清空此表的原有資料,如有主從關聯,則不能建立約束,或在執行前清除關聯子表).
/// </summary>
/// <param name="tagDS">目標Dataset</param>
/// <param name="tableName">目標表名</param>
/// <param name="sqlText">SQL語句</param>
/// <param name="paras">引數</param>
/// <returns></returns>
public bool SqlToDS(DataSet tagDS, string tableName, string sqlText, params SqlParameter[] paras)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
tagDS.Tables[tableName].Clear();//先清除再填充,如有建立主從關聯的會出錯,可在呼叫端先處理清除.
adapter.Fill(tagDS, tableName);
cmd.Parameters.Clear();//清除掉引數,如有OUTPUT則不能使用此句
return true;
}
}
}
catch (Exception ex)
{
MyMsg.Information("提取資料到Dataset出錯.", ex.Message);
return false;
}
}
/// <summary>
/// 執行SQL語句,並返回一個Datatable結果集
/// </summary>
/// <param name="sqlText"></param>
/// <param name="paras"></param>
/// <returns></returns>
public DataTable SqlToDT(string sqlText, params SqlParameter[] paras)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
cmd.Parameters.Clear();//清除掉引數,如有OUTPUT則不能使用此句
return dataset.Tables[0];
}
}
}
catch (Exception ex)
{
return null;
throw ex;
}
}
/// <summary>
/// 執行SQL語句,並返回第一行第一列的值為string,如果記錄不存在則返回空字串, 執行出錯則返回{error:XX}
/// </summary>
/// <param name="sqlText">sql語句</param>
/// <param name="paras">引數</param>
/// <returns></returns>
public string SqlToString(string sqlText, params SqlParameter[] paras)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(paras);
object rtnObj = cmd.ExecuteScalar();
cmd.Parameters.Clear();//清除掉引數,如有OUTPUT則不能使用此句
if (rtnObj == null)
{
return string.Empty;
}
else return rtnObj.ToString();
}
}
}
catch (Exception ex)
{
return "{error:" + ex.Message + "}";
}
}
/// <summary>
/// 從不同伺服器插入大量資料到目標伺服器的資料表中(使用了系統加密後的連線字串)
/// </summary>
/// <param name="targetConnStr">加密後的目標伺服器的連線字串</param>
/// <param name="targetDBTable">目標伺服器中的目標表名稱</param>
/// <param name="sourceConnStr">加密後的資料獲取來源伺服器的連線字串</param>
/// <param name="sourceSqlText">獲取資料的SQL語句</param>
/// <param name="columnMap">表結構對映(可留空)</param>
public bool BulkCopy(string targetConnStr, string targetDBTable, string sourceConnStr, string sourceSqlText, Dictionary<string, string> columnMap = null)
{
//還原連線字串
targetConnStr = OCrypto.AES16Decrypt(targetConnStr, DftAESKeyOfDlls);
sourceConnStr = OCrypto.AES16Decrypt(sourceConnStr);
// 源
using (SqlConnection sourceConnection = new SqlConnection(sourceConnStr))
{
SqlCommand myCommand = new SqlCommand(sourceSqlText, sourceConnection);
sourceConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();
// 目的
using (SqlConnection targetConn = new SqlConnection(targetConnStr))
{
// 開啟連線
targetConn.Open();
using (SqlTransaction tran = targetConn.BeginTransaction()) //開始一個事務
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConn))
{
if (columnMap != null && columnMap.Count >= 0)
{
foreach (KeyValuePair<string, string> pair in columnMap)
{
bulkCopy.ColumnMappings.Add(pair.Key, pair.Value);
}
}
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 5000;
bulkCopy.NotifyAfter = 100000;
bulkCopy.DestinationTableName = targetDBTable;
bulkCopy.WriteToServer(reader);
}
try
{
tran.Commit();//事務提交
return true;
}
catch
{
tran.Rollback();//事務回滾
return false;
}
finally
{
reader.Close();
}
}
}
}
}
/// <summary>
/// 從Datatable中插入大量資料到目標伺服器的資料表中
/// </summary>
/// <param name="targetConnStr">目標伺服器的連線字串</param>
/// <param name="targetDBTable">目標伺服器中的目標表名稱</param>
/// <param name="sourceDT">原始資料Datatable</param>
/// <param name="columnMap">表結構對映(可留空)</param>
public bool BulkCopyDT(string targetConnStr, string targetDBTable, DataTable sourceDT, Dictionary<string, string> columnMap = null)
{
//還原連線字串
targetConnStr = OCrypto.AES16Decrypt(targetConnStr, DftAESKeyOfDlls);
using (SqlConnection targetConn = new SqlConnection(targetConnStr))
{
// 開啟連線
targetConn.Open();
using (SqlTransaction tran = targetConn.BeginTransaction()) //開始一個事務
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConn, SqlBulkCopyOptions.Default, tran))//放入事務之中
{
if (columnMap != null && columnMap.Count >= 0)
{
foreach (KeyValuePair<string, string> pair in columnMap)
{
bulkCopy.ColumnMappings.Add(pair.Key, pair.Value);
}
}
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 800;
bulkCopy.NotifyAfter = 80000;
bulkCopy.DestinationTableName = targetDBTable;
bulkCopy.WriteToServer(sourceDT);
}
try
{
tran.Commit();//事務提交
return true;
}
catch
{
tran.Rollback();//事務回滾
return false;
}
}
}
}
/// <summary>
/// 先插入到根據目標表建立的臨時表,再做相關插入操作.如果目標表中有自增列，則表結構必須全部欄位對映.如果先刪除後插入,請注意使用的是TRUNCATE TABLE模式,資料不可恢復.
/// </summary>
/// <param name="targetConnStr">目標伺服器的連線字串</param>
/// <param name="targetDBTable">目標伺服器中的目標表名稱</param>
/// <param name="sourceConnStr">資料獲取來源伺服器的連線字串</param>
/// <param name="sourceSqlText">獲取資料的SQL語句</param>
/// <param name="keyField">主鍵欄位名稱(預設需提供),先刪舊後插入新的情況下,可以留空.</param>
/// <param name="DelBeforInsert">是否先刪除舊資料,再新增新資料(預設為否)</param>
/// <param name="columnMap">表結構對映(可留空)</param>
/// <param name="hasAutoRKEY">如果有自增列，則表結構必須對映</param>
/// <param name="needUpdateFlds">是否需要對舊資料執行更新操作,是的話則必須指定更新表示式</param>
/// <param name="updFldsName">舊資料更新表示式,以逗號分隔.如:(YYPass,YYDept)表示將寫入目標表中的YYPass,YYDept欄位更新為來源資料.</param>
public bool BulkCopyByTempTable(string targetConnStr, string targetDBTable, string sourceConnStr, string sourceSqlText, string keyField = "", bool DelBeforInsert = false, Dictionary<string, string> columnMap = null, bool hasAutoRKEY = false, bool needUpdateFlds = false, string updFldsName = "")
{
//還原連線字串
targetConnStr = OCrypto.AES16Decrypt(targetConnStr, DftAESKeyOfDlls);
sourceConnStr = OCrypto.AES16Decrypt(sourceConnStr, DftAESKeyOfDlls);
string columnsList = string.Empty;
string whereStr = string.Empty;
// 源
using (SqlConnection sourceConnection = new SqlConnection(sourceConnStr))
{
SqlCommand myCommand = new SqlCommand(sourceSqlText, sourceConnection);
sourceConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();
// 目的
using (SqlConnection targetConn = new SqlConnection(targetConnStr))
{
// 開啟連線
targetConn.Open();
using (SqlTransaction tran = targetConn.BeginTransaction()) //開始一個事務
{
string tmpTabName = "atmpBCP" + DateTime.Now.Ticks.ToString();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = targetConn;
cmd.Transaction = tran;//必須指定事務物件
cmd.CommandText = "SELECT * INTO " + tmpTabName + " FROM " + targetDBTable + " WHERE 1=2 "; //依照目標表建立臨時表
cmd.ExecuteNonQuery();
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConn, SqlBulkCopyOptions.Default, tran))//放入事務之中
{
if (columnMap != null && columnMap.Count >= 0)
{
foreach (KeyValuePair<string, string> pair in columnMap)
{
bulkCopy.ColumnMappings.Add(pair.Key, pair.Value);
columnsList += pair.Value + ",";
}
columnsList = columnsList.Substring(0, columnsList.Length - 1);
}
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 800;
bulkCopy.NotifyAfter = 80000;
bulkCopy.DestinationTableName = tmpTabName;
bulkCopy.WriteToServer(reader);
}
//完成資料到臨時表的插入後
using (SqlCommand dCommand = new SqlCommand())
{
dCommand.Connection = targetConn;
dCommand.Transaction = tran;//必須指定事務物件
if (DelBeforInsert) //先刪除再插入
{
dCommand.CommandText = "TRUNCATE TABLE " + targetDBTable;
dCommand.ExecuteNonQuery();
//插入新記錄
if (hasAutoRKEY)
{
dCommand.CommandText = "INSERT INTO " + targetDBTable + " (" + columnsList + ") SELECT " + columnsList + " FROM " + tmpTabName;
}
else
{
dCommand.CommandText = "INSERT INTO " + targetDBTable + " SELECT * FROM " + tmpTabName;
}
dCommand.ExecuteNonQuery();
}
else //插入不存在的記錄
{
//如果需要則更新舊資料,舊錶和臨時表結構一致
if (needUpdateFlds)
{
if (!string.IsNullOrEmpty(updFldsName))
{
string updStr = string.Empty;
string[] updFlds = OString.SplitStr(updFldsName, ",");//獲取要更新的欄位名
foreach (string fldName in updFlds)
{
updStr += "[" + fldName + "]=" + tmpTabName + "." + "[" + fldName + "],";
}
updStr = OString.CutLastStrIf(updStr, ",");
dCommand.CommandText = "UPDATE " + targetDBTable + " SET " + updStr + " FROM " + tmpTabName + " INNER JOIN " + targetDBTable + " ON " + tmpTabName + "." + keyField + " = " + targetDBTable + "." + keyField + "";
dCommand.ExecuteNonQuery();
}
}
//插入不存在的新記錄
if (!string.IsNullOrEmpty(keyField))
{
whereStr = " WHERE (" + keyField + " NOT IN (SELECT " + keyField + " FROM " + targetDBTable + "))";
}
if (hasAutoRKEY)
{
dCommand.CommandText = "INSERT INTO " + targetDBTable + " (" + columnsList + ") SELECT " + columnsList + " FROM " + tmpTabName + whereStr;
}
else
{
dCommand.CommandText = "INSERT INTO " + targetDBTable + " SELECT * FROM " + tmpTabName + whereStr;
}
dCommand.ExecuteNonQuery();
}
}
try
{
tran.Commit();//事務提交
return true;
}
catch
{
tran.Rollback();//事務回滾
return false;
}
finally
{
reader.Close();
//tran.Dispose();
using (SqlCommand dCmd = new SqlCommand())
{
dCmd.Connection = targetConn;
dCmd.CommandText = "DROP TABLE " + tmpTabName;//操作完成刪除臨時表
dCmd.ExecuteNonQuery();
}
}
}
}
}
}
/// <summary>
/// 先清空目標表,再直接插入.
/// </summary>
/// <param name="targetConnStr">目標伺服器的連線字串</param>
/// <param name="targetDBTable">目標伺服器中的目標表名稱</param>
/// <param name="sourceConnStr">資料獲取來源伺服器的連線字串</param>
/// <param name="sourceSqlText">獲取資料的SQL語句</param>
/// <param name="columnMap">表結構對映(可留空)</param>
public bool BulkCopyFirstClear(string targetConnStr, string targetDBTable, string sourceConnStr, string sourceSqlText, Dictionary<string, string> columnMap = null)
{
//還原連線字串
targetConnStr = OCrypto.AES16Decrypt(targetConnStr, DftAESKeyOfDlls);
sourceConnStr = OCrypto.AES16Decrypt(sourceConnStr, DftAESKeyOfDlls);
// 源
using (SqlConnection sourceConnection = new SqlConnection(sourceConnStr))
{
SqlCommand myCommand = new SqlCommand(sourceSqlText, sourceConnection);
sourceConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();
// 目的
using (SqlConnection targetConn = new SqlConnection(targetConnStr))
{
targetConn.Open();// 開啟連線
using (SqlTransaction tran = targetConn.BeginTransaction()) //開始一個事務
{
using (SqlCommand dCommand = new SqlCommand())
{
dCommand.Connection = targetConn;
dCommand.Transaction = tran;//必須指定事務物件
dCommand.CommandText = "TRUNCATE TABLE " + targetDBTable;
dCommand.ExecuteNonQuery();
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConn, SqlBulkCopyOptions.Default, tran))//將批量作業加入事務之中
{
if (columnMap != null && columnMap.Count >= 0)
{
foreach (KeyValuePair<string, string> pair in columnMap)
{
bulkCopy.ColumnMappings.Add(pair.Key, pair.Value);
}
}
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 800;
bulkCopy.NotifyAfter = 80000;
bulkCopy.DestinationTableName = targetDBTable;
bulkCopy.WriteToServer(reader);
}
try
{
tran.Commit();//事務提交
return true;
}
catch
{
tran.Rollback();//事務回滾
return false;
}
finally
{
reader.Close();
}
}
}
}
}
#endregion
#region 儲存過程PROCEDURE作業區域
/// <summary>
/// 執行儲存過程,返回儲存過程中return的int值
/// </summary>
/// <param name="procName">儲存過程名稱</param>
/// <param name="paras">SqlParameter[]</param>
/// <returns></returns>
public int RunProcedure(string procName, params SqlParameter[] paras)
{
try
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(procName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paras);
SqlParameter sp = cmd.Parameters.Add("procReturn", SqlDbType.Int);
sp.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int pr = Convert.ToInt32(cmd.Parameters["procReturn"].Value);
return pr;
}
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 執行儲存過程,並返回字串.
/// </summary>
/// <param name="procName">儲存過程名稱</param>
/// <param name="inputOutputPra">輸出引數名稱,必須與儲存過程中的引數定義相符</param>
/// <param name="paras">引數</param>
/// <returns></returns>
public string ProcToString(string procName, string inputOutputPra, params SqlParameter[] paras)
{
try
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(procName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paras);
SqlParameter sp = cmd.Parameters.Add(inputOutputPra, SqlDbType.NVarChar, 4000);
sp.Direction = ParameterDirection.InputOutput;
cmd.ExecuteNonQuery();
return cmd.Parameters[inputOutputPra].Value.ToString();
}
}
}
catch (Exception ex)
{
return "{error:" + ex.Message + "}";
}
}
/// <summary>
/// 執行儲存過程,並將第一行第一列作為返回字串返回.
/// </summary>
/// <param name="procName">儲存過程名稱</param>
/// <param name="paras">引數</param>
/// <returns></returns>
public string ProcSelToString(string procName, params SqlParameter[] paras)
{
try
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(procName, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paras);
return cmd.ExecuteScalar().ToString();
}
}
}
catch (Exception ex)
{
return "{error:" + ex.Message + "}";
}
}
/// <summary>
/// 執行儲存過程,並返回DataTable.
/// </summary>
/// <param name="procName">儲存過程名稱</param>
/// <param name="paras">引數</param>
/// <returns></returns>
public DataTable ProcToDT(string procName, params SqlParameter[] paras)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
cmd.Parameters.Clear();//清除掉引數,如有OUTPUT則不能使用此句
return dataset.Tables[0];
}
}
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 判斷記錄是否存在
/// </summary>
/// <param name="fltSql">提供查詢語句,如select A from Table1 where 1=1,必須返回一個欄位</param>
/// <returns></returns>
public bool HasRecord(string fltSql)
{
string tmpRst = SqlToString(fltSql);
if (!string.IsNullOrEmpty(tmpRst))
{
if (!(tmpRst.IndexOf("{error:") >= 0))
{
return true;
}
}
return false;
}
/// <summary>
/// 根據指定查詢條件查詢記錄,沒找到則執行新增語句,找到則執行更新語句.
/// </summary>
/// <param name="Sqlsearch">類似Select a from b where 1=1</param>
/// <param name="sqlUpdate">更新語句</param>
/// <param name="sqlInsert">新增語句</param>
/// <returns></returns>
public bool UpdateOrInsert(string Sqlsearch, string sqlUpdate, string sqlInsert)
{
int ret = 0;
if (HasRecord(Sqlsearch))
{
ret = RunSql(sqlUpdate);
}
else
{
ret = RunSql(sqlInsert);
}
if (ret > 0) return true; else return false;
}
/// <summary>
/// 按照指定的資料來源型別獲取資料DT
/// </summary>
/// <param name="rcdSourceType">資料型別:SQLTEXT,VIEW,PROC,STRINGARRAY,錯誤型別將按SQLTEXT處理</param>
/// <param name="rcdSource">資料來源</param>
/// <param name="whereStr">查詢字串</param>
/// <param name="paras">查詢字串中應用到的引數SqlParameter[]</param>
/// <returns></returns>
public DataTable GetDTBySourceType(string rcdSourceType, string rcdSource, string whereStr, SqlParameter[] paras)
{
rcdSourceType = rcdSourceType.ToUpper();
DataTable dt = new DataTable();
string sqlText = string.Empty;
try
{
switch (rcdSourceType)
{
case "SQLTEXT"://SQL語句,一般應採用檢視,效能比較高
sqlText = "SELECT * FROM (" + rcdSource + ") TmpA";
if (!string.IsNullOrEmpty(whereStr))
{
sqlText += " WHERE " + whereStr;
}
dt = SqlToDT(sqlText, paras);
break;
case "VIEW": //檢視(使用者輸入的引數值用於檢視查詢)
sqlText = "SELECT * FROM " + rcdSource;
if (!string.IsNullOrEmpty(whereStr))
{
sqlText += " WHERE " + whereStr;
}
dt = SqlToDT(sqlText, paras);
break;
case "PROC": //儲存過程只能接收引數,不能再附加WHERE條件
dt = ProcToDT(rcdSource, paras);
break;
case "STRINGARRAY": //字串陣列
dt = OString.StringToDT(rcdSource);
break;
default:
sqlText = "SELECT * FROM (" + rcdSource + ") TmpA";
if (!string.IsNullOrEmpty(whereStr))
{
sqlText += " WHERE " + whereStr;
}
dt = SqlToDT(sqlText, paras);
break;
}
return dt;
}
catch (Exception)
{
return null;
}
}
#endregion
}
DBContext.cs
其中最基礎的一個方法：
/// <summary>
/// 執行UPDATE、INSERT 和 DELETE 語句，返回值為該命令所影響的行數。對於所有其他型別的語句，返回值為 -1。如果發生回滾，返回值也為 -1
/// </summary>
public int RunSql(string sqlText, params SqlParameter[] paras)
{
try
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sqlText, con))
{
cmd.Parameters.AddRange(paras);
int rst = cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); //清除掉引數,如有OUTPUT則不能使用此句
return rst;
}
}
}
catch (Exception ex)
{
MyMsg.Exclamation(ex.Message);
throw ex;
}
}
前提是引用 ：
using System.Data;
using System.Data.SqlClient;
關於引數SqlParameter[]的傳遞：
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@UserID", postUserID),
new SqlParameter("@PasswordHash", postUserPass)
};
然後呼叫：
RunSql("Select * from Users Where UserID=@UserID,PasswordHash=@PasswordHash", paras);
=========================================
大部分操作都可以通過這個類的方法來直接呼叫，我們需要的是編寫優秀的SQL語句，那是一門更需要鑽研的技能。
這個類的例項可以檢視上一章登入窗體的程式碼。