Typed Dataset and transactions in ADO.NET 2.0

[Guest]

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

 

[RobinS]

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.

 

[John Sitka]

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]

Right, nice example. But it's still not using a strongly
typed dataset, right? That's what the OP wants to do.
Robin S.
--------------------------