C# and ADO.NET

Last updated February 1, 2005

With the introduction of .NET technology came a new concept in database connectivity called ADO.NET. While similar to traditional Activex Data Objects (or ADO), it is very different mainly because of the underlying architecture. Most programmers and developers entering into the .NET world assume that since they are well-versed in ADO 2.x that ADO.NET is just another flavor of it. Once they get deep into the code of a C# forms-based application they suddenly realize that they need to learn a few new concepts with ADO.NET.

The main difference between ADO.NET and prior versions of ADO is that ADO.NET is a "disconnected" technology when it comes to dealing with databases. What do we mean by a disconnected technology? Basically, all database access operations involve first retrieving the data from the database into the computer's cache and disconnecting from the database. This is automatic with ADO.NET and seamless to the developer. Data is manipulated, viewed, and deleted within the local computer's cache and not while connected to the physical database. When the data in the cache is ready for updating, the connection to the database is reestablished and the data is transmitted and applied.

Why would we want to work in a disconnected technology? It's easy. We don't have to stay connected to the database to work with data in our local memory and this is a tremendous resource saver. Fewer active connections in the database help the database perform better.

In this article, we will go through a small tutorial that addresses the basic operations we would want to do in any forms-based C# applications. We would want to add, delete, view, and change records within a table. Our example uses a Microsoft Access database but can be easily adapted to a MS SQL or Oracle database using the OLE (Object Linking and Embedding) database drivers.

The Example Program

For this tutorial, we will use as an example a basic forms application which accesses one table with two fields. This example uses a Microsoft Access 2000 database however it can be easily adapted to an SQL Server or Oracle environment by merely changing the connection string. Figure 1 shows our graphical user interface (GUI) for the sample application.

Figure 1

The listbox in the middle of the GUI will display existing records in the table PhoneNumbers. The "New" button is used to insert a new record into the table, the "Change" button is used to update a record, and the "Delete" button will delete a row in the table. The "Exit" button will do just that -- quit the application.

Getting Started - The Connection String and the OleDbConnection Object

In any ADO.NET or ADO application, the connection string is what describes the specific connect parameters we need to successfully connect to a database. When an application is developed to run in multiple relational database environments, you typically change the connectionstring.

public static string connectionString =
"provider=Microsoft.JET.OLEDB.4.0; + "data source = " + Application.StartupPath + "\\ADONETTutorialDb.mdb";

The database, ADONETTutorialDb.mdb, should be placed in the same directory as this application's .exe file is located in.

The OleDataAdapter Object

An object we will discuss in sections to follow is the "DataSet" object which is merely the data table in cache memory -- the results of the query stored in cache. The OleDataAdapter object creates the bridge between the dataset in cache and the actual database table in the database. You basically feed it the connection string which we discussed earlier and the command string that contains the SQL statement to apply. In the next code fragment, we will define the SQL statement to apply when we declare a new OleDbDataAdapter instance:

string commandstring = "select * from PhoneNumbers";
dataAdapter = new OleDbDataAdapter(commandstring, connectionString);

The BuildCommands Method

This concept in ADO.NET seems a little tricky at first however you will see the advantages later on. In ADO.NET, a DataAdapter object can have four explicit SQL commands. They are the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand. This method actually builds the syntax for each command that we will use for operating on the data. However, in addition to the syntax, we can actually define the parameters or variable arguments that will be passed to the commands. This adds a dynamic feature to ADO.NET SQL commands. Remember in ADO, we had to actually build the SQL statement with whatever values of current variables. These were strung together along with the SQL syntax. We no longer have to do this with ADO.NET as now we can define "reusable" SQL commands that are part of the current DataAdapter object.

Here is how the InsertCommand will be setup:

// Declare a reusable insert command with parameters
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText =
"insert into PhoneNumbers " +
"(Phonenum, Subscriber) " +
"values " +
"(?, ?)";

dataAdapter.InsertCommand.Parameters.Add("Phonenum", OleDbType.Char, 0, "Phonenum");
dataAdapter.InsertCommand.Parameters.Add("Subscriber", OleDbType.Char, 0, "Subscriber");

Notice that we will use the CreateCommand method on the current connection object and assign the text of the SQL insert command. For our tutorial, the PhoneNumbers table only has two columns: Phonenum and Subscriber. When we get to the part where the values go, we simply put "?,?" as a substitution for both column values. Now, how is the value of the column in memory linked to the column in the SQL command? The answer is through the Parameters.Add method that will tell the InsertCommand object that the first parameter will come from the columns "Phonenum" and "Subscriber" in the DataTable (we will discuss the DataTable further).

And now for the Update and Delete commands:

The same concept applies here as well. We are using the Parameters.Add method to create the link between the table in memory and the physical database table. Here is the code for the Update and Delete commands below:

// Declare a reusable update command with parameters
dataAdapter.UpdateCommand = connection.CreateCommand();
dataAdapter.UpdateCommand.CommandText = "update PhoneNumbers " +
"set Subscriber = ? " +
"where Phonenum = ? ";
dataAdapter.UpdateCommand.Parameters.Add("Subscriber", OleDbType.Char, 0, "Subscriber");
dataAdapter.UpdateCommand.Parameters.Add("Phonenum", OleDbType.Char, 0, "Phonenum");

// Declare a reusable delete command with parameters
dataAdapter.DeleteCommand = connection.CreateCommand();
dataAdapter.DeleteCommand.CommandText =
"delete from PhoneNumbers where Phonenum = ?";
dataAdapter.DeleteCommand.Parameters.Add("Phonenum", OleDbType.Char, 0, "Phonenum");

The DataSet Class

With ADO.NET, we have a new structure known as the DataSet. The DataSet class can be thought of as an in-cache memory database table filled with data from the physical database and the current select statement. We use the Fill method of the OleDbDataAdapter object to refresh the data in the DataSet. Invoking the Fill method will synchronize the data in the DataSet to match what is in the ADO recordset (database). The next code fragment will fill the data set with the current contents of the PhoneNumbers table in a new DataSet instance named dataSet. Once filled, the Fill_lb method is called to display the current dataset in cache memory in the listbox on the main form.

dataSet = new DataSet();
dataSet.CaseSensitive = true;
dataAdapter.Fill(dataSet,"PhoneNumbers");
Fill_lb();

The Fill_lb Method

The Fill_lb method in the tutorial program will first declare an instance of a DataTable which is one table of relational data residing in memory and is usually used in conjunction with the DataSet object. In our example, we are associating the DataTable object with the dataSet.tables[0] pointer or, in other words, the first table in the dataSet object. Now we have DataTable methods accessible to us. The first step is to open up something like a cursor (but not really a cursor) on the dataTable and read each row and post to the listbox. To do this, we will use a foreach construct to fill each row in the listbox:

dataTable = dataSet.Tables[0];
listBox1.Items.Clear();

foreach (DataRow dataRow in dataTable.Rows)
{
LoadBuffers(dataRow);
listBox1.Items.Add( Phonenum + "\t\t" + Subscriber);
}

Note the LoadBuffers method. Here we pass an individual DataRow object to this method and its purpose is to extract data from the columns in the row and store in the global variable strings Phonenum and Subscriber.

Ready to Process

Once the Fill_lb method is invoked, we are ready to process. You will see a screen as follows:

Figure 2

At this point, the program is ready to handle data-altering events. We will discuss the following events generated by clicking the buttons on this form.

The "New" Event

If you fill in the Phone number and name fields in the form and click the "New" button, a new row will get added. Beware that Phone# is a unique key so don't try to add one already there. What happens is that we add a new row to the DataSet object. However before this, we must build the new row via the DataRow object which we named newrow for this instance. The dataAdapter is the link between the in-cache table and the physical database table therefore the Update method will send the transaction and the AcceptChanges method will actually commit it. Note that for Oracle databases, we found that trailing spaces had to be trimmed from the end of field. It does not seem to be a problem in other database platforms but does not hurt anything if the Trim method is left in.

The "Change" Event

Changing data in the dataset is a little more complex than with inserting and deleting. First, we have to select the row in the listbox and then move that information into an object of DataRow type which in our example is named selectedRow. We apply the changes by invoking the BeginEdit method on the target row. The BeginEdit method marks the beginning of a transaction to apply changes to the row. Once the new values are applied, we issue the EndEdit method on the target row.

// get the selected row
DataRow selectedRow = dataTable.Rows[listBox1.SelectedIndex];

// inform the user
Application.DoEvents();

// Begin an edit transaction on the row.
selectedRow.BeginEdit();
selectedRow["Subscriber"] = txtSubscriber.Text.Trim();
selectedRow.EndEdit();

The next step is to create a new DataSet (dsChanges) that will contain each row that changed in the original DataSet object. Then, with this new DataSet, we will check the HasErrors property to see if there were any changes that produced an error. The remaining code will determine which row has the error and display the key (Phonenum) to the user.

// retrieve each row that changed
DataSet dsChanges =
dataSet.GetChanges(DataRowState.Modified);

// check for any changed rows with errors
bool okayFlag = true;
if (dsChanges.HasErrors)
{...}

If all is okay, then we will call the Update method in the dataAdapter object and pass to it the dsChanges object that contains our changes. This will stage the changes for updating to the physical database. The actual update will take place when we invoke the AcceptChanges method on the DataSet object. And of course, we will call the RejectChanges method on the DataSet object if there are errors.

// No errors -- all okay
if (okayFlag)
{
    // apply updates to the database
    dataAdapter.Update(dsChanges,"PhoneNumbers");
    // tell the user
    MessageBox.Show("Updated " + selectedRow["Phonenum"]);
    Application.DoEvents();
    // apply changes and refresh the listbox
    dataSet.AcceptChanges();
    Fill_lb();
}
else // if any errors then throw out the changes
    dataSet.RejectChanges();

So let's summarize the steps involved in changing a database row:

1. Apply the changes to the row: BeginEdit -> apply changes -> EndEdit
2. Create a new DataSet object with changes via the GetChanges method on the edited DataSet object.
3. Check the new dataset with changes for errors -- inform the user of the bad row.
4. If everything is okay, then call the Update method on the DataAdapter object and pass the dataset with changes (dsChanges).
5. Invoke the DataSet.AcceptChanges method to post the updates to the database. If there are errors, invoke the RejectChanges method to discard any updates.

The "Delete" Event

The very last event we will discuss has to do with the process of deleting a row from the database table. The first step in deleting is to get the selected row from the listbox and place it into a new object of type DataRow (selectedRow in our example). You then will call the Delete method on the selectedRow object to mark the row for deletion. Note that we have only marked the row and not followed through with the actual delete. This does not happen until you apply the dataAdapter.Update method and the dataSet.AcceptChanges method. Of course, if we have any exceptions or errors then the dataSet.RejectChanges method is applied to back out from the delete. The next code fragment shows our delete logic:

// fetch the selected row in the listbox
DataRow selectedRow = dataTable.Rows[listBox1.SelectedIndex];
string msg = selectedRow["Phonenum"] + " deleted.";

// delete the selected row
selectedRow.Delete();

// apply delete to the database
try
{
    dataAdapter.Update(dataSet,"PhoneNumbers");
    dataSet.AcceptChanges();
   // refresh the listbox after the delete
    Fill_lb();
   // inform the user
   MessageBox.Show(msg);
    Application.DoEvents();
}
catch (OleDbException ex)
{
    dataSet.RejectChanges();
    MessageBox.Show(ex.Message);
}

Attachments:

Project Files : ADONetTutorial.zip

Conclusion

The combination of this tutorial document and the sample program should give you enough foundation to create most C# forms applications with an ADO.NET interface. In fact, you can probably take the example tutorial application and expand it to fit your more complex applications. All the basics are in it: select, insert, change, and delete rows in a database. ADO.NET is very different from classic ADO because of the type of technology it uses. It uses a "disconnected" technology in which we connect to the database to retrieve rows based on our select query, disconnect from the database and work with our data. We only connect back to the database when there are modifications, inserts, or deletes to apply to a specific table. Download the application that goes with this tutorial and give it a try.