Devart Forums

Posted: **Wed 13 Apr 2011 08:21**

dotConnect for Oracle Can lock table or record?
How to do?

Posted: **Wed 13 Apr 2011 08:49**

Please use the FOR UPDATE statement like:

try
{
//Lock DataSet
cmd = new OracleCommand("SELECT * FROM myTable WHERE id = :id FOR UPDATE NOWAIT", con);
cmd.Parameters.AddWithValue("id", id);

da.SelectCommand = cmd;
da.Fill(myDt);
//Do sth. with myDt
}

catch(OracleException ex)
{
if(ex.Code == 54)
errorMessage = "Data is in use. Please try again later.";
else
errorMessage = ex.Message;
}

Posted: **Wed 13 Apr 2011 10:11**

Shalex wrote:Please use the FOR UPDATE statement like:

Code: Select all

try
{
//Lock DataSet
cmd = new OracleCommand("SELECT * FROM myTable WHERE id = :id FOR UPDATE NOWAIT", con);
cmd.Parameters.AddWithValue("id", id);

da.SelectCommand = cmd;
da.Fill(myDt);
//Do sth. with myDt
}

catch(OracleException ex)
{
if(ex.Code == 54)
errorMessage = "Data is in use. Please try again later.";
else
errorMessage = ex.Message;
}

But we use Devart LinqConnect Model!
We use Entity Developer Model Wizard?
How to do?

Posted: **Wed 13 Apr 2011 14:17**

You can explicitly lock a row with the DataContext.ExecuteQuery method, e.g.,

Code: Select all

IEnumerable dept = context.ExecuteQuery
("select * from dept where deptno = 10 for update").ToList();

Besides this, you can wrap a part of your code into TransactionScope:

Code: Select all

using (TransactionScope ts = new TransactionScope()) {

  MyDataContext context = new MyDataContext();
  Dept dept = context.Depts.First();
  ... // modify dept

  context.SubmitChanges();
  ts.Complete();
}

In this case, the rows being updated will be locked until the TransactionScope object is disposed.

Please tell us if this helps.