//自动批量DataSet至数据库 public static int Update(DataSet ds) { int res = 0; using (SqlConnection sqlconn = new SqlConnection("Data Source=.;Initial Catalog=NorthWind;User ID=sa;Password=sa")) { sqlconn.Open(); SqlTransaction tran = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted); try { foreach (DataRow dr in ds.Tables[0].Rows) {
dr.SetModified(); } //用于定位DataAdapter目标表 SqlCommand cmd = new SqlCommand(string.Format("select * from {0} where 1=0 ", ds.Tables[0].TableName), sqlconn, tran); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da); da.AcceptChangesDuringUpdate = false; //sqlCmdBuilder.SetAllValues = false; SqlCommand updatecmd = new SqlCommand(" UPDATE [Customers] SET [CompanyName] = @CompanyName WHERE ([CustomerID] = @CustomerID) "); updatecmd.UpdatedRowSource = UpdateRowSource.None; da.UpdateCommand = updatecmd; da.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 50, "CompanyName "); da.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, ds.Tables[0].Columns[0].ColumnName); da.UpdateBatchSize = 10000; res = da.Update(ds.Tables[0],ds.Tables[0].TableName); ds.AcceptChanges(); tran.Commit(); sqlconn.Close(); } catch { tran.Rollback(); return -1; } } return res; }
UpdateCommand你构造了么?
SqlCommand scCommand = scConnection.CreateCommand();
scCommand.CommandText = "select customerID,contactName from customers";
//建立Adapter
sdaAdapter = new SqlDataAdapter(scCommand); //该对象负责生成用于更新数据库的SQL语句,不必自己创建这些语句
scbBuilder = new SqlCommandBuilder(sdaAdapter);
这里的 sdaAdapter 就是你的 oleDbDataAdapter1
this.oleDbDataAdapter1.SelectCommand.CommandText = "select * from ReportData";
this.oleDbDataAdapter1.Fill(this.dataSet11, "ReportData");
this.dataGridView1.DataSource = this.dataSet11.Tables["ReportData"]; 放在dataGridView1中,但修改的流值是通過其他控件修改了,現隻想將該數據流值回傳並修改保存煩請你幫忙修改下,謝了!
public static int Update(DataSet ds)
{
int res = 0; using (SqlConnection sqlconn = new SqlConnection("Data Source=.;Initial Catalog=NorthWind;User ID=sa;Password=sa"))
{
sqlconn.Open();
SqlTransaction tran = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
dr.SetModified();
}
//用于定位DataAdapter目标表
SqlCommand cmd = new SqlCommand(string.Format("select * from {0} where 1=0 ", ds.Tables[0].TableName), sqlconn, tran); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da);
da.AcceptChangesDuringUpdate = false; //sqlCmdBuilder.SetAllValues = false; SqlCommand updatecmd = new SqlCommand(" UPDATE [Customers] SET [CompanyName] = @CompanyName WHERE ([CustomerID] = @CustomerID) "); updatecmd.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand = updatecmd;
da.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 50, "CompanyName ");
da.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 4, ds.Tables[0].Columns[0].ColumnName);
da.UpdateBatchSize = 10000;
res = da.Update(ds.Tables[0],ds.Tables[0].TableName);
ds.AcceptChanges();
tran.Commit();
sqlconn.Close(); }
catch
{
tran.Rollback();
return -1;
}
}
return res; }
我目的是这样的: 有一个资料表字段为: 图表名称 + 图表流(二进制格式) 1. 我现要先将打开资料表字段为: 图表名称 放在 dataGridView;
2. 在用户选择 dataGridView中的一个图表名称后用其它控件打开并修改
3. 现如何将修改的图表流(二进制格式)保存修改回原打开的图表名称对应的图表流
注: =this.rmReport1.ReportData是可以取出修改的图表流(二进制格式)烦请各位帮忙修正及指教! 谢谢!
2. 在用户选择 dataGridView中的一个图表名称后用其它控件打开并修改
3. 现如何将修改的图表流(二进制格式)保存修改回原打开的图表名称对应的图表流
注: =this.rmReport1.ReportData是可以取出修改的图表流(二进制格式) 烦请各位帮忙修正及指教! 谢谢!
this.oleDbDataAdapter1.Update(this.dataSet11, "ReportData");
之前加上:
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da);
就行了