I have been working on a software which uses database which is shared with multiple PCs. The project is all about to store missing baggage information. We have given a facility to copy the newly inserted record into the master DB. Now what here happens is when multiple users are trying to update the db at the same time single item get stored for multiple time in the global DB.
So I have tried to use TableLock using serializable but I got nothing here.
Edit
query = "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION;";
insertdata(query); //using cmd.ExecuteNonQuery();
query = "select * from Goods WITH (TABLOCKX)" ;
DataSet dsGoods = getdata(query, "config");//Function to get the data
updateitem();
query = "COMMIT TRANSACTION";
insertdata(query);//using cmd.ExecuteNonQuery();
And the updateitem() is as follow
public static void updateitem()
{
string query = "select * from config where param='lastsync'";
DataSet ds = dataaccess.getdata(query, "config");
query = "select isonlive,associateid,itemid,founddate,regdate,status,foundbyname,categoryid,subcatid,item,model,color,foundwhere,returnedtoname,showonline,officeid,isdeleted,(select username from [user] where userid=registeredby) as reguser,(select username from [user] where userid=returnby) as returnedby,notes,returneddate from item ";
String updatedDate =ds.Tables[0].Rows[0]["value"].ToString();
if (updatedDate != "")
{
query = "select isonlive,associateid,itemid,founddate,regdate,foundbyname,status,categoryid,subcatid,item,model,color,foundwhere,returnedtoname,officeid,showonline,isdeleted,(select username from [user] where userid=registeredby) as reguser,(select username from [user] where userid=returnby) as returnedby,notes,returneddate from item where updateat >= @updateat";
}
System.Data.SqlClient.SqlCommand cmd = new SqlCommand(query);
if (updatedDate != "")
{
cmd.Parameters.AddWithValue("@updateat",DateTime.ParseExact(updatedDate,"dd-MM-yyyy HH:mm:ss",null,System.Globalization.DateTimeStyles.None));
}
DataRow dr;
ds = dataaccess.getdata(cmd, "item");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
try
{
int status = 0;
dr = ds.Tables[0].Rows[i];
if (dr["status"].ToString() == "Transferred")
{
status = 2;
}
else if (dr["status"].ToString() != "Received")
{
status = 1;
}
DateTime regdate = Convert.ToDateTime(dr["regdate"]);
DateTime founddate = Convert.ToDateTime(dr["founddate"]);
//returndatetime = String.Format("MMM dd yyyy H:mm:ss", returndate);
if (dr["showonline"].ToString() == "False")
{
status = 1;
}
if (dr["isdeleted"].ToString() == "true")
{
insertdata("delete from goods where AssociateID='" + dr["associateid"] + "' and ID='" + dr["itemid"] + "'");
continue;
}
if (dr["isonlive"].ToString() == "true")
{
query = "update goods set Status='" + status + "',officeid='" + dr["officeID"] + "', notes='" + dr["notes"].ToString().Replace("'", "''") + "',ReturnedTo='" + dr["returnedtoname"].ToString().Replace("'", "''") + "',founddate=@founddate,ReturnedDate=@returndate,ReturnedBy='" + dr["returnedby"].ToString().Replace("'", "''") +
"',Model='" + dr["model"].ToString().Replace("'", "''") + "',ColorID='" + dr["color"].ToString().Replace("'", "''") + "',FoundWhere='" + dr["foundwhere"].ToString().Replace("'", "''") + "',MainCat='" + dr["categoryid"] + "',SubCat='" + dr["subcatid"] + "',ItemID='" + dr["item"] + "' where AssociateID='" + dr["associateid"] + "' and ID='" + dr["itemid"] + "'";
}
else
{
query = "select * from goods where AssociateID='" + dr["associateid"] + "' and ID='" + dr["itemid"] + "' and MainCat='" + dr["categoryid"] + "' and SubCat='" + dr["subcatid"] + "' and ItemID='" + dr["item"] + "'";
DataSet dsItems = getdata(query, "config");
if(dsItems.Tables[0].Rows.Count==0)
{
query = "insert into goods (AssociateID,ID,DateReg,Status,MainCat,SubCat,ItemID,Model,ColorID,FoundWhere,RegBy,FoundBy,ReturnedTo,ReturnedDate,ReturnedBy,Notes,IP,founddate,officeid) values('" + dr["associateid"] + "','" + dr["itemid"] + "',@regdate,'" + status + "'," +
"'" + dr["categoryid"] + "','" + dr["subcatid"] + "','" + dr["item"] + "','" + dr["model"].ToString().Replace("'", "''") + "','" + dr["color"].ToString().Replace("'", "''") + "'," +
"'" + dr["foundwhere"].ToString().Replace("'", "''") + "','" + dr["reguser"].ToString().Replace("'", "''") + "','" + dr["reguser"].ToString().Replace("'", "''") + "','" + dr["returnedtoname"].ToString().Replace("'", "''") + "',@returndate," +
"'" + dr["returnedby"].ToString().Replace("'", "''") + "','" + dr["notes"].ToString().Replace("'", "''") + "','',@founddate,'" + dr["officeID"].ToString() + "')";
}
}
SqlCommand sce = new SqlCommand(query);
if (dr["returneddate"].ToString() != "")
{
sce.Parameters.AddWithValue("@returndate", Convert.ToDateTime(dr["returneddate"]));
}
else
{
sce.Parameters.Add("@returndate", SqlDbType.DateTime).Value = DBNull.Value;
}
sce.Parameters.AddWithValue("@regdate", regdate);
sce.Parameters.AddWithValue("@founddate", founddate);
insertdata(sce);
query = "update item set isonlive = 'true',updateat=@updateDate where itemid = '" + dr["itemid"] + "'";
sce = new SqlCommand(query);
sce.Parameters.AddWithValue("@updateDate", DateTime.Now);
dataaccess.insertdata(sce);
}
catch (Exception ex)
{
App.writelog(ex.Message + "\n" + ex.StackTrace);
}
}
}
P.S.: I want it to be done though ASP.Net.
