SQLServer⼤量数据秒级插⼊新增删除
原⽂:
1.快速保存，该⽅法有四个参数，第⼀个参数为数据库连接，第⼆个参数为需要保存的DataTable，该参数的TableName属性需要设置为数据库中⽬标数据表的表名，第三个参数为输出参数，如果保存过程中发⽣错误则错误信息会输出在这个参数⾥⾯，第四个参数为可选参数，是否保持连接为打开状态。
/// <summary>
/// 快速保存数据，⾃动识别inrt和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 = fal)
{
bool result = true;
_errorMsg = string.Empty;
DataTable sourceTable = _sourceTable.Copy();
if (string.IsNullOrEmpty(sourceTable.TableName))
{
_errorMsg = "数据源表的TableName属性不能为空！";
return fal;
}
List<string> colList = new List<string>();
foreach (DataColumn col in sourceTable.Columns)
{
colList.Add(col.ColumnName);
}
int updateNum, inrtNum;
updateNum = inrtNum = 0;
try
{
#region
if (_sqlCon.State == ConnectionState.Clod)
{
_sqlCon.Open();
}
SqlCommand cmd = _sqlCon.CreateCommand();
StringBuilder sb = new StringBuilder();
DataTable pk = new DataTable();
string tempTableName = "#" + sourceTable.TableName;//#表名为当前连接有效的临时表 ##表名为全局有效的临时表
string tempTableFullCloumn = "";//临时表获取表结构命令字符串
string updateSetStr = "";//update t 命令字符串
string inrtWhereStr = "";//inrt 命令⽤来排除已经存在记录的 not exist 命令中where条件字符串
string inrtColumnStr = "";//列名字符串
string tempColmunstr = "";//t.+列名字符串
sb = new StringBuilder();
sb.AppendFormat(@"lect a.name as Name,b.name as 'type',a.length as 'length' ,a.collation as 'collation' from syscolumns a
left join systypes b
pe = b.xtype
where colid in
(lect colid from sysindexkeys
where id = object_id('{0}')
and indid =
(lect indid from sysindexes
where name = (lect 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++)
{
/* 如果当前列是主键,t命令字符串跳过不作处理,
* 临时表获取表结构命令字符串不论何种情况都不跳过 */
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 (inrtColumnStr.Length > 0)
{
inrtColumnStr += ",";
}
if (tempColmunstr.Length > 0)
{
tempColmunstr += ",";
}
updateSetStr += colList[i] + "= t." + colList[i];
inrtColumnStr += colList[i];
tempColmunstr += colList[i];
}
}
el
{
if (updateSetStr.Length > 0)
{
updateSetStr += ",";
}
if (inrtColumnStr.Length > 0)
{
inrtColumnStr += ",";
}
从句if (tempColmunstr.Length > 0)
{
tempColmunstr += ",";
}
updateSetStr += colList[i] + "= t." + colList[i];
inrtColumnStr += colList[i];
tempColmunstr += colList[i];
}
if (i > 0)
{
tempTableFullCloumn += ",";
}
tempTableFullCloumn += "s." + colList[i];
}
}
#endregion
#region
造化弄人英文
sb = new StringBuilder();
sb.AppendFormat("lect top 0 {0} into {1} from {2} s;", tempTableFullCloumn, tempTableName, sourceTable.TableName);
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();//创建临时表
/* 根据获得的⽬标表主键，来为SQL Server 系统中的临时表增加相应的⾮主键但是数据相等
* 的影射列，因为有些系统的主键为⾃增类型，在调⽤bulk.WriteToServer⽅法的时候，⾃增主键会
bright star* 在临时表中从0开始计算，没办法⽤临时表的主键和⽬标表的主键做 where 条件，故⽤影射列代替*/
for (int i = 0; i < pk.Rows.Count; i++)
{
if (i > 0)
{
inrtWhereStr += " and ";
}
string newColName = pk.Rows[i]["name"].ToString() + "New";
sb = new StringBuilder();
switch (pk.Rows[i]["type"].ToString())
{
ca "char":
ca "varchar":
ca "nchar":
ca "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();
}
inrtWhereStr += "t." + newColName + "=s." + pk.Rows[i]["name"];
}
using (System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(_sqlCon))
{
//string SQl = "lect * 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;
bulk.BulkCopyTimeout = 36000;
try
{
bulk.WriteToServer(sourceTable);//将数据写⼊临时表
//string sql = "lect * from #bulktable";
//SqlDataAdapter sda = new SqlDataAdapter(sql, _sqlCon);
//DataTable dt = new DataTable();
/
/sda.Fill(dt);
}
catch (Exception e)
{
_errorMsg = e.Message;
result = fal;
//MessageBox.Show(e.Message);
//return e.Message.Trim();
}rajon rondo
}
#endregion
#region
if (inrtWhereStr.Equals(""))//如果不存在主键
{
sb = new StringBuilder();
sb.AppendFormat("inrt into {0} lect {1} from {2} s;", sourceTable.TableName, tempTableFullCloumn, tempTableName); cmd.CommandText = sb.ToString();
inrtNum = cmd.ExecuteNonQuery();//插⼊临时表数据到⽬的表
//_errorMsg = "1";
}
el
{
sb = new StringBuilder();
sb.AppendFormat("update {0} t {1} from( {2} t INNER JOIN {0} s on {3} );",
sourceTable.TableName, updateSetStr, tempTableName, inrtWhereStr);
//cmd.CommandText = sb.ToString();
//Stopwatch sw = new Stopwatch();
//sw.Start();
//updateNum = cmd.ExecuteNonQuery();//更新已存在主键数据
//_errorMsg += "更新" + updateNum + "条记录";
//sw.Stop();
shinny
//sb = new StringBuilder();
sb.AppendFormat("inrt into {0}({4}) lect {1} from {2} t where not EXISTS(lect 1 from {0} s where {3});",
sourceTable.TableName, tempColmunstr, tempTableName, inrtWhereStr, inrtColumnStr);
cmd.CommandText = sb.ToString();
//inrtNum = cmd.ExecuteNonQuery();//插⼊新数据
//_errorMsg += "插⼊" + inrtNum + "条记录";
//MessageBox.Show("共⽤时" + sw.Elapd + "\n 共新增:" + inrtNum + "条记录，更新:" + updateNum + "条记录！");
//return_str = "1";
var st = _sqlCon.BeginTransaction();
cmd.Transaction = st;
try
{
cmd.ExecuteNonQuery();
st.Commit();
}
catch (Exception ee)
{
_errorMsg += ee.Message;
result = fal;
st.Rollback();
}
}
#endregion
}
}
catch (Exception e)
{
_errorMsg = e.Message.Trim();
result = fal;
}
finally
{
if (!_keepConnectionAlive && _sqlCon.State == ConnectionState.Open)
{
_sqlCon.Clo();
}
}
return result;
}
2.快速删除，该⽅法有四个参数，第⼀个参数为数据库连接，第⼆个参数为需要删除的DataTable，该参数的TableName属性需要设置为数
据库中⽬标数据表的表名，第三个参数为输出参数，如果删除过程中发⽣错误则错误信息会输出在这个参数⾥⾯，第四个参数为可选参数，
是否保持连接为打开状态。
/// <summary>
/// 快速删除
/// </summary>
/// <param name="_sourceTable">需要删除的源数据表</param>
/// <param name="_sqlCon">数据库连接</param>
/// <param name="_errorMsg">输出参数，错误信息</param>
/// <param name="_keepConnectionAlive">是否保持连接，可选参数，默认否</param>
/// <returns></returns>
private bool BulkDelete(DataTable _sourceTable, SqlConnection _sqlCon, out string _errorMsg, bool _keepConnectionAlive = fal)
{
bool result = true;
_errorMsg = string.Empty;
DataTable sourceTable = _sourceTable.Copy();
string SQl = "";
DataTable pkTable = new DataTable();
DataSet ds = new DataSet();martial law
string whereStr = string.Empty;
string colList = string.Empty;
if (string.IsNullOrEmpty(sourceTable.TableName))
{
_errorMsg += "数据源表的TableName属性不能为空！";
return fal;和外国人聊天
}
try
{
#region 检查数据表是否存在
SqlCommand sqlComm = _sqlCon.CreateCommand();
SqlDataAdapter sda = new SqlDataAdapter();
string tempTableName = "#" + sourceTable.TableName;
SQl = string.Format("lect COUNT(*) from sysobjects where id = object_id(N'[{0}]') and OBJECTPROPERTY(id, N'IsUrTable') = 1", sourceTa sqlComm.CommandText = SQl;
if (_sqlCon.State != ConnectionState.Open)
{
_sqlCon.Open();
}
int count = Convert.ToInt32(sqlComm.ExecuteScalar());
#endregion
if (count == 0)
{
_errorMsg += string.Format("在数据库中，找不到名为{0}的数据表！", sourceTable.TableName);
}
el
