We will now take a look at updating records using the disconnected approach. Like inserting and deleting records using the disconnected approach, updating records also uses the DataAdapter, DataSet, and CommandBuilder classes to do its job. The following are the basic steps for updating a record using the said classes. 1. Create a Connection 2. Create a Command 3. Create a DataAdapter 4. Create a CommandBuilder and associate it to the DataAdapter 5. Create a DataSet 6. Specify connection string for the connection 7. Specify Connection to be used by the Command 8. Specify the SELECT statement for the CommandText of the Command 9. Add values to command parameters if any 10. Specify the SelectCommand for the DataAdapter 11. Fill the DataSet with the result set from the database table 12. Find the row to update. 13. Edit the fields you want to update 14. Send the changes to the database The following example application allows you to update a certain record of a student based on the StudentID. A typical application like this would normally show the current values of the record first, but for simplicity, we will go straight to the updating. You will simply need to type the StudentID and then the new values for the corresponding record. Create a new Windows Forms Application and create a form similar to the following one.

Label

Name

Label Name 1 textBoxStudentId 2 textBoxFirstName 3 textBoxLastName 4 textBoxGender 5 textBoxAge 6 textBoxAddress 7 buttonUpdate Figure 1 After creating the GUI, double click buttonUpdate and use the following event handler for its Click event.1: private void buttonUpdate_Click(object sender, EventArgs e) 2: { 3: SqlConnection connection = new SqlConnection(); 4: SqlCommand command = new SqlCommand(); 5: SqlDataAdapter adapter = new SqlDataAdapter(); 6: SqlCommandBuilder builder = new SqlCommandBuilder(adapter); 7: DataSet dataset = new DataSet(); 8: 9: connection.ConnectionString = @"Data Source=.\SQLEXPRESS;" + 10: "Initial Catalog=University;Integrated Security=SSPI"; 11: command.Connection = connection; 12: command.CommandText = "SELECT * FROM Students"; 13: 14: adapter.SelectCommand = command; 15: adapter.Fill(dataset, "Students"); 16: 17: foreach (DataRow row in dataset.Tables["Students"].Rows) 18: { 19: if (row["StudentID"].ToString() == textBoxStudentID.Text) 20: { 21: row["FirstName"] = textBoxFirstName.Text; 22: row["LastName"] = textBoxLastName.Text; 23: row["Gender"] = textBoxGender.Text; 24: row["Age"] = textBoxAge.Text; 25: row["Address"] = textBoxAddress.Text; 26: } 27: } 28: 29: try 30: { 31: int result = adapter.Update(dataset, "Students"); 32: 33: if (result > 0) 34: MessageBox.Show("Update Successful."); 35: else 36: MessageBox.Show("Update Failed."); 37: }

38: 39: 40: 41: 42: }

catch (SqlException ex) { MessageBox.Show(ex.Message); }

Figure 2 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. Thats 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 an UPDATE Sql command. The UPDATE command will be generated by the SqlCommandBuilder object. The update will be done using searching and simple value assignment with C#. Line 17 uses a foreach loop to check each row. We used the appropriate table from the dataset as the source. Remember that each row in a DataTable is of type DataRow. Line 19 checks if the StudentID field of the current DataRow is equal to the specified StudentID in the textboxStudentID. If so, then inside the if statement, we update each of the field 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 an existing student. Type new values for each text box.

Figure 3 Click the update button to initiate the update process. If everything is working properly, then you will be presented with a success message.

Deleting RecordsDeleting database using the disconnected approach is simillar to inserting a row using the DataSet and the DataAdapter classes. But instead of adding a new DataRow object, you will delete it from the DataTable of the DataSet. The following are the basic steps to delete a row using disconnected approach. 1. Create a Connection 2. Create a Command 3. Create a DataAdapter 4. Create a CommandBuilder and associate it to the DataAdapter 5. Create a DataSet 6. Specify connection string for the connection 7. Specify Connection to be used by the Command 8. Specify CommandText for the Command 9. Add values to command parameters if any 10. Specify the SelectCommand for the DataAdapter 11. Fill the DataSet with the result set from the database table 12. Find the row to delete. 13. Delete the row from the DataTable if found. 14. Send the changes to the database Lets create a simple application that allows you to specify the StudentId and delete the corresponding record having that ID. Create a new Windows Forms Application and name it DeletingRecordDisconnected. Add a label and text box for the StudentID and a button that will execute the commands. Name the text box studentIdTextBox and the button as deleteButton.

Figure 1 Double click the button to generate an event handler for its Click event. Be sure to import the System.Data.SqlClient namespace. Use the following code for the event handler.1: private void deleteButton_Click(object sender, EventArgs e) 2: { 3: 4: SqlConnection connection = new SqlConnection(); 5: SqlCommand command = new SqlCommand(); 6: SqlDataAdapter adapter = new SqlDataAdapter(); 7: SqlCommandBuilder builder = new SqlCommandBuilder(adapter); 8: DataSet dataset = new DataSet(); 9: 10: connection.ConnectionString = @"Data Source=.\SQLEXPRESS;" + 11: "Initial Catalog=University;Integrated Security=SSPI"; 12: command.Connection = connection; 13: command.CommandText = "SELECT * FROM Students"; 14: adapter.SelectCommand = command; 15: adapter.Fill(dataset, "Students"); 16: 17: foreach (DataRow row in dataset.Tables["Students"].Rows) 18: { 19: if (row["StudentID"].ToString() == studentIdTextBox.Text) 20: { 21: row.Delete(); 22: 23: } 24: } 25: 26: try 27: { 28: int result = adapter.Update(dataset, "Students"); 29: 30: if (result > 0) 31: MessageBox.Show("Success!"); 32: else 33: MessageBox.Show("Failed!"); 34: } 35: catch (SqlException ex) 36: { 37: MessageBox.Show(ex.Message); 38: } 39: }

Figure 2 We created the necessary objects in lines 4-8. Note the create of SqlCommandBuilder in line 7. In its constructor, we pass the created DataAdapter object to associate the CommandBuilder to it. Lines 13-15 fills the DataSet of values from the Student table and adds the result to the DataTable named Students inside the DataSet. Now that we have the values of the table from the database, we can now delete a row from that DataTable. We first need to search which record to delete. We used a foreach loop (line 17-24) to iterate through each record in the DataSet. Line 19 tests whether the content of the StudentID field of the current row is equal to what the user specified in the studentIdTextBox. If it matches, we use the DataRow.Delete() method to delete that row from the table. Line 28 calls the DataAdapters Update() method and we passed the DataSet and the name of the DataTable. When Update() is called, the CommandBuilder generates the required SQL statements for every changes of modification it detects. Those SQL commands are then executed to the database. In our case, since we deleate a row from the DataTable, CommandBuilder generates a DELETE Statement together with parameters filled with the values you specified. After the execution of the command, the row or rows are deleted from the actual database table. The Update() method returns an integer value representing the number of row affected. Since we 1 row was deleted, we are expecting a return value of 1. We test if the result is greater than 0 in line 30. If it is, then we show a success message. Otherwise, we tell the user that the deletion of the row has failed. Run the program and type a StudentId that exist in the Students table.

Figure 3 If a matching row is found, it is removed from the table.

Querying a Database: Disconnected Approach

We can query results from a database without keeping an open connection. This is through the help of the DataAdapter classes and the DataSet class. The DataAdapter executes commands to the database and fills the results returned by the query inside a table of the DataSet. You can then access every record using the Rows property of the DataTable or every values of its fields by specifying the name of the column. The following are the basic steps for querying and displaying a result set from a database using the disconnected approach. 1. Create a Connection 2. Create a Command 3. Create a DataAdapter 4. Create a DataSet 5. Specify connection string for the Connection 6. Specify Connection that will be used by the Command 7. Specify the CommandText that will be executed by the command 8. Add values to command parameters (if any) 9. Specify SelectCommand for the DataAdapter 10. Fill the DataSet using the DataAdapter 11. Display results Lets create a simple application to demonstrate these steps. We will create an application that shows the result returned by a query using the disconnected approach. Create a new Windows Forms Application and name it QueryingDatabaseDisconnected. Add a ListBox to the form and name it studentsListBox.

Figure 1 Double click the form to generate the event handler for the forms Load event. Be sure to import the System.Data namespace for the DataSet class, and the System.Data.SqlClient namespace for classes required for connecting to SQL Server.using System.Data; using System.Data.SqlClient;

After that, use the following code for the Load event handler.1: private void Form1_Load(object sender, EventArgs e) 2: { 3: SqlConnection connection = new SqlConnection(); 4: SqlCommand command = new SqlCommand(); 5: SqlDataAdapter adapter = new SqlDataAdapter(); 6: DataSet dataset = new DataSet(); 7: 8: connection.ConnectionString = @"Data Source=.\SQLEXPRESS;" + 9: "Initial Catalog=University;Integrated Security=SSPI"; 10: command.Connection = connection; 11: command.CommandText = "SELECT FirstName, LastName, Age FROM Students"; 12: adapter.SelectCommand = command; 13: adapter.Fill(dataset, "Students"); 14: 15: foreach (DataRow student in dataset.Tables["Students"].Rows) 16: { 17: studentsListBox.Items.Add(String.Format("{0} {1},{2}", 18: student["FirstName"], student["LastName"], student["Age"])); 19: } 20: }

Figure 2 Lines 3-6 creates the necessary objects as stated in Steps 1 to 4. We then assigned the connection string to the ConnectionString property of the Connection object (lines 8-9). Lines 10-11 assigns the Connection and CommandText to be used by the Command object. Since there is no command parameters specified in the command text, we can skip Step 8. Line 12 specifies the Command to be used as the SelectCommand by the DataAdapter. We passed the Command that we created which retrieves the FirstName, LastName, and Age of every student. Line 13 uses the Fill() method of the DataAdapter to execute the command. The first argument of the method is the DataSet to be filled and the second argument is the name of the table that will hold the result set. The Fill() method creates a DataTable with the specified name where the result set will be contained. Then that DataTable will be added to the Tables property of the DataSet. Lines 15 to 19 access each row in the DataTable using a foreach loop. As you can see, we used the Tables property of the DataSet and specified the name of the table using an indexer. We then used the Rows property of the returned DataTable which contains the collection of all DataRows of the result set. We assigned each row in a temporary variable student. Line 17 adds the value FirstName, LastName, and Age fields using indexers and specifying the name of the collumn. Execute the application and you will see all the students loaded in the ListBox.

Figure 2 Using the disconnected approach, we can also easily bind a DataTable into a DataGridView control. As an example, lets modify our previous application by deleting the ListBox control and replacing it with a DataGridView control with the name studentsDataGridView.

Figure 3 Modify the code inside the forms Load event handler.1: private void Form1_Load(object sender, EventArgs e) 2: { 3: SqlConnection connection = new SqlConnection(); 4: SqlCommand command = new SqlCommand(); 5: SqlDataAdapter adapter = new SqlDataAdapter(); 6: DataSet dataset = new DataSet(); 7: 8: connection.ConnectionString = @"Data Source=.\SQLEXPRESS;" + 9: "Initial Catalog=University;Integrated Security=SSPI"; 10: command.Connection = connection; 11: command.CommandText = "SELECT FirstName, LastName, Age FROM Students"; 12: adapter.SelectCommand = command; 13: adapter.Fill(dataset, "Students"); 14: 15: studentsDataGridView.DataSource = dataset.Tables["Students"]; 16: }

Figure 4 We used the DataSource property of the DataGridView the specifies the source of data to be shown by the control. We specified its value to be the DataTable that we have field by accessing the Tables property and providing the name we have specified in the indexer. Execute the program and see the results.

Figure 5 We have seen how to query results using the disconnected approach using the DataAdapter and the DataSet classes.

Simple Student Enrollment System Disconnected Approach

Create a new windows forms application and create a user interface similar to the one shown in Figure 1.

Figure 1 Label Name Properties 1 textBoxStudentId 2 textBoxFirstName Enabled: False 3 textBoxLastName Enabled: False 4 textBoxGender Enabled: False 5 textBoxAge Enabled: False 6 textBoxAddress Enabled: False 7 buttonDelete Text: Delete 8 buttonEdit Text: Edit 9 buttonAddNew Text: Add New 10 buttonShow Text: Show Figure 2 Go to the Code Editor by pressing F7 and be sure to first import the two neccessary namespaces for this database connection.using System.Data; using System.Data.SqlClient;

Add the following private fields into the Form1 class:private SqlConnection connection; private SqlCommand command; private SqlDataAdapter adapter; private SqlCommandBuilder builder; private DataSet dataSet; bool updateReady = false; bool insertReady = false;

We declared the fields that will be used for connecting and issuing commands to the database. We declared them as class fields instead of as local variables so they will be shared by all database operations (For example, no need to declare Connection objects for querying and another one for inserting records). The updateReady and inserReady field will be used later for updating and inserting new students. On the Forms constructor add the codes in lines 5-11.1: public Form1() 2: { 3: InitializeComponent(); 4: 5: connection = new SqlConnection(); 6: connection.ConnectionString = @"Data Source=.\SQLEXPRESS; Initial Catalog=University;" + 7: "Integrated Security=SSPI"; 8: 9: command = connection.CreateCommand(); 10: adapter = new SqlDataAdapter(command); 11: builder = new SqlCommandBuilder(adapter); 12: dataSet = new DataSet(); 13: }

Figure 3 Line 5 initializes our Connection object. Line 6 specifies the connection string that will be used for connection. Line 9 creates the command using the DbCommand.CreateCommand() method which returns a Command object that is already assigned to the Connection object that called that method. Line 10 creates a DataAdapter object and we passed the Command object that will be used as the SelectCommand inside its constructor. Line 11 passes the created DataAdapter in the constructor of the CommandBuilder object which will be used to generate SQL commands when the Update() method is called later. Finally, line 12 creates a DataSet() object which will contain the result sets obtained from the database. The program for now can only do queries to the database by typing the StudentId in textBoxStudentId and clicking the buttonShow. The code for the Click event of buttonShow is as follows:1: private void buttonShow_Click(object sender, EventArgs e) 2: { 3: command.CommandText = "SELECT * FROM Students WHERE StudentID=@StudentID"; 4: command.Parameters.Clear(); 5: command.Parameters.AddWithValue("@StudentID", textBoxStudentID.Text); 6:

7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: }

dataSet.Tables.Clear(); int result = adapter.Fill(dataSet, "Students"); if (result > 0) { DataRow student = dataSet.Tables["Students"].Rows[0]; textBoxFirstName.Text = student["FirstName"].ToString(); textBoxLastName.Text = student["LastName"].ToString(); textBoxGender.Text = student["Gender"].ToString(); textBoxAge.Text = student["Age"].ToString(); textBoxAddress.Text = student["Address"].ToString(); } else { ClearFields(); MessageBox.Show("Student does not exist."); }

Figure 4 Since we declared our Connection and Command objects as class fields, we can simply change the properties of them depending on the operation to be made. Line 3 changes the CommandText property of our Commandobject to a SELECT statement the retrieves everything from the Students table that has a StudentID specified. The command text has one command parameter for StudentID which will be replaced by the value typed by the user in textBoxStudentID as shown in line 5. Note that before that, we cleared the Parameters property first because it might already contain some values from other operation. We do that using the Clear() method (line 4). Line 7 clears the Tables collection property of the dataset incase there are already tables stored in it. Line 9 calls the DbDataAdapter.Fill() method to query the student from the database table and add it to a table named "Students" in thedataSet. The Fill() method will return the number of rows or result sets returned and we are expecting at least 1 so line 11 tests if the value returned is greater than 0. If so line 13 first stores the first row of the Students table in the dataSet into a DataRow object for easier typing and readablity although it is optional. Lines 14-18 shows the values of individual columns of the retrieved student row into their respective text boxes. Lines 20-24 are codes to be executed if no student with the specified student number is returned or found. Next in the list is the buttonAddNew which is responsible for adding or enrolling new students to the database. The following is the event handler for the buttonAddNews Click event.1: private void buttonAddNew_Click(object sender, EventArgs e) 2: { 3: if (!insertReady) 4: { 5: buttonAddNew.Text = "Enroll"; 6: ClearFields(); 7: textBoxStudentID.Text = GetNextStudentID().ToString(); 8: 9: textBoxStudentID.Enabled = false; 10: textBoxFirstName.Enabled = true;

11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: }

textBoxLastName.Enabled = true; textBoxGender.Enabled = true; textBoxAge.Enabled = true; textBoxAddress.Enabled = true; buttonShow.Enabled = false; buttonEdit.Enabled = false; buttonDelete.Enabled = false; insertReady = true; } else { buttonAddNew.Text = "Add New"; command.CommandText = "SELECT * FROM Students"; dataSet.Tables.Clear(); adapter.Fill(dataSet, "Students"); DataRow row = dataSet.Tables["Students"].NewRow(); row["FirstName"] = textBoxFirstName.Text; row["LastName"] = textBoxLastName.Text; row["Gender"] = textBoxGender.Text; row["Age"] = textBoxAge.Text; row["Address"] = textBoxAddress.Text; dataSet.Tables["Students"].Rows.Add(row); try { int result = adapter.Update(dataSet, "Students"); if (result > 0) MessageBox.Show("Student successfully enrolled."); else MessageBox.Show("Failed to enroll student."); } catch (SqlException ex) { MessageBox.Show(ex.Message); } textBoxStudentID.Enabled = true; textBoxFirstName.Enabled = false; textBoxLastName.Enabled = false; textBoxGender.Enabled = false; textBoxAge.Enabled = false; textBoxAddress.Enabled = false; buttonShow.Enabled = true; buttonEdit.Enabled = true; buttonDelete.Enabled = true; insertReady = false; }

Figure 5

The buttonAddNew will serve as a toggle button. The insertReady field will determine the action that this button will perform. Since insertReady is initially false, lines 5-18 will be executed. Line 5 changes the text of the button to Enroll simply to notify the user that this button should be clicked once the details are entered. Line 6 calls a method that will clear all the fields if so it will be ready to accept new details from the user. The definition for the ClearFields() method is as follows:private void ClearFields() { textBoxFirstName.Text = String.Empty; textBoxLastName.Text = String.Empty; textBoxGender.Text = String.Empty; textBoxAge.Text = String.Empty; textBoxAddress.Text = String.Empty; }

Figure 6 Since we need will be adding a new student, we need to generate the next unique StudentID. We cant simply type a new student ID because the same student ID may already be in the database. Thats why, line 7 calls another utility method called GetNextStudentID(). The defnition for this method is as follows:1: private int GetNextStudentID() 2: { 3: command.CommandText = "SELECT IDENT_CURRENT('Students') + IDENT_INCR('Students')"; 4: 5: try 6: { 7: connection.Open(); 8: int nextID = Convert.ToInt32(command.ExecuteScalar()); 9: return nextID; 10: } 11: catch (SqlException ex) 12: { 13: MessageBox.Show(ex.Message); 14: } 15: finally 16: { 17: connection.Close(); 18: } 19: return 0; 20: }

Figure 7 Line 3 creates an SQL Server command that will get the next Identity field value. Remember that the StudentID field of the Students table is an Identity field which auto increments when new records are added. The thing is, we cant simply get the last StudentID and add 1 because SQL server saves the last Identity value used so if we delete a record with StudentID 5, making the new last StudentID is 4, the next StudentID that will be generated will be 6. There is an SQL

Server function called IDENT_CURRENT() which returns the current identity field and IDENT_INCR() which returns the increment value of the identity field. We passed the name of the table to these functions and added their return values to get the latest StudentID. We open the connection in line 7 and line 8 uses the DbCommand.ExecuteScalar() since we are expecting a single value (not record) to be returned. The value is the generated last StudentID but we need to convert it first to integer. Finally, wer returned that value to the caller of the method (line 9). Note that this method used the connected approach for obtaining a single value. You can also retrieve the value using the disconnected way. Back to the code in Figure 5, after getting a new StudentID, we simply display it using textBoxStudentID. Lines 9-14 enables the text boxes so the user can enter the details for the new student and lines 15-17 disables buttonShow, buttonInsert, and buttonDelete so the user can only click the Enroll button. Finally, line 18 changes the value ofinsertReady to true so the next time the user clicks this button, lines 22-60 will be executed instead. The first time the user clicks the buttonAddNew, the program is now ready to accept the information for the new student. After the user types the information for each field, the user should now then click the buttonAddNew again to execute the SQLINSERT command to the database. Let's now discuss lines 22-63 of figure 5 which does the actual communication to the database. Line 22 reverts back the label of the button to "Add New". Lines 24 changes the CommandTextproperty of our Command object into a SELECT Statement that queries all records form the database.Line 25 resets the Tables property to remove any tables that might be there. Line 26 Fills the dataSet with all the records from the database table. Line 28 uses the DataTable.NewRow() method to create a DataRow with the same number of columns and type as the Row found in the Students table of the dataSet. If you will not useNewRow(), then you will need to manually create each column witch would be tedious. Lines 29-33 simply assigns the values in the text box to each column of the new row to be added.Line 35 adds the new row to the Rows collection of the Students table of the dataSet.Now all that is left is to submit the changes to the database. Inside a try block, we called the DbAdapter.Update() method and passed the dataSet and the name of the DataTable that contains the updated results. By calling this method, the corresponding INSERT statement is generated by CommandBuilder and then it is executed to the database. It returns an integer which indicates the number of rows that were successfully updated. We check its values in line 41 to determine if the insertion is successfull. Lines 51-52 reverts the textboxes and the buttons to their previous states and we switch back the insertReady flag to false. The buttonEdit also acts as a toggle button and most of the code is similar to the code of buttonAddNew.1: private void buttonEdit_Click(object sender, EventArgs e) 2: { 3: if (!updateReady) 4: { 5: buttonEdit.Text = "Update"; 6: textBoxStudentID.Enabled = false; 7: textBoxFirstName.Enabled = true; 8: textBoxLastName.Enabled = true; 9: textBoxGender.Enabled = true;

10: textBoxAge.Enabled = true; 11: textBoxAddress.Enabled = true; 12: buttonShow.Enabled = false; 13: buttonAddNew.Enabled = false; 14: buttonDelete.Enabled = false; 15: updateReady = true; 16: } 17: else 18: { 19: buttonEdit.Text = "Edit"; 20: 21: command.CommandText = "SELECT * FROM Students"; 22: dataSet.Tables.Clear(); 23: adapter.Fill(dataSet, "Students"); 24: 25: foreach (DataRow student in dataSet.Tables["Students"].Rows) 26: { 27: if (student["StudentID"].ToString() == textBoxStudentID.Text) 28: { 29: student["FirstName"] = textBoxFirstName.Text; 30: student["LastName"] = textBoxLastName.Text; 31: student["Gender"] = textBoxGender.Text; 32: student["Age"] = textBoxAge.Text; 33: student["Address"] = textBoxAddress.Text; 34: } 35: } 36: 37: try 38: { 39: int result = adapter.Update(dataSet, "Students"); 40: 41: if (result > 0) 42: MessageBox.Show("Update successful."); 43: else 44: MessageBox.Show("Failed to update."); 45: } 46: catch (SqlException ex) 47: { 48: MessageBox.Show(ex.Message); 49: } 50: 51: textBoxStudentID.Enabled = true; 52: textBoxFirstName.Enabled = false; 53: textBoxLastName.Enabled = false; 54: textBoxGender.Enabled = false; 55: textBoxAge.Enabled = false; 56: textBoxAddress.Enabled = false; 57: buttonShow.Enabled = true; 58: buttonAddNew.Enabled = true; 59: buttonDelete.Enabled = true; 60: updateReady = false; 61: } 62: }

Figure 8

Clicking the buttonEdit for the first time will enable the text boxes and disable the buttons except the buttonEdit itself. We used the updateReady field to make the button toggle its functionality. After the changes has been made to the current student, clicking the button the second time will send the changes to the database. Lines 25-35 searches the student with theStudentID indicated in the textBoxStudentID. We did this using a foreach loop and we retrieve every row from the Students table of the dataSet. If the current retrieve row has a StudentID equal to the one in the textbox, then we update every field to whatever new value typed by the user in the text boxes. Finally, the buttonDeletes Click event handler is shown in Figure 9.1: private void buttonDelete_Click(object sender, EventArgs e) 2: { 3: command.CommandText = "SELECT * FROM Students"; 4: dataSet.Tables.Clear(); 5: adapter.Fill(dataSet, "Students"); 6: 7: 8: foreach (DataRow student in dataSet.Tables["Students"].Rows) 9: { 10: if (student["StudentID"].ToString() == textBoxStudentID.Text) 11: { 12: student.Delete(); 13: break; 14: } 15: } 16: 17: try 18: { 19: int result = adapter.Update(dataSet, "Students"); 20: 21: if (result > 0) 22: MessageBox.Show("Student successfully deleted."); 23: else 24: MessageBox.Show("Failed to delete student."); 25: } 26: catch (SqlException ex) 27: { 28: MessageBox.Show(ex.Message); 29: } 30: 31: command.CommandText = "SELECT * FROM Students"; 32: adapter.Fill(dataSet, "Students"); 33: }

Figure 9 The code for deleting a row is simillar to updating in which we simply used aforeach loop to find the student to delete. If the row has a StudentID equal to the one indicated in the textBoxStudentID, then line 12 deletes that row using the Delete() method. We then exit the loop by using the break statement.

Our simple enrollment system is now complete. To test it, run the program and type an existing StudentID in the first text box. Click the show button to see the students information. If you want to edit those information, you can click the buttonEdit, type the new values, and click buttonEdit again to save the changes. You can also delete that student by clicking buttonDelete. To add or enroll a new student, click the buttonAddNew and type the information of the new student into the fields. Click the buttonAddNew once more to add the student into the Students database table. We will now take a look at updating records using the disconnected approach. Like inserting and deleting records using the disconnected approach, updating records also uses the DataAdapter, DataSet, and CommandBuilder classes to do its job. The following are the basic steps for updating a record using the said classes. 1. Create a Connection 2. Create a Command 3. Create a DataAdapter 4. Create a CommandBuilder and associate it to the DataAdapter 5. Create a DataSet 6. Specify connection string for the connection 7. Specify Connection to be used by the Command 8. Specify the SELECT statement for the CommandText of the Command 9. Add values to command parameters if any 10. Specify the SelectCommand for the DataAdapter 11. Fill the DataSet with the result set from the database table 12. Find the row to update. 13. Edit the fields you want to update 14. Send the changes to the database The following example application allows you to update a certain record of a student based on the StudentID. A typical application like this would normally show the current values of the record first, but for simplicity, we will go straight to the updating. You will simply need to type the StudentID and then the new values for the corresponding record. Create a new Windows Forms Application and create a form similar to the following one.

Label Name 1 textBoxStudentId 2 textBoxFirstName 3 textBoxLastName 4 textBoxGender 5 textBoxAge 6 textBoxAddress 7 buttonUpdate Figure 1 After creating the GUI, double click buttonUpdate and use the following event handler for its Click event.1: private void buttonUpdate_Click(object sender, EventArgs e) 2: { 3: SqlConnection connection = new SqlConnection(); 4: SqlCommand command = new SqlCommand(); 5: SqlDataAdapter adapter = new SqlDataAdapter(); 6: SqlCommandBuilder builder = new SqlCommandBuilder(adapter); 7: DataSet dataset = new DataSet(); 8: 9: connection.ConnectionString = @"Data Source=.\SQLEXPRESS;" + 10: "Initial Catalog=University;Integrated Security=SSPI"; 11: command.Connection = connection; 12: command.CommandText = "SELECT * FROM Students"; 13: 14: adapter.SelectCommand = command;

15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: }

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); }

Figure 2 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. Thats 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 an UPDATE Sql command. The UPDATE command will be generated by the SqlCommandBuilder object. The update will be done using searching and simple value assignment with C#. Line 17 uses a foreach loop to check each row. We used the appropriate table from the dataset as the source. Remember that each row in a DataTable is of type DataRow. Line 19 checks if the StudentID field of the current DataRow is equal to the specified StudentID in the textboxStudentID. If so, then inside the if statement, we update each of the field 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 an existing student. Type new values for each text box.

Figure 3 Click the update button to initiate the update process. If everything is working properly, then you will be presented with a success message.

Inserting records using the disconnected approach is a little different compared to doing it using the connected approach. We need to use the DataAdapter and the DataSet classes. When modifying records using the disconnected approach, what we modify is the contents of the DataTable, then after the modification, we submit the changes to the database. So if we want to insert a new records, then we add a DataRow in the Rows property of the DataTable. After that, we invoke the Update() method which will submit the inserted row to the actual database table. The INSERT command used for it is generated by a CommandBuilder object which is available

for each data provider. For example, for SQL provider, you can use the SqlCommandBuilder class. The following are the basic steps for inserting a record using the disconnected approach. 1. Create a Connection 2. Create a Command 3. Create a DataAdapter 4. Create a CommandBuilder and associate it to the DataAdapter 5. Create a DataSet 6. Specify connection string for the connection 7. Specify Connection to be used by the Command 8. Specify CommandText for the Command 9. Add values to command parameters if any 10. Specify the SelectCommand for the DataAdapter 11. Fill the DataSet with the result set from the database table 12. Create a new DataRow object 13. Assign values for each of its field 14. Add the DataRow object to the specified DataTable of the DataRow 15. Send the changes to the database We need to retrieve the contents of a database table that we want to add a record. That is why we still need to specify the SelectCommand for the DataAdapter. After that, we can make any changes to the DataTable containing the results. We can add a new DataRow representing the new record in its Rows property. To demonstrate the steps above, create a new Windows Forms Application and name it InsertingRecordsDisconnected. Add labels and text boxes for FirstName, LastName, Gender, Age, and Address fields. Name the textboxes firstNameTextBox, lastNameTextBox, genderTextBox, ageTextBox, and addressTextBox. Also add a button and name it addButton.

Figure 1

Double click the button to generate an event handler for its Click event. Import the System.Data and System.Data.SqlClient. Use the following code for the handler.1: private void addButton_Click(object sender, EventArgs e) 2: { 3: SqlConnection connection = new SqlConnection(); 4: SqlCommand command = new SqlCommand(); 5: SqlDataAdapter adapter = new SqlDataAdapter(); 6: SqlCommandBuilder builder = new SqlCommandBuilder(adapter); 7: DataSet dataset = new DataSet(); 8: 9: connection.ConnectionString = @"Data Source=.\SQLEXPRESS;" + 10: "Initial Catalog=University;Integrated Security=SSPI"; 11: command.Connection = connection; 12: command.CommandText = "SELECT * FROM Students"; 13: adapter.SelectCommand = command; 14: adapter.Fill(dataset, "Students"); 15: 16: DataRow row = dataset.Tables["Students"].NewRow(); 17: row["FirstName"] = firstNameTextBox.Text; 18: row["LastName"] = lastNameTextBox.Text; 19: row["Gender"] = genderTextBox.Text; 20: row["Age"] = Int32.Parse(ageTextBox.Text); 21: row["Address"] = addressTextBox.Text; 22: 23: dataset.Tables["Students"].Rows.Add(row); 24: 25: try 26: { 27: int result = adapter.Update(dataset, "Students"); 28: 29: if (result > 0) 30: MessageBox.Show("Success!"); 31: else 32: MessageBox.Show("Failed!"); 33: } 34: catch (SqlException ex) 35: { 36: MessageBox.Show(ex.Message); 37: } 38: }

Figure 2 We created the necessary objects in lines 3-7. Note the create of SqlCommandBuilder in line 6. In its constructor, we pass the created DataAdapter object to associate the CommandBuilder to it. Lines 12-14 fills the DataSet of values from the Student table and adds the result to the DataTable named Students inside the DataSet. Now that we have the values of the table from the database, we can now insert values to that DataTable. Line 16 creates a new DataRow object which represents the row of a DataTable. We specify which Table the row will be added and used the DataTables NewRow() method. Doing this instead of intializing a DataRow using a constructor saves automatically provides the necessary columns for the DataRow together with their associated column names. Lines 17-21 accesses each column using the DataRows indexer

that accepts the name of the column. We assigned each of the value of every text box. In line 23, we added the row to the Rows property of the DataTable that contains the records. We have now modifies the DataTable because we added a new record, but the record is only added to the DataTable, not the actual database table. Line 27 calls the DataAdapters Update() method and we passed the DataSet and the name of the DataTable. When Update() is called, the CommandBuilder generates the required SQL statements for every changes or modification it detects. Those SQL commands are then executed to the database. In our case, since we added a new row to the DataTable, CommandBuilder generates an INSERT Statement together with parameters filled with the values you specified. After the execution of the command, the values is then added to the actual database table. The Update() method returns an integer value representing the number of row affected. Since we inserted 1 row, then we are expecting a return value of 1. We test if the result is greater than 0 in line 29. If it is, then we show a success message. Otherwise, we tell the user that the insertion has failed. Execute the program and provide a value to be added to the database table.

Clicking the Add Student button will add the values to the DataTable and the changes will be sent to the database where a new row will be added to the actual database table.