DataSet之增删改查操作(DataGridView绑定)
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); } } } [转载]DataSet之增删改查操作(DataGridView绑定) 或者 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]; }