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

In this post we shall see on how to get the rows which are Deleted 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 were deleted 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 deleted rows and deleted the details from database by forming DELETE 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 Deleted Rows by filtering on their RowState

    dtChanges = dtEmployee.GetChanges(DataRowState.Deleted);

    if (dtChanges != null)

    {

        //

        // Form the DELETE Query to Delete the Rows.

        objConn.Open();

        objCmd.Connection = objConn;

        for (int i = 0; i < dtChanges.Rows.Count; i++)

        {

            strQuery = "DELETE FROM Employee ";

            strQuery += "WHERE ID = '" + dtChanges.Rows[i]["ID", DataRowVersion.Original].ToString() + "'";

            //

            // Execute the Insert Query.

            objCmd.CommandText = strQuery;

            objCmd.ExecuteNonQuery();

        }

        objConn.Close();

        dtChanges = null;

    }

}

The DataTable.GetChanges(DataRowState.Deleted), returns a DataTable which contains the detials of all the rows which were deleted in the DataGridView.

Notice that we are using DataRowVersion.Original, along with the column name ID, this is because the row is deleted and the values from the deleted row cannot be obtained.

If we use dtChanges.Rows[i]["ID"], instead of dtChanges.Rows[i]["ID", DataRowVersion.Original], then the following exception will be thrown.

System.Data.DeletedRowInaccessibleException: Deleted row information cannot be accessed through the row.

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

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

Related Posts
DataRow RowState
DataTable AcceptChanges
DataTable RejectChanges
Get new rows in a DataTable / DataGridView in C# Windows Forms
Get Modified rows in a DataTable / DataGridView in C# Windows Forms
Get Deleted rows in a DataTable / DataGridView in C# Windows Forms

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. 

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


Related Posts
DataRow RowState
DataTable AcceptChanges
DataTable RejectChanges
Get new rows in a DataTable / DataGridView in C# Windows Forms
Get Modified rows in a DataTable / DataGridView in C# Windows Forms
Get Deleted rows in a DataTable / DataGridView in C# Windows Forms

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

In this post we shall see on how to get the rows which are added newly to 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 added newly 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 newly inserted rows and inserts the details into the database by forming INSERT 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 Newly added Rows by filtering on their RowState

    dtChanges = dtEmployee.GetChanges(DataRowState.Added);

    if (dtChanges != null)

    {               

        // Form the INSERT Query to insert the New Rows.

        objConn.Open();

        objCmd.Connection = objConn;

        for (int i = 0; i < dtChanges.Rows.Count; i++)

        {

            strQuery = "INSERT INTO Employee (Name,DOB,DOJ,Phone,Email,DepartmentID,Salery)";

            strQuery += " VALUES (";

            strQuery += "'" + dtChanges.Rows[i]["Name"].ToString() + "',";

            strQuery += "'" + dtChanges.Rows[i]["DOB"].ToString() + "',";

            strQuery += "'" + dtChanges.Rows[i]["DOJ"].ToString() + "',";

            strQuery += "'" + dtChanges.Rows[i]["Phone"].ToString() + "',";

            strQuery += "'" + dtChanges.Rows[i]["Email"].ToString() + "',";

            strQuery += "'" + dtChanges.Rows[i]["DepartmentID"].ToString() + "',";

            strQuery += "'" + dtChanges.Rows[i]["Salery"].ToString() + "')";

            //

            // Execute the Insert Query.

            objCmd.CommandText = strQuery;

            objCmd.ExecuteNonQuery();

        }

        objConn.Close();

        dtChanges = null;

    }
}

The DataTable.GetChanges(DataRowState.Added), returns a DataTable which contains the detials of all the rows which are added newly to the DataGridView.

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

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

Related Posts
DataRow RowState
DataTable AcceptChanges
DataTable RejectChanges
Get new rows in a DataTable / DataGridView in C# Windows Forms
Get Modified rows in a DataTable / DataGridView in C# Windows Forms
Get Deleted rows in a DataTable / DataGridView in C# Windows Forms

Binding data to DataGridView in C# Windows Form

In this post Binding data to DataGridView in C#, we shall see on how to query data from a SQL Server database and bind the results to a DataGridView control.

First let us create a new Windows form and add a Button and DataGridView control to the form, the initial form design should be as follows.

Once we are done we can navigate to the code view and add the code to get data from SQL Server and bind the same to the DataGridView.

Since we are going to get the data from SQL Server database, add a reference to 

using System.Data.SqlClient;

Add the following code to the click event of the Button, to load the DataGridView with data.

private void btnLoadEmployees_Click(object sender, EventArgs e)

{

    // Read the connection string from the app.config file.

    string strConn = ConfigurationSettings.AppSettings["ConnectionString"].ToString();

    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");

    //

    gvEmployees.DataSource = dsEmployee.Tables["dtEmployee"];

}

Run the application, click on the btnLoadEmployees button, the DataGridView will be populated with data from the SQL Server database, the loaded DataGridView looks as follows.

That’s it we have populated a DataGridView, with data from a SQL Server database using C# Windows forms.