C# Tutorial - Binding a DataGridView to a Database

In this tutorial, I'm going to show a fairly simple way to bind a .NET DataGridView to a database. The form designer has some support for data binding, but I found doing it without the form designer is a little easier to understand and implement. Also, when I'm developing a desktop database application, my database schemas are rarely 100% defined, and the form designer doesn't easily support changes to the database.

The database I'm going to use for the example code will be an Access database. I know Access databases aren't the preferred database type for developers - because of their speed and scalability. However, for simple database apps, Access is hard to beat - since you don't need to install any outside database engines. In reality, the concepts shown in this tutorial can be used with any number of databases.

The first thing we'll need to do is generate a connection string to connect to our Access database. For simplicity, the database I'm using doesn't require any authentication. If your database has authentication, MSDN has some great documentation on how to accomplish that.

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myDatabase.mdb";
The connection string is broken up into two parts, a provider and a data source. The provider is the engine we're going to be using - in this case, Microsoft's Jet engine. The data source, for Access, is simply the path to the database file.

Now let's use the connection string and get some data from our database.

//create the connection string
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myDatabase.mdb";

//create the database query
string query = "SELECT * FROM MyTable";

//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

//create a DataTable to hold the query results
DataTable dTable = new DataTable();

//fill the DataTable
dAdapter.Fill(dTable);
To keep the code simple, I've left out a lot of error handling. You'll definitely want to surround dAdapter.Fill with some exception handling. That call will fail for many different reasons - for instance the database isn't where the connection string says it is, or the query string is invalid SQL code.

So let's go through what this code is actually doing. The first thing to do is to create the connection string as described above. Then we need an SQL statement to execute on our database. This can be any SELECT statement you want. Next we create an OleDbDataAdapter which serves as a bridge between our DataTable and our database. An OleDbCommandBuilder comes next. This beautiful object automatically generates SQL insert, update, and delete statements to rectify changes made to our DataTable. Next we need to make a DataTable to hold the information retrieved from the database. And lastly, we call dAdapter.Fill(dTable) which executes our SQL query and fills dTable with the results.