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

///
/// 快速保存数据，自动识别insert和update ///
///需要保存的源数据表 ///数据库连接 ///输出参数，错误信息 ///是否保持连接，可选参数，默认否 /// 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 colList = new List();
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[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 #region sb = new StringBuilder();
sb.AppendFormat("select top 0 {
0}
into {
1}
from {
2}
s;
", tempTableFullCloumn, tempTableName, sourceTable.TableName);
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
//创建临时表 /* 根据获得的目标表主键，来为SQL Server 系统中的临时表增加相应的非主键但是数据相等 * 的 影射列，因为有些系统的主键为自增类型，在调用bulk.WriteToServer方法的时候，自增主键会 * 在临时表中从0开始计算，没办法用临时表的主键和目标表的主键做 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);
//将数据写入临时表 //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 #region if (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();
//插入临时表数据到目的表 //_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();
//更新已存在主键数据 //_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();
//插入新数据 //_errorMsg += "插入" + insertNum + "条记录";
//MessageBox.Show("共用时" + 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;
}