Optimistic Concurrency Issues
11/9/2006 1:17:26 PM
Optimistic concurrency is not fun at all. We are currently working on a project that has 2 mothers. By 2 mothers I mean there are 2 people CRUD`ing data at any given time. Imagine one person executing a series of stored procedures that result in setting the PrintedFlag to 1 and someone coming behind you executing a set of stored procedures that result in setting the PrintedFlag to 0. ddd
What was happening was I was executing a series of stored procedures and someone else was coming in behind me and executing the same stored procedures before I was finished. The simple solution was to use a StringBuilder and create SQL Transaction statements.
private
void UpdateDatabase(XmlTextReader reader)
{
// loops through an Xml file and builds a string
StringBuilder sql = new StringBuilder("BEGIN TRANSACTION\r\n");
while(reader.Read())
{
switch(reader.LocalName)
{
case"localName1":
sql.Append("exec StoredProcedure1 ");
sql.Append("@p1=" + Convert.ToInt32(attributes["one"]) + ",");
sql.Append("@p2=" + Convert.ToInt32(attributes["two"]) + ",");
sql.Append("@p3=" + Convert.ToInt32(attributes["three"]) + ",");
sql.Append("@p4=" + Convert.ToInt32(attributes["four"]));
sql.Append("\r\n");
break;
case"localName2":
sql.Append("exec StoredProcedure2 ");
sql.Append("@p1=" + Convert.ToInt32(attributes["one"]) + ",");
sql.Append("@p2=" + Convert.ToInt32(attributes["two"]) + ",");
sql.Append("@p3=" + Convert.ToInt32(attributes["three"]) + ",");
sql.Append("\r\n");
break;
}
}
sql.Append("COMMIT\r\n");
// this.cn is the application connection string
using ( OleDbConnection cn = new OleDbConnection(this.cn) )
{
using ( OleDbCommand cm = new OleDbCommand(sql.ToString(), cn) )
{
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
}
}
}
C#,
Code,
Programming,
SQL
