| The easiest way to manipulate data using ADO.NET is to create a DataTable object containing the resultset of a table, query, or stored procedure. Using a DataTable object, you can add, edit, delete, find, and navigate records. The following sections explain how to use DataTables. Understanding DataTables DataTables contain a snapshot of data in the data source. You generally start by filling a DataTable, manipulating its results, and finally sending the changes back to the data source. The DataTable is populated using the Fill() method of a DataAdapter object, and changes are sent back to the database using the Update() method of a DataAdapter. Any changes made to the DataTable appear only in the local copy of the data until you call the Update method. Having a local copy of the data reduces contention by preventing users from blocking others from reading the data while it's being viewed. If you're familiar with ADO, you'll note that this is similar to the Optimistic Batch Client Cursor in ADO. Creating a DataAdapter To populate a DataTable, you must create a DataAdapter. The DataAdapter you're going to create uses the connection you've already defined to connect to the data source and then executes a query you'll provide. The results of that query will be pushed into a DataTable. As I mentioned earlier, there are multiple connection objects in the .NET Framework. There are multiple ADO.NET DataAdapter objects as well. You'll be using the OleDbDataAdapter because you will be connecting to Microsoft Access database. The constructor for a DataAdapter optionally takes the command to execute when filling a DataTable or DataSet, as well as a connection specifying the data source (you could have multiple connections open in a single project). This constructor has the following syntax: OleDbDataAdapter cnADONetAdapter = new OleDbDataAdapter([CommandText],[Connection]);
To add a DataAdapter to your project, follow these steps: 1. | Add the following statement immediately below the statement you entered to declare the m_cnADONewConnection object (in the class header, not in the Load event) to create a module-level variable:
OleDbDataAdapter m_daDataAdapter;
| | | 2. | Add the following statement at the bottom of the Load event of the form (immediately following the statement that opens the connection):
m_daDataAdapter = new OleDbDataAdapter("Select * From Contacts",m_cnADONetConnection);
Because you're going to use the DataAdapter to update the original data source, you must specify the insert, update, and delete statements to use to submit changes from the DataTable to the data source. ADO.NET lets you customize how updates are submitted by enabling you to manually specify these statements as database commands or stored procedures. In this case, you're going to have ADO.NET automatically generate these statements for you by creating a CommandBuilder object.
| 3. | Enter this statement in the class header (with the other two variable declarations) to create the CommandBuilder module-level variable:
OleDbCommandBuilder m_cbCommandBuilder;
The CommandBuilder is an interesting object in that after you initialize it, you no longer work with it directly: It works behind the scenes to handle the updating, inserting, and deleting of data. To make this work, you have to attach the CommandBuilder to a DataAdapter. You do so by passing a DataAdapter to the CommandBuilder. The CommandBuilder then registers for update events on the DataAdapter and provides the insert, update, and delete commands as needed.
| 4. | Add the following statement to the end of the Load event to initialize the CommandBuilder object:
OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter);
|
By the Way When using a Jet database, the CommandBuilder object can create the dynamic SQL code only if the table in question has a primary key defined.
Your code should now look like Figure 21.1 (notice that I collapsed the public frmMain procedure to make more code visible in the figure). Figure 21.1. You will be jumping around a lot in this example. Be sure to follow the steps exactly! 
Creating and Populating DataTables You're going to create a module-level DataTable in your project. Follow these steps: 1. | Create the DataTable variable by adding the following statement on the class header to create another module-level variable:
DataTable m_dtContacts = new DataTable();
| | | 2. | You're going to use an integer variable to keep track of the user's current position (row) within the DataTable. To do this, add the following statement immediately below the statement you just entered to declare the new DataTable object:
int m_rowPosition = 0;
| 3. | You now have a DataAdapter that allows access to a data source via the connection. You've declared a DataTable that will hold a reference to data. Next, add the following statement to the Load event of the form, after the existing code, to fill the DataTable with data:
m_daDataAdapter.Fill(m_dtContacts);
|
Because the DataTable doesn't hold a connection to the data source, it isn't necessary to close it when you're finished. Your class should now look like the one in Figure 21.2. Figure 21.2. This code accesses a database and creates a DataTable that can be used anywhere in the class. 
Referencing Fields in a DataRow DataTables contain a collection of DataRows. To access a row within the DataTable, you specify the ordinal (index) of that DataRow. For example, you could access the first row of your DataTable like this: DataRow m_rwContact = m_dtContacts.Rows[0];
Data elements in a DataRow are called columns. In the Contacts table I've created, for example, there are two columns: ContactName and State. To reference the value of a column, you can pass the column name to the DataRow like this: // Change the value of the column. m_rwContact["ContactName"] = "Bob Brown";
or // Get the value of the column. strContactName = m_rwContact("ContactName");
By the Way If you spell a column name incorrectly, an exception occurs when the statement executes at runtime; no errors are raised at compile time.
You're now going to create a procedure that's used to display the current record in the data table. Follow these steps: 1. | Position the cursor after the right bracket that ends the frmMain_FormClosing event and press Enter a few times to create some blank lines.
| | | 2. | Enter the following procedure in its entirety:
private void ShowCurrentRecord() { if (m_dtContacts.Rows.Count==0) { txtContactName.Text = ""; txtState.Text = ""; return; } txtContactName.Text = m_dtContacts.Rows[m_rowPosition]["ContactName"].ToString(); txtState.Text = m_dtContacts.Rows[m_rowPosition]["State"].ToString(); }
| 3. | Make sure that the first record is shown when the form loads by adding this statement to the Load event, after the existing statements:
this.ShowCurrentRecord();
You've now ensured that the first record in the DataTable is shown when the form first loads. To display the data, you must add a few controls to the form.
| 4. | Switch to the form designer, create a new text box, and set its properties as follows (you may have to switch back to view the properties if the events are still visible):
Property | Value |
|---|
Name | txtContactName | Location | 48,112 | Size | 112,20 |
| 5. | Add a second text box to the form and set its properties according to the following table:
Property | Value |
|---|
Name | txtState | Location | 168,112 | Size | 80,20 |
| | | 6. | Press F5 to run the project, and you'll see the first contact in the Contacts table displayed in the text box (see Figure 21.3).
Figure 21.3. It takes quite a bit of prep work to display data. 
|
Navigating Records The ADO.NET DataTable object supports a number of methods that can be used to access its DataRows. The simplest of these is the ordinal accessor that you used in your ShowCurrentRecord() method. Because the DataTable has no dependency on the source of the data, this same functionality is available regardless of where the data comes from. You're now going to create buttons that the user can click to navigate the DataTable. The first button will be used to move to the first record in the DataTable. Follow these steps: 1. | Stop the running project and display the Form Designer for frmMain.
| 2. | Add a new button to the form and set its properties as follows:
Property | Value |
|---|
Name | btnMoveFirst | Location | 16,152 | Size | 32,23 | Text | << |
| 3. | Double-click the button and add the following code to its Click event:
// Move to the first row and show the data. m_rowPosition = 0; this.ShowCurrentRecord();
| 4. | A second button will be used to move to the previous record in the DataTable. Add another button to the form and set its properties as shown in the following table:
Property | Value |
|---|
Name | btnMovePrevious | Location | 56,152 | Size | 32,23 | Text | < |
| | | 5. | Double-click the button and add the following code to its Click event:
// If not at the first row, go back one row and show the record. if (m_rowPosition != 0) { mm_rowPosition; this.ShowCurrentRecord(); }
| 6. | A third button will be used to move to the next record in the DataTable. Add a third button to the form and set its properties as shown in the following table:
Property | Value |
|---|
Name | btnMoveNext | Location | 96,152 | Size | 32,23 | Text | > |
| 7. | Double-click the button and add the following code to its Click event:
// If not on the last row, advance one row and show the record. if (m_rowPosition < m_dtContacts.Rows.Count-1) { m_rowPosition++; this.ShowCurrentRecord(); }
| 8. | A fourth button will be used to move to the last record in the DataTable. Add yet another button to the form and set its properties as shown in the following table:
Property | Value |
|---|
Name | btnMoveLast | Location | 136,152 | Size | 32,23 | Text | >> |
| 9. | Double-click the button and add the following code to its Click event:
// If there are any rows in the data table, // move to the last and show the record. if (m_dtContacts.Rows.Count != 0) { m_rowPosition = m_dtContacts.Rows.Count-1; this.ShowCurrentRecord(); }
|
Editing Records To edit records in a DataTable, you change the value of a particular column in the desired DataRow. Remember, though, that changes aren't made to the original data source until you call Update() on the DataAdapter, passing in the DataTable containing the changes. You're now going to add a button that the user can click to update the current record. Follow these steps: 1. | Add a new button to the form now and set its properties as follows:
Property | Value |
|---|
Name | btnSave | Location | 176,152 | Size | 40,23 | Text | Save |
| 2. | Double-click the Save button and add the following code to its Click event:
// If there is existing data, update it. if (m_dtContacts.Rows.Count !=0) { m_dtContacts.Rows[m_rowPosition]["ContactName"]= txtContactName.Text; m_dtContacts.Rows[m_rowPosition]["State"] = txtState.Text; m_daDataAdapter.Update(m_dtContacts); }
|
Creating New Records Adding records to a DataTable is performed much like editing records. However, to create a new row in the DataTable, you must first call the NewRow() method. After creating the new row, you can set its column values. The row isn't actually added to the DataTable, however, until you call the Add() method on the DataTable's RowCollection. You're now going to modify your interface so that the user can add new records. You'll use one text box for the contact name and a second text box for the state. When the user clicks the button you'll provide, the values in these text boxes will be written to the Contacts table as a new record. Follow these steps: | | 1. | Start by adding a group box to the form and setting its properties as shown in the following table:
Property | Value |
|---|
Name | grpNewRecord | Location | 16,192 | Size | 264,64 | Text | New Contact |
| 2. | Next, add a new text box to the group box and set its properties as follows:
Property | Value |
|---|
Name | txtNewContactName | Location | 8,24 | Size | 112,20 |
| 3. | Add a second text box to the group box and set its properties as shown:
Property | Value |
|---|
Name | txtNewState | Location | 126,24 | Size | 80,20 |
| 4. | Finally, add a button to the group box and set its properties as follows:
Property | Value |
|---|
Name | btnAddNew | Location | 212,24 | Size | 40,20 | Text | Add |
| 5. | Double-click the Add button and add the following code to its Click event:
DataRow drNewRow = m_dtContacts.NewRow(); drNewRow["ContactName"] = txtNewContactName.Text; drNewRow["State"] = txtNewState.Text; m_dtContacts.Rows.Add(drNewRow); m_daDataAdapter.Update(m_dtContacts); m_rowPosition = m_dtContacts.Rows.Count-1; this.ShowCurrentRecord();
|
Notice that after the new record is added, the position is set to the last row, and the ShowCurrentRecord() procedure is called. This causes the new record to appear in the display text boxes you created earlier. Deleting Records To delete a record from a DataTable, you call the Delete() method on the DataRow to be deleted. Follow these steps: 1. | Add a new button to your form (not to the group box) and set its properties as shown in the following table:
Property | Value |
|---|
Name | btnDelete | Location | 224,152 | Size | 56,23 | Text | Delete |
| 2. | Double-click the Delete button and add the following code to its Click event:
// If there is data, delete the current row. if (m_dtContacts.Rows.Count !=0) { m_dtContacts.Rows[m_rowPosition].Delete(); m_daDataAdapter.Update(m_dtContacts); m_rowPosition=0; this.ShowCurrentRecord(); }
|
Your form should now look like that in Figure 21.4. Figure 21.4. A basic data-entry form. 
Running the Database Example Press F5 to run the project. If you entered all the code correctly and you placed the Contacts database into the C:\Temp folder (or modified the path used in code), the form should display without errors, and the first record in the database will appear. Click the navigation buttons to move forward and backward. Feel free to change the information of a contact; click the Save button, and your changes will be made to the underlying database. Next, enter your name and state into the New Contact section of the form and click Add. Your name will be added to the database and displayed in the appropriate text boxes. Note, there is no provision in the code to deal with duplicate records, so attempting to add a duplicate name will generate an error. |
