Working with multiple typed datasets under same transaction

6 Nov 2007 3:33 PM

bz

Hi,

I have two typed datasets I use on the same form. Each contains some
TableAdapters and corresponding data tables. The TableAdapters
contains, beside default Fill methods (to populate the DataTable) some
queries which returns scalar or some action queries (insert / delete)
which return nothing, just work on database

What I need is to perform some actions as above (various processing on
both datasets) but under the same transaction.

Typed dataset does not expose the Connection or Transaction property,
but I enhanced the Table Adapter classes for the tableadapters I want
to work with under same transaction as below, to assign a Connection:

namespace AssurantieService.BL.DataSets.AppSecurityDSTableAdapters
{

    public partial class as_ExtAppSecurityTA :
System.ComponentModel.Component
    {
        /// <summary>
        /// Set the connection to be able to work with transactions
        /// </summary>
        public void SetConnection(IDbConnection parConn)
        {
            foreach (SqlCommand cmd in _commandCollection)
                cmd.Connection = (SqlConnection)parConn;
        }
    }
}

And I created a SessionManager class which basically creates a
connection, start transaction, I want to use the connection from this
class to assign to TA's Connection prop, and process all inside a
using statement

The SessionManager basically looks as below

        public SessionManager(string connectionString)
        {
            try
            {
                prvDataConnection = new
SqlConnection(connectionString);
                if (prvDataConnection.State != ConnectionState.Open)
                    prvDataConnection.Open();
            }
            catch (Exception ex)
            {
                ClearSession();
                ErrorHandler.LogError(ex);
                throw new SessionManagerException("ConnError"), ex);
            }
        }

        public SessionManager(string connectionString, bool
useTransaction)

: this(connectionString)

        {
            if (useTransaction)
                try
                {
                    if (prvTransaction == null)
                    {
                        prvTransaction =
prvDataConnection.BeginTransaction();
                        prvInTransaction = true;
                        prvUseTransaction = true;
                    }
                }
                catch (Exception ex)
                {
                    ClearSession();
                    ErrorHandler.LogError(ex);
                    throw new SessionManagerException("TranError"),
ex);
                }
        }

However, this is not working. When I execute some action queries or
scalar from tableadapters I get various error messages - Connection
for Command object is not set, etc

Can anyone help me? Or is there any other way to create a transaction
across different connections?

Thanks you

6 Nov 2007 4:18 PM

Jim Rand

I use data adapters instead of table adapters.

Here is some code that uses transactions - might give you some ideas
----------------------------------------------------------------------
/* Update all tables in the dataset */
internal static void Update(System.Data.DataSet ds,
                System.Collections.Generic.List<System.Data.SqlClient.SqlDataAdapter>
adapterList,
                System.Collections.Generic.Dictionary<string, string[]>
stringColumnDictionary,
                             int eventID, Guid userGuid)
{

   SqlConnection cn = adapterList[0].SelectCommand.Connection;
   try
   {
    cn.Open();

    /* Use the first dataAdapter's connection to start the transaction */
    using (SqlTransaction tran =
adapterList[0].SelectCommand.Connection.BeginTransaction(IsolationLevel.Serializable))
    {

     try
     {

      /* Enlist transaction for each adapter */
      foreach (SqlDataAdapter da in adapterList)
      {
       if (da.UpdateCommand != null) da.UpdateCommand.Transaction = tran;
       if (da.DeleteCommand != null) da.DeleteCommand.Transaction = tran;
       if (da.InsertCommand != null) da.InsertCommand.Transaction = tran;
      }

      System.Data.DataSet dsDeleted =
ds.GetChanges(System.Data.DataRowState.Deleted);
      System.Data.DataSet dsAdded =
ds.GetChanges(System.Data.DataRowState.Added);
      System.Data.DataSet dsModified =
ds.GetChanges(System.Data.DataRowState.Modified);

      /* Replace zero length strings with nulls */
      if (stringColumnDictionary != null)
      {
       if (dsAdded != null)
       {
        ReplaceWithNulls(dsAdded, DataRowState.Added,
stringColumnDictionary);
       }

       if (dsModified != null)
       {
        ReplaceWithNulls(dsModified, DataRowState.Modified,
stringColumnDictionary);
       }
      }

      UpdateOperation(dsDeleted, adapterList,
System.Data.DataRowState.Deleted);
      UpdateOperation(dsAdded, adapterList, System.Data.DataRowState.Added);
      UpdateOperation(dsModified, adapterList,
System.Data.DataRowState.Modified);

      /* Check the event in */
      string sql = "UPDATE dbo.CheckOut " +
             "SET Upload = GetUTCDate() " +
             "WHERE EventID = @EventID AND UserGUID = @UserGUID AND Upload
Is Null";
      SqlCommand cmd = new SqlCommand(sql, tran.Connection);
      cmd.Transaction = tran;
      SqlParameter param = cmd.Parameters.Add("@EventID", SqlDbType.Int);
      param.Value = eventID;
      param = cmd.Parameters.Add("@UserGUID", SqlDbType.UniqueIdentifier);
      param.Value = userGuid;
      cmd.ExecuteNonQuery();
      cmd.Dispose();

      /* Commit the transaction */
      tran.Commit();

      if (dsDeleted != null) ds.Merge(dsDeleted, false);
      if (dsAdded != null) ds.Merge(dsAdded, false);
      if (dsModified != null) ds.Merge(dsModified, false);

      ds.AcceptChanges();

     }
     catch (Exception ex)
     {
      if (tran != null) tran.Rollback();
      throw ex;
     }
    }

   }
   catch (Exception ex)
   {
    throw ex;
   }
   finally
   {
    if ((cn != null) && (cn.State == ConnectionState.Open)) cn.Close();
   }

} /* internal static void Update */

8 Nov 2007 10:56 PM

bz

Thanks, I'll study it and see if I get something that might help.

Regards,
Bogdan

Show quote

On 6 Noi, 18:18, "Jim Rand" <jimr***@ix.netcom.com> wrote:
> I use data adapters instead of table adapters.
>
> Here is some code that uses transactions - might give you some ideas
> ----------------------------------------------------------------------
>   /* Update all tables in the dataset */
>   internal static void Update(System.Data.DataSet ds,
>                 System.Collections.Generic.List<System.Data.SqlClient.SqlDataAdapter>
> adapterList,
>                 System.Collections.Generic.Dictionary<string, string[]>
> stringColumnDictionary,
>                              int eventID, Guid userGuid)
>   {
>
>    SqlConnection cn = adapterList[0].SelectCommand.Connection;
>    try
>    {
>     cn.Open();
>
>     /* Use the first dataAdapter's connection to start the transaction */
>     using (SqlTransaction tran =
> adapterList[0].SelectCommand.Connection.BeginTransaction(IsolationLevel.Serializable))
>     {
>
>      try
>      {
>
>       /* Enlist transaction for each adapter */
>       foreach (SqlDataAdapter da in adapterList)
>       {
>        if (da.UpdateCommand != null) da.UpdateCommand.Transaction = tran;
>        if (da.DeleteCommand != null) da.DeleteCommand.Transaction = tran;
>        if (da.InsertCommand != null) da.InsertCommand.Transaction = tran;
>       }
>
>       System.Data.DataSet dsDeleted =
> ds.GetChanges(System.Data.DataRowState.Deleted);
>       System.Data.DataSet dsAdded =
> ds.GetChanges(System.Data.DataRowState.Added);
>       System.Data.DataSet dsModified =
> ds.GetChanges(System.Data.DataRowState.Modified);
>
>       /* Replace zero length strings with nulls */
>       if (stringColumnDictionary != null)
>       {
>        if (dsAdded != null)
>        {
>         ReplaceWithNulls(dsAdded, DataRowState.Added,
> stringColumnDictionary);
>        }
>
>        if (dsModified != null)
>        {
>         ReplaceWithNulls(dsModified, DataRowState.Modified,
> stringColumnDictionary);
>        }
>       }
>
>       UpdateOperation(dsDeleted, adapterList,
> System.Data.DataRowState.Deleted);
>       UpdateOperation(dsAdded, adapterList, System.Data.DataRowState.Added);
>       UpdateOperation(dsModified, adapterList,
> System.Data.DataRowState.Modified);
>
>       /* Check the event in */
>       string sql = "UPDATE dbo.CheckOut " +
>              "SET Upload = GetUTCDate() " +
>              "WHERE EventID = @EventID AND UserGUID = @UserGUID AND Upload
> Is Null";
>       SqlCommand cmd = new SqlCommand(sql, tran.Connection);
>       cmd.Transaction = tran;
>       SqlParameter param = cmd.Parameters.Add("@EventID", SqlDbType.Int);
>       param.Value = eventID;
>       param = cmd.Parameters.Add("@UserGUID", SqlDbType.UniqueIdentifier);
>       param.Value = userGuid;
>       cmd.ExecuteNonQuery();
>       cmd.Dispose();
>
>       /* Commit the transaction */
>       tran.Commit();
>
>       if (dsDeleted != null) ds.Merge(dsDeleted, false);
>       if (dsAdded != null) ds.Merge(dsAdded, false);
>       if (dsModified != null) ds.Merge(dsModified, false);
>
>       ds.AcceptChanges();
>
>      }
>      catch (Exception ex)
>      {
>       if (tran != null) tran.Rollback();
>       throw ex;
>      }
>     }
>
>    }
>    catch (Exception ex)
>    {
>     throw ex;
>    }
>    finally
>    {
>     if ((cn != null) && (cn.State == ConnectionState.Open)) cn.Close();
>    }
>
>   } /* internal static void Update */