代码如下:/// <summary>
/// 批量操作表
/// </summary>
/// <param name="cmdType"></param>
/// <param name="dt"></param>
/// <param name="strTblName">数据库表名</param>
/// <param name="where">除主键外的条件</param>
/// <returns></returns>
private int BulkTable(EnumCommandType cmdType, DataTable dt, string strTblName, string where) {
int affect = 0;
lock (locker) {
StringBuilder cmdFields = new StringBuilder();
foreach (DataColumn column in dt.Columns) {
cmdFields.Append(column.ColumnName + ",");
}
if (cmdFields.Length > 0) {
cmdFields = cmdFields.Remove(cmdFields.Length - 1, 1);
}
using (SqlCommand myCommand = this.BuildCommand(string.Format("select top 0 {0} from {1}", cmdFields.ToString(), strTblName))) {
try {
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder {
DataAdapter = myAdapter,
SetAllValues = true,
ConflictOption = ConflictOption.OverwriteChanges,
};
myCommandBuilder.RefreshSchema();
switch (cmdType) {
case EnumCommandType.Insert:
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand(true);
break; case EnumCommandType.Update:
myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(true);
break; case EnumCommandType.Delete:
myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand(true);
break;
}
//myAdapter.RowUpdating += new SqlRowUpdatingEventHandler(myAdapter_RowUpdating);
if (!string.IsNullOrEmpty(where)) {
myAdapter.UpdateCommand.CommandText = myAdapter.UpdateCommand.CommandText.Replace("WHERE", "WHERE " + where + " and ");
}
affect = myAdapter.Update(dt);
}
catch (Exception ex) {
throw ex;
}
finally {
this.Dispose();
}
}
}
return affect;
}由于更新的数据高达上万条,一条一条的在程序里修改非常耗时,找到这个用这个方法进行批量修改,但并不知道如何自定义条件我准备在更新时,除了主键做为条件外,还想自定义条件。但修改了myAdapter.UpdateCommand.CommandText 后,一调用 affect = myAdapter.Update(dt);被重设了并更新了数据库的记录。请问有什么方法可以达到我想要的这个目的不?
/// 批量操作表
/// </summary>
/// <param name="cmdType"></param>
/// <param name="dt"></param>
/// <param name="strTblName">数据库表名</param>
/// <param name="where">除主键外的条件</param>
/// <returns></returns>
private int BulkTable(EnumCommandType cmdType, DataTable dt, string strTblName, string where) {
int affect = 0;
lock (locker) {
StringBuilder cmdFields = new StringBuilder();
foreach (DataColumn column in dt.Columns) {
cmdFields.Append(column.ColumnName + ",");
}
if (cmdFields.Length > 0) {
cmdFields = cmdFields.Remove(cmdFields.Length - 1, 1);
}
using (SqlCommand myCommand = this.BuildCommand(string.Format("select top 0 {0} from {1}", cmdFields.ToString(), strTblName))) {
try {
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder {
DataAdapter = myAdapter,
SetAllValues = true,
ConflictOption = ConflictOption.OverwriteChanges,
};
myCommandBuilder.RefreshSchema();
switch (cmdType) {
case EnumCommandType.Insert:
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand(true);
break; case EnumCommandType.Update:
myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(true);
break; case EnumCommandType.Delete:
myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand(true);
break;
}
//myAdapter.RowUpdating += new SqlRowUpdatingEventHandler(myAdapter_RowUpdating);
if (!string.IsNullOrEmpty(where)) {
myAdapter.UpdateCommand.CommandText = myAdapter.UpdateCommand.CommandText.Replace("WHERE", "WHERE " + where + " and ");
}
affect = myAdapter.Update(dt);
}
catch (Exception ex) {
throw ex;
}
finally {
this.Dispose();
}
}
}
return affect;
}由于更新的数据高达上万条,一条一条的在程序里修改非常耗时,找到这个用这个方法进行批量修改,但并不知道如何自定义条件我准备在更新时,除了主键做为条件外,还想自定义条件。但修改了myAdapter.UpdateCommand.CommandText 后,一调用 affect = myAdapter.Update(dt);被重设了并更新了数据库的记录。请问有什么方法可以达到我想要的这个目的不?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货