DataSet数据集，数据缓存在客户端内存中，支持断开式连接.DataGridView控件绑定DataSet时，它自动的改变的DS的行的状态,而且在做增删改查的时候，可以借助SqlCommandBuilder类来完成.
SqlCommandBuilder必须执行SELECT命令来检索元数据，所以它要求多往返服务器一次，从而增加了应用程序的开销，而且操作的表必须要有主键约束。优点是自动建立insertcommand等命令
1,添加操作
private void button2_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
dapt = new SqlDataAdapter("select stucode,spcode,ctcode,stunits from stunits", con);
SqlCommandBuilder sdb = new SqlCommandBuilder(dapt);
DataSet ds1 = ds.GetChanges();
if (ds.HasChanges())
{
try
{
dapt.Update(ds, "stunit");
MessageBox.Show("保存成功", "提示");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
}
this.dataGridView1.AllowUserToAddRows =false;
}
2)删除操作(选中整行)
private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(connstring))
{
//con.Open();
StringBuilder sb =new StringBuilder();
sb.Append("delete stunits where stucode='"+this.dataGridView1.SelectedCells[0].Value.ToString()+"'");
SqlCommand cmd = new SqlCommand(sb.ToString(), con);
dapt.DeleteCommand = cmd;
int rowindex = this.dataGridView1.CurrentCell.RowIndex;
ds.Tables["stunit"].Rows[rowindex].Delete();//修改行的状态
// string celltext = this.dataGridView1.SelectedCells[rowindex].Value.ToString();
// MessageBox.Show(celltext);
if(ds.HasChanges(DataRowState.Deleted))
{
try
{
dapt.Update(ds, "stunit");
MessageBox.Show("删除成功", "提示");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
2)删除操作(单击第一个单无格删除)
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 0 && e.RowIndex >= 0)
{
using (conn = new SqlConnection(con))
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.Append("delete zy_bbxmxx where 代码='" + this.dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString()+"'");
//MessageBox.Show(sb.ToString());
dapt.DeleteCommand = new SqlCommand(sb.ToString(), conn);
DialogResult answer = MessageBox.Show("是否删除", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (answer == DialogResult.Yes)
{
try
{
ds.Tables[0].Rows[e.RowIndex].Delete();//改变行的状态
dapt.Update(ds, "zy_bbxx");
MessageBox.Show("删除成功");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
}
3)修改操作
private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
//DataSet ds1 = ds.GetChanges();
dapt = new SqlDataAdapter("select stucode,spcode,ctcode,stunits from stunits", con);
SqlCommandBuilder sdb = new SqlCommandBuilder(dapt);
if (ds.HasChanges())
{
try
{
dapt.Update(ds, "stunit");
MessageBox.Show("修改成功","提示");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
}
或者
private void btnEdit_Click(object sender, EventArgs e)
{
using (SqlConnection conn=new SqlConnection(con))
{
DataSet ds1 = ds.GetChanges();//得到修改后的DataSet
if (ds1 != null)
{
try
{
dapt = new SqlDataAdapter("select * from zy_bbxmxx", conn);
SqlCommandBuilder sb = new SqlCommandBuilder(dapt);
dapt.Update(ds,"zy_bbxx");
MessageBox.Show("修改成功");
}
catch (ArgumentNullException ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
4）查询操作
private void textBox5_TextChanged(object sender, EventArgs e)
{
this.dataGridView2.DataSource = ds.Tables[1];
DataView dv = ds.Tables[1].DefaultView;
dv.RowFilter = "stucode like '" + '%' + this.textBox5.Text + '%' + "'";
this.dataGridView2.DataSource = ds.Tables[1];
}