有关更新记录集的问题

Question

参考它:)public void SaveToDataBase()
{
//存贮数据
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
try
{
SetStatus("正在连接数据库...");
conn.ConnectionString = ConnectString;
conn.Open();
System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(SelectString,conn);
System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adp);
builder.RefreshSchema();
SetStatus("正在存贮数据...");
adp.Update(objDs.ACGStat);
}
catch(Exception Ex)
{
MessageBox.Show(this,"错误:"+Ex.Message.ToString(),"错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
finally
{
if (!conn.State.Equals(System.Data.ConnectionState.Closed))
{
conn.Close();
}

}
}

Answer 1

首先取得数据,放到DataGrid里System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("server=localhost;database=northWind;uid=sa;password=110");
conn.Open();
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("select * from student",conn);
dt = new System.Data.DataSet();
da.Fill(dt,"student");然后绑定数据集和DataGrid
DataGrid.SetDataBinding(dt,"student");
如果需要，可以绑定TextBox来作录入，而用DataGrid显示
this.textBox16.DataBindings.Add("Text",dt,"student.stuno");
然后进行数据的操作如：
增加:
this.BindingContext[dt,"student"].AddNew();
删除:
this.BindingContext[dt,"student"].RemoveAt(this.BindingContext[dt,"student"].Position);
最后把结果写回数据库:
// sqlInsertCommand1
// 
this.sqlInsertCommand1.CommandText = "INSERT INTO student(stuno, name) VALUES (@stuno, @name)"; 
this.sqlInsertCommand1.Connection = this.conn;
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@stuno", System.Data.SqlDbType.VarChar, 4, "stuno"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar, 50, "name"));
// 
// sqlUpdateCommand1
// 
this.sqlUpdateCommand1.CommandText = "UPDATE student SET stuno = @stuno, name = @name WHERE (stuno = @Original_stuno)";
this.sqlUpdateCommand1.Connection = this.conn;
this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@stuno", System.Data.SqlDbType.VarChar, 4, "stuno"));
this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar, 50, "name"));
this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_stuno", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "stuno", System.Data.DataRowVersion.Original, null));
 
// sqlDeleteCommand1
// 
this.sqlDeleteCommand1.CommandText = "DELETE FROM student WHERE (stuno = @Original_stuno)";
this.sqlDeleteCommand1.Connection = this.conn;
this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_stuno", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "stuno", System.Data.DataRowVersion.Original, null));

this.sqlDa.DeleteCommand = this.sqlDeleteCommand1;
this.sqlDa.InsertCommand = this.sqlInsertCommand1;
this.sqlDa.UpdateCommand = this.sqlUpdateCommand1;
try
{
sqlDa.Update(dt.GetChanges,"student"); 
return true;
}
catch(System.Data.SqlClient.SqlException ex)
{

return false;
} 
finally
{
conn.Close();
}

Answer 2

不一定适合你。参照一下。那个循环应该还有更有效率的写法
private void btn_save_Click(object sender, System.EventArgs e)
{
DataRow foundRow;
string mc="",id="";
int l_count=0;
SqlConnection myConnection = new SqlConnection(publics.Comm.StringConnection);
myConnection.Open();

SqlTransaction trans=myConnection.BeginTransaction();
try
{
for(int i=0;i<qxDataSet1.jc_dqlz.Rows.Count;i++)
{
     
foundRow=qxDataSet1.jc_dqlz.Rows[i];
if(foundRow.RowState!=DataRowState.Deleted)
{
mc=foundRow["mc"].ToString();
id=foundRow["dqid"].ToString();
if(mc=="" || mc==null)
{
MessageBox.Show("地区名称不能为空！","提示",MessageBoxButtons.OK,
MessageBoxIcon.Warning);
return ;
}
//MessageBox.Show(foundRow.RowState.ToString());
if(foundRow.RowState==DataRowState.Added) //如果行新增,  被修改过foundRow.RowState==DataRowState.Modified
{
//MessageBox.Show("Added");
string myQuery="select count(*) from jc_dqlz where mc='"+mc+"'";
SqlCommand CleaCommand=new SqlCommand(myQuery,myConnection);
CleaCommand.Transaction=trans;
l_count=(int)CleaCommand.ExecuteScalar();

}
else if(foundRow.RowState==DataRowState.Modified)
{
//MessageBox.Show("modified");
string myQuery="select count(*) from jc_dqlz where mc='"+mc+"'  and dqid<>'"+id+"'"; 
SqlCommand CleaCommand=new SqlCommand(myQuery,myConnection);
CleaCommand.Transaction=trans;
l_count=(int)CleaCommand.ExecuteScalar();
}
if(l_count>0)
{
MessageBox.Show("地区名称["+mc+"]已经存在！请重新输入。","提示",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
return ;
}
//foundRow.AcceptChanges();
}

} sqlDAdq.Update(qxDataSet1,"jc_dqlz");
trans.Commit();
}
catch(SqlException ew)
{
trans.Rollback();
publics.Comm.f_DisplaySqlErrors(ew);
}
finally
{
myConnection.Close();
}
}

Answer 3

简而言之，就是用适配器的update方法，当然要有CommandBuilder