首先,你的表必须要有主键,才能删除和更改,其次,你是否用过SqlCommandBuilder 来创建删除修改的sql语句,就像下面的代码一样, SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand(queryString, connection); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); connection.Open(); DataSet dataSet = new DataSet(); adapter.Fill(dataSet, tableName); //code to modify data in DataSet here builder.GetUpdateCommand();
adapter.SelectCommand = new SqlCommand(queryString, connection);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter); connection.Open(); DataSet dataSet = new DataSet();
adapter.Fill(dataSet, tableName); //code to modify data in DataSet here builder.GetUpdateCommand();
顺便贴下测试原码
//数据加载
SqlConnection conn = new SqlConnection("server=127.0.0.1;database=Test_DB;uid=sa;pwd=123456");
SqlCommand scd = new SqlCommand("select name,age,serid,nFieldType,sFieldType from tUser", conn);
sda.SelectCommand = scd;
sda.Fill(dt);
conn.Close();//手工操作dataTable
//手工加一行,并自动更新到数据库
private void button2_Click(object sender, EventArgs e)
{
//dt.Rows.Clear();
DataRow dr = dt.NewRow();
dr["name"] = "哈哈!";
dr["age"] = 24;
dr["nFieldType"] = 3;
dr["sFieldType"] = "varchar"; dt.Rows.Add(dr); dt.Rows.RemoveAt(1);
dt.AcceptChanges();
try
{
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
dt.Rows.Clear();
sda.Update(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
} MessageBox.Show("更新成功!");
}
主键ID是自增长的。如果我更新了一数据,或删除一条数据,都不会对数据库有任务操作
dt = new DataTable();
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
adpter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(adpter);
adpter.UpdateCommand = cmdbuilder.GetUpdateCommand();
adpter.AcceptChangesDuringUpdate = true;
adpter.Fill(dt);
dt.Select("case when SSex=0 then '男' else '女' end");
GridViewColumn cs = new GridViewColumn();
cs.DisplayMemberBinding = new Binding();
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
adpter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(adpter);
adpter.UpdateCommand = cmdbuilder.GetUpdateCommand();
adpter.AcceptChangesDuringUpdate = true;
adpter.Fill(dt);
dt.AcceptChanges();