SubSonic Transactions - Inserting to a second table with the return identity from first table

Question

I am using the code below to update a second table (Info2) with the identity used from the first table (info2.Id = info.Id;). When the second save is carried out (info2.Save()) I get the error: "There is already an open DataReader associated with this Command which must be closed first.". Can anyone see what I may be doing wrong.

SubSonic version 3.0.0.3 and SQL Server 2005

Thanks

                using (SharedDbConnectionScope sharedConnectionScope = new SharedDbConnectionScope())
                {
                    using (TransactionScope ts = new TransactionScope())
                    {
                        Info info = new Info();
                        info.Desc = "Some information";
                        info.Save();

                        Info2 info2 = new Info2();
                        info2.Id = info.Id;
                        info2.Desc = "More information";
                        info2.Save();

                        ts.Complete();
                    }
                }

Answer 1

Looks like you've got the TransactionScope and the SharedDbConnectionScope the wrong way round, try:

using (TransactionScope ts = new TransactionScope())
{
  using (SharedDbConnectionScope sharedConnectionScope = new SharedDbConnectionScope())
  {
    Info info = new Info();
    info.Desc = "Some information";
    info.Save();

    Info2 info2 = new Info2();
    info2.Id = info.Id;
    info2.Desc = "More information";
    info2.Save();

    ts.Complete();
  }
}

Answer 2

Hi Adam, thanks for your answer but I had already tried that. After carrying out a little research I found that I had to add "MultipleActiveResultSets=True;" to the connection string.

I have a different issue now where if the first insert succeeds and then an error occurs so ts.Complete(); does not get called there is no rollback. Basically the first save succeeds and does not rollback and obviously the second save does not get carried out due to the exception. There is no point in implementing SharedDbConnectionScope and TransactionScope!

try
            {
                using (SharedDbConnectionScope sharedConnectionScope = new SharedDbConnectionScope())
                {
                    using (TransactionScope ts = new TransactionScope())
                    {
                        Info info = new Info();
                        info.Desc = "Some information3";
                        info.Save();

                        //TODO: Above still adds to database after this Exception!!!
                        throw new Exception("STOP");

                        Info2 info2 = new Info2();
                        info2.Id = info.Id;
                        info2.Desc = "More information";
                        info2.Save();

                        ts.Complete();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

Answer 3

As per what I have written in the comments, the only way I can get this working is using TransactionScope first then SharedDbConnectionScope (thanks Adam) and to add MultipleActiveResultSets=True; (SQL Server 2005) to the connection string.

Anybody have any better solutions or other suggestions? Thanks

        try
        {
            using (TransactionScope ts = new TransactionScope())
            {
                using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                {
                    Info info = new Info();
                    info.Desc = "Some information";
                    info.Save();

                    //Test for rollback
                    //throw new Exception("STOP");

                    Info2 info2 = new Info2();
                    info2.Id = info.Id;
                    info2.Desc = "More information";
                    info2.Save();

                    ts.Complete();
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

Answer 4

Speed,

It seems working fine but do you find any other way or answer for doing this?

As I do not find any answer except your post which works.

Manish