SQL Server大量数据秒级插入/新增/删除

1.快速保存，该方法有四个参数，第一个参数为数据库连接，第二个参数为需要保存的DataTable，该参数的TableName属性需要设置为数据库中目标数据表的表名，第三个参数为输出参数，如果保存过程中发生错误则错误信息会输出在这个参数里面，第四个参数为可选参数，是否保持连接为打开状态。

        /// <summary>
        /// 快速保存数据，自动识别insert和update
        /// </summary>
        /// <param name="_sourceTable">需要保存的源数据表</param>
        /// <param name="_sqlCon">数据库连接</param>
        /// <param name="_errorMsg">输出参数，错误信息</param>
        /// <param name="KeepConnectionAlive">是否保持连接，可选参数，默认否</param>
        /// <returns></returns>
        private bool BulkSave(DataTable _sourceTable, SqlConnection _sqlCon,out string _errorMsg, bool _keepConnectionAlive = false)
        {
            bool result = true;
           _errorMsg = string.Empty;
            DataTable sourceTable = _sourceTable.Copy();
            if (string.IsNullOrEmpty(sourceTable.TableName))
            {
                _errorMsg = "数据源表的TableName属性不能为空！";
                return false;
            }
            List<string> colList = new List<string>();
            foreach (DataColumn col in sourceTable.Columns)
            {
                colList.Add(col.ColumnName);
            }
            int updateNum, insertNum;
            updateNum = insertNum = 0;
            try
            {
                #region
                if (_sqlCon.State == ConnectionState.Closed)
                {
                    _sqlCon.Open();
                }
                SqlCommand cmd = _sqlCon.CreateCommand();
                StringBuilder sb = new StringBuilder();
                DataTable pk = new DataTable();
                string tempTableName = "#" + sourceTable.TableName;//#表名 为当前连接有效的临时表 ##表名 为全局有效的临时表
                string tempTableFullCloumn = "";//临时表获取表结构命令字符串
                string updateSetStr = "";//update set 命令字符串
                string insertWhereStr = "";//insert 命令用来排除已经存在记录的 not exist 命令中where条件字符串
                string insertColumnStr = "";//列名字符串
                string tempColmunstr = "";//t.+列名 字符串

                sb = new StringBuilder();
                sb.AppendFormat(@"select a.name as Name,b.name as 'type',a.length as 'length' ,a.collation as 'collation' from syscolumns a
                                  left join systypes b 
                                  on a.xtype = b.xtype 
                                    where colid in 
                                        (select colid from sysindexkeys 
                                            where id = object_id('{0}') 
                                            and indid = 
                                                (select indid from sysindexes 
                                                    where name = (select name from sysobjects 
                                                        where xtype='PK' 
                                                        and parent_obj = object_id('{0}')
                                                                  )
                                                 )
                                         ) and a.id = object_id('{0}');", sourceTable.TableName);
                cmd.CommandText = sb.ToString();
                pk.Load(cmd.ExecuteReader());//查询主键列表
                #endregion

                #region
                /* 利用传递进来的DataTable列名列表，从数据库的源表获取
                     * 临时表的表结构*/
                for (int i = 0; i < colList.Count; i++)
                {

                    /* 如果当前列是主键,set命令字符串跳过不作处理,
                     * 临时表获取表结构命令字符串不论何种情况都不跳过 */

                    if (pk.Select("Name= '" + (colList