Updating Records: Disconnected Approach
We will currently take a glance at change records exploitation the disconnected approach. Like inserting and deleting records using the disconnected approach, updating records also use the DataAdapter, DataSet, and CommandBuilder classes to do its job. The following are the essential steps for change a record mistreatment the same categories.
- Create a Connection
- Create a Command
- Create a DataAdapter
- Make a CommandBuilder and relate it to the DataAdapter
- Create a DataSet
- Specify connection string for the connection
- Specify Connection to be used by the Command
- Indicate the SELECT explanation for the CommandText of the Command
- Add values to command parameters if any
- Specify the SelectCommand for the DataAdapter
- Fill the DataSet with the outcome set from the database table
- Find the row to update.
- Edit the fields you want to update
- Send the changes to the database
The accompanying example application enables you to update a specific record of a student dependent on the StudentID. A typical application like this may commonly show this values of the record 1st, except for simplicity, we’ll go straight to the change. You will merely got to type the StudentID and then the new values for the corresponding record. Make another Windows Forms Application and make a form like the accompanying one.
| Label | Name |
|---|---|
| 1 | textBoxStudentId |
| 2 | textBoxFirstName |
| 3 | textBoxLastName |
| 4 | textBoxGender |
| 5 | textBoxAge |
| 6 | textBoxAddress |
| 7 | buttonUpdate |
Figure 1
After making the graphical user interface, double-click buttonUpdate and use the subsequent event handler for its Click event.
private void buttonUpdate_Click(object sender, EventArgs e) { SqlConnection connection = new SqlConnection(); SqlCommand command = new SqlCommand(); SqlDataAdapter adapter = new SqlDataAdapter(); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); DataSet dataset = new DataSet(); connection.ConnectionString = @"Data Source=.SQLEXPRESS;" + "Initial Catalog=University;Integrated Security=SSPI"; command.Connection = connection; command.CommandText = "SELECT * FROM Students"; adapter.SelectCommand = command; adapter.Fill(dataset, "Students"); foreach (DataRow row in dataset.Tables["Students"].Rows) { if (row["StudentID"].ToString() == textBoxStudentID.Text) { row["FirstName"] = textBoxFirstName.Text; row["LastName"] = textBoxLastName.Text; row["Gender"] = textBoxGender.Text; row["Age"] = textBoxAge.Text; row["Address"] = textBoxAddress.Text; } } try { int result = adapter.Update(dataset, "Students"); if (result > 0) MessageBox.Show("Update Successful."); else MessageBox.Show("Update Failed."); } catch (SqlException ex) { MessageBox.Show(ex.Message); } }Example 1
After initializing the required variables, we first need to transfer the contents of the table in a dataset so we will have an in-memory database that we can work with. That’s why we use the SELECT command and used the DbDataAdapter.Fill() method to retrieve all the records from the Students table. Updating using disconnected approach does not require aaUPDATE SQL command. The UPDATE command are generated by the SqlCommandBuilder object. The update are done exploitation searching and simple worth assignment with C#.
Line seventeen(17) uses a foreach loop to check each row. We used the suitable table from the dataset as the source. bear in mind that every row in a DataTable is of type DataRow. Line 19 checks if the StudentID field of the current DataRow is equal to the specified StudentIDin the textboxStudentID. If so, then inside the if statement, we update each of the fields of that DataRow to the new values from the textboxes.
After that, we simply need to call the DbDataAdapter.Update() method to send the changes to the database (line 31). This method accepts two arguments, the dataset where the updated row is contained, and the name of the table in that dataset (not the database table). If the method is successful at updating the actual database table, then the number of rows updated is returned. We simply assign the value returned in a variable so we can test if the update was successful, that is, if the value returned is not 0.
Run the program and type the StudentID of a current student. Type new values for each text box.
Click the refresh button to start the refresh procedure. If everything is working properly, then you will be presented with a success message.
The program in this lesson simple shows how to update a record. But it will be hard to update a record without knowing the current values first. A separate tutorial will apply everything we have learned (querying, inserting, deleting, updating).