Subsonic 3 Simple Repository And Transactions

Question

So this is what I have so far. Am I doing something wrong or is there a bug in 3.0.0.3?

    var Repository = new SimpleRepository("DBConnectionName");

    using (TransactionScope ts = new TransactionScope())
    {
        using (SharedDbConnectionScope scs = new SharedDbConnectionScope("connstring", "providerName"))
        {
            try
            {
                for (int i = 0; i < 5; i++)
                {
                    Supplier s = new Supplier();
                    s.SupplierCode = i.ToString();
                    s.SupplierName = i.ToString();

                    Repository.Add<Supplier>(s);
                }

                ts.Complete();
            }
            catch
            {
            }
        }
    }

I'm getting an error in SubSonic DbDataProvider public DbConnection CurrentSharedConnection { get { return __sharedConnection; }

        protected set
        {
            if(value == null)
            {
                __sharedConnection.Dispose();

etc.. __sharedConnection == null :( Object Null Reference Exception :(

Thymine · Accepted Answer · 2011-04-16T21:10:28.673

Finally solved this for myself. All of the above code does not work for me (SubSonic 3.0.0.3, using SQLite) but adding BeginTransaction() caused it to work as expected, greatly speeding up the transaction and rolling back the updates if any exceptions occur.

using (SharedDbConnectionScope sharedConnectionScope = new SharedDbConnectionScope(Access.Provider))
{
    using (IDbTransaction ts = sharedConnectionScope.CurrentConnection.BeginTransaction())
    {
        IRepository repo = new SimpleRepository(Access.Provider);
        //Do your database updates

        //throw new ApplicationException("Uncomment this and see if the updates get rolled back");
        ts.Commit();
    }
}

For completeness: Access.Provider is a static property in a helper class for me that returns return SubSonic.DataProviders.ProviderFactory.GetProvider(ConnectionString, "System.Data.SQLite");

score 0 · Answer 2 · answered Aug 22 '09 at 23:51

Perhaps switching the SharedDbConnectionScope and TransactionScope around may help.

using (SharedDbConnectionScope scs = new SharedDbConnectionScope("connstring", "providerName"))
{
    using (TransactionScope ts = new TransactionScope())
    {
    }
}

score 0 · Answer 3 · answered Dec 23 '09 at 11:42

This will happen when Migration is set - On tablemigration the dbconnection will be closed.

Try the SimpleRepository with SimpleRepositoryOptions.None.

Don't know if this is a bug. I think the transactions don't work with SimpleRepository, I've always half of the data saved when throwing an exception in the transaction... perhaps it's only for ActiveRecord? Anybody knows?

Subsonic 3 Simple Repository And Transactions

3 Answers3