dotConnect for Oracle Can lock table or record?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
ying515_huang
Posts: 13
Joined: Mon 21 Feb 2011 03:35

dotConnect for Oracle Can lock table or record?

Post by ying515_huang » Wed 13 Apr 2011 08:21

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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 13 Apr 2011 08:49

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;
}

ying515_huang
Posts: 13
Joined: Mon 21 Feb 2011 03:35

But we use Devart LinqConnect Model!

Post by ying515_huang » 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?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » 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.

Post Reply