假设有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);
}
}