轉(zhuǎn)載自詩(shī)人江湖老,原文地址

/// <summary>/// 快速保存數(shù)據(jù)，自動(dòng)識(shí)別insert和update/// </summary>/// <param name="_sourceTable">需要保存的源數(shù)據(jù)表</param>/// <param name="_sqlCon">數(shù)據(jù)庫(kù)連接</param>/// <param name="_errorMsg">輸出參數(shù)，錯(cuò)誤信息</param>/// <param name="KeepConnectionAlive">是否保持連接，可選參數(shù)，默認(rèn)否</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 = "數(shù)據(jù)源表的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{#regionif (_sqlCon.State == ConnectionState.Closed){_sqlCon.Open();}SqlCommand cmd = _sqlCon.CreateCommand();StringBuilder sb = new StringBuilder();DataTable pk = new DataTable();string tempTableName = "#" + sourceTable.TableName;//#表名 為當(dāng)前連接有效的臨時(shí)表 ##表名 為全局有效的臨時(shí)表string tempTableFullCloumn = "";//臨時(shí)表獲取表結(jié)構(gòu)命令字符串string updateSetStr = "";//update set 命令字符串string insertWhereStr = "";//insert 命令用來排除已經(jīng)存在記錄的 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 aleft 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/* 利用傳遞進(jìn)來的DataTable列名列表，從數(shù)據(jù)庫(kù)的源表獲取* 臨時(shí)表的表結(jié)構(gòu)*/for (int i = 0; i < colList.Count; i++){/* 如果當(dāng)前列是主鍵,set命令字符串跳過不作處理,* 臨時(shí)表獲取表結(jié)構(gòu)命令字符串不論何種情況都不跳過 */if (pk.Select("Name= '" + (colList[i]) + "'").Length > 0){string sql = string.Format("SELECT COLUMNPROPERTY(OBJECT_ID('{0}'), '{1}', 'IsIdentity')", sourceTable.TableName, colList[i]);cmd.CommandText = sql;bool flag = Convert.ToBoolean(cmd.ExecuteScalar());if (!flag){if (updateSetStr.Length > 0){updateSetStr += ",";}if (insertColumnStr.Length > 0){insertColumnStr += ",";}if (tempColmunstr.Length > 0){tempColmunstr += ",";}updateSetStr += colList[i] + "= t." + colList[i];insertColumnStr += colList[i];tempColmunstr += colList[i];}}else{if (updateSetStr.Length > 0){updateSetStr += ",";}if (insertColumnStr.Length > 0){insertColumnStr += ",";}if (tempColmunstr.Length > 0){tempColmunstr += ",";}updateSetStr += colList[i] + "= t." + colList[i];insertColumnStr += colList[i];tempColmunstr += colList[i];}if (i > 0){tempTableFullCloumn += ",";}tempTableFullCloumn += "s." + colList[i];}#endregion#regionsb = new StringBuilder();sb.AppendFormat("select top 0 {0} into {1} from {2} s;", tempTableFullCloumn, tempTableName, sourceTable.TableName);cmd.CommandText = sb.ToString();cmd.ExecuteNonQuery();//創(chuàng)建臨時(shí)表/* 根據(jù)獲得的目標(biāo)表主鍵，來為SQL Server 系統(tǒng)中的臨時(shí)表增加相應(yīng)的非主鍵但是數(shù)據(jù)相等* 的 影射列，因?yàn)橛行┫到y(tǒng)的主鍵為自增類型，在調(diào)用bulk.WriteToServer方法的時(shí)候，自增主鍵會(huì)* 在臨時(shí)表中從0開始計(jì)算，沒辦法用臨時(shí)表的主鍵和目標(biāo)表的主鍵做 where 條件，故用影射列代替*/for (int i = 0; i < pk.Rows.Count; i++){if (i > 0){insertWhereStr += " and ";}string newColName = pk.Rows[i]["name"].ToString() + "New";sb = new StringBuilder();switch (pk.Rows[i]["type"].ToString()){case "char":case "varchar":case "nchar":case "nvarchar":sb.AppendFormat("alter table {0} add {1} {2}({3}) ", tempTableName, newColName, pk.Rows[i]["Type"].ToString(), pk.Rows[i]["length"]);break;default:sb.AppendFormat("alter table {0} add {1} {2} ", tempTableName, newColName, pk.Rows[i]["Type"].ToString());break;}if (!(pk.Rows[i]["collation"] is DBNull)){sb.AppendFormat("COLLATE {0}", pk.Rows[i]["collation"]);}cmd.CommandText = sb.ToString();cmd.ExecuteNonQuery();sourceTable.Columns.Add(new DataColumn(newColName, sourceTable.Columns[pk.Rows[i]["name"].ToString()].DataType));foreach (DataRow dr in sourceTable.Rows){dr[newColName] = dr[pk.Rows[i]["name"].ToString()].ToString().Trim();}insertWhereStr += "t." + newColName + "=s." + pk.Rows[i]["name"];}using (System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(_sqlCon)){//string SQl = "select * from #bulktable ";//DataTable tempx = new DataTable();//cmd.CommandText = SQl;//tempx.Load(cmd.ExecuteReader());//_souceTable.Rows[0]["unit_name"] = string.Empty;//_souceTable.Rows[1]["unit_name"] = string.Empty;int colCount = sourceTable.Columns.Count;foreach (DataRow row in sourceTable.Rows){for (int i = 0; i < colCount; i++){row[i] = row[i].ToString().Trim();}}bulk.DestinationTableName = tempTableName;bulk.BulkCopyTimeout = 36000;try{bulk.WriteToServer(sourceTable);//將數(shù)據(jù)寫入臨時(shí)表//string sql = "select * from #bulktable";//SqlDataAdapter sda = new SqlDataAdapter(sql, _sqlCon);//DataTable dt = new DataTable();//sda.Fill(dt);}catch (Exception e){_errorMsg = e.Message;result = false;//MessageBox.Show(e.Message);//return e.Message.Trim();}}#endregion#regionif (insertWhereStr.Equals(""))//如果不存在主鍵{sb = new StringBuilder();sb.AppendFormat("insert into {0} select {1} from {2} s;", sourceTable.TableName, tempTableFullCloumn, tempTableName);cmd.CommandText = sb.ToString();insertNum = cmd.ExecuteNonQuery();//插入臨時(shí)表數(shù)據(jù)到目的表//_errorMsg = "1";}else{sb = new StringBuilder();sb.AppendFormat("update {0} set {1} from( {2} t INNER JOIN {0} s on {3} );",sourceTable.TableName, updateSetStr, tempTableName, insertWhereStr);//cmd.CommandText = sb.ToString();//Stopwatch sw = new Stopwatch();//sw.Start();//updateNum = cmd.ExecuteNonQuery();//更新已存在主鍵數(shù)據(jù)//_errorMsg += "更新" + updateNum + "條記錄";//sw.Stop();//sb = new StringBuilder();sb.AppendFormat("insert into {0}({4}) select {1} from {2} t where not EXISTS(select 1 from {0} s where {3});",sourceTable.TableName, tempColmunstr, tempTableName, insertWhereStr, insertColumnStr);cmd.CommandText = sb.ToString();//insertNum = cmd.ExecuteNonQuery();//插入新數(shù)據(jù)//_errorMsg += "插入" + insertNum + "條記錄";//MessageBox.Show("共用時(shí)" + sw.Elapsed + "\n 共新增:" + insertNum + "條記錄,更新:" + updateNum + "條記錄！");//return_str = "1";var st = _sqlCon.BeginTransaction();cmd.Transaction = st;try{cmd.ExecuteNonQuery();st.Commit();}catch (Exception ee){_errorMsg += ee.Message;result = false;st.Rollback();}}#endregion}catch (Exception e){_errorMsg = e.Message.Trim();result = false;}finally{if (!_keepConnectionAlive && _sqlCon.State == ConnectionState.Open){_sqlCon.Close();}}return result;}

2.快速刪除，該方法有四個(gè)參數(shù)，第一個(gè)參數(shù)為數(shù)據(jù)庫(kù)連接，第二個(gè)參數(shù)為需要?jiǎng)h除的DataTable，該參數(shù)的TableName屬性需要設(shè)置為數(shù)據(jù)庫(kù)中目標(biāo)數(shù)據(jù)表的表名，第三個(gè)參數(shù)為輸出參數(shù)，如果刪除過程中發(fā)生錯(cuò)誤則錯(cuò)誤信息會(huì)輸出在這個(gè)參數(shù)里面，第四個(gè)參數(shù)為可選參數(shù)，是否保持連接為打開狀態(tài)。

/// <summary>/// 快速刪除/// </summary>/// <param name="_sourceTable">需要?jiǎng)h除的源數(shù)據(jù)表</param>/// <param name="_sqlCon">數(shù)據(jù)庫(kù)連接</param>/// <param name="_errorMsg">輸出參數(shù)，錯(cuò)誤信息</param>/// <param name="_keepConnectionAlive">是否保持連接，可選參數(shù)，默認(rèn)否</param>/// <returns></returns>private bool BulkDelete(DataTable _sourceTable, SqlConnection _sqlCon, out string _errorMsg, bool _keepConnectionAlive = false){bool result = true;_errorMsg = string.Empty;DataTable sourceTable = _sourceTable.Copy();string SQl = "";DataTable pkTable = new DataTable();DataSet ds = new DataSet();string whereStr = string.Empty;string colList = string.Empty;if (string.IsNullOrEmpty(sourceTable.TableName)){_errorMsg += "數(shù)據(jù)源表的TableName屬性不能為空！";return false;}try{#region 檢查數(shù)據(jù)表是否存在SqlCommand sqlComm = _sqlCon.CreateCommand();SqlDataAdapter sda = new SqlDataAdapter();string tempTableName = "#" + sourceTable.TableName;SQl = string.Format("select COUNT(*) from sysobjects where id = object_id(N'[{0}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1", sourceTable.TableName);sqlComm.CommandText = SQl;if (_sqlCon.State != ConnectionState.Open){_sqlCon.Open();}int count = Convert.ToInt32(sqlComm.ExecuteScalar());#endregionif (count == 0){_errorMsg += string.Format("在數(shù)據(jù)庫(kù)中，找不到名為{0}的數(shù)據(jù)表！", sourceTable.TableName);}else{#region 獲取主鍵信息SQl = string.Format(@"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);sqlComm.CommandText = SQl;sda.SelectCommand = sqlComm;sda.Fill(ds, "pkTable");pkTable = ds.Tables["pkTable"];#endregion#region 生成where條件foreach (DataColumn col in sourceTable.Columns){colList += colList.Length == 0 ? col.ColumnName : "," + col.ColumnName;}SQl = string.Format("select top 0 {0} into {1} from {2}", colList, tempTableName, sourceTable.TableName);sqlComm.CommandText = SQl;sqlComm.ExecuteNonQuery();if (pkTable.Rows.Count <= 0){_errorMsg += string.Format("獲取{0}表主鍵信息失敗，請(qǐng)重試或者檢查數(shù)據(jù)庫(kù)！", sourceTable.TableName);}else{foreach (DataRow dr in pkTable.Rows){string newColName = dr["name"].ToString() + "New";/* 如果當(dāng)前列是主鍵,set命令字符串跳過不作處理,* 臨時(shí)表獲取表結(jié)構(gòu)命令字符串不論何種情況都不跳過 */SQl = string.Format("SELECT COLUMNPROPERTY(OBJECT_ID('{0}'), '{1}', 'IsIdentity')", sourceTable.TableName, dr["name"]);sqlComm.CommandText = SQl;bool flag = Convert.ToBoolean(sqlComm.ExecuteScalar());switch (dr["type"].ToString()){case "char":case "varchar":case "nchar":case "nvarchar":SQl = string.Format("alter table {0} add {1} {2}({3}) ", tempTableName, newColName, dr["Type"].ToString(), dr["length"]);break;default:SQl = string.Format("alter table {0} add {1} {2} ", tempTableName, newColName, dr["Type"].ToString());break;}if (!(dr["collation"] is DBNull)){SQl = string.Format("{0} COLLATE {1}", SQl, dr["collation"]);}sqlComm.CommandText = SQl;sqlComm.ExecuteNonQuery();whereStr += string.IsNullOrEmpty(whereStr) ? string.Format("{0}.{2} in( select {1}.[{3}] from {1} )", sourceTable.TableName, tempTableName, dr["name"], newColName) : string.Format(" and {0}.{2} in( select {1}.[{3}] from {1} )", sourceTable.TableName, tempTableName, dr["name"], newColName);sourceTable.Columns.Add(new DataColumn(newColName, sourceTable.Columns[dr["name"].ToString()].DataType));foreach (DataRow row in sourceTable.Rows){row[newColName] = row[dr["name"].ToString()].ToString().Trim();}}}}#endregion#region 將數(shù)據(jù)放進(jìn)臨時(shí)表SqlBulkCopy bulk = new SqlBulkCopy(_sqlCon);bulk.DestinationTableName = tempTableName;bulk.BulkCopyTimeout = 3600;try{bulk.WriteToServer(sourceTable);}catch (Exception ee){_errorMsg += ee.Message;bulk.Close();}#endregion#region 開始刪除//SQl = string.Format("select * from {0}", tempTableName);//sqlComm.CommandText = SQl;//sda.SelectCommand = sqlComm;//sda.Fill(ds, tempTableName);SQl = string.Format(@" DELETE FROM {0} WHERE {1}", sourceTable.TableName, whereStr);sqlComm.CommandText = SQl;var tx = _sqlCon.BeginTransaction();try{sqlComm.Transaction = tx;count = sqlComm.ExecuteNonQuery();tx.Commit();_errorMsg += string.Format("應(yīng)該刪除{0}條記錄\r\n共刪除{1}條記錄！", sourceTable.Rows.Count, count);}catch (Exception ee){_errorMsg += ee.Message;tx.Rollback();}#endregion}catch (Exception e){_errorMsg += e.Message;}finally{if (_sqlCon.State == ConnectionState.Open && !_keepConnectionAlive){_sqlCon.Close();}}return result;}

添加鏈接描述

總結(jié)

以上是生活随笔為你收集整理的SQL Server大量数据秒级插入/新增/删除的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。