Command Object’s ExecuteNonQuery Method

1. Command Object’s ExecuteNonQuery Method ISYS 512

2. Using ExecuteNonQuery Method • To run SQL: • Insert • Delete • Update • The ExecuteNonQuery method also returns a value indicating the number of records affected by the SQL statement.

3. Use ExecuteNonQuery to Insert A New Record Create unbound text boxes to enter new customer record. string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\CSharpexamples\\SalesDB2011.accdb"; OleDbConnectionobjConn = new OleDbConnection(strConn); string strSQLInsert; strSQLInsert = "Insert into Customer values ('"; strSQLInsert += textBox1.Text + "','" + textBox2.Text + "','"; strSQLInsert += textBox3.Text + "','" + textBox4.Text + "')"; OleDbCommandobjCommInsert= new OleDbCommand(strSQLInsert, objConn); objConn.Open(); MessageBox.Show(strSQLInsert.ToString()); try { objCommInsert.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } objConn.Close();

4. Use ExecuteNonQuery to Delete A New Record • Create a listbox with CIDs • Delete the selected record and remove the CID from the listbox. string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\CSharpexamples\\SalesDB2011.accdb"; OleDbConnectionobjConn = new OleDbConnection(strConn); string strSQL = "delete from customer where cid = '" + listBox1.SelectedItem + "'"; OleDbCommandobjComm = new OleDbCommand(strSQL, objConn); try { intaffectedRecords; objConn.Open(); affectedRecords= objComm.ExecuteNonQuery(); MessageBox.Show(affectedRecords.ToString() + "records deleted"); listBox1.Items.RemoveAt(listBox1.SelectedIndex); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close();

5. Use ExecuteNonQuery to Update A New Record • Create a project that do the following tasks: • Use a DataReader to retrieve customer IDs and populate a listbox. • Select a new rating from radio buttons for the selected customer. • Update customer’s rating using the ExecuteNonQuery method of a Command object.

6. create CID listbox: private void Form4_Load(object sender, EventArgs e) { string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\CSharpexamples\\SalesDB2011.accdb"; OleDbConnectionobjConn = new OleDbConnection(strConn); string strSQL = "select cid from customer;"; OleDbCommandobjComm = new OleDbCommand(strSQL, objConn); try { objConn.Open(); OleDbDataReaderobjDataReader; objDataReader = objComm.ExecuteReader(); while (objDataReader.Read() == true) { listBox1.Items.Add(objDataReader["cid"]); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close(); }

7. Update customer rating: private void button1_Click(object sender, EventArgs e) { string newRating; if (radioButton1.Checked) { newRating = "A"; } else if (radioButton2.Checked) { newRating = "B"; } else { newRating = "C"; } string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\CSharpexamples\\SalesDB2011.accdb"; OleDbConnectionobjConn = new OleDbConnection(strConn); string strSQL = "Update customer set rating = '" + newRating + "' where cid='" + listBox1.SelectedItem + "'"; OleDbCommandobjComm = new OleDbCommand(strSQL, objConn); try { objConn.Open(); objComm.ExecuteNonQuery(); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } objConn.Close(); }