Transaction pb with CF 2.0 and Sql Mobile

S

Steve B.

Hi,

I've a problem when I use a transaction with my SQL Mobile DB from an
application build with CF 2.0 on PPC 2003.

I need to update 5 tables, and be able to cancel the whole operation if any
of theses updates fails.

If I call the Transaction.Commit() statement once, no problem.
If I call the Transaction.Commit() statement once again, I've get an
exception telling me :

"Access to the database file is not allowed. [ File name = ] "

The connection is opened at the beginning of the program and closed at the
end of the program.
Here is a part of my code (simplified for readability):

public void UpdateOrder(
DataSet docHead,
DataSet docPos,
DataSet docNotes,
DataSet docPosNotes,
DataSet docAddress
)
{

IDbDataAdapter daDocHead = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocPos = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocNotes = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocPosNotes = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocAddress = GetTheDaFromBusinessObjects();

// A global connection object which is a "IDBConnection" object
// declared and a SqlCeConnection object instancied
bool wasOpened = (GlobalConnection.State ==
ConnectionState.Open );
IDbTransaction myTransaction = null;
try
{

if ( !wasOpened )
DAL.GlobalConnection.Default.Connection.Open();
myTransaction =
GlobalConnection.BeginTransaction(IsolationLevel.ReadCommitted);

daDocHead.DeleteCommand.Transaction = myTransaction;
daDocHead.InsertCommand.Transaction = myTransaction;
daDocHead.UpdateCommand.Transaction = myTransaction;
daDocHead.Update(docHead);

daDocPos.DeleteCommand.Transaction = myTransaction;
daDocPos.InsertCommand.Transaction = myTransaction;
daDocPos.UpdateCommand.Transaction = myTransaction;
daDocPos.Update(docPos);

daDocNotes.DeleteCommand.Transaction = myTransaction;
daDocNotes.InsertCommand.Transaction = myTransaction;
daDocNotes.UpdateCommand.Transaction = myTransaction;
daDocNotes.Update(docNotes);

daDocPosNotes.DeleteCommand.Transaction = myTransaction;
daDocPosNotes.InsertCommand.Transaction = myTransaction;
daDocPosNotes.UpdateCommand.Transaction = myTransaction;
daDocPosNotes.Update(docPosNotes);

daDocAddress.DeleteCommand.Transaction = myTransaction;
daDocAddress.InsertCommand.Transaction = myTransaction;
daDocAddress.UpdateCommand.Transaction = myTransaction;
daDocAddress.Update(docAddress);
}

myTransaction.Commit(); // Throw the exception here
}
catch(Exception exc)
{
if ( myTransaction != null )
myTransaction.Rollback();
throw exc;
}
finally
{
daDocHead.DeleteCommand.Transaction = null;
daDocHead.InsertCommand.Transaction = null;
daDocHead.UpdateCommand.Transaction = null;

daDocPos.DeleteCommand.Transaction = null;
daDocPos.InsertCommand.Transaction = null;
daDocPos.UpdateCommand.Transaction = null;

daDocNotes.DeleteCommand.Transaction = null;
daDocNotes.InsertCommand.Transaction = null;
daDocNotes.UpdateCommand.Transaction = null;

daDocPosNotes.DeleteCommand.Transaction = null;
daDocPosNotes.InsertCommand.Transaction = null;
daDocPosNotes.UpdateCommand.Transaction = null;

daDocAddress.DeleteCommand.Transaction = null;
daDocAddress.InsertCommand.Transaction = null;
daDocAddress.UpdateCommand.Transaction = null;

if (!wasOpened )
if ( GlobalConnection.State != ConnectionState.Closed )
GlobalConnection.Close();
}
}

Do you see anything I've made wrong ?
Thanks,
Steve
 
D

Darren Shaffer

Steve,

Your order of calls should be:

open a new connection,
create a new transaction,
commit (or rollback),
close the connection,
repeat (starting with opening the connection)


--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com


Steve B. said:
Hi,

I've a problem when I use a transaction with my SQL Mobile DB from an
application build with CF 2.0 on PPC 2003.

I need to update 5 tables, and be able to cancel the whole operation if
any of theses updates fails.

If I call the Transaction.Commit() statement once, no problem.
If I call the Transaction.Commit() statement once again, I've get an
exception telling me :

"Access to the database file is not allowed. [ File name = ] "

The connection is opened at the beginning of the program and closed at the
end of the program.
Here is a part of my code (simplified for readability):

public void UpdateOrder(
DataSet docHead,
DataSet docPos,
DataSet docNotes,
DataSet docPosNotes,
DataSet docAddress
)
{

IDbDataAdapter daDocHead = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocPos = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocNotes = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocPosNotes = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocAddress = GetTheDaFromBusinessObjects();

// A global connection object which is a "IDBConnection" object
// declared and a SqlCeConnection object instancied
bool wasOpened = (GlobalConnection.State ==
ConnectionState.Open );
IDbTransaction myTransaction = null;
try
{

if ( !wasOpened )
DAL.GlobalConnection.Default.Connection.Open();
myTransaction =
GlobalConnection.BeginTransaction(IsolationLevel.ReadCommitted);

daDocHead.DeleteCommand.Transaction = myTransaction;
daDocHead.InsertCommand.Transaction = myTransaction;
daDocHead.UpdateCommand.Transaction = myTransaction;
daDocHead.Update(docHead);

daDocPos.DeleteCommand.Transaction = myTransaction;
daDocPos.InsertCommand.Transaction = myTransaction;
daDocPos.UpdateCommand.Transaction = myTransaction;
daDocPos.Update(docPos);

daDocNotes.DeleteCommand.Transaction = myTransaction;
daDocNotes.InsertCommand.Transaction = myTransaction;
daDocNotes.UpdateCommand.Transaction = myTransaction;
daDocNotes.Update(docNotes);

daDocPosNotes.DeleteCommand.Transaction = myTransaction;
daDocPosNotes.InsertCommand.Transaction = myTransaction;
daDocPosNotes.UpdateCommand.Transaction = myTransaction;
daDocPosNotes.Update(docPosNotes);

daDocAddress.DeleteCommand.Transaction = myTransaction;
daDocAddress.InsertCommand.Transaction = myTransaction;
daDocAddress.UpdateCommand.Transaction = myTransaction;
daDocAddress.Update(docAddress);
}

myTransaction.Commit(); // Throw the exception here
}
catch(Exception exc)
{
if ( myTransaction != null )
myTransaction.Rollback();
throw exc;
}
finally
{
daDocHead.DeleteCommand.Transaction = null;
daDocHead.InsertCommand.Transaction = null;
daDocHead.UpdateCommand.Transaction = null;

daDocPos.DeleteCommand.Transaction = null;
daDocPos.InsertCommand.Transaction = null;
daDocPos.UpdateCommand.Transaction = null;

daDocNotes.DeleteCommand.Transaction = null;
daDocNotes.InsertCommand.Transaction = null;
daDocNotes.UpdateCommand.Transaction = null;

daDocPosNotes.DeleteCommand.Transaction = null;
daDocPosNotes.InsertCommand.Transaction = null;
daDocPosNotes.UpdateCommand.Transaction = null;

daDocAddress.DeleteCommand.Transaction = null;
daDocAddress.InsertCommand.Transaction = null;
daDocAddress.UpdateCommand.Transaction = null;

if (!wasOpened )
if ( GlobalConnection.State != ConnectionState.Closed )
GlobalConnection.Close();
}
}

Do you see anything I've made wrong ?
Thanks,
Steve
Click to expand...
 
S

Steve B.

I actually solved my problem :

myTransaction.Commit();

myTransaction.Dispose();

GlobalConnection..Close();

GlobalConnection.Open();



I'm quite surprised why I have to close the connection after a commit...

Is it a bug or is it the normal way?



Thanks,

steve

Darren Shaffer said:
Steve,

Your order of calls should be:

open a new connection,
create a new transaction,
commit (or rollback),
close the connection,
repeat (starting with opening the connection)


--
Darren Shaffer
.NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com


Steve B. said:
Hi,

I've a problem when I use a transaction with my SQL Mobile DB from an
application build with CF 2.0 on PPC 2003.

I need to update 5 tables, and be able to cancel the whole operation if
any of theses updates fails.

If I call the Transaction.Commit() statement once, no problem.
If I call the Transaction.Commit() statement once again, I've get an
exception telling me :

"Access to the database file is not allowed. [ File name = ] "

The connection is opened at the beginning of the program and closed at
the end of the program.
Here is a part of my code (simplified for readability):

public void UpdateOrder(
DataSet docHead,
DataSet docPos,
DataSet docNotes,
DataSet docPosNotes,
DataSet docAddress
)
{

IDbDataAdapter daDocHead = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocPos = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocNotes = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocPosNotes = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocAddress = GetTheDaFromBusinessObjects();

// A global connection object which is a "IDBConnection"
object
// declared and a SqlCeConnection object instancied
bool wasOpened = (GlobalConnection.State ==
ConnectionState.Open );
IDbTransaction myTransaction = null;
try
{

if ( !wasOpened )
DAL.GlobalConnection.Default.Connection.Open();
myTransaction =
GlobalConnection.BeginTransaction(IsolationLevel.ReadCommitted);

daDocHead.DeleteCommand.Transaction = myTransaction;
daDocHead.InsertCommand.Transaction = myTransaction;
daDocHead.UpdateCommand.Transaction = myTransaction;
daDocHead.Update(docHead);

daDocPos.DeleteCommand.Transaction = myTransaction;
daDocPos.InsertCommand.Transaction = myTransaction;
daDocPos.UpdateCommand.Transaction = myTransaction;
daDocPos.Update(docPos);

daDocNotes.DeleteCommand.Transaction = myTransaction;
daDocNotes.InsertCommand.Transaction = myTransaction;
daDocNotes.UpdateCommand.Transaction = myTransaction;
daDocNotes.Update(docNotes);

daDocPosNotes.DeleteCommand.Transaction = myTransaction;
daDocPosNotes.InsertCommand.Transaction = myTransaction;
daDocPosNotes.UpdateCommand.Transaction = myTransaction;
daDocPosNotes.Update(docPosNotes);

daDocAddress.DeleteCommand.Transaction = myTransaction;
daDocAddress.InsertCommand.Transaction = myTransaction;
daDocAddress.UpdateCommand.Transaction = myTransaction;
daDocAddress.Update(docAddress);
}

myTransaction.Commit(); // Throw the exception here
}
catch(Exception exc)
{
if ( myTransaction != null )
myTransaction.Rollback();
throw exc;
}
finally
{
daDocHead.DeleteCommand.Transaction = null;
daDocHead.InsertCommand.Transaction = null;
daDocHead.UpdateCommand.Transaction = null;

daDocPos.DeleteCommand.Transaction = null;
daDocPos.InsertCommand.Transaction = null;
daDocPos.UpdateCommand.Transaction = null;

daDocNotes.DeleteCommand.Transaction = null;
daDocNotes.InsertCommand.Transaction = null;
daDocNotes.UpdateCommand.Transaction = null;

daDocPosNotes.DeleteCommand.Transaction = null;
daDocPosNotes.InsertCommand.Transaction = null;
daDocPosNotes.UpdateCommand.Transaction = null;

daDocAddress.DeleteCommand.Transaction = null;
daDocAddress.InsertCommand.Transaction = null;
daDocAddress.UpdateCommand.Transaction = null;

if (!wasOpened )
if ( GlobalConnection.State !=
ConnectionState.Closed )
GlobalConnection.Close();
}
}

Do you see anything I've made wrong ?
Thanks,
Steve
Click to expand...
Click to expand...
 
S

Steve B.

Hi,

I've finally found a bug in my application. This bug caused the application
to use two connections instead of one.

When the patch has been applied to the code... the second transaction commit
successfully, without needed to close and reopen the connection.

Thanks,
Steve

Darren Shaffer said:
Steve,

Your order of calls should be:

open a new connection,
create a new transaction,
commit (or rollback),
close the connection,
repeat (starting with opening the connection)


--
Darren Shaffer
.NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com


Steve B. said:
Hi,

I've a problem when I use a transaction with my SQL Mobile DB from an
application build with CF 2.0 on PPC 2003.

I need to update 5 tables, and be able to cancel the whole operation if
any of theses updates fails.

If I call the Transaction.Commit() statement once, no problem.
If I call the Transaction.Commit() statement once again, I've get an
exception telling me :

"Access to the database file is not allowed. [ File name = ] "

The connection is opened at the beginning of the program and closed at
the end of the program.
Here is a part of my code (simplified for readability):

public void UpdateOrder(
DataSet docHead,
DataSet docPos,
DataSet docNotes,
DataSet docPosNotes,
DataSet docAddress
)
{

IDbDataAdapter daDocHead = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocPos = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocNotes = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocPosNotes = GetTheDaFromBusinessObjects();
IDbDataAdapter daDocAddress = GetTheDaFromBusinessObjects();

// A global connection object which is a "IDBConnection"
object
// declared and a SqlCeConnection object instancied
bool wasOpened = (GlobalConnection.State ==
ConnectionState.Open );
IDbTransaction myTransaction = null;
try
{

if ( !wasOpened )
DAL.GlobalConnection.Default.Connection.Open();
myTransaction =
GlobalConnection.BeginTransaction(IsolationLevel.ReadCommitted);

daDocHead.DeleteCommand.Transaction = myTransaction;
daDocHead.InsertCommand.Transaction = myTransaction;
daDocHead.UpdateCommand.Transaction = myTransaction;
daDocHead.Update(docHead);

daDocPos.DeleteCommand.Transaction = myTransaction;
daDocPos.InsertCommand.Transaction = myTransaction;
daDocPos.UpdateCommand.Transaction = myTransaction;
daDocPos.Update(docPos);

daDocNotes.DeleteCommand.Transaction = myTransaction;
daDocNotes.InsertCommand.Transaction = myTransaction;
daDocNotes.UpdateCommand.Transaction = myTransaction;
daDocNotes.Update(docNotes);

daDocPosNotes.DeleteCommand.Transaction = myTransaction;
daDocPosNotes.InsertCommand.Transaction = myTransaction;
daDocPosNotes.UpdateCommand.Transaction = myTransaction;
daDocPosNotes.Update(docPosNotes);

daDocAddress.DeleteCommand.Transaction = myTransaction;
daDocAddress.InsertCommand.Transaction = myTransaction;
daDocAddress.UpdateCommand.Transaction = myTransaction;
daDocAddress.Update(docAddress);
}

myTransaction.Commit(); // Throw the exception here
}
catch(Exception exc)
{
if ( myTransaction != null )
myTransaction.Rollback();
throw exc;
}
finally
{
daDocHead.DeleteCommand.Transaction = null;
daDocHead.InsertCommand.Transaction = null;
daDocHead.UpdateCommand.Transaction = null;

daDocPos.DeleteCommand.Transaction = null;
daDocPos.InsertCommand.Transaction = null;
daDocPos.UpdateCommand.Transaction = null;

daDocNotes.DeleteCommand.Transaction = null;
daDocNotes.InsertCommand.Transaction = null;
daDocNotes.UpdateCommand.Transaction = null;

daDocPosNotes.DeleteCommand.Transaction = null;
daDocPosNotes.InsertCommand.Transaction = null;
daDocPosNotes.UpdateCommand.Transaction = null;

daDocAddress.DeleteCommand.Transaction = null;
daDocAddress.InsertCommand.Transaction = null;
daDocAddress.UpdateCommand.Transaction = null;

if (!wasOpened )
if ( GlobalConnection.State !=
ConnectionState.Closed )
GlobalConnection.Close();
}
}

Do you see anything I've made wrong ?
Thanks,
Steve
Click to expand...
Click to expand...
 
Top