Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

ConflictDetection property of objectdatasource control - Part 16

Suggested Videos 
Part 13 - Deleting data from gridview using sqldatasource control
Part 14 - ConflictDetection property of SqlDataSource control
Part 15 - Deleting data from gridview using objectdatasource control



In Part 15 of the asp.net gridview tutorial we discussed about deleting data from gridview control using objectdatasource control. We did not discuss about concurrency in Part 15. Let me explain what I mean.
1. When you access the webform, data is loaded into gridview control.
2. While you are looking at this data in the gridview control, someone else has updated a row in the database table
3. Now, in the gridview, when you click the delete button on the row that has changed, the row gets deleted.



This may be fine in most cases. However, let's say we don't want to allow the row to be deleted, in case if it has changed, then, we can make use of ConflictDetection property of the objectdatasource control.

We will be modifying the "DeleteEmployee()" method in EmployeeDataAccessLayer.cs file. We discussed about this in Part 15. Please change the implementation of DeleteEmployee() method as shown below. Notice that the ORIGINAL EmployeeId, Name, Gender and City are now passed as parameters to the DeleteEmployee() method. These parameters are then used in the "DELETE" query, to check if the data has changed after it was loaded into the gridview control.
public static void DeleteEmployee(int original_EmployeeId, string original_Name, 
    string original_Gender, string original_City)
{
    string CS = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        string deleteQuery = "DELETE FROM tblEmployee WHERE EmployeeId = @original_EmployeeId " + "AND Name = @original_Name AND Gender = @original_Gender AND City = @original_City";
        SqlCommand cmd = new SqlCommand(deleteQuery, con);
        SqlParameter paramEmployeeId = new SqlParameter("@original_EmployeeId", original_EmployeeId);
        cmd.Parameters.Add(paramEmployeeId);
        SqlParameter paramName = new SqlParameter("@original_Name", original_Name);
        cmd.Parameters.Add(paramName);
        SqlParameter paramGender = new SqlParameter("@original_Gender", original_Gender);
        cmd.Parameters.Add(paramGender);
        SqlParameter paramCity = new SqlParameter("@original_City", original_City);
        cmd.Parameters.Add(paramCity);
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Compile the project and re-configure ObjectDataSource1 control, to use the above method as it's DELETE method.

Finally on "ObjectDataSource1" control, set properties
ConflictDetection="CompareAllValues"
OldValuesParameterFormatString="original_{0}"

Setting ConflictDetection="CompareAllValues", will pass original values for EmployeeId, Name, Gender and City to DeleteEmployee() method. 

Notice the parameters of the DeleteEmployee() method. All of them have a prefix of "original_". ObjectDataSource control uses "OldValuesParameterFormatString" property to figure out the exact name of the parameters for the original values. This is the reason we have set OldValuesParameterFormatString="original_{0}"
public static void DeleteEmployee(int original_EmployeeId, string original_Name, string original_Gender, string original_City)

At this point, the declarative HTML markup of gridview and objectdatasource control, should be as shown below.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" />
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllEmployees"
    TypeName="Demo.EmployeeDataAccessLayer" DeleteMethod="DeleteEmployee" 
    OldValuesParameterFormatString="original_{0}"
    ConflictDetection="CompareAllValues">
    <DeleteParameters>
        <asp:Parameter Name="original_EmployeeId" Type="Int32" />
        <asp:Parameter Name="original_Name" Type="String" />
        <asp:Parameter Name="original_Gender" Type="String" />
        <asp:Parameter Name="original_City" Type="String" />
    </DeleteParameters>
</asp:ObjectDataSource>

2 comments:

  1. protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {

    if (e.AffectedRows > 0)
    {
    lblMessage.Text = "The Employee ID = \"" + e.Keys[0].ToString() + "\" is deleted ";
    lblMessage.ForeColor = System.Drawing.Color.Red;
    }
    else
    {
    lblMessage.Text = "The Employee ID = \"" + e.Keys[0].ToString() + "\" is not deleted due to Conflict Detection ";
    lblMessage.ForeColor = System.Drawing.Color.Navy;


    }

    my else if block not working correctly, all time its went through the else block even its delete the row.

    Thank

    ReplyDelete
  2. got the answer after watching video 20.

    ReplyDelete

It would be great if you can help share these free resources