Working with a Database in C#

This chapter is from the book

Sams Teach Yourself C# in 24 Hours

Learn More Buy

This chapter is from the book

This chapter is from the book 

Sams Teach Yourself C# in 24 Hours

Learn More Buy

Manipulating Data

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, you can add, edit, delete, find, and navigate records. The following sections explain how to use DataTables.

Understanding DataTables

DataTables contain a snapshot of the data in the data source. You generally start by filling a DataTable, and then you manipulate the results of the DataTable before 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 is being viewed. This is similar to the Optimistic Batch Client Cursor in ADO.

Creating a DataAdapter

To populate a DataTable, you need to create a DataAdapter, an object that provides a set of properties and methods to retrieve and save data between a DataSet and its source data. The DataAdapter you're going to create will use the connection you've already defined to connect to the data source and will then execute a query you'll provide. The results of that query will be pushed into a DataTable.

Just as two ADO.NET connection objects are in the .NET Framework, there are two ADO.NET DataAdapter Objects as well: the OleDbDataAdapter and the SqlDataAdapter. Again, you'll be using the OleDbDataAdapter because you aren't connecting to Microsoft SQL Server.

The constructor for the 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 the DataAdapter to your project, first add the following statement immediately below the statement you entered to declare the m_cnADONewConnection object.

OleDbDataAdapter m_daDataAdapter = new OleDbDataAdapter();

Next, add the following statement to the Load event of the form, immediately following the statement that creates the connection:

_daDataAdapter =
new OleDbDataAdapter("Select * From Contacts",m_cnADONetConnection);

Because you're going to use the DataAdapter to update the original data source, you need to 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 allowing 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. Enter the following statement to create the CommandBuilder.

OleDbCommandBuilder m_cbCommandBuilder = 
     new OleDbCommandBuilder(m_daDataAdapter);

When you create the CommandBuilder, you pass into the constructor the DataAdapter that you want the CommandBuilder to work with. The CommandBuilder then registers for update events on the DataAdapter and provides the insert, update, and delete commands as needed. You don't need to do anything further with the CommandBuilder.

Creating and Populating DataTables

You're going to create a module-level DataTable in your project. First, create the DataTable variable by adding the following statement on the line below the statement you entered previously to declare a new module-level m_daDataAdapter object:

DataTable m_dtContacts = new DataTable();

You are going to use an integer variable to keep track of the user's current position 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;

Next, add the following statement to the Load event of the form, immediately following the statement that creates the CommandBuilder:

m_daDataAdapter.Fill(m_dtContacts);

Your class should now look like the one in Figure 21.1.

Figure 21.1 This code accesses a database and creates a DataTable that can be used anywhere in the class.

Referencing Columns in a DataRow

DataTables contain a collection of DataRows. To access a row within the DataTable, you specify the ordinal 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. For example, two columns, ContactName and State, are in the Contacts table I've created. To reference the value of a column, you can pass the column name to the DataRow like this:

m_rwContact["ContactName"] = "Bob Brown";

or

Debug.WriteLine(m_rwContact["ContactName"]);

You're now going to create a procedure that is used to display the current record in the database. To display the data, you need to add a few controls to the form. Create a new text box and set its properties as follows (you'll probably need to click the Properties button on the Properties window to view the text box's properties rather than its events):

Add a second text box to the form and set its properties according to the following table:

Next, click the Form1.cs tab in the IDE to return to the code window. Position the cursor after the right bracket that ends the fclsMain_Closed() event and press Enter a few times to create some blank lines. Next, 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();
}

Ensure that the first record is shown when the form loads by adding the following statement to the Load event, after the statement that fills the DataTable:

this.ShowCurrentRecord();

You've now ensured that the first record in the DataTable is shown when the form first loads. Next, you'll learn how to navigate and modify records in a DataTable.

Navigating and Modifying 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 came from.

You're now going to create buttons that the user can click to navigate the DataTable.

The first button is used to move to the first record in the DataTable. Add a new button to the form and set its properties as follows:

Double-click the button and add the following code to its Click event:

m_rowPosition = 0;
this.ShowCurrentRecord();

A second button is 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:

Double-click the button and add the following code to its Click event:

if (m_rowPosition > 0)
{
  m_rowPosition = m_rowPosition-1;
  this.ShowCurrentRecord();
}

A third button is 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:

Double-click the button and add the following code to its Click event:

if (m_rowPosition < m_dtContacts.Rows.Count-1)
{
  m_rowPosition = m_rowPosition + 1;
  this.ShowCurrentRecord();
}

A fourth button is 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:

Double-click the button and add the following code to its Click event:

If (m_dtContacts.Rows.Count !=0)
{
  m_rowPosition = m_dtContacts.Rows.Count-1;
  this.ShowCurrentRecord();
}

Editing Records

To edit records in a DataTable, simply change the value of a particular column in the desired DataRow. Remember, however, that changes are not 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. Add a new button to the form now and set its properties as follows:

Double-click the Save button and add the following code to its Click event:

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 very 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 a button you'll provide, the values in these text boxes will be written to the Contacts table as a new record.

Start by adding a group box to the form and set its properties as shown in the following table:

Next, add a new text box to the group box and set its properties as follows:

Add a second text box to the group box and set its properties as shown:

Finally, add a button to the group box and set its properties as follows:

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 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. Add a new button to your form (not to the group box) and set its properties as shown in the following table.

Double-click the Delete button and add the following code to its Click event:

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.2.

Figure 21.2 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.