关于DataGridView与DataSet绑定的一些操作例子(检索,更新)

1.DataGridView与DataSet绑定. 2.DataGridView中的DataGridViewComboBoxColumn列与DataSet绑定. 3.通过绑定DataSet来过滤DataGridView中数据行的显示. 4.DataGridView中通过CurrentCell来实现焦点控制. 5.DataGridView的DB更新操作(删除,更新,插入). private DataGridViewControl grdResult; protected DataSet dsMaster; 1.DataGridView与DataSet绑定. dsMaster = GetDataIntoDataSet(); grdResult.DataSource = dsMaster.Tables[0].DefaultView; 2.DataGridView中的DataGridViewComboBoxColumn列与DataSet绑定. ①.直接与DataSet绑定 DataGridViewComboBoxColumn colComboBoxColumn = grdResult.Columns["colComboBoxColumn"] as DataGridViewComboBoxColumn; colComboBoxColumn.DataSource = GetDataIntoDataSet(); colComboBoxColumn.DisplayMember = "TEXT_COLUMN"; colComboBoxColumn.ValueMember = "VALUE_COLUMN"; ②.与DataSet绑定以后,追加一空行的选项 DataTable dtTestTable = GetDataIntoDataTable(); DataRow dtTestDataRow = dtTestTable.NewRow(); dtStorageFormRowTmp["TEXT_COLUMN"] = ""; // 空行的设定 dtStorageFormRowTmp["VALUE_COLUMN"] = ""; // 空行的设定 dtTestTable .Rows.Add(dtTestDataRow ); colComboBoxColumn.DataSource = dtTestTable; colComboBoxColumn.DisplayMember = "TEXT_COLUMN"; colComboBoxColumn.ValueMember = "VALUE_COLUMN"; 3.通过绑定DataSet来过滤DataGridView中数据行的显示. ①.过滤条件的追加 (/**使用此种方式进行数据检索的效率,要比单纯遍历DataSet中的Table要高的多!**/) string strSQL = " A_COLUMN = 'Test' AND B_COLUMN = 2 "; // 该处过滤条件没有WHERE关键字 dsMaster.Tables[0].DefaultView.RowFilter = strSQL; // 添加过滤条件 grdResult.DataSource = dsMaster.Tables[0].DefaultView; // 数据再绑定 ②.绑定DataSet执行SELECT语句进行数据的过滤. dsMaster.Tables[0].Select(strSQL); 4.DataGridView中通过CurrentCell来实现焦点控制. grdResult.CurrentCell = grdResult.Rows[iIndex].Cells["clmFocusGridColumn"]; grdResult.CurrentCell.Selected = true; 5.DataGridView的DB更新操作(删除,更新,插入). DataView dv = dsMaster.Tables[0].DefaultView; DataView dvDelete = new DataView(dv.Table, string.Empty, dv.Sort, DataViewRowState.Deleted); // 删除行记录 DataView dvUpdateO = new DataView(dv.Table, string.Empty, dv.Sort, DataViewRowState.ModifiedOriginal); // 更新之前的行记录 DataView dvUpdateC = new DataView(dv.Table, string.Empty, dv.Sort, DataViewRowState.ModifiedCurrent); // 更新之后的行记录 DataView dvAdd = new DataView(dv.Table, string.Empty, dv.Sort, DataViewRowState.Added); // 新建之后的行记录 // 更新行存在判断 int[] indexList = new int[dvUpdateC.Count]; int iUCount = 0; for (int rowIndex = 0; rowIndex < dvUpdateC.Count; rowIndex++) { for (int colIndex = 2; colIndex < dvUpdateC.Table.Columns.Count - 6; colIndex++) { if (dvUpdateC[rowIndex][colIndex].ToString() != dvUpdateO[rowIndex][colIndex].ToString()) { indexList[iUCount] = rowIndex; iUCount += 1; break; } } } // 是否需要执行DB操作判断 if (dvDelete.Count + iUCount + dvAdd.Count == 0) { return; } // 启动事务处理 SqlExecutorWithTran dbAccessTrans = new DefaultSqlExecutorWithTran(ConStr); try { // 执行删除DB操作 string strSQL = @"DELETE FROM Test_Table WHERE A_Column = @A_Column"; for (int rowIndex = 0; rowIndex < dvDelete.Count; rowIndex++) { System.Data.SqlClient.SqlParameter[] pars = new System.Data.SqlClient.SqlParameter[1]; pars[0] = new System.Data.SqlClient.SqlParameter("@A_Column", dvDelete[rowIndex]["A_Column"].ToString()); dbAccessTrans.ExecuteNonQuery(strSQL, pars); } // 执行更新DB操作 strSQL = @"UPDATE Test_Table SET A_Column = @A_Column_C , TESTDATE_YMD=GETDATE() WHERE B_Column = @B_Column_O"; iUCount = dvUpdateC.Count; for (int rowIndex = 0; rowIndex < iUCount; rowIndex++) { System.Data.SqlClient.SqlParameter[] pars = new System.Data.SqlClient.SqlParameter[2]; pars[0] = new System.Data.SqlClient.SqlParameter("@A_Column_C", dvUpdateC[rowIndex]["A_Column"].ToString()); pars[1] = new System.Data.SqlClient.SqlParameter("@B_Column_O", dvUpdateO[rowIndex]["B_Column"].ToString()); dbAccessTrans.ExecuteNonQuery(strSQL, pars); } // 执行插入DB操作 strSQL = @"INSERT INTO Test_Table (A_Column ,B_Column ,Common_Column ) VALUES ( @A_Column ,@B_Column ,@Common_Column)"; for (int rowIndex = 0; rowIndex < dvAdd.Count; rowIndex++) { System.Data.SqlClient.SqlParameter[] pars = new System.Data.SqlClient.SqlParameter[3]; pars[0] = new SqlParameter("@A_Column", dvAdd[rowIndex]["A_Column"].ToString()); pars[1] = new SqlParameter("@B_Column", dvAdd[rowIndex]["B_Column"].ToString()); pars[2] = new SqlParameter("@Common_Column", System.Reflection.MethodInfo.GetCurrentMethod().DeclaringType.Name + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name); dbAccessTrans.ExecuteNonQuery(strSQL, pars); } dbAccessTrans.Commit(); dsMaster = GetDataIntoDataSet(); // 数据再绑定处理 } catch (System.Data.SqlClient.SqlException ex) { dbAccessTrans.Rollback(); throw ex; } catch (Exception ex) { dbAccessTrans.Rollback(); throw ex; }