C# Winform下一个热插拔的MIS/MRP/ERP框架12（数据处理基类）
作为ERP等数据应用程序，数据库的处理是重中之重。
在框架中，我封装了一个数据库的基类，在每个模组启动或窗体启动过程中，实例化一个基类即可调用CRUD操作（create 添加read读取 update 修改delete删除），当然，还包括基础的SQL事务处理。
这个过程中，还考虑到对插件的管控，利用反射增加了调用授权，避免未经授权的程序盗用数据连接。
看看完整的代码：
/// <summary> /// 数据库连接基类 /// </summary> public class DBContext { /// <summary> /// 本类内部明文连接串 /// </summary> private string ConnStr = null; /// <summary> /// 经测试后,数据库是否可正常连接 /// </summary> public bool IsConnected = false; /// <summary> /// 连接出错后记录的错误信息 /// </summary> public string ConnectErrorMessage = string.Empty; /// <summary> /// 加密后的数据库连接字符串,引用的地方要进行解密. /// <para>在需要做Socket快速检测的情况下，连接串会包含TestRegion->FastCheckIP:192.168.0.1;FastCheckPort:1433;FastCheckTimeOut:2000;附串，解密后应该去除才能正常使用打开连接。</para> /// </summary> public string ConnStrShare = null; /// <summary> /// 当前DBContext使用的数据库名称 /// </summary> public string DBName = string.Empty; #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, Core.MasterAESKey)) { MyMsg.Warning("调用者:" + tmpTitle + "未经过框架授权,无法引用数据连接."); return; } //没有指定连接则获取当前操作帐套的连接字符串; ConnStrShare = GlbInfo.ConnStrCurAct; ConnStr = OCrypto.AES16Decrypt(GlbInfo.ConnStrCurAct, Core.MasterAESKey); Initial(); } /// <summary> /// 以一个加密后的连接字串为依据初始化一个数据库连接. /// <para>在需要做Socket快速检测的情况下，连接串应包含TestRegion->FastCheckIP:192.168.0.1;FastCheckPort:1433;FastCheckTimeOut:2000;附串以便做Socket快速连接测试。</para> /// </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, Core.MasterAESKey)) { MyMsg.Warning("调用者:" + tmpTitle + "未经过框架授权,无法引用数据连接."); return; } ConnStrShare = connStrEncrypted; ConnStr = OCrypto.AES16Decrypt(connStrEncrypted, Core.MasterAESKey); Initial(); } private void Initial() { try { //StackTrace trace = new StackTrace(); //MethodBase method = trace.GetFrame(1).GetMethod(); //MyMsg.Asterisk(method.Module.Name); //Guid ownGUID = new Guid(((GuidAttribute)Attribute.GetCustomAttribute(Assembly.GetCallingAssembly(), typeof(GuidAttribute))).Value); //MyMsg.Asterisk("GetCallingAssembly:"+ownGUID.ToString()); //Guid ownGUID1 = new Guid(((GuidAttribute)Attribute.GetCustomAttribute(Assembly.GetEntryAssembly(), typeof(GuidAttribute))).Value); //MyMsg.Asterisk("GetEntryAssembly:" + ownGUID1.ToString()); //Guid ownGUID2 = new Guid(((GuidAttribute)Attribute.GetCustomAttribute(Assembly.GetExecutingAssembly(), typeof(GuidAttribute))).Value); //MyMsg.Asterisk("GetExecutingAssembly:" + ownGUID2.ToString()); //Guid ownGUID3 = new Guid(((GuidAttribute)Attribute.GetCustomAttribute(Assembly.GetAssembly(method.Module.GetType()), typeof(GuidAttribute))).Value); //MyMsg.Asterisk("GetAssembly(method.GetType()):"+ownGUID3.ToString()); //调用者在授权模块清单之中.ATCore.dll,AITO.exe,ATCust.exe,ATData.dll,ATProj.exe //bool Authed = StrFunc.StrInArray(method.Module.Name, Crypt.AES16Decrypt(Properties.Settings.Default.CORE_SHARED_TO, AES_KEY)); bool authorized = 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; if(ConnStr.IndexOf("FastCheckIP:")>=0 && ConnStr.IndexOf("FastCheckPort:") >= 0) { string fastChkIP = OString.GetMidStr(ConnStr, "FastCheckIP:", ";").Trim(); int fastCheckPort = OString.NZ2Int(OString.GetMidStr(ConnStr, "FastCheckPort:", ";").Trim()); int fastCheckTimeOut = OString.NZ2Int(OString.GetMidStr(ConnStr, "FastCheckTimeOut:", ";").Trim()); if (!string.IsNullOrEmpty(fastChkIP) && fastCheckPort>0 && fastCheckTimeOut>0) { string textRst = ONetwork.TestSokectConn(fastChkIP, fastCheckPort, fastCheckTimeOut); if (textRst != "OK") { ConnectErrorMessage = textRst; return false; } } } ConnStr = OString.LeftOf(ConnStr, "TestRegion->"); using (SqlConnection con = new SqlConnection(ConnStr)) { con.Open(); DBName = con.Database; return true; } } catch (Exception ex) { ConnectErrorMessage = ex.Message; return false; } } #endregion /// <summary> /// 用MasterAESKey还原已经加密过的字符串,并去除掉额外的用于Socket测试的属性 /// </summary> /// <param name="connStrEncred"></param> /// <returns></returns> private string GetRConnStr(string connStrEncred) { return ConnStr = OString.LeftOf(OCrypto.AES16Decrypt(connStrEncred, Core.MasterAESKey), "TestRegion->"); } #region SQL作业区域 /// <summary> /// 执行UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于所有其他类型的语句，返回值为 -1。如果发生回滚，返回值也为 -1 /// <para>尽量使用 “>=”，不要使用 ">"</para> /// <para>进行Insert、update的时候，应该防止冲突（特别是数据量大的时候，锁会升级）</para> /// <para>LIKE使用注意（尽量不采用 “%1%” 之类的方式处理，多采用 “1% ”、“%1”）</para> /// <para>避免连接字段查询（如 a.c1+'|'+b.c2 = XXXX，应该采用 a.c1= 'X' and b.c2 = 'XXX'）</para> /// </summary> /// <param name="sqlText"></param> /// <param name="paras">参数列表,如new SqlParameter ("@Age",100)</param> public int RunSql(string sqlText, params SqlParameter[] paras) { try { using (SqlConnection conn = new SqlConnection(ConnStr)) { conn.Open(); if(conn.Database!=DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = new SqlCommand(sqlText, conn)) { if (paras != null) 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(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 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(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 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:RunSeqSqlsInTran-" + rtnValue + "}"; } } else { runRstRows = cmd.ExecuteNonQuery(); if (runRstRows <= 0) { tran.Rollback();//事务回滚 return "{error:RunSeqSqlsInTran-" + 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中的表(在填充之前会清空此表的原有数据,如有主从关联,则不能建立约束,或在执行前清除关联子表). /// <para>尽量使用 “>=”，不要使用 ">"</para> /// <para>进行Insert、update的时候，应该防止冲突（特别是数据量大的时候，锁会升级）</para> /// <para>LIKE使用注意（尽量不采用 “%1%” 之类的方式处理，多采用 “1% ”、“%1”）</para> /// <para>避免连接字段查询（如 a.c1+'|'+b.c2 = XXXX，应该采用 a.c1= 'X' and b.c2 = 'XXX'）</para> /// </summary> /// <param name="tagDS">目标Dataset</param> /// <param name="tableName">目标表名,当为空时将填充Tables[0]</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(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sqlText; if (paras != null) cmd.Parameters.AddRange(paras); SqlDataAdapter adapter = new SqlDataAdapter(cmd); if (string.IsNullOrEmpty(tableName)) { tagDS.Tables[0].Clear(); //先清除再填充,如有建立主从关联的会出错,可在调用端先处理清除. adapter.Fill(tagDS.Tables[0]); } else { tagDS.Tables[tableName].Clear(); adapter.Fill(tagDS,tableName); } if(cmd.Parameters!=null) cmd.Parameters.Clear(); //清除掉参数,如有OUTPUT则不能使用此句 return true; } } } catch (Exception) { return false; } } /// <summary> /// 执行SQL语句,并返回一个Datatable结果集 /// <para>尽量使用 “>=”，不要使用 ">"</para> /// <para>进行Insert、update的时候，应该防止冲突（特别是数据量大的时候，锁会升级）</para> /// <para>LIKE使用注意（尽量不采用 “%1%” 之类的方式处理，多采用 “1% ”、“%1”）</para> /// <para>避免连接字段查询（如 a.c1+'|'+b.c2 = XXXX，应该采用 a.c1= 'X' and b.c2 = 'XXX'）</para> /// </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(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sqlText; if (paras != null) 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) { MyMsg.Exclamation(ex.Message); return null; throw ex; } } /// <summary> /// 执行SQL语句,并返回一个Datatable结果集的首行 /// <para>尽量使用 “>=”，不要使用 ">"</para> /// <para>进行Insert、update的时候，应该防止冲突（特别是数据量大的时候，锁会升级）</para> /// <para>LIKE使用注意（尽量不采用 “%1%” 之类的方式处理，多采用 “1% ”、“%1”）</para> /// <para>避免连接字段查询（如 a.c1+'|'+b.c2 = XXXX，应该采用 a.c1= 'X' and b.c2 = 'XXX'）</para> /// </summary> /// <param name="sqlText"></param> /// <param name="paras"></param> /// <returns></returns> public DataRow SqlToDRow(string sqlText, params SqlParameter[] paras) { try { using (SqlConnection conn = new SqlConnection(ConnStr)) { conn.Open(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sqlText; if (paras != null) cmd.Parameters.AddRange(paras); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet dataset = new DataSet(); adapter.Fill(dataset); cmd.Parameters.Clear(); //清除掉参数,如有OUTPUT则不能使用此句 DataTable DTTmp = dataset.Tables[0]; if (DTTmp == null) return null; if (DTTmp.Rows.Count < 1) return null; return DTTmp.Rows[0]; } } } catch (Exception ex) { return null; throw ex; } } /// <summary> /// 执行SQL语句,并返回第一行第一列的值为string,如果记录不存在则返回空字符串, 执行出错则返回{error:XX} /// <para>尽量使用 “>=”，不要使用 ">"</para> /// <para>进行Insert、update的时候，应该防止冲突（特别是数据量大的时候，锁会升级）</para> /// <para>LIKE使用注意（尽量不采用 “%1%” 之类的方式处理，多采用 “1% ”、“%1”）</para> /// <para>避免连接字段查询（如 a.c1+'|'+b.c2 = XXXX，应该采用 a.c1= 'X' and b.c2 = 'XXX'）</para> /// </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(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sqlText; if (paras != null) 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:SqlToString-" + 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 = GetRConnStr(targetConnStr); sourceConnStr = GetRConnStr(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 = GetRConnStr(targetConnStr); 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 = GetRConnStr(targetConnStr); sourceConnStr = GetRConnStr(sourceConnStr); 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 = GetRConnStr(targetConnStr); sourceConnStr = GetRConnStr(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 (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 conn = new SqlConnection(ConnStr)) { conn.Open(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = new SqlCommand(procName, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (paras != null) 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 conn = new SqlConnection(ConnStr)) { conn.Open(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = new SqlCommand(procName, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (paras != null) cmd.Parameters.AddRange(paras); SqlParameter sp = cmd.Parameters.Add(inputOutputPra, SqlDbType.NVarChar, 4000); sp.Direction = ParameterDirection.InputOutput; cmd.ExecuteNonQuery(); return OString.NZ2Str(cmd.Parameters[inputOutputPra].Value); } } } catch (Exception ex) { return "{error:ProcToString-" + 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 conn = new SqlConnection(ConnStr)) { conn.Open(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = new SqlCommand(procName, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (paras != null) cmd.Parameters.AddRange(paras); return OString.NZ2Str(cmd.ExecuteScalar()); } } } catch (Exception ex) { return "{error:ProcSelToString-" + 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(); if (conn.Database != DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题 using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = procName; cmd.CommandType = CommandType.StoredProcedure; if (paras != null) 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> /// 判断记录是否存在(IF EXISTS (查找语句) SELECT 'OK' ELSE SELECT '') /// <para>当输入的语句错误时,也会返回False</para> /// </summary> /// <param name="fltSql">提供查找语句,如SELECT UserID FROM TB20U_User WHERE (UserID = 'uid'),必须返回一个字段</param> /// <returns></returns> public bool HasRecord(string fltSql) { fltSql = "IF EXISTS (" + fltSql + ") SELECT 'OK' ELSE SELECT ''"; string tmpRst = SqlToString(fltSql); if (!string.IsNullOrEmpty(tmpRst)) { if (!(tmpRst.IndexOf("{error:") >= 0)) { return true; } else { MyMsg.Warning("原始SQL语句执行异常,记录存在性判断可能会存在失误!请联系系统管理员处理。", tmpRst); return false; } } 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> /// <param name="orderByStr">排序表达式(如:A DESC,B)</param> /// <returns></returns> public DataTable GetDTBySourceType(string rcdSourceType, string rcdSource, string whereStr, SqlParameter[] paras, string orderByStr = "") { 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; } if (!string.IsNullOrEmpty(orderByStr)) { sqlText += " ORDER BY " + orderByStr; } dt = SqlToDT(sqlText, paras); break; case "VIEW": //视图(用户输入的参数值用于视图查询) sqlText = "SELECT * FROM " + rcdSource; if (!string.IsNullOrEmpty(whereStr)) { sqlText += " WHERE " + whereStr; } if (!string.IsNullOrEmpty(orderByStr)) { sqlText += " ORDER BY " + orderByStr; } 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; } if (!string.IsNullOrEmpty(orderByStr)) { sqlText += " ORDER BY " + orderByStr; } dt = SqlToDT(sqlText, paras); break; } return dt; } catch (Exception) { return null; } } #endregion }
其中最基础的一个方法：
public int RunSql(string sqlText, params SqlParameter[] paras)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
if(conn.Database!=DBName) conn.ChangeDatabase(DBName);//避免架构变更后引起的对象名无效问题
using (SqlCommand cmd = new SqlCommand(sqlText, conn))
{
if (paras != null) 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语句，那是一门更需要钻研的技能。
这个类的实例可以查看上一章登录窗体的代码。
