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
which serves as a bridge between our
DataTable and our database. An
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
to hold the information retrieved from the database. And lastly, we call
dAdapter.Fill(dTable) which executes our SQL query and fills
with the results.
Now that we have a
DataTable filled with database information, let's
see how to synchronize it with a
//the DataGridView DataGridView dgView = new DataGridView(); //BindingSource to sync DataTable and DataGridView BindingSource bSource = new BindingSource(); //set the BindingSource DataSource bSource.DataSource = dTable; //set the DataGridView DataSource dgView.DataSource = bSource;
object is what will be keeping our
DataTable synchronized with the
DataGridView. So we set the
DataSource of the
dTable, then set the
DataSource of the
DataGridView to the
BindingSource. Now when your program runs, the DataGridView should be
filled with the results of your SQL query.
At point, any changes made by the user in the DataGridView will
automically be made to the DataTable, dTable. Now we need a way to get
the changes back into the database. All you have to do is call the
Update function of the
OleDbDataAdapter with the
DataTable as the
argument to accomplish this.
This call will use the
OleDbCommandBuilder to create all of the
necessary SQL code to synchronize your database with the changes made to
dTable. But, when should you call this function? There's lots of
different answers to that. If you have a save button, call it when the
user pushes save. If you want the database updated in real-time, I like
to call it on the DataGridView's Validating event.
The last topic to discuss is error handling. What happens when the user
types something in the DataGridView that can't be put in the database -
like text where a number is supposed to go? Because you're using
DataBinding, it would take a lot of work to get the data type at each
column and manually make sure the user typed in the correct thing.
Fortunately, the DataGridView has an event,
to handle that for you. Whenever the user enters something incorrectly,
this event will be fired. From this event you can get the row and column
index of the cell where the incorrect value was placed, and you can also
cancel the event so you don't attempt to update the database with bad
That's all the code required to perform two-way data binding with an Access database.