Wednesday, June 20, 2012

Get Modified rows in a DataTable / DataGridView in C# Windows Forms


In this post we shall see on how to get the rows which are Modified in a DataGridView in a C# Windows forms application.

Unlike web applications, the Win Apps allow users to perform multiple operations like INSERT, MODIFY, DELETE to the data rows which are loaded in the DataGridView, once done the user clicks the Save button to send all the updates to the database at one shot.

Here we will see on how to capture the details of the rows which are modified by the user.
Initially load the DataGridView with data and allow the user to work on the data.

private void btnLoadEmployees_Click(object sender, EventArgs e)
{
    // Get the Connection string from App.config.
    string strConn = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
    //
    // Query the DB to get the Data           
    SqlConnection objConn = new SqlConnection(strConn);
    SqlCommand objCmd = new SqlCommand("SELECT * FROM EMPLOYEE", objConn);
    SqlDataAdapter objDA = new SqlDataAdapter(objCmd);
    DataSet dsEmployee = new DataSet();
    objDA.Fill(dsEmployee, "dtEmployee");
    //
    // Populate the GridView with Data
    gvEmployees.DataSource = dsEmployee.Tables["dtEmployee"];
}

Once the user is down with all the chages, he will click on the Save button, the following code filters the modified rows and updates the details into the database by forming UPDATE queries.

private void btnSave_Click(object sender, EventArgs e)
{
    string strQuery = string.Empty;
    DataTable dtChanges;
    //
    // Get the Updated DataTable back from the DataGridView
    DataTable dtEmployee = (DataTable)gvEmployees.DataSource;
    //
    // Get the Connection string from App.config.
    string strConn = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
    SqlConnection objConn = new SqlConnection(strConn);
    SqlCommand objCmd = new SqlCommand();
    //
    // Get the Modified Rows by filtering on their RowState
    dtChanges = dtEmployee.GetChanges(DataRowState.Modified);
    if (dtChanges != null)
    {
        //
        // Form the UPDATE Query to Update the Rows.
        objConn.Open();
        objCmd.Connection = objConn;
        for (int i = 0; i < dtChanges.Rows.Count; i++)
        {
            strQuery = "UPDATE Employee SET ";
            strQuery += "Name = '" + dtChanges.Rows[i]["Name"].ToString() + "',";
            strQuery += "DOB = '" + dtChanges.Rows[i]["DOB"].ToString() + "',";
            strQuery += "DOJ = '" + dtChanges.Rows[i]["DOJ"].ToString() + "',";
            strQuery += "Phone = '" + dtChanges.Rows[i]["Phone"].ToString() + "',";
            strQuery += "Email = '" + dtChanges.Rows[i]["Email"].ToString() + "',";
            strQuery += "DepartmentID = '" + dtChanges.Rows[i]["DepartmentID"].ToString() + "',";
            strQuery += "Salery = '" + dtChanges.Rows[i]["Salery"].ToString() + "'";
            strQuery += "WHERE ID = '" + dtChanges.Rows[i]["ID"].ToString() + "'";
            //
            // Execute the Update Query.
            objCmd.CommandText = strQuery;
            objCmd.ExecuteNonQuery();
        }
        objConn.Close();
        dtChanges = null;
    }
}
The DataTable.GetChanges(DataRowState. Modified), returns a DataTable which contains the detials of all the rows which were modified in the DataGridView.

To see on how to get the newly added rows refer the post Get new rows in a DataTable / DataGridView in C# Windows Forms. 

Search Flipkart Products:
Flipkart.com

2 comments:

Memtech said...

Hi!

DataGridView control are used very frequently in C#. It has various type of functionality but comman funcatin are CRUD operation. So thanks for sharing your

kanowledge. There are few other links that have described CRUD (Insert, Delete, Update) operation with good explaination and proper sample. I hope that's helpful for

beginners.


http://www.mindstick.com/Articles/9422cfc8-c2ed-4ec1-9fab-589eb850a863/?Insert%20Delete%20Update%20in%20DataGridView%20with%20DataTable%20in%20C

http://www.dreamincode.net/forums/topic/238727-insert-update-and-delete-records-in-table-with-datagridview-using-c%23/

Anonymous said...

Customize gridview rows C#