DevCity.NET - http://devcity.net
ADO.NET for Beginners Part Two
http://devcity.net/Articles/240/1/article.aspx
David Jeavons
I have been programming database applications for almost 12 years starting out with VB3 and using every major version since then. I currently work for a retailer analysis company writing ASP.NET code for our ever evolving web site. I also enjoy helping other programmers with Visual Basic and .NET and am a moderator at vbCity. 
by David Jeavons
Published on 7/18/2006
 

Part two of this series will look at managing your data by showing you how to update, add and delete records from the Contacts table. In this section we will look at the OleDbCommand object which we will utilise quite a bit when it comes to running "Action" queries against the database.

We will also look at how we can filter the data using the DataView object.


Modifying the Address Book User Interface

In order to demonstrate how we can manage data in the database we will add a few controls to the existing form that will allow us to modify some of the fields in the Contacts table. Note that I have purposely only added controls to modify some of the fields in the table. The reason for this is twofold.  

First, it will be a good exercise for you to add the remaining fields to the interface once you are happy with your understanding of working with databases, and secondly, the code samples will be quite long winded if all fields were listed. After all, the aim of this tutorial is to show you some of the techniques that you can employ when working with databases and I am sure that your next database application will be somewhat different to a simple Address Book.

So, without further ado, start by adding three new buttons to the Address Book form as follows:

Name Text
   
btnAdd Add
btnEdit Edit
btnDelete Delete

We will also modify our existing code slightly (not that there is much of it at the moment). Currently, we are retrieving all Contacts data within the Form Load event which is fine for displaying the initial data to the user. However, when we add new records or amend existing records, it will be quite awkward having to keep writing the same retrieval code again in order to show the changes. So the easiest approach would be to take the code that retrieves the contacts data and place it into a sub routine which we can then call from both the Form Load event and any other event that we see fit.

I have created the following routine called RetrieveContacts and moved the code from the Form Load event to this new routine:

and then added a call to the above routine within the Form Load event:

The last thing we need to do before moving on to modifying our data is to create a form that we can use to add new data and edit existing data. So, if you add a new form to your project and call it "ManageData", then add the following controls to the form:

Control Name Text
     
Label lblTitle Title
TextBox txtTitle  
Label lblFirstName First Name
TextBox txtFirstName  
Label lblLastName Last Name
TextBox txtLastName  
Button btnSave Save
Button btnCancel Cancel

it should then look similar to the following:

As you can see, we will only be concentrating on adding and updating three of the fields in the Contacts table. It will be a further exercise should you wish to complete the project to add the additional controls and modify the code accordingly to manage all of the fields in the Contacts table.

 

Adding data to the Contacts table

Before we look at the code to add new data to our Contacts table, we will need to add some logic to the ManageData form to allow us to know whether we are adding a new record or editing an existing record. The simplest way of achieving this would be to add a property to the ManageData form that will accept a ContactID value. Then in our Save button code, we can determine if we have a ContactID or not (new record or existing record).   This also serves another purpose as when we are editing an existing record we will need to have the ContactID in order to update the correct record in the Contacts table.

Adding the ContactID Property

Add the following code to the ManageData form:

If you are unfamiliar with the above syntax or properties in general then the following is a quick overview.

The above property has a name of ContactID and works very much like a function>   There is a Get Method which allows you to retrieve the value of the _contactID variable and a Set method which takes a Value parameter which is assigned to the _contactID variable.

You will notice that the _contactID variable is marked as Private.   This is to ensure that it is not visible outside of the form.   Of course this could have been marked as Public but by doing so will mean that you have no method whereby you could validate the data coming in before then assigning it to the variable.

By using properties, if you wanted to limit the ContactID value to a number between 1 and 100 then you could add this validation within the Set method.    If it fell within the range then the value could be assigned to the _contactID variable, otherwise you could throw an exception or set the _contactID variable to a default value.

OleDbCommand Object

In order to execute Action queries (Action queries being those that modify data) we need to use the OleDbCommand object. If you open the Object Browser and drill down to the System.Data object and then further down to the System.Data.OleDb object, you will see that the first object listed is the OleDbCommand object. As you can see from its Summary field, it states that the OleDbCommand object "Represents an SQL statement or stored procedure to execute against a data source".  This is exactly what we will be doing in a moment.

Firstly, however, take a moment to look at the ExecuteNonQuery() method. This method will execute an SQL Statement or stored procedure against the data source and return the number of rows affected by the query. This is the method that we will be using in the code that follows.

Creating the code to Add new data to the Contacts table

Add the following code to the Save button on the ManageData form:

The above code creates a new connection to the AddressBook database and declares an empty string variable called sql. Then in line 41, the _contactID variable is checked to determine if we are adding a new record (later you will see that we also use this check to determine if we are editing an existing record). Once it is determined that we are adding a new record, the sql string variable is then assigned an INSERT INTO sql statement (more on this in a moment). Line 52 then opens the database connection as you have seen in the previous part of this article.

Now line 54 and 55 are the most important parts of this code block as they are responsible for doing the work of modifying the database. First, line 54 declares a new command object and passes to it's constructor the SQL statement that we want the command object to work with and the connection object that it will work against. Line 55 then calls the command object's ExecuteNonQuery method which all going well will then execute the query and write the data to the Contacts table. As previously stated, the ExecuteNonQuery method returns the number of rows affected by the query, so assuming that there were no problems with the above code, the returned value should be equal to 1 as one row of data should have been added to the Contacts table. You can assign the returned value to an Integer variable should you wish to notify users of how many rows were affected. Another important point is that if no rows were affected by the query then the return value will be equal to -1. Finally, Lines 57 and 60 simply close the connection and the form.

In the above code, we used an INSERT INTO SQL statement to write the contents of our three text boxes to the Contacts table. The INSERT INTO statement can be wrote in a number of ways, for example, if you were writing to every single field in the Contacts table then you could have simply wrote:

 INSERT INTO Contacts (fieldvalue, fieldvalue, fieldvalue etc..)

However, two reasons why we are not doing this in the above example. The first and most obvious reason is that we are only concentrating on three fields for this example so we must explicitly state which fields we want to write to, hence the syntax:

 INSERT INTO Contacts (Title, FirstName, LastName) VALUES (etc.)

The other reason however, is that the Contacts table contains a primary key field called ContactID which is an AutoNumber field (meaning that it increments by one each time a new record is added) and this AutoNumber field should be handled by the database, not us. For this reason, if we did not include the list of fields to write to, the database would expect us to supply a field value for the ContactID field which we certainly do not want to do.

You will also notice with the above INSERT INTO syntax that each text box is enclosed within single quotes. This is because the desired output of the SQL string should look like:

 INSERT INTO Contacts (Title, FirstName, LastName) VALUES ('Mr', 'Joe', 'Bloggs')

However, depending on the data type of field you are writing to, you may not need to enclose the field value in quotes. The following table shows the generic Data Types and the characters that should be used to enclose the field values. The table also includes the characters to be used when working with SQL Server data:

Data Type Access Character SQL Server Character 
     
String Apostraphe (') Apostraphe (')
Numeric No character No character
Date Hash (#) Apostraphe (')

So assuming that we had a table containing three fields called FirstName, Age and DOB, to write to these fields our SQL syntax (for Access) would need to look like:

 INSERT INTO TableName (FirstName, Age, DOB) VALUES ('Dave', 18, #01/01/1998#)

These rules apply throughout all SQL queries, not just the INSERT INTO statement.

A further tip that catches many people out is to ensure that your string values are properly formatted to avoid breaking the SQL string. By this I refer to values that contain an apostraphe such as the Surname O'Reilly. If you imagine that you are writing to a field with this value the SQL String will look like:

 INSERT INTO TableName (LastName) VALUES ('O'Reilly')

which will cause a syntax error as the query will stop after the O' and treat Reilly as an improperly formatted string value. In order to avoid this, you must escape any apostrophe characters within the string value by doubling them up so that the SQL string would look like:

 INSERT INTO TableName (LastName) VALUES ('O''Reilly')

this is easily achieved by using the strings Replace method and replacing all single apostrophes with double apostrophes. For example:

 

Trying out the new Add Data code

Before we can try the new functionality out, we need to modify our main form so that we can load the ManageData window. Place the following code into the Add button's click event:

You should now be good to go. Launch the program and press the Add button and enter some information into the three fields. Once you press the Save button, you should then see the new data refreshed in the grid.

 

Editing and Deleting data from the Contacts table

Now that we know how to add data to the Contacts table, we can look at the SQL syntax for updating an existing record. When updating records we use the UPDATE syntax which takes the form of:

 UPDATE tableName SET fieldName=Value, secondFieldName=Value WHERE fieldName=Criteria

The above would update two fields in a specific table to specific values using a WHERE clause to limit the number of rows affected. If you wanted to update the entire table then you would omit the WHERE clause.

So, to put this into the context of our AddressBook application, the SQL we will need to add to our Save button on the ManageData form for performing an update would be:

The entire save routine should therefore look like:

As you can see in the above, the code has not changed that much from the previous version that simply creates a new record. This is because the ExecuteNonQuery method of the OleDbCommand object executes action queries.   Both the INSERT INTO and UPDATE satatements are action queries, so we need only supply the correct SQL statement to the ExecuteNonQuery method for it to function correctly.

Before we can test the new update routine, we again have to attach the code to the Edit button on our Main form to show the ManageData form. This time however, we also need to use the ContactID property on the ManageData form to notify the save routine that

a) it is editing a record and

b) which record to update.

Add the following code to the Edit button of your main form:

The above code is very similar to that found in the Add button's click event with the difference being that in Line 54 we are updating the ContactID property of the ManageData form to the value selected in the dgContacts grid.   One point to make about the syntax above is that the dgContacts.Item method requires that you pass it a row index and a column index. The row index is easily obtained by using the dgContacts.CurrentRowIndex property and for the purpose of this tutorial the ContactID column is the first column in the returned data so is referred to by its index of zero. Also the dgContacts.Item property has a return type of object so in order to use it properly, it has to be cast to an Integer value; hence the CType function.

If you launch the program and select a row in the dgContacts grid that you want to edit and make some changes before then pressing the Save button, you should then see your changes reflected in the dgContacts grid. Now there is a slight problem with the current setup.  Currently, when you press the Edit button, the ManageData form is displayed but the fields on the ManageData form are blank.

This is not an ideal solution for the user as they may only want to edit one field, but with the current implementation, they would have to supply values for all fields otherwise they will be updated to empty strings. So we need to add a method in our ManageData form that will retrieve the data from the Contacts table based on the value of the _contactID variable that was passed from the main form. The method that we will create will be very similar to that we used to retrieve all Contacts for our DataGrid, the only difference being that we only need to retrieve the record that matches the _contactID passed. Add the following method to the ManageData form:

The first thing to note with the above routine is that it is marked Private. This is because I don't see a reason for the main form to ever need to invoke this method. The second thing to note is the inclusion of a new object that we haven't discussed yet, namely the OleDbDataReader.

There are many ways of reading data from a database, such as using an OleDbDataAdapter and using the Fill method to populate a DataSet/DataTable as we have already seen.  We can then enumerate through the DataTable should we wish to retrieve specific information and show the values in controls on the form.  However, the overhead required to create a DataSet or DataTable is relatively large compared to using a simple OleDbDataReader to access a forward only cursor to the data.

What do I mean by forward only cursor? Well, the OleDbDataReader can only be used in a forward only fashion, which means that you can enumerate through the OleDbDataDataReader using a Do Loop but once you have read a record, you cannot go back to a previous record. This is different to a DataTable as you have access to its Rows property which allows you to specify any particular row of data at any given time. The OleDbDataReader however, is much quicker in this scenario as all we are interested in is a single row of data.

Now that we have the RetrieveContactDetails method in place, we need to call it from somewhere. I think the best place to call this for our example is in the Set method of the ContactID property as the Set method is only called when we are about to edit an existing record. You will need to modify the Set method of the ContactID property to the following:

Now if you launch the program and select a row to edit, you will see that the values from the Contacts table are populated into the fields on the ManageData form. This is a more desirable interface for the user.

Deleting Records

This section is the shortest of all of the sections as you now have enough knowledge of executing action queries that I need not explain in too much detail how to execute a DELETE query. The syntax of the DELETE query takes the form:

 DELETE FROM tableName WHERE fieldName=fieldValue

It goes without saying that when you write your DELETE statement, don't forget to add the WHERE clause, otherwise you will delete every record from the table. If this is your intention then fine, but I have on occasion found myself going to a backup of a database because I forgot to add the WHERE clause to limit the number of rows deleted.

Add the following code to your Delete button:

The code above is similar to the Edit button's code in that we grab the ContactID value from the dgContacts grid by use of it's Item property.

The code we have just looked at in the previous section and this section is now enough for you to be able to fully manage your database table. You can retrieve data, update and add data and delete data from the table.

 

Filtering Data

Often you will want a method to allow your users to search for specific information within the data that is presented to them. This can be done in a number of ways, one of which you have already seen in the previous section where we used a SELECT statement to retrieve a single record based on the ContactID.

However, if we were to stick with this method, we would end up with a fair bit of code as we would first need to perform the search and populate a DataTable with the results before then binding the results to the dgContacts grid. We would also then need a mechanism allowing the user to retrieve all of the data again (i.e. clear the search results). Another drawback to this method is that we would need to hit the database twice; the first time to perform the search and the second time to clear the search.

There is an alternative which makes filtering the data extremely easy:-  Enter the DataView. The DataView - like the DataSet and DataTable - exists in the System.Data hierarchy which means that it is the same object that you will use regardless of which database you are working with. The DataView allows you to perform a number of functions against the data in a DataTable and one of those functions is the ability to filter the data. Also, the DataView can be bound to controls in exactly the same way as the DataTable can.

Before we move onto the code for filtering the data we will need to add a couple of controls to the main form to allow the user to enter their filter criteria. Add the following controls to your main form:

Control Type Name Text
     
Label lblSearch Search
TextBox txtSearch  

For the purpose of this demonstration, we will perform the filtering on the FirstName field of the Contacts table and we will also do this filtering via the TextChanged event of the Search text box. This allows us to give the user the ability to enter the first character of the first name and the filter will kick in immediately showing only those records that begin with that character.

Add the following code to the Search TextBox TextChanged event:

The code above declares a DataView object and then assigns it to the DataSource of the dgContacts grid. However, it is important to note that the DataSource of the dgContacts grid could be one of two types. The first time the form is loaded the DataSource of the grid is set to a DataTable, but as soon as any filtering is performed, the DataSource will then be set to a DataView object. For this reason, the TypeOf operator is used to determine how to cast the DataSource in order to assign it to the DataView. In addition, if the DataSource object type is a DataTable then the DataTable's DefaultView property is used which returns a DataView object based on the DataTable.

The next part of the code determines how to set the RowFilter of the DataView. If any text is entered then the RowFilter property is set to an SQL WHERE clause which states that all records where the FirstName is "LIKE" that entered in the txtSearch box. Also note that the * character is used as a wildcard character at the end of the search string so that all records starting with the characters entered are returned. Likewise, if the txtSearch text box does not contain any data then the RowFilter property is set to an empty string which states that no RowFilter should be used and thus all data is returned. Finally the dvFilter DataView is assigned as the DataSource to the dgContacts grid.

If you run your program now and enter some text into the search criteria, you should see the grid start filtering the data based on the FirstName field. If you enter text that doesn't match any field values then the grid will not display any records. Likewise, if you delete the contents of the search criteria text box, the grid will then show all of the records. This is all achieved without ever hitting the database.

 

Conclusion

We now have a fully functional AddressBook program that we can use to store, manage and search our contacts. During this tutorial we have learnt what objects are used to retrieve the data using the OleDbDataAdapter and OleDbDataReader objects and the objects used to manage the data via the use of the OleDbCommand object. We have also learnt how we can filter data without having to re-query the database each time, and did this by using the DataView object.

Further Improvements

This project could be improved in numerous ways. The first most obvious improvement is to provide the user with the ability to add and edit all fields in the Contacts table. Another improvement would be to introduce some exception handling routines to the code (which I left out intentionally for space reasons) in order to gracefully handle any exceptions that may occur.

Whether you decide to finish the project to make it more complete or move onto your next killer app, the information in this article will have hopefully given you an introduction to and understanding of working with databases using ADO.NET.

In Part three, which will be published later this year, I will use an SQL Server database and show you how to use Stored Procedures to access and modify the data. The user interface and functionality will remain the same, only the database used will change.