我的数据是从远程数据库读取到本地数据库的,,好像是不可以用 Adapter 先 Fill 再 Update回去吧?
因为数据量比较大 一条条处理速度太慢,,
当中当中有插入 有修改 不过好没有删除,,但以防万一我还是做了,,
并且表实在太多 不能每一个都做模版 所以我做了一个通用的 ,,
运行起来不报错,,但是记录却没有插入成功 请高手指点
下边贴我的代码
StringBuilder sb_InsertColumns = new StringBuilder();
StringBuilder sb_Update = new StringBuilder();
StringBuilder sb_Delete = new StringBuilder();
StringBuilder sb_InsertValue = new StringBuilder();
string CMD_InsertSql = "INSERT INTO {0} ({1}) VALUES ({2})";
string CMD_UpdateSql = "Update {0} Set {1} WHERE {2}";
string CMD_DeleteSql = "DELETE FROM {0} WHERE {1}"; for (int i = 0; i < dtb.Columns.Count; i++)
{
if (i == 0)
{
sb_Delete.Append(dtb.Columns[i].ColumnName + " = @" + dtb.Columns[i].ColumnName);
}
else
{
sb_Update.Append(dtb.Columns[i].ColumnName + " = @" + dtb.Columns[i].ColumnName + " , ");
}
sb_InsertColumns.Append(dtb.Columns[i].ColumnName + ",");
sb_InsertValue.Append("@" + dtb.Columns[i].ColumnName + ","); }
if (sb_InsertColumns.Length > 0 || sb_Update.Length > 0 || sb_InsertValue.Length > 0)
{
sb_InsertColumns.Remove(sb_InsertColumns.Length - 1, 1);
//updateValue.Remove(updateValue.Length - 1, 1);
sb_Update.Remove(sb_Update.Length - 1, 1);
sb_InsertValue.Remove(sb_InsertValue.Length - 1, 1);
}
else
{
throw new Exception();
} // 手动建立更新逻辑
SqlConnection conn = new SqlConnection(connectionStringLocal);
SqlCommand InsertCmd = new SqlCommand(string.Format(CMD_InsertSql, dtb.TableName, sb_InsertColumns.ToString(), sb_InsertValue.ToString()), conn); SqlCommand DeleteCmd = new SqlCommand(string.Format(CMD_DeleteSql, dtb.TableName, sb_Delete.ToString()), conn); SqlCommand UpdateCmd = new SqlCommand(string.Format(CMD_UpdateSql, dtb.TableName, sb_Update.ToString(), sb_Delete.ToString()), conn); for (int i = 0; i < dtb.Columns.Count; i++)
{
InsertCmd.Parameters.Add("@" + dtb.Columns[i].ColumnName, SqlDbType.VarChar, 500, dtb.Columns[i].ColumnName);
UpdateCmd.Parameters.Add("@" + dtb.Columns[i].ColumnName, SqlDbType.VarChar, 500, dtb.Columns[i].ColumnName);
if (i == 0)
{
DeleteCmd.Parameters.Add("@" + dtb.Columns[i].ColumnName, SqlDbType.VarChar, 500, dtb.Columns[i].ColumnName);
}
} try
{
conn.Open();
// 将DataSet的数据更新提交到数据库
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = UpdateCmd;
dataAdapter.InsertCommand = InsertCmd;
dataAdapter.DeleteCommand = DeleteCmd; // Update the dataset changes in the data source
dataAdapter.Update(dt_temp); // Commit all the changes made to the DataSet
dt_temp.AcceptChanges();
}
}
catch
{ }
finally
{
InsertCmd.Dispose();
DeleteCmd.Dispose();
UpdateCmd.Dispose();
conn.Close();
conn.Dispose();
}
因为数据量比较大 一条条处理速度太慢,,
当中当中有插入 有修改 不过好没有删除,,但以防万一我还是做了,,
并且表实在太多 不能每一个都做模版 所以我做了一个通用的 ,,
运行起来不报错,,但是记录却没有插入成功 请高手指点
下边贴我的代码
StringBuilder sb_InsertColumns = new StringBuilder();
StringBuilder sb_Update = new StringBuilder();
StringBuilder sb_Delete = new StringBuilder();
StringBuilder sb_InsertValue = new StringBuilder();
string CMD_InsertSql = "INSERT INTO {0} ({1}) VALUES ({2})";
string CMD_UpdateSql = "Update {0} Set {1} WHERE {2}";
string CMD_DeleteSql = "DELETE FROM {0} WHERE {1}"; for (int i = 0; i < dtb.Columns.Count; i++)
{
if (i == 0)
{
sb_Delete.Append(dtb.Columns[i].ColumnName + " = @" + dtb.Columns[i].ColumnName);
}
else
{
sb_Update.Append(dtb.Columns[i].ColumnName + " = @" + dtb.Columns[i].ColumnName + " , ");
}
sb_InsertColumns.Append(dtb.Columns[i].ColumnName + ",");
sb_InsertValue.Append("@" + dtb.Columns[i].ColumnName + ","); }
if (sb_InsertColumns.Length > 0 || sb_Update.Length > 0 || sb_InsertValue.Length > 0)
{
sb_InsertColumns.Remove(sb_InsertColumns.Length - 1, 1);
//updateValue.Remove(updateValue.Length - 1, 1);
sb_Update.Remove(sb_Update.Length - 1, 1);
sb_InsertValue.Remove(sb_InsertValue.Length - 1, 1);
}
else
{
throw new Exception();
} // 手动建立更新逻辑
SqlConnection conn = new SqlConnection(connectionStringLocal);
SqlCommand InsertCmd = new SqlCommand(string.Format(CMD_InsertSql, dtb.TableName, sb_InsertColumns.ToString(), sb_InsertValue.ToString()), conn); SqlCommand DeleteCmd = new SqlCommand(string.Format(CMD_DeleteSql, dtb.TableName, sb_Delete.ToString()), conn); SqlCommand UpdateCmd = new SqlCommand(string.Format(CMD_UpdateSql, dtb.TableName, sb_Update.ToString(), sb_Delete.ToString()), conn); for (int i = 0; i < dtb.Columns.Count; i++)
{
InsertCmd.Parameters.Add("@" + dtb.Columns[i].ColumnName, SqlDbType.VarChar, 500, dtb.Columns[i].ColumnName);
UpdateCmd.Parameters.Add("@" + dtb.Columns[i].ColumnName, SqlDbType.VarChar, 500, dtb.Columns[i].ColumnName);
if (i == 0)
{
DeleteCmd.Parameters.Add("@" + dtb.Columns[i].ColumnName, SqlDbType.VarChar, 500, dtb.Columns[i].ColumnName);
}
} try
{
conn.Open();
// 将DataSet的数据更新提交到数据库
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = UpdateCmd;
dataAdapter.InsertCommand = InsertCmd;
dataAdapter.DeleteCommand = DeleteCmd; // Update the dataset changes in the data source
dataAdapter.Update(dt_temp); // Commit all the changes made to the DataSet
dt_temp.AcceptChanges();
}
}
catch
{ }
finally
{
InsertCmd.Dispose();
DeleteCmd.Dispose();
UpdateCmd.Dispose();
conn.Close();
conn.Dispose();
}
换成 DataSet 也行 不过好像没什么区别吧,,,我这个数据来源也是用 SqlHelper ExecuteDataSet 获得的 DataSet
但因为装载的DataSet 是作为一个 temp 所以修改次数太多,,可能不太适合作这里进行填充数据的数据源吧,,,所以我在进行这些语句前 把 里边的数据 用 ImportRow 添加到一个新的 DataTable 里了
看了,,没有问题,,,insert into tablename (columns) values (@columns)
这种格式,,没问题的,,而且 作为参数值,,我在后便也故意写了一个添加cmd的参数
catch
{ }
了当然不会报错,把catch去掉看报什么错
----------------------------------------------------------------SqlServer 2000
我在主楼说过 一个在远程 一个在本地,,,
至于数据订阅,,不考虑这个,,,
因为测试的时候是 1W4数据量 用了好几分钟,,
这次数据量突然多了 5W8 那时间就更长了,,所以才考虑使用批量写入的方法一开始做的比较简单,标识列 主键都拿掉了,,以远程服务器数据为主,
本地存在同ID的 先 DELETE 后INSERT 其实根本涉及不到 UPDATE
但因为数据量太大 速度太慢 所以现在要优化插入数据的速度
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM table", conn); DataSet ds = new DataSet();
da.Fill(ds, "table"); //然后用SqlCommandBuilder自动为SqlDataAdapter生成Insert、Update、Delete命令
SqlCommandBuilder commBuilder = new SqlCommandBuilder(da); da.Update(ds.Tables["table"]);
ds.AcceptChanges();
不过我只查到Insert 的 不能 Update么 我设置一个标识列 他根据这个列里的记录在表中是否存在
而进行更新还是插入动作的自动调用?
在DAL层实现数据库操作
DbTransaction DbTransaction = connection.BeginTransaction();
this.cmd.CommandText = selectText;
cmd.Connection = G_connection;
cmd.Transaction = DbTransaction;
其中涵盖了很多操作数据库的方法。
结贴,,问题已经解决,,,用药师的 数据订阅 就可以了,,速度确实快了万倍不只
虽然没有 update 我先做了一个 delete动作 有点小遗憾
{
bool result = false;
SqlDataAdapter ada = new SqlDataAdapter();
SqlTransaction tran = this.cn.BeginTransaction("NewTran");
SqlCommand Icmd = new SqlCommand();
SqlCommand Ucmd = new SqlCommand();
SqlCommand Dcmd = new SqlCommand();
SqlCommand Ecmd = new SqlCommand();
Icmd.Connection = this.cn;
Ucmd.Connection = this.cn;
Dcmd.Connection = this.cn;
Ecmd.Connection = this.cn;
Icmd.Transaction = tran;
Ucmd.Transaction = tran;
Dcmd.Transaction = tran;
Ecmd.Transaction = tran;
CreateSQLCmd cmdSQL = new CreateSQLCmd();
try
{
try
{
int i;
for (i = 0; i < this.sourceSQL.Count; i++)
{
Ecmd.CommandText = this.sourceSQL[i];
Ecmd.ExecuteNonQuery();
}
for (i = 0; i < this.dsTables.Tables.Count; i++)
{
Icmd.CommandText = cmdSQL.InsSQL(this.dsTables.Tables[i]);
Ucmd.CommandText = cmdSQL.UpdSQL(this.dsTables.Tables[i]);
Dcmd.CommandText = cmdSQL.DelSQL(this.dsTables.Tables[i]);
DataColumn[] colKey = this.dsTables.Tables[i].PrimaryKey;
cmdSQL.AddParamter(this.dsTables.Tables[i], Icmd);
cmdSQL.AddParamter(this.dsTables.Tables[i], Ucmd);
cmdSQL.AddParamter(colKey, Dcmd);
ada.InsertCommand = Icmd;
ada.UpdateCommand = Ucmd;
ada.DeleteCommand = Dcmd;
ada.Update(this.dsTables.Tables[i]);
}
tran.Commit();
result = true;
}
catch (Exception ex)
{
if (!string.IsNullOrEmpty(ex.Message))
{
this.errorMsg = ex.Message;
tran.Rollback();
result = false;
}
}
}
finally
{
}
this.ClearData();
return result;
}public string InsSQL(DataTable InsertTable)
{
if (((InsertTable.TableName == null) || (InsertTable.Rows.Count <= 0)) || (InsertTable.TableName == ""))
{
return "";
}
string tableName = InsertTable.TableName;
string Fields = "";
string Values = "";
for (int i = 0; i < InsertTable.Columns.Count; i++)
{
if (!ServerTools.ToBoolean(InsertTable.Columns[i].ExtendedProperties["CalcField"]))
{
if (Fields == "")
{
Fields = "insert " + tableName + " (" + InsertTable.Columns[i].ColumnName;
Values = " values (@" + InsertTable.Columns[i].ColumnName;
}
else
{
Fields = Fields + ", " + InsertTable.Columns[i].ColumnName;
Values = Values + ", @" + InsertTable.Columns[i].ColumnName;
}
}
}
Fields = Fields + ")";
Values = Values + ")";
return (Fields + Values);
}
public string UpdSQL(DataTable UpdateTable)
{
if (UpdateTable.Rows.Count <= 0)
{
return "";
}
string tableName = UpdateTable.TableName;
string Fields = "";
string Wheres = "";
DataColumn[] keys = UpdateTable.PrimaryKey;
for (int i = 0; i < UpdateTable.Columns.Count; i++)
{
if (!ServerTools.ToBoolean(UpdateTable.Columns[i].ExtendedProperties["CalcField"]))
{
if (Fields == "")
{
Fields = string.Format("update {0} set {1}=@{1}", tableName, UpdateTable.Columns[i].ColumnName);
}
else
{
Fields = Fields + string.Format(",{0}=@{0}", UpdateTable.Columns[i].ColumnName);
}
}
}
for (int j = 0; j < keys.Length; j++)
{
if (Wheres == "")
{
Wheres = string.Format(" where {0}=@{0}", keys[j].ColumnName);
}
else
{
Wheres = Wheres + string.Format(" and {0}=@{0}", keys[j].ColumnName);
}
}
return (Fields + Wheres);
}
public string DelSQL(DataTable DeleteTable)
{
if (DeleteTable.Rows.Count <= 0)
{
return "";
}
string tableName = DeleteTable.TableName;
string strDelete = "";
DataColumn[] keys = DeleteTable.PrimaryKey;
for (int i = 0; i < keys.GetLength(0); i++)
{
if (strDelete == "")
{
strDelete = string.Format("delete {0} where {1}=@{1}", tableName, keys[i].ColumnName);
}
else
{
strDelete = strDelete + string.Format(" and {0}=@{0}", keys[i].ColumnName);
}
}
return strDelete;
}
public void AddParamter(DataTable table, SqlCommand command)
{
SqlParameter[] parameter = new SqlParameter[table.Columns.Count];
int i = 0;
command.Parameters.Clear();
for (int j = 0; j < table.Columns.Count; j++)
{
parameter[i] = new SqlParameter();
parameter[i].ParameterName = "@" + table.Columns[j].ColumnName;
parameter[i].SourceColumn = table.Columns[j].ColumnName;
command.Parameters.Add(parameter[i]);
i++;
}
}