Use DataGridView to modify the complete database data code

 

1. Place a dview control in the window named dv 2. Add two columns to the DataGridview designer: Column 1: Name = "sph_fe-", HeaderText =" no ", columnType = DataGridViewTextBoxColumn Column 2: Name = "sph_zt", HeaderText = "State", ColumnType = DataGridViewComboBoxColumn 3. inherit the DataTable and customize a DataTable, used to display the drop-down options for the "status" column in the DataGridView: class DataList_sph_zt: able {public DataColumn zt_value = null; public DataColumn zt_display = null; public DataList_sph_zt () {// DataTable Zt_value = new DataColumn (); zt_display = new DataColumn (); zt_value.ColumnName = "value"; zt_display.ColumnName = "display"; zt_value.DataType = typeof (int ); zt_display.DataType = typeof (string); // Add the column to DataTable this. columns. add (zt_display); this. columns. add (zt_value); // Add the data row DataRow dr = this. newRow (); dr ["value"] = 0; dr ["display"] = "status 1"; this. rows. add (dr); dr = this. newRow (); dr ["v Alue "] = 1; dr [" display "] =" status 2 "; this. rows. add (dr); dr = this. newRow (); dr ["value"] = 2; dr ["display"] = "status 3"; this. rows. add (dr) ;}}4. Define the private member variable for the window: // The data storage DataTable object private DataTable dataList = new DataTable (); // DataTable private DataList_sph_zt sph_zt_list = new DataList_sph_zt (); // data connection object private SqlConnection _ connection = null; // data adapter object, used to load data and update the database private SqlDataAdapter sda = new SqlDataAdapter (); // select statement used by the data adapter to load data private string _ sqlSelect = ""; // The command generator object used to generate insert, delete, and update statements for the data adapter: private SqlCommandBuilder scb = new SqlCommandBuilder (); // database command object associated with SelectCommand of the data adapter private SqlCommand sqlSelect = new SqlCommand (); // transaction object SqlTransaction st = null; 5. Declare accessors for data Connection objects and select statement member variables, and use the command generator to generate database command objects for data adapter objects: public SqlConnection Connection {Get {return _ connection;} set {_ connection = value; // associate the newly set data connection object with sqlSelect on the database command object. connection = _ connection;} public string SqlSelect {get {return _ sqlSelect;} set {// if the new select query statement is different from the original one, set the _ sqlSelect member variable to a new value. If (_ sqlSelect! = Value) {_ sqlSelect = value; // reset the CommandText attribute of the database command object // and regenerate InsertCommand, DeleteCommand, and UpdateCommand sqlSelect for the data adapter object. commandText = _ sqlSelect; scb. refreshSchema (); sda. insertCommand = scb. getInsertCommand (); sda. deleteCommand = scb. getDeleteCommand (); sda. updateCommand = scb. getUpdateCommand () ;}}6. Set the attributes of each object in the window constructor: // The initialization function InitializeComponent () defined by the system (); // set the data source of the DataGridView control to DataTable Object dataList dv. dataSource = dataList; // you can specify the object sph_sph.DataPropertyName = "sph_fe-"; sph_zt.DataPropertyName =" sph_zt "; // set the data source object sph_zt.DataSource = sph_zt_list; sph_zt.ValueMember = "value"; sph_zt.DisplayMember = "display"; // set the SelectCommand of the data adapter to the sqlSelect member variable sda. selectCommand = sqlSelect; // sets the data adapter object scb associated with the command generator. dataAdapter = sda; 7. load data to the DataGridView public Void retrieve () {// clear all rows of the DataGridView dataList. rows. clear (); // Open Database connection _ connection. open (); // fill in the sable data sda. fill (dataList); // closes database connection _ connection. close ();} 8. Get the current row of the dview (according to your habits, add 1 to the actual row number (starting from scratch) to start with 1): public int getRow () {if (this. dv. rows. count> 0 & this. dv. currentRow. index> = 0) return this. dv. currentRow. index + 1; else return 0;} 9. Delete the specified row of the dview (whether to add 1 to the actual row number, or is it my habit): public voi D deleteRow (int row) {if (row <= 0 | row> this. dv. rows. count) return; // use the DataBoundItem attribute of the DataGridViewrow to obtain the currently bound raw data row, which is a DataRowview object // use this object to obtain the corresponding DataRow (dv. rows [row-1]. dataBoundItem as DataRowView ). row. delete ()} 10. Add a public void insertRow () {DataRow dr = dataList at the end of the DataGridView. newRow (); // specify content for the new row // dr ["sph_fe-"] =" "; // dr [" sph_zt "] = 0; dataList. rows. add (dr); // specify the last row of the current DataGridView action, That is, the new row dv. currentCell = this. dv. rows [this. dv. rows. count-1]. cells [0];} 11. update the modification to the DataGridView data to the public void update () {// Open Database connection _ connection. open (); // start a transaction st = _ connection. beginTransaction (); // associate the transaction object with the database command object sda of the data adapter. insertCommand. transaction = st; sda. deleteCommand. transaction = st; sda. updateCommand. transaction = st; // update to database sda. update (dataList); // commit a transaction. An exception should be caught. If an exception occurs, use st. rollback St. commit (); // close database connection _ connection. close ();} 12. postscript 1. When using the command generator to generate a database command object for the data adapter, you must at least specify the SelectCommand object for the data adapter. 2. If the CommandText attribute of the SelectCommand object of the data adapter is changed, the database command object should be generated for the data adapter again. The RefreshSchema () of the command generator should be called first () method to clear the old command object. 3. If you use the transaction control adapter to update database data, you should use the BeginTransaction () method of the database connection object to generate a transaction object after the command generator has generated all command objects for the adapter, assign the Transaction object to the Transaction attribute of all command objects of the data adapter. After calling the Update () method of the data adapter, call the Commit () method of the transaction object to Commit the transaction or call the Rollback () method of the transaction object to roll back the transaction in case of an exception.