ASP.NET GridView Tutorial - ConflictDetection property of objectdatasource control – Part 16

ConflictDetection property of objectdatasource control – Part 16

Link for csharp, asp.net, ado.net, dotnet basics and sql server video tutorial playlists
http://www.youtube.com/user/kudvenkat/playlists

Link for text version of this video
http://csharp-video-tutorials.blogspot.com/2013/03/conflictdetection-property-of_6.html

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)