假设有A，B两个人同时取到如下表中的一行元组值:
id 书名(varchar(max)) 是否过期(bit)
7 《c#开发》 1
可以看到当AB两人取到的这个记录是id:7,书名:《c#开发》,是否过期:1(1表示过期了，0表示没过期)
现在A觉得这本书过期了要将这条记录删除掉，B因为发现这本书应该是没有过期的想立即将其是否过期设置为0
由于B手脚快些所以先执行了update，现在数据库中这条记录应该是：
id 书名(varchar(max)) 是否过期(bit)
7 《c#开发》 0
但是A看到的还是
id 书名(varchar(max)) 是否过期(bit)
7 《c#开发》 1
所以A就执行了delete命令将这条记录删除了
那么事实上A就将一本不该删除的书删除了
犯下这种错误既不能怪A，也不能怪B，是因为A看到的并不是最新的信息，设想如果在A进行delete操作之前，将这条记录从数据库检索出来，在和A刚开始看到这条记录时候的值进行对比，如果值发生了变化有取消删除操作，没有改变再进行执行delete，就不会发生这种悲剧
这就要求对程序进行并发控制，而ObjectDataSource为我们提供了并发控制机制:
ObjectDataSource 控件有一个属性ConflictDetection , 叫做冲突检测. 可以设置再应用程序中如何处理并发冲突.
ConflictDetection 有两个可选值:
默认设置为OverwriteChanges 这是最小化的并发冲突设置,在更新和删除的时候不会提供原始数据信息，无法进行并发控制.
CompareAllValues 这将给予你最大的灵活性,它将在更新和删除的时候提供原始的数据信息，所以可以进行并发控制
请看如下示例:
用于ObjectDataSource的实体对象类:
public class Student
{
private int _id;
public string _name;
private int _age;
private bool _sex;
public int Id
{
set
{
_id = value;
}
get
{
return _id;
}
}
public string Name
{
set
{
_name = value;
}
get
{
return _name;
}
}
public int Age
{
set
{
_age = value;
}
get
{
return _age;
}
}
public bool Sex
{
set
{
_sex = value;
}
get
{
return _sex;
}
}
public Student()
{
}
}
{
private int _id;
public string _name;
private int _age;
private bool _sex;
public int Id
{
set
{
_id = value;
}
get
{
return _id;
}
}
public string Name
{
set
{
_name = value;
}
get
{
return _name;
}
}
public int Age
{
set
{
_age = value;
}
get
{
return _age;
}
}
public bool Sex
{
set
{
_sex = value;
}
get
{
return _sex;
}
}
public Student()
{
}
}
ObjectDataSource和GridView的设置:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" AllowPaging="True" PageSize="5" AllowSorting="True" DataKeyNames="Id">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" ReadOnly="true" />
<asp:CheckBoxField DataField="Sex" HeaderText="Sex" SortExpression="Sex" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteStudent"
SelectMethod="GetPageStudent" TypeName="StudentDAL" UpdateMethod="UpdateStudent" OldValuesParameterFormatString="old_{0}" EnablePaging="True" MaximumRowsParameterName="size" SelectCountMethod="GetAllCount" StartRowIndexParameterName="start" SortParameterName="order" InsertMethod="InsertStudent" DataObjectTypeName="Student" >
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Age" Type="Int32" />
<asp:Parameter Name="Sex" Type="Boolean" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="s" Type="Object" />
<asp:Parameter Name="old_s" Type="Object" />
</UpdateParameters>
</asp:ObjectDataSource>
<asp:Button ID="btnAdd" runat="server" OnClick="btnAdd_Click" Text="Add" />
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" ReadOnly="true" />
<asp:CheckBoxField DataField="Sex" HeaderText="Sex" SortExpression="Sex" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteStudent"
SelectMethod="GetPageStudent" TypeName="StudentDAL" UpdateMethod="UpdateStudent" OldValuesParameterFormatString="old_{0}" EnablePaging="True" MaximumRowsParameterName="size" SelectCountMethod="GetAllCount" StartRowIndexParameterName="start" SortParameterName="order" InsertMethod="InsertStudent" DataObjectTypeName="Student" >
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Age" Type="Int32" />
<asp:Parameter Name="Sex" Type="Boolean" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="s" Type="Object" />
<asp:Parameter Name="old_s" Type="Object" />
</UpdateParameters>
</asp:ObjectDataSource>
<asp:Button ID="btnAdd" runat="server" OnClick="btnAdd_Click" Text="Add" />
然后是最关键的!后台供ObjectDataSource调用的DAL层次代码:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// StudentDAL 的摘要说明
/// </summary>
public class StudentDAL
{
string conn = "Data Source=localhost;Initial Catalog=qy;Integrated Security=True";
SqlConnection oConn;
SqlCommand oCommand;
public StudentDAL()
{
oConn = new SqlConnection(conn);
oCommand = new SqlCommand();
}
public int GetAllCount()
{
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "select count(*) as T from Test_Student";
int count=Convert.ToInt32(oCommand.ExecuteScalar());
oConn.Close();
return 50;
}
public List<Student> GetPageStudent(int start,int size,string order)
{
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "select * from Test_Student";
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(oCommand);
sda.Fill(dt);
oConn.Close();
List<Student> students = new List<Student>();
int i;
for (i = 0; i < dt.Rows.Count; i++)
{
Student s = new Student();
s.Id = Convert.ToInt32(dt.Rows[i]["ID"]);
s.Name = dt.Rows[i]["Name"].ToString();
s.Age = Convert.ToInt32(dt.Rows[i]["Age"]);
s.Sex = Convert.ToBoolean(dt.Rows[i]["Sex"]);
students.Add(s);
}
return students;
}
public void InsertStudent(Student s)
{
oConn.Open();
oCommand.Connection = oConn;
string sql = "insert into Test_Student(Name,Age,Sex) values('" + s.Name + "'," + s.Age.ToString() + "," + Convert.ToInt32(s.Sex) + ")";
oCommand.CommandText = sql;
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void InsertStudent(string Name,int Age,bool Sex)
{
oConn.Open();
oCommand.Connection = oConn;
string sql = "insert into Test_Student(Name,Age,Sex) values('" + Name + "'," + Age.ToString() + "," + Convert.ToInt32(Sex) + ")";
oCommand.CommandText = sql;
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void UpdateStudent(Student s)//在DataObjectType模式下,ConflictDetection= Overwrite Changes的时候,只需要传入一个Student，来进行更新
{
oConn.Open();
oCommand.Connection = oConn;
string sql = "update Test_Student set Name='" + s.Name + "',Age=" + s.Age.ToString() + ",Sex=" + Convert.ToInt32(s.Sex) + " where id=" + s.Id.ToString();
oCommand.CommandText = sql;
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void UpdateStudent(Student s, Student old_s)//在DataObjectType模式下,ConflictDetection=CompareAllValues需要两个Student,一个叫s里边记录了更新的字段信息,另外个叫old_s(这个对象名是根据ObjectDataSource的OldValuesParameterFormatString设置的格式old_{0})记录了原始的字段信息,然后比较两个Student对象进行冲突检测
{
//冲突检测逻辑.....略
int Age = 0;
oConn.Open();
oCommand.Connection = oConn;
string sql = "update Test_Student set Name='" + s.Name + "',Age=" + Age.ToString() + ",Sex=" + Convert.ToInt32(s.Sex) + " where id=" + s.Id.ToString();
oCommand.CommandText = sql;
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void UpdateStudent(int old_Id, string Name, bool Sex, int Id, int Age)//ObjectDataSource在ConflictDetection= Overwrite Changes的时候要求只把现在更新的字段,以及原来的主键字段old_{DataKeyName}(这个名字是根据ObjectDataSource的OldValuesParameterFormatString设置的格式old_{0})传进来,以便索引到更新行
{
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "update Test_Student set Name='" + Name + "',Age=" + Age.ToString() + ",Sex=" + Convert.ToInt32(Sex).ToString() + " where id=" + old_Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void UpdateStudent(int old_Id, string Name, bool Sex, int Id, int Age, string old_Name, bool old_Sex, int old_Age)//ObjectDataSource在ConflictDetection= CompareAllValues的时候要求把现在更新的字段,以及原来的字段old_{0}(这个名字是根据ObjectDataSource的OldValuesParameterFormatString设置的格式old_{0})都传进来,以便进行冲突检测,然后再更新
{
//冲突检测逻辑.....略
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "update Test_Student set Name='" + Name + "',Age=" + Age.ToString() + ",Sex=" + Convert.ToInt32(Sex).ToString() + " where id=" + old_Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void DeleteStudent(Student s)//在DataObjectType模式下,ConflictDetection= Overwrite Changes/CompareAllValues只需要一个Student就可以了,因为传过来的记录本来就含有删除字段的所有属性,可以进行冲突检测
{
//冲突检测逻辑.....略
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "delete from Test_Student where id=" + s.Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void DeleteStudent(int old_Id)//ObjectDataSource在ConflictDetection= Overwrite Changes的时候只会把old_{DataKeyName}的字段传进来,所以GridView如果没有设置DataKeyName，那么参数就不会传进来，也无法执行删除的sql语句
{
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "delete from Test_Student where id=" + old_Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void DeleteStudent(int old_Id, string old_Name, bool old_Sex, int old_Age)//ObjectDataSource在ConflictDetection= CompareAllValues的时候要求把现在更新的字段,以及原来的字段old_{0}都传进来,以便进行冲突检测
{
//冲突检测逻辑.....略
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "delete from Test_Student where id=" + old_Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
}
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// StudentDAL 的摘要说明
/// </summary>
public class StudentDAL
{
string conn = "Data Source=localhost;Initial Catalog=qy;Integrated Security=True";
SqlConnection oConn;
SqlCommand oCommand;
public StudentDAL()
{
oConn = new SqlConnection(conn);
oCommand = new SqlCommand();
}
public int GetAllCount()
{
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "select count(*) as T from Test_Student";
int count=Convert.ToInt32(oCommand.ExecuteScalar());
oConn.Close();
return 50;
}
public List<Student> GetPageStudent(int start,int size,string order)
{
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "select * from Test_Student";
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(oCommand);
sda.Fill(dt);
oConn.Close();
List<Student> students = new List<Student>();
int i;
for (i = 0; i < dt.Rows.Count; i++)
{
Student s = new Student();
s.Id = Convert.ToInt32(dt.Rows[i]["ID"]);
s.Name = dt.Rows[i]["Name"].ToString();
s.Age = Convert.ToInt32(dt.Rows[i]["Age"]);
s.Sex = Convert.ToBoolean(dt.Rows[i]["Sex"]);
students.Add(s);
}
return students;
}
public void InsertStudent(Student s)
{
oConn.Open();
oCommand.Connection = oConn;
string sql = "insert into Test_Student(Name,Age,Sex) values('" + s.Name + "'," + s.Age.ToString() + "," + Convert.ToInt32(s.Sex) + ")";
oCommand.CommandText = sql;
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void InsertStudent(string Name,int Age,bool Sex)
{
oConn.Open();
oCommand.Connection = oConn;
string sql = "insert into Test_Student(Name,Age,Sex) values('" + Name + "'," + Age.ToString() + "," + Convert.ToInt32(Sex) + ")";
oCommand.CommandText = sql;
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void UpdateStudent(Student s)//在DataObjectType模式下,ConflictDetection= Overwrite Changes的时候,只需要传入一个Student，来进行更新
{
oConn.Open();
oCommand.Connection = oConn;
string sql = "update Test_Student set Name='" + s.Name + "',Age=" + s.Age.ToString() + ",Sex=" + Convert.ToInt32(s.Sex) + " where id=" + s.Id.ToString();
oCommand.CommandText = sql;
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void UpdateStudent(Student s, Student old_s)//在DataObjectType模式下,ConflictDetection=CompareAllValues需要两个Student,一个叫s里边记录了更新的字段信息,另外个叫old_s(这个对象名是根据ObjectDataSource的OldValuesParameterFormatString设置的格式old_{0})记录了原始的字段信息,然后比较两个Student对象进行冲突检测
{
//冲突检测逻辑.....略
int Age = 0;
oConn.Open();
oCommand.Connection = oConn;
string sql = "update Test_Student set Name='" + s.Name + "',Age=" + Age.ToString() + ",Sex=" + Convert.ToInt32(s.Sex) + " where id=" + s.Id.ToString();
oCommand.CommandText = sql;
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void UpdateStudent(int old_Id, string Name, bool Sex, int Id, int Age)//ObjectDataSource在ConflictDetection= Overwrite Changes的时候要求只把现在更新的字段,以及原来的主键字段old_{DataKeyName}(这个名字是根据ObjectDataSource的OldValuesParameterFormatString设置的格式old_{0})传进来,以便索引到更新行
{
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "update Test_Student set Name='" + Name + "',Age=" + Age.ToString() + ",Sex=" + Convert.ToInt32(Sex).ToString() + " where id=" + old_Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void UpdateStudent(int old_Id, string Name, bool Sex, int Id, int Age, string old_Name, bool old_Sex, int old_Age)//ObjectDataSource在ConflictDetection= CompareAllValues的时候要求把现在更新的字段,以及原来的字段old_{0}(这个名字是根据ObjectDataSource的OldValuesParameterFormatString设置的格式old_{0})都传进来,以便进行冲突检测,然后再更新
{
//冲突检测逻辑.....略
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "update Test_Student set Name='" + Name + "',Age=" + Age.ToString() + ",Sex=" + Convert.ToInt32(Sex).ToString() + " where id=" + old_Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void DeleteStudent(Student s)//在DataObjectType模式下,ConflictDetection= Overwrite Changes/CompareAllValues只需要一个Student就可以了,因为传过来的记录本来就含有删除字段的所有属性,可以进行冲突检测
{
//冲突检测逻辑.....略
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "delete from Test_Student where id=" + s.Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void DeleteStudent(int old_Id)//ObjectDataSource在ConflictDetection= Overwrite Changes的时候只会把old_{DataKeyName}的字段传进来,所以GridView如果没有设置DataKeyName，那么参数就不会传进来，也无法执行删除的sql语句
{
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "delete from Test_Student where id=" + old_Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
public void DeleteStudent(int old_Id, string old_Name, bool old_Sex, int old_Age)//ObjectDataSource在ConflictDetection= CompareAllValues的时候要求把现在更新的字段,以及原来的字段old_{0}都传进来,以便进行冲突检测
{
//冲突检测逻辑.....略
oConn.Open();
oCommand.Connection = oConn;
oCommand.CommandText = "delete from Test_Student where id=" + old_Id.ToString();
oCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
}