How to connect to an Access database from Visual C# .NET, retrieve data and display it using a DataGrid with ADO.NET, using a dataSet.

In the tutorial I assume you already know the basics of the SQL language.

First download the database we will use in this example and save it somewhere:

Start a new ‘Windows Application’ project named ‘accessDB’.

From the View menu of Microsoft Visual C# .NET select Server Explorer, or press Ctrl+Alt+S.

From the list right-click Data Connections and choose ‘Add Connection…’.

In the ‘Provider’ tab select Microsoft Jet 4.0 OLE DB Provider (used for connecting to an Access database), and click Next.

Use the ‘…’ button to browse for an Access Database and choose the database you have downloaded, db1.mdb. After clicking OK, test the connection by clicking the ‘Test Connection’ button.

It should say ‘Test connection succeeded.’.

Press OK and a window pops up that says ‘Please Enter MS JET OLE DB Initialization Information’.

Leave the defaults and just press OK.

Just like you see in the above screenshot, you can browse the Access file database. Yet the database is not connected to our program. For this you need to drag the node below ‘Data Connections’ on the form.

The node is named using the form ‘ACCESS.X:\PathToYourDatabase\file.mdb.Admin’. As I said, drag it on the form and ‘oleDbConnection1’ should appear below the form:

Next open the Toolbox (Ctrl+Alt+X) and from the ‘Data’ group drag an ‘OleDbDataAdapter’. The ‘Data Adapter Configuration Wizard’ starts.

Clicking next will take you to the part where you need to select the connection you wish to use. Choose the connection we have just created (ends up in db1.mdb.Admin). Press Next and then again Next (leave the default ‘Use SQL statements’).

Now you are beeing asked ‘What data should the data adapter load in the dataset?’. We want all the tables and all the columns therefore we need to take the following steps. Open the Query Builder using the button and you should now be able to add the two tables named ‘books’ and ‘categs’. Add them and close the small window and now we have two small windows representing the two tables. We want to select all the columns, therefore check ‘* (All Columns)’ on both tables.

The following SQL query is created:

SELECT books.*, categs.*

FROM (books INNER JOIN

categs ON books.ID = categs.ID)

We could also do it more simple by typing ‘SELECT * FROM books, categs’… it would have been the same.

Press OK to exit the Query Builder and then press Finish.

Now add a DataSet to our application by dragging one from the Data group (Toolbox). Choose ‘Untyped DataSet (no schema)’.

DataSets are used to store the query results with which we later populate the DataGrid.

Before we start coding we need something where we display the results. The DataGrid is perfect for that, so drag one from the Toolbox, the Windows Forms group.

Now doubleclick the form so we can go to the ‘Form1_Load’ event.

First we should set the SQL query:

oleDbDataAdapter1.SelectCommand.CommandText = "SELECT * FROM books, categs where books.bookCateg = categs.ID";

Next we clear the DataSet preparing it for the operation

dataSet1.Clear();

Next we use the ‘Fill()’ method of oleDbDataAdapter1 to fill the dataSet1 with the result of the SQL query:

oleDbDataAdapter1.Fill(dataSet1, "Books");

Finally we bind it using the DataGrid:

dataGrid1.SetDataBinding(dataSet1, "Books");

Here is all the code we wrote in one place:

private void Form1_Load(object sender, System.EventArgs e)

{

oleDbDataAdapter1.SelectCommand.CommandText = "SELECT * FROM books, categs where books.bookCateg = categs.ID";

dataSet1.Clear();

oleDbDataAdapter1.Fill(dataSet1, "Books");

dataGrid1.SetDataBinding(dataSet1, "Books");

}

ADO.NET and Access database II