Update database from DataGridView

| December 21, 2010 | 0 Comments

Most of time developer want their users to be able to update database from DataGridView in Window Forms. So this article deals with concept of how to update tables in database from DataGridView. The main task of updating database is done with the help of System.Data.SqlClient.SqlDataAdapter.Update() method.  System.Data.SqlClient.SqlCommandBuilder generates required command for SqlDataAdapter when Select command and SqlConnection object are passed as argument to SqlDataAdapter object.

Update database from datagridview

Update database from datagridview

For demonstration we have developed small Window Application with two DataGridView and Update button. And changes made in both DataGridView can be updated in database at same time.

Learn Step-by-Step :

This article assumes that you are familiar with ADO.NET concepts.

1. Declare datagridview in Form1.cs or you can also drag same from Toolbox. Name it dgvCustomer.

System.Windows.Forms.DataGridView dgvCustomer = new System.Windows.Forms.DataGridView();

2.Declare SqlConnection to connect to database.

string conString = "Data Source=ENCORE; Initial Catalog=Market;Integrated Security=True";
SqlConnection con = new SqlConnection(conString);

3.Declare SqlDataAdapter that acts as bridge between Server and DataSet. First parameter passed as Select Command and second parameter passed is SqlConnection instance.

SqlDataAdapter custAdapter= new SqlDataAdapter("SELECT * FROM dbo.Customer", con);

4. Declare SqlCommandBuilder that generates Update command (Insert, Update and Delete). These commands are generated based on Select Command passed as argument to SqlDataAdapter. SqlCommandBuilder uses metadatato generate rest of commands based on the SqlDataAdapter object passed as parameter to SqlCommandBuilder object as shown below.

SqlCommandBuilder scbCust = new SqlCommandBuilder(custAdapter);

5.Open SqlConnection to database for further transaction to be possible.

con.Open();

6.Declare DataSet and Fill same with SqlDataAdapter by passing dataset as first parameter and table name as second parameter.

DataSet dsCommon=new DataSet();
custAdapter.Fill(dsCommon, "Customer");

7.Set DataSource of DataGridView to Table in DataSet.

dgvCustomer.DataSource = dsCommon.Tables["Customer"];

8. To update database from DataGridView we would need to call Update command of SqlDataAdapter object. BUT if you do not want to declare all Sql related objects again in Update function you can declare them as Class level variables.

We are retrieving UpdateCommand by SqlCommandBuilder’s GetUpdateCommand() method. And we have passed true argument so that it should generate Update queries with parameter names matching to Columns within Tables. And if false argument passed as parameter; names would be generated somewhat as @param1, @param2 and so on.

And at last we have passed Table in Update() of which records we want to udpate. And if rows updated successfully it would return number of rows affected.

this.custAdapter.UpdateCommand = this.scbCust.GetUpdateCommand(true);
int rowCust = this.custAdapter.Update(dsCommon.Tables["Customer"]);

if (rowCust > 0)
{
   lblMessage.Text = "INFO: Record updated successfully!";
}

Download Source Code

Category: .NET, C#

Leave a Reply

Sex Chat Live Jasmin WP-Clear 3.0 Theme
%d bloggers like this: