Re: Typed Dataset and transactions in ADO.NET 2.0

Maybe this style...just across a couple of commands

try
{
SqlTransaction trans;
cmd_One.Connection = cmd_Two.Connection;
cmd_Two.Connection.Open();
trans = cmd_Two.Connection.BeginTransaction();
cmd_Two.Transaction = trans;
cmd_One.Transaction = trans;
try
{ int Execvar = 0;
object o = cmd_Two.ExecuteScalar();
Execvar = o == null ? 0 : Convert.ToInt32( o );
cmd_One.ExecuteNonQuery();
trans.Commit();
}
catch (SqlException sqlError)
{
trans.Rollback();
// Debug.WriteLine(sqlError.ToString());
bool bReturnLog = true;
bReturnLog = ErrorLog.ErrorRoutine(false,sqlError);
if (false == bReturnLog)
MessageBox.Show("Unable to write a log");
}

}
catch(Exception ex)
{
Debug.WriteLine(ex.ToString());
bool bReturnLog = true;
bReturnLog = ErrorLog.ErrorRoutine(false,ex);
if (false == bReturnLog)
MessageBox.Show("Unable to write a log");
}
finally
{
if (cmd_One.Connection.State.ToString() == "Open")
{
cmd_One.Connection.Close();
}
if (cmd_Two.Connection.State.ToString() == "Open")
{
cmd_Two.Connection.Close();
}
}


"RobinS" <RobinS@xxxxxxxxxxxxxxx> wrote in message news:ROmdnTvlotJwCT_YnZ2dnUVZ_revnZ2d@xxxxxxxxxxxxxx
You can't use transactions with a table adapter. If you want to do that,
you'll need to use a data adapter.

Dim dt as New DataTable()
Dim cn as New SqlConnection(ss)
Dim da As New SqlDataAdapter(ss, cn)
da.SelectCommand.Parameters.AddWithValue("@OrderID", 1050")
'define updating logic for sqldataadapter here (set up InsertCommand, DeleteCommand, UpdateCommand)
cn.Open()
da.Fill(tbl)
'modify the contents
Using txn as SqlTransaction = cn.BeginTransaction()
'set the transaction property of the da's commands
da.UpdateCommand.Transaction = txn
da.SelectCommand.Transaction = txn
da.DeleteCommand.Transaction = txn

'submit the changes
da.Update(dt)
'commit the changes
txn.Commit()
End Using
cn.Close()

Robin S.
---------------------------------
"STeW" <STeW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:0EBBF267-80CC-4343-AA02-458B7F148FF8@xxxxxxxxxxxxxxxx
Hi all,

I have a general question about typed datased and transaction. Do you
anybody know the best way how to manage transaction typed dataset? Generaly,
when I create dataset and INSERT/UPDATE/DELETE command in a table adapter,
there are connections on commands which are not in transaction.
I need to have it in transaction. I can do it programaticly. I'm asking if
anybody knows about any property which enables code generation for
transaction support in typed dataset.

Thanks

Stepan




.

Relevant Pages

  • Re: Typed Dataset and transactions in ADO.NET 2.0
    ... SqlTransaction trans; ... bReturnLog = ErrorLog.ErrorRoutine; ... I have a general question about typed datased and transaction. ... anybody know the best way how to manage transaction typed dataset? ...
    (microsoft.public.dotnet.framework.adonet)
  • "ExecuteReader request the command to have a transaction..." error occurring when the reader plainly
    ... I'm using the TableAdapterHelper to set the connection and transaction ... properties on all the commands of all my typed table adapters.... ... using (SqlTransaction trans = ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: "ExecuteReader request the command to have a transaction..." error occurring when the reader pla
    ... properties on all the commands of all my typed table adapters.... ... Transaction property set. ... foreach (DocumentDataSet.RequestRow requestRow in documentDS.Request) ... using (SqlTransaction trans = ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Backups and Transaction Log file size
    ... It sounds like a classic case of a long running open transaction. ... Find the client and either commit or roll it back. ... If you know it is a garbage connection you can kill the SPID and it will roll back any changes that the SPID may have open and allow you to backup and truncate properly. ... Once the committed trans have been ...
    (microsoft.public.sqlserver.setup)
  • Re: Transaction.Commit() and Transaction.Rollback()
    ... They are used to ensure that an entire group of commands succeeds or else none of them succeed. ... This is done through the use of a transaction log. ... The Begin Transaction, Commit Transaction, End Transaction & Rollback Transaction commands are ways to specify which groups of queries to treat as Atomic. ... Commit & End Transaction tells the database that your are finished with that atomic operation and that all the commands you had sent since the trasaction began should now get committed to the actual database tables. ...
    (microsoft.public.dotnet.languages.csharp)