DevCity.NET - http://devcity.net
ADO.NET for Beginners Part One
http://devcity.net/Articles/215/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/12/2006
 

This series of articles aims to introduce you to the use of ADO.NET. However, unlike other tutorials, I will concentrate on showing you how to communicate with your database programmatically (i.e. no wizards). This article will make use of an Address Book program to take you through the various aspects of database programming and who knows, by the end of it, you may decide to make use of the program to store all of those hundreds of contacts that you have.

Part one will look at the basic outline of the user interface and will include the necessary code to connect to the database and retrieve all of your contacts for displaying on the form.

Part two will go further and show you how to filter the data and modify the data including saving new contact information and deleting existing contact information.

Part one and two will make use of an Access database. However, Part three 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.


The Address Book User Interface

First things first, we will need a database. Attached to this article is an Access database that I prepared earlier and will form the basis for this guide. If you download this database now, we will then move along to creating the user interface.

I have based this tutorial on code that is compatible with the .NET Framework 1.1 so you can use either VS 2003 or 2005 when following along. Assuming that you now have your development environment loaded, the first thing we will do is to create a project. A Windows Application project type is required for this demonstration and the code in this tutorial is written in VB.NET.

As the first part of this tutorial is solely concentrating on how to connect to a database and retrieve simple information, the user interface does not need to be too complex. So the ideal control to drop onto our form at this point will be the DataGrid (Note, if you are using VS 2005 you can add the DataGrid component to your Toolbox by right clicking on the Toolbox and selecting "Choose Items...". Alternatively, use the DataGridView component). I have named my DataGrid "dgContacts" and have also added a button to the bottom of the form in order to close the application, aptly named "btnClose". Your user interface should then resemble something similar to the following screen shot:


The Address Book User Interface

The next part of this tutorial will look at connecting to the database that you downloaded previously and populating the DataGrid with the contents of the "Contacts" table from said database. Before moving on however, I recommend that you copy the database that you have downloaded to your projects bin\Debug folder. This will make connecting to the database easier as we will know that the location of the database will be in the same place as the executable for this program and thus we can use the Application.StartupPath method to grab this location.

 

An Introduction to the System.Data object

Before we start writing any code to connect to the database, it is important to understand some of the objects that you will be using and their storage locations within the Namespace hierarchy.

In order to start working with databases you first need to ensure that you have a reference to the System.Data object. To do this, select the Add Reference menu option from the Project menu and scroll down to the System.Data entry. Once you have added this reference, we can then take a closer look at what is offered via this object and also look at some of the methods we will be using in this part of the article.

Now, some of you may already know this, but there is a very nice tool in Visual Studio called the Object Browser. This tool isn't new to Visual Studio.NET but has been available in versions of Visual Basic as far back as I can remember. To access this tool, select the Object Browser menu option from the View menu. The Object Browser allows you to look at all objects referenced by your application and to drill down and look at the methods and properties that are exposed by those objects. Well designed objects will also have information in the form of remarks and descriptions that you can view when selecting a method or property.

So, with the Object Browser open, you should see the System.Data entry. If you expand the node you will see the available objects exposed by the System.Data object. The first point of interest is the sub node System.Data, this object houses all of the generic data objects that you will undoubtably use when working with databases. These objects include the DataSet, DataTable and DataView objects that you may already be familiar with.

The next point of interest are the different data provider objects that are available, such as System.Data.OleDb and System.Data.SqlClient. The System.Data.OleDb object contains objects that allow you to work with database that are oleDb compatible, such as Microsoft Access and mySQL. The System.Data.SqlClient object is an object that is specifically written to work with Microsoft SQL Server databases. There is also an System.Data.Odbc object should you need to work with databases via ODBC. As this part of the article is using an Microsoft Access database, we will concentrate on the System.Data.OleDb object from this point on, but when we reach Part Three of this article (using Microsoft SQL Server) you will see that the System.Data.SqlClient object is very similar to the System.Data.OleDb object).

If you expand the System.Data.OleDb node you will be presented with a list of the objects exposed. For this section of the tutorial we will be using the OleDbConnection and the OleDbDataAdapter objects to connect and retrieve an initial set of data from the database. By selecting the OleDbConnection node entry you will see the available methods and properties of this object as indicated in the following screen shot:


The OleDbConnection object in the Object Browser

As you can see in the above screen shot, I have selected the New(string) method, this is an overloaded constructor which as the summary states, initialises an instance of the connection object using a specified connection string.

If this is the first time that you have used the Object Browser, I recommend that you take some time to get to know it as it will undoubtedly aid you in your work.

 

Retrieving Data from the database

Now that we know what objects we will be using for this first part of the article, it is time to get stuck in and produce something a little more interesting.

Show me the Data!

Open the design of Form1 and double click in the form area to create the Form_Load event where we will place the code to retrieve all Contacts from our Address Book database. Now, before moving onto the actual code, we will add an "Imports" statement to the top of the code file in order to access the System.Data.OleDb objects without having to refer to the full namespace each time:

Imports System.Data.OleDb
 
So your code file should now look like the following screen shot:
 
 
Code file with Imports statement
 
By adding the above "Imports" statement, it allows us to write code such as "Dim connection As OleDbConnection". Without the above "Imports" statement we still have access to the OleDbConnection object, but in order to use it we would have to write code such as "Dim connection As System.Data.OleDb.OleDbConnection". You can think of the "Imports" statement as a shortcut to coding.
 
The next set of code to write will retrieve all data from the Contacts table within the database and show that data on the DataGrid. We will use the Form_Load event for this so that all data will be immediately visible when the program starts.
 
The first thing we need to do however is to create a Connection String for the database. A Connection String informs the OleDbConnection object what parameters to use when attempting to connect to the database. In it's most simplest form, the Connection String (for OleDb) will state the Provider (Microsoft Access, Microsoft Excel etc.) to use and the location of the data file.
 
The ConnectionString that we will be using looks like:
 
"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\AddressBook.mdb"
 
This specifies that we will be using the Microsoft.Jet.OleDb.4.0 provider and the location of the database is in the StartupPath of the application (should be your bin\Debug folder of your project) and the database is called AddressBook.mdb.
 
So putting this together with the code necessary to populate the DataGrid, we should have the following code in our Form_Load event:
 
 
Code to retrieve data and populate DataGrid
 
If you want to run this now then press F5 or select "Start Debugging" from the "Debug" menu. When the form loads you should see all entries from the Contacts table of the Address Book database loaded into the DataGrid.
 
Looking at the above code we can see that Line 7 declares a variable called "conn" which is declared as an OleDbConnection object. This declaration creates a new instance of the OleDbConnection object and makes use of its overloaded constructor to supply the Connection String. Any communication from our program to the database will occur through this connection object. 
 
Line 8 creates a new OleDbDataAdapter object. You can think of a DataAdapter as a bridge between the program and the database. Again, the OleDbDataAdapter has a number of overloaded constructors and in the above example, we are making use of one that allows us to specify a SELECT command and OleDbConnection object to use.
 
Note, in the above example we are selecting all fields from the Contacts table. However, it is considered (and rightly so) bad programming practice to make use of SELECT * queries. Usually, you would specify which fields to return from the database, such as SELECT ContactName, Telephone etc. but for the purpose of this tutorial, we will keep it simple. We will come back to the DataAdapter in a minute.
 
Line 9 creates a new DataTable variable. A DataTable is simply a collection of rows and fields and represents a database table in memory.
 
Line 11 makes use of the DataAdapter's Fill method to populate the DataTable with data retrieved from the database based on the SELECT statement used when constructing the DataAdapter. Quite a bit is actually happening at this point, namely:
 
  • A connection to the database is opened (based on the conn variable)
  • The DataTable is populated with data (based on the DataAdapter's SelectCommand Text). This includes both actual data and the table's schema (it's design definition).
  • The connection to the database is closed.

That's quite a lot of work for a single line call.

Finally, line 13 sets the DataSource property of the DataGrid to the DataTable. The DataGrid reads the schema information from the DataTable and uses the column names of the DataTable to create it's own columns and set the column heading text.  

 

Conclusion to Part One

To conclude Part One of this article, we will quickly summarise what we have covered.

Firstly, we have looked at the System.Data object which contains the generic Data components required to store data, such as the DataTable and DataSet objects. We also looked at the System.Data.OleDb object which allows us to communicate with OleDb databases such as Microsoft Access and mySQL etc.

We also took a quick look at the Object Browser tool which allows us to gather information such as Properties and Methods of all objects referenced by our program.

Secondly, we looked at the code required to retrieve data from the Address Book database and to present that data in a DataGrid or a DataGridView.

Admittedly, this part of the article is quite basic and doesn't provide much excitement. We will remedy this in Part Two when we will look at allowing the user to modify, add and delete data from the database. We will also look at methods for searching within the data.