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";
"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);
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.
Now that we have a
DataTable filled with database information, let's see how to synchronize it with a DataGridView.
//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;
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;
The
BindingSource object is what will be keeping our DataTable synchronized with the DataGridView. So we set the DataSource of the BindingSource to 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.
dAdapter.Update(dTable);
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,
DataError, 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 data.
That's all the code required to perform two-way data binding with an Access database. If you have any questions or concerns about the above code, leave a comment.
Posted in C#, All Tutorials by The Reddest |
October 20th, 2007 at 3:35 am
How can i restict to type text in a datagridview cell and alow only numerical data.
October 20th, 2007 at 10:00 pm
Very helpful info…..Thanks…
October 21st, 2007 at 10:20 pm
@Arif - The next to last paragraph explains what will happen when the user inputs invalid data based on the column’s data type. The DataError event will be thrown where you can display the error of your choosing.
This event will only be thrown after the text has been entered and the cell loses focus. If you want to limit characters as the user is typing, you’ll have to manually check each character on a key event. I’ll be discussing it further in an upcoming tutorial.
October 30th, 2007 at 8:58 pm
Great article; simple to understand and precise. Many thanks.
November 3rd, 2007 at 4:40 pm
Thanks, this was the first article I found on a professional (i.e. non-designer) way to bind the DataGridView.
December 2nd, 2007 at 10:07 am
man thanks a lot ! i was getting bugged by this problem ! i scanned msdn , this and that forum . documentation sucks till i landed up here ! wow man u did an excellent job . thanks a lot again
December 31st, 2007 at 5:32 pm
Nice article. Very straight forward. I too have been looking for this answer for awhile, mostly using Google.
January 9th, 2008 at 8:42 pm
I had to look for this in thousands of pages and here in few lines u show us how to do it nice and simple.
Kudos to u.
January 15th, 2008 at 5:40 pm
This article is just what I was looking for. Thanks for writing it!
January 18th, 2008 at 12:54 am
WOW. . . . .
January 28th, 2008 at 10:00 am
Very good information supplied in an easy read and easy understood way, nice work.
Thank you for your effort.
January 31st, 2008 at 4:19 pm
really nice… thanks friend
February 4th, 2008 at 5:15 pm
Fantastic. The grid works great with the update() in the validating event. But, I’m having trouble figuring out when validating fires?
I seem to be able to enter many rows before validating fires.
February 4th, 2008 at 5:31 pm
Curious, I use almost this exact same method for simple grids, but I don’t use a bindingSource. I just call the update method of the dataAdapter. What does the bindingSource give you?
February 14th, 2008 at 3:46 pm
If you use the binding source you can handle its positionchanged event. It’s the best place to trigger the update .
February 16th, 2008 at 4:31 am
Thank you.
March 4th, 2008 at 8:49 am
Been looking for a clean explanation of how all these different objects fit together for a long time. Thanks!!
March 6th, 2008 at 11:22 am
Worked the first time.
Remember, if you have a placed a DataGridView in the form designer you don’t need to this line that creates a new one.
DataGridView dgView = new DataGridView();
Replace ‘dgView’ with the variable you created.
Thanks for the site.
March 13th, 2008 at 2:55 pm
Gracias, muy util esta información… y muy bien explicado.
Thank You!
March 28th, 2008 at 6:38 am
Supperayittundu nayinte mone!
April 2nd, 2008 at 11:06 am
So, How to update / insert data?
thanks
April 2nd, 2008 at 8:22 pm
To update and insert data, all you have to do is modify the DataGridView. Adding rows to the DataGridView will add rows to the database when the OleDbDataAdapter is updated. Any cells modified in the DataGridView will also be updated in the database. You just have to remember to call dAdapter.Update(dTable) to update the database with the changes made to the DataGridView.
April 14th, 2008 at 7:21 pm
Thanks for been so concise. I spent a lot of time reviewing internet articles and all of them are so bulky. This one is so small and quick to understand
April 24th, 2008 at 2:43 am
I also appreciate this article very much, but I have a related question: I created a datatable and bindingsource and bound the table to a listbox via the bindingsource at run time. I also specified the DisplayMember (a string) and ValueMember (that one is the record id) properties of the listbox, but I cannot get the record id from the SelectedValue property of the listbox. What I get is a DataSourceView (or stg.like that) that can’t be cast to an int. Any opinions?
May 15th, 2008 at 3:57 pm
When i put the da.update(dTable) in the click event for a button i get a error saying that “da is not recognized”.
How to solve this?
May 16th, 2008 at 8:01 am
I did like you wrote, but I still can’t update database.
I tried to delete row from datagridview and to update row, but changes only performed to data table not to database.
May 16th, 2008 at 9:07 am
Changes won’t automatically be saved back to the database. You have to call Update on your OleDbDataAdapter. If you’re already doing that then there’s another problem I sometimes experience that you might be seeing also.
I’m sure yet what causes it, but sometimes the modified flag on a DataRow doesn’t get set to true, even though a value has changed. What I’ve found is that if you suspend the bindings (bSource.SuspendBindings()), the modified flag will be set and the database will update correctly. Make sure you resume the bindings when you’re done.
May 18th, 2008 at 10:40 pm
Thank you.
June 2nd, 2008 at 4:11 am
thanks for this great article!!!
June 11th, 2008 at 9:43 am
Very helpful. Thank you!
June 17th, 2008 at 1:47 am
very useful information. Thanks
August 7th, 2008 at 12:00 pm
PLZ i want to do the opposite :
i want to update datagridview from database in real time
plz help!!!!!!!!
NoussaL@live.fr
August 19th, 2008 at 4:28 am
Simple and clear..
Nice
August 19th, 2008 at 10:55 am
how do i convert the the result into an array?
example:
i have a COLUMN of words and i want it to convert to an array because i will use it in the string compartion..
August 20th, 2008 at 6:31 am
When I do ‘dAdapter.Update(dTable);’ I get ‘Syntax error in INSERT INTO statement’. Any help will be appreciated. Regards, Manne
August 29th, 2008 at 4:39 pm
useful tutorial, thanks a lot
September 4th, 2008 at 2:55 pm
I’m in the same boat with “25. Martin May 15th, 2008″.
If you put all the load/binding code in the Form_Load event, and you put the code “dAdapter.Update(dTable)” in a button_click event then the data adapter (dAdapter) and data table (dTable) are out of scope.
And if you put that code in the Form_Load event, then it just updates your freshly fetched data - giving no time for the user to make any changes.
I love simplicity of this article, but it “simply” doesn’t work. I know I am missing something totally basic, but I don’t know what it is.
September 7th, 2008 at 12:36 pm
This article is nice….Thanks for this.
Can any body provide some information how can I bind add custom coloums to datagrid like I can add in wen appication…
Developer4you.com
September 8th, 2008 at 10:13 am
i’m trying to do the exact same thing using the MySQL data connector but for some reason i can compile with no errors, but my DataGridView shows nothing!
September 9th, 2008 at 8:21 am
ok, figured it out. i didn’t need the line ‘DataGridView dgView = new DataGridView();’ because i already had the DataGridView created in Design View. thanks for writing this up, it was very helpful!!
September 9th, 2008 at 11:37 am
Don’t forget
bSource.EndEdit();
before
dAdapter.Update(dTable);
or your last modifications will be lost.
October 23rd, 2008 at 10:08 am
It’s just a great article, I am a professional programmer but I didn’t see something like that kind of approach, It looks that you are also a professional coder. I also use to bind it like this. I really like your code. Keep it up
GOD Bless you.
October 28th, 2008 at 4:16 am
I tried this code, but when i call:
dataAdapter.update(dataTable);
an exception “Update requires a valid UpdateCommand when passed DataRow collection with modified rows”. And i do not know how to solve it
Your answer is welocme.
October 28th, 2008 at 10:03 am
I sounds like you might not have an OleDbCommandBuilder associated with the OleDbDataAdapter.
OleDbDataAdapter dAdapter =
new OleDbDataAdapter(query, connString);
//create a command builder
OleDbCommandBuilder cBuilder =
new OleDbCommandBuilder(dAdapter);
November 13th, 2008 at 8:42 am
Very interesting to read..I appreciate your knowledge and effort.
Taake care,