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语句，那是一门更需要钻研的技能。

这个类的实例可以查看上一章登录窗体的代码。