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.
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.
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.
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.
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.
10/20/2007 - 02:35
How can i restict to type text in a datagridview cell and alow only numerical data.
10/21/2007 - 21:20
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.
10/20/2007 - 21:00
Very helpful info.....Thanks...
10/30/2007 - 19:58
Great article; simple to understand and precise. Many thanks.
11/03/2007 - 15:40
Thanks, this was the first article I found on a professional (i.e. non-designer) way to bind the DataGridView.
12/02/2007 - 09:07
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
12/31/2007 - 16:32
Nice article. Very straight forward. I too have been looking for this answer for awhile, mostly using Google.
01/09/2008 - 19:42
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.
01/15/2008 - 16:40
This article is just what I was looking for. Thanks for writing it!
01/17/2008 - 23:54
WOW. . . . .
01/28/2008 - 09:00
Very good information supplied in an easy read and easy understood way, nice work.
Thank you for your effort.
01/31/2008 - 15:13
really nice... thanks friend
02/04/2008 - 16:15
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.
02/04/2008 - 16:31
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?
02/14/2008 - 14:46
If you use the binding source you can handle its positionchanged event. It's the best place to trigger the update .
02/16/2008 - 03:31
Thank you.
03/04/2008 - 07:49
Been looking for a clean explanation of how all these different objects fit together for a long time. Thanks!!
03/06/2008 - 10:22
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.
Replace 'dgView' with the variable you created.
Thanks for the site.
03/13/2008 - 13:55
Gracias, muy util esta información... y muy bien explicado.
Thank You!
03/28/2008 - 05:38
Supperayittundu nayinte mone!
04/02/2008 - 10:06
So, How to update / insert data?
thanks
04/02/2008 - 19:22
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.
04/14/2008 - 18:21
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
;)
04/24/2008 - 01:43
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?
05/15/2008 - 14:57
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?
09/04/2008 - 13:55
I'm in the same boat.
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.
05/16/2008 - 07:01
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.
05/16/2008 - 08:07
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.
05/18/2008 - 21:40
Thank you.
06/02/2008 - 03:11
thanks for this great article!!!
06/11/2008 - 08:43
Very helpful. Thank you!
06/17/2008 - 00:47
very useful information. Thanks
08/07/2008 - 11:00
PLZ i want to do the opposite :
i want to update datagridview from database in real time
plz help!!!!!!!!
NoussaL@live.fr
08/19/2008 - 03:28
Simple and clear..
Nice
08/19/2008 - 09:55
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..
08/20/2008 - 05:31
When I do 'dAdapter.Update(dTable);' I get 'Syntax error in INSERT INTO statement'. Any help will be appreciated. Regards, Manne
08/29/2008 - 15:39
useful tutorial, thanks a lot
09/07/2008 - 11:36
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
09/08/2008 - 09:13
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!
09/09/2008 - 07:21
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!!
09/09/2008 - 10:37
Don't forget
bSource.EndEdit();
before
dAdapter.Update(dTable);
or your last modifications will be lost.
10/23/2008 - 09:08
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.
10/28/2008 - 03:16
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.
10/28/2008 - 09:03
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);
11/13/2008 - 07:42
Very interesting to read..I appreciate your knowledge and effort.
Taake care,
11/27/2008 - 04:42
this is what i am looking for.
12/06/2008 - 08:26
REALLY GREAT !!!
For anybody who is using MySQL like me:
After added DataAdapter and CommandBuilder add the following lines:
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.InsertCommand = builder.GetInsertCommand();
as you can read on:
http://forums.mysql.com/read.php?38,56936,60594#msg-60594
Cheers,
Marco D.
12/11/2008 - 19:41
i try the code i get the error
“Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.”
any help is welcome
12/12/2008 - 09:53
It sounds like it doesn’t know how to track changes made to the DataGridView. Does your database table have a primary key?
03/17/2009 - 12:55
Change the query you are using to populate the datatable to include the primary key in your table.
01/05/2009 - 22:41
a good job! thanks for your artical.
01/19/2009 - 00:07
I have been banging my head against the wall for days trying to figure out how to do this. I can't believe how poorly documented it is.
Your concise description is perfect.
03/04/2009 - 05:24
When i am trying to use DataGridView control it is not showing the data.Data is comming to the DataSet and i have assigned DataSource if the DataGridView control is DataSet.But it is not displaying Data.I have tried with BindingDataSource also still same problem.Please help me in this regard
04/21/2009 - 07:52
bSource.DataSource = dTable;
dgView.DataSource = bSource;
Make sure you have both those rows included. I just had the same error until i noticed i forgot to add one row :)
05/21/2009 - 04:36
Hi
I have followed your example without a hitch, however,when doing the update in the gridviews validating event
private void dataGridView1_Validating(object sender, CancelEventArgs e)
{
bSource.EndEdit();
dAdapter.Update(dTable);
}
The following errors are encountered:
Error 1: The name 'bSource' does not exist in the current context
Error 2: The name 'dAdapter' does not exist in the current context
Error 3:The name 'dTable' does not exist in the current context
I know there is something simple to fix here, just don't know what it is.
The article is extremely helpful, thanks
05/21/2009 - 08:52
Where did you declare bSource, dAdapter, and dTable? You'll probably have to make these member variables of the class in order to use them in other functions.
05/22/2009 - 03:25
I placed the code in the click event of a button, as is, how would you declare the bSource, dAdapter, and dTable somewhere else, as the objects are instantiated with parameters ie
OleDbDataAdapter dAdapter = new OleDbDataAdapter(strSql, con);
AND
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
If i can get my head around this it will go along way to helping with other apps that i am coding
Much appreciated
Chris
PS I am coming from a VB6 background and finding the transition to C# a bit challenging
CODE:
private void btnSearch_Click_1(object sender, EventArgs e)
{
txtSearch.BackColor = Color.White;
if (txtSearch.Text.Trim() != "")
{
try
{
OleDbConnection con = new OleDbConnection(clsConnSrt.GetConnectionString("PastelCostingSlip.Properties.Settings.PastelCostingSlipConnectionString").ToString());
string strSql;
strSql = "SELECT * FROM Sheet1 WHERE PlantDescription LIKE '" + txtSearch.Text.Trim() + "%" + "'";
OleDbDataAdapter dAdapter = new OleDbDataAdapter(strSql, con);
//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
con.Open();
//fill the DataTable
dAdapter.Fill(dTable);
//BindingSource to sync DataTable and DataGridView
BindingSource bSource = new BindingSource();
//set the BindingSource DataSource
bSource.DataSource = dTable;
//set the DataGridView DataSource
dataGridView1.DataSource = bSource;
con.Close();
con.Dispose();
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
}
05/22/2009 - 08:33
What's happening is you're declaring the variables inside the search event handler, which means they won't be available anywhere else except inside that function. All you need to is move the declaration to the class. Here's an example.
{
private DataTable dTable;
...
private void btnSearch_Click_1(object sender,
EventArgs e)
{
this.dTable = new DataTable();
...
}
}
Here the DataTable is declared as a private member of the class, then set in the search event handler. Now any function inside MyClass can reference the dTable variable. Of course, you'll have to do this for all the variables you'd want to reuse, not just dTable.
06/01/2009 - 09:10
Any idea how to do this when you want to display the data as a pivot table? Let's say I have a SQL table with normalized data and they are stored into the data table as key's. and then I want to display values not key's in the datagridview and then pivot one of the key's as column header?
thanks for any help! I have been online all day trying to find a solution!
06/13/2009 - 15:35
Hi, thanks for the tutorial.
I can't get it to work. It doesn't display anything. I copy everything correctly but when I run the app it doesn't display anything. What could be the problem?
06/19/2009 - 15:28
Hi
This is by far the best Tutorial I've read on DataGridView but one thing is maybe missing.
Using the code above the DataGrid does not appear - I had to create a DataGrid by dragging the control onto my Form. Any ideas why the grid did not display please.
Thanks
David
06/19/2009 - 15:36
This tutorial assumes a DataGridView already exists. It does not cover how to create and place one on a control.
06/25/2009 - 15:34
Hi
I don't think tutorials should assume anything - if the tutorial assumes you know something, then why would you even write tutorial at all. No one can assume anything if its aimed at many different programmers at different levels of knowledge.
But great tutorial all the same.
cheers
David
06/25/2009 - 15:40
David, that isn't quite true, if you assume nothing then you have to start from the ground up everytime you create any tutorial. You have to assume some knowledge or you don't have real starting point for the post.
I would love every tutorial to cover everything from the ground up but that isn't practically possible and we have plenty of other tutorials on the basics of most topics (not all).
06/22/2009 - 22:03
Very great Article..
very simple to understand...
Thanks thanks and thanks to you for this article