CodeCall Programming Blog

Introduction
Most programs nowadays use some sort of database. The usual favourite is SQL Server, but the .NET Framework includes another form of database - a .mdb Microsoft Access database. Commence ADO.NET.

ADO.NET
The .NET Framework provides us with another database technology, known as ADO.NET. The original ADO (Active Data Objects) is still used, but enhanced with new DataReaders (the equivalent of a forward-only, read-only ADO RecordSet). ADO.NET allows us to connect to a Microsoft Jet database, like the ones created in Microsoft Access.

Connecting to the Database
Unfortunately, there are lots of objects involved in connecting to databases. These are part of the System.Data.OleDb namespace, as OLEDB is the technology we use when connecting to Microsoft Jet databases. Add the following two C# import statements into the top of the code file:

 

Now, the first object we need is the Connection object. Use the following code to create a new variable and store the connection in it:

 
We have to tell .NET two things - the source of the data and the type of data. To do this, we set the ConnectionString property of the Connection object:

 
We can also set other parameters in the connection string, such as UID (username), PWD (password), DRIVER (database driver, not required if a DSN is specified) and SERVER (the network name of the data source server). However, for a simple database without a username/password, this is fine.

Now we just open the connection:

 
By now, the connection is opened to the database. We now need to extract the data and store it. To extract it, we use a DataAdapter:

 
There are two parameters - the connection we set up earlier, and the SQL we need to extract the data from the database. We could use update, insert and delete commands in SQL to everything, but we can use another object to do this for us:

 
We pass in the adapter. From here onwards, the CommandBuilder takes care of all the handling of data, leaving our hands free to populate the DataTable.

Storing the Data
Now we have a connection to the database, we need somewhere to dump the data. We could use either a DataSet or a DataTable. Here we are going to use a datatable:

 
Now we have a new DataTable called 'table'. Now we let the DataAdapter do its magic and pull the data out of the database, using the connection we set up, under instruction by the CommandBuilder... into our table.

 
Now, we still have an open connection. We must close the connection, or else it may not work properly:

 

Conclusion
And that's it! The DataTable is full of lovely data. To get a row of data, simply use the Rows[] property, which in turn can be accesses using an object collection. For example, to get the fourth row of data (indexed with 3 because arrays are zero-based), simple use:

 
Then, to display a message box with the data from that row under the column "Address":

 
What you do with the data is up to you. And don't forget, the Rows[] and Columns[] are both object collections, so you can loop through them with either a for loop or a foreach loop, perhaps to populate a grid of data.