Where have I been?! In transition

So I trainwrecked pretty hard after november   But It doesn’t mean I don’t want to write.  In fact what I’ve been doing is setting up a new host for my blog.  So if you saunter over to www.mcgaffigan.com you’ll see that I’m playing with a content management system (drupal) so that I can have more control over my site .  Anyway, you’ll see less posting here (if you can believe that) as I transfer over to the new host.  Wish me luck. 

 

wierd!

I posted last night before midnite here but it registered as posting this morning..

 That is so bizarre.   It turns out the “time” setting for my blog was off by an hour so if I posted after 11pm it appeared on the next day.

 So I still posted 1 per day this month!

 

The grinch is on tonite!

One of my favorite christmas shows is on tonite… I don’t know why but I really enjoyed the Grinch christmas special growing up.  It was a lot of fun the way the words and songs just kept rhyming and grabbing at my attention.  I truly enjoy that show.  We are Recording it tonite (along with the Shrek special) for the kids to enjoy

 

IGive.com

Apparently there is this website out there where you can register and when you shop online at certain places (some well known), a donation is given to the fund you “associate” your account with.  It sounds pretty interesting. If you shop online maybe igive is something you might want to look into.  (although I’m not sure if the prices are the “absolute best” you can do.)  So there is a reason I’m talking about Igive… 

My sister-in-law’s husband was recently diagnosed with Cancer of the esophagus and it has now metastasized in the liver.  The prognosis is not good.  Tony Hurd is 40 years old, he’s mentally and physically handicapped, and the health insurance he has available to him, frankly, sucks.  Tony can no longer work so the little money that he was bringing in is not coming in at all.  They have to fight through all kinds of red tape to attempt to get better health care.  Tony’s family is trying hard to get him the best care they can but they need help, so they’ve registered at igive.com under the “Tony Hurd cancer fund.” 

Rough fall over here…

 

Learning something new each day

We had a consultant into work today… His name is Dan Sullivan (author of a number of SQL Server books).  It was a super productive day.  We spent time going over our database specifically looking at indices and how they impact (both positively and negatively).  I guess I never really thought about how indexing works in a database, but it’s pretty cool.  One of the more important things we learned is… “DON’T blindly follow the Database tuning advisor’s recommendations.”  While the tuning advisor might suggest an index, it doesn’t know a lot about your data in the database so it makes suggestions on indices that don’t always make sense (ie if you have an int as a column that can only have 3 values… it probably doesn’t need to be included in the key). 

Anyway.  Tomorrow I should be breakingout the batching code I talked about last week.

 

I’m Full friday

I think I ate too much yesterday .  Anyway, I spent the afternoon in a pool with my kids today.  We visited our friends the swindells up in NH.  They were staying at steele hill resorts just outside of Tilton.  They were thinking about time shares (they met with a sales guy).  From the sounds of things time shares seem overly complex.  I just don’t get em .

 

Happy Thanksgiving

I give thanks every day for my friends and family.  Without you/them life would be a whole lot harder.  You make the good times better and the hard times easier.  I especially would like to thank my wife for being my friend and companion, and for always being there for me.

 

Database batching code in .Net and why Unit testing is cool.

So I created the Database batching code that I spoke about recently and the way I went about doing it was to create the unit tests first.  And then get the code to work.   My goal from the beginning was 100% code coverage of all my unit tests.  So using NCover and NUnit and NMock I was able to achieve that.  Then when I replaced the existing database access code with my new code.   ALL the preexisting DB access unit tests passed (we have some unit tests that work with the database) in one try!  WOOOTT!!!  And at the same time a coworker of mine changed some of his code, didn’t even run the existing unit tests and checked his code in and he broke the build for 5 hrs.  I had to wait that 5 hours before I could check my code in.

Here are my unit tests, the class in question that I am testing is the database executor class (called Database in this version). If you are interested in seeing the DB execution implementation post here and I’ll put it up in my next post. (I’d like to see if people are actually interested in this series of articles )

[TestFixture]
public class DatabaseAccessingTestFixture
{
    private List myConnectionStrings;
    [SetUp]
    protected virtual void SetUp()
    {
        myConnectionStrings = new List();
        myConnectionStrings.Add("Blah!");

    }

    [Test]
    public void SingletonAccessShouldReturnDatabaseObject()
    {
        Single.Instance = null;
        Assert.IsInstanceOfType(typeof(Database), Database.Instance);
    }

    [Test]
    public void ExecuteSingleCommandShouldOpenANewConnectionAndExecuteTheCommand()
    {
        Mockery mocks = new Mockery();
        IDbCommand cmd = mocks.NewMock();
        IDbConnection con = mocks.NewMock();
        IDataReader rdr = mocks.NewMock();

        Database db = new Database();
        int workCalled = 0;
        DataReaderConsumer work = delegate(IDataReader reader) { workCalled++;
                                                                   reader.Read(); };
        Expect.Once.On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
        Expect.Once.On(con).Method("Open");
        Expect.Once.On(cmd).Method("ExecuteReader").WithNoArguments().Will(Return.Value(rdr));
        Expect.Once.On(rdr).Method("Close");
        Expect.Once.On(cmd).SetProperty("Connection");
        Expect.Once.On(con).Method("Dispose");
        Expect.Once.On(rdr).Method("Read").Will(Return.Value(true));

        using (CreatorScope scope = new CreatorScope(delegate { return con;}))
        {
            db.ExecuteSingleCommand(cmd, work, myConnectionStrings, IsolationLevel.ReadCommitted, null);
        }
        Assert.IsTrue(workCalled==1);
        mocks.VerifyAllExpectationsHaveBeenMet();
    }

    [Test]
    public void ExecutingABatchShouldInvokeWork2Times()
    {
        Mockery mocks = new Mockery();
        IDbCommand cmd = mocks.NewMock();
        IDbConnection con = mocks.NewMock();
        IDataReader rdr = mocks.NewMock();

        Database db = new Database();
        int workCalled = 0;
        DataReaderConsumer work = delegate(IDataReader reader)
        {
            workCalled++;
            reader.Read();
        };

        using (mocks.Ordered)
        {
            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            //Open connection once.
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //ExecuteReader for every "singlecommand" in the batch
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(Return.Value(rdr));
            Expect.Exactly(1).On(rdr).Method("Read").Will(Return.Value(true));
            //Close reader when done
            Expect.Exactly(1).On(rdr).Method("Close");
            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Open));
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //ExecuteReader for every "singlecommand" in the batch
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(Return.Value(rdr));
            Expect.Exactly(1).On(rdr).Method("Read").Will(Return.Value(true));
            //Close reader when done
            Expect.Exactly(1).On(rdr).Method("Close");
            Expect.Once.On(con).Method("Dispose");
        }
        using (CreatorScope scope = new CreatorScope(delegate { return con; }))
        {
            db.Execute(IsolationLevel.ReadCommitted, null, myConnectionStrings,
                       delegate
                           {
                               db.ExecuteSingleCommand(cmd, work, myConnectionStrings, IsolationLevel.ReadCommitted, null);
                               db.ExecuteSingleCommand(cmd, work, myConnectionStrings, IsolationLevel.ReadCommitted, null);
                           });
        }
        Assert.IsTrue(workCalled == 2);
        mocks.VerifyAllExpectationsHaveBeenMet();

    }

    [Test]
    [ExpectedException(typeof(OurException))]
    public void AttemptingABatchWithIsolationOfNoneShouldThrowOurException()
    {

        Database db = new Database();
        db.Execute(IsolationLevel.None, null, myConnectionStrings, null);
    }

    [Test]
    [ExpectedException(typeof (OurException))]
    public void AttemptingABatchWithNullWorkParameterShouldThrowOurException()
    {
        Database db = new Database();

        db.Execute(IsolationLevel.ReadCommitted, null, myConnectionStrings, null);
    }

    [Test]
    public void IfStatementRequiresNewTransactionDuringABatchWeShouldThrowOurException()
    {
        Mockery mocks = new Mockery();
        IDbCommand cmd = mocks.NewMock();
        IDbConnection con = mocks.NewMock();
        IDataReader rdr = mocks.NewMock();

        Database db = new Database();
        int workCalled = 0;
        DataReaderConsumer work = delegate(IDataReader reader)
        {
            workCalled++;
            reader.Read();
        };

        using (mocks.Ordered)
        {
            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            //Open connection once.
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //ExecuteReader for every "singlecommand" in the batch
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(Return.Value(rdr));
            Expect.Exactly(1).On(rdr).Method("Read").Will(Return.Value(true));
            //Close reader when done
            Expect.Exactly(1).On(rdr).Method("Close");
            //clean up connection on the way out.
            Expect.Once.On(con).Method("Dispose");
        }
        bool caughtException = false;
        try
        {
            using (CreatorScope scope = new CreatorScope(delegate { return con; }))
            {
                db.Execute(IsolationLevel.ReadCommitted, null, myConnectionStrings,
                           delegate
                               {
                                   db.ExecuteSingleCommand(cmd, work, myConnectionStrings, IsolationLevel.ReadCommitted, null);
                                   db.ExecuteSingleCommand(cmd, work, myConnectionStrings, IsolationLevel.ReadCommitted, null);
                               });
            }
        }
        catch (OurException e)
        {
            caughtException = true;
        }
        Assert.IsTrue(caughtException);
        mocks.VerifyAllExpectationsHaveBeenMet();
    }

    [Test]
    public void IfStatementHasIncompatibleIsolationTypeDuringABatchWeShouldThrowOurException()
    {
        Mockery mocks = new Mockery();
        IDbCommand cmd = mocks.NewMock();
        IDbConnection con = mocks.NewMock();
        IDataReader rdr = mocks.NewMock();

        Database db = new Database();
        int workCalled = 0;
        DataReaderConsumer work = delegate(IDataReader reader)
        {
            workCalled++;
            reader.Read();
        };

        using (mocks.Ordered)
        {
            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            //Open connection once.
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //ExecuteReader for every "singlecommand" in the batch
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(Return.Value(rdr));
            Expect.Exactly(1).On(rdr).Method("Read").Will(Return.Value(true));
            //Close reader when done
            Expect.Exactly(1).On(rdr).Method("Close");
            //clean up connection on the way out.
            Expect.Once.On(con).Method("Dispose");
        }
        bool caughtException = false;
        try
        {
            using (CreatorScope scope = new CreatorScope(delegate { return con; }))
            {
                db.Execute(IsolationLevel.ReadCommitted, null, myConnectionStrings,
                           delegate
                           {
                               db.ExecuteSingleCommand(cmd, work, myConnectionStrings,IsolationLevel.ReadCommitted, null);
                               db.ExecuteSingleCommand(cmd, work, myConnectionStrings,IsolationLevel.Snapshot, null);
                           });
            }
        }
        catch (OurException e)
        {
            caughtException = true;
        }
        Assert.IsTrue(caughtException);
        mocks.VerifyAllExpectationsHaveBeenMet();
    }

    [Test]
    public void ExecuteSingleCommandWithSnapshotIsolationShouldExecuteUnderSnapshotIsolation()
    {
        Mockery mocks = new Mockery();
        IDbCommand cmd = mocks.NewMock();
        IDbConnection con = mocks.NewMock();
        IDataReader rdr = mocks.NewMock();

        Database db = new Database();
        int workCalled = 0;
        DataReaderConsumer work = delegate(IDataReader reader)
        {
            workCalled++;
            reader.Read();
        };

        Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
        Expect.Once.On(con).Method("Open");
        Expect.Once.On(cmd).Method("ExecuteReader").WithNoArguments().Will(Return.Value(rdr));
        Expect.Once.On(rdr).Method("Close");
        Expect.Once.On(cmd).SetProperty("Connection");
        Expect.Once.On(con).Method("Dispose");
        Expect.Once.On(rdr).Method("Read").Will(Return.Value(true));

        using (CreatorScope scope = new CreatorScope(delegate { return con; }))
        {
            db.ExecuteSingleCommand(cmd, work, myConnectionStrings, IsolationLevel.Snapshot, new TimeSpan(20000));
        }
        Assert.IsTrue(workCalled == 1);
        mocks.VerifyAllExpectationsHaveBeenMet();
    }

    [Test]
    public void StatementFailsIfMirroringServerFailsOverwithSQLAndTransactionException()
    {
        Mockery mocks = new Mockery();
        IDbCommand cmd = mocks.NewMock();
        IDbConnection con = mocks.NewMock();
        IDataReader rdr = mocks.NewMock();

        Database db = new Database(2, 50);
        int workCalled = 0;
        DataReaderConsumer work = delegate(IDataReader reader)
        {
            workCalled++;
            reader.Read();
        };

        using (mocks.Ordered)
        {
            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            //Open connection once.
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //retry command for each retry.
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(
                Throw.Exception(SqlExceptionCreator.CreateSqlException("Blah", 8506,20)));

            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //retry command for each retry.
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(
                Throw.Exception(new TransactionInDoubtException("Transaction ambiguous")));

            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //retry command for each retry.
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(
                Throw.Exception(SqlExceptionCreator.CreateSqlException("Blah", 8506,20)));
            Expect.Once.On(con).Method("Dispose");
        }
        bool exceptionThrown = false;
        try
        {
            using (CreatorScope scope = new CreatorScope(delegate { return con; }))
            {
                db.ExecuteSingleCommand(cmd, work, myConnectionStrings, IsolationLevel.ReadCommitted, null);
            }
        }
        catch (OurException e)
        {
            Assert.AreEqual(8506, ((SqlException)e.InnerException).Number);
            exceptionThrown = true;
        }
        Assert.IsTrue(exceptionThrown);
        Assert.IsTrue(workCalled == 0);
        mocks.VerifyAllExpectationsHaveBeenMet();
    }

    [Test]
    public void StatementShouldThrowExceptionIfExecuteReaderThrowsExceptionWithErrorClassLessThan20()
    {
        Mockery mocks = new Mockery();
        IDbCommand cmd = mocks.NewMock();
        IDbConnection con = mocks.NewMock();
        IDataReader rdr = mocks.NewMock();

        Database db = new Database(2, 50);
        int workCalled = 0;
        DataReaderConsumer work = delegate(IDataReader reader)
        {
            workCalled++;
            reader.Read();
        };

        using (mocks.Ordered)
        {
            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            //Open connection once.
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //retry command for each retry.
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(
                Throw.Exception(SqlExceptionCreator.CreateSqlException("Blah", 22, 10)));
            Expect.Once.On(con).Method("Dispose");
        }
        bool exceptionThrown = false;
        try
        {
            using (CreatorScope scope = new CreatorScope(delegate { return con; }))
            {
                db.ExecuteSingleCommand(cmd, work, myConnectionStrings,IsolationLevel.ReadCommitted, null);
            }
        }
        catch (OurException e)
        {
            Assert.AreEqual(22, ((SqlException)e.InnerException).Number);
            exceptionThrown = true;
        }
        Assert.IsTrue(exceptionThrown);
        Assert.IsTrue(workCalled == 0);
        mocks.VerifyAllExpectationsHaveBeenMet();
    }

    [Test]
    public void SingleStatementShouldSucceedOnDualRailFailover()
    {
        Mockery mocks = new Mockery();
        IDbCommand cmd = mocks.NewMock();
        IDbConnection con = mocks.NewMock();
        IDataReader rdr = mocks.NewMock();

        Database db = new Database(2, 50);
        int workCalled = 0;
        DataReaderConsumer work = delegate(IDataReader reader)
        {
            workCalled++;
            reader.Read();
        };

        myConnectionStrings.Add("dualRail");

        using (mocks.Ordered)
        {
            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            //Open connection once.
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //retry command for each retry.
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(
                Throw.Exception(SqlExceptionCreator.CreateSqlException("Blah", 8506, 20)));

            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //retry command for each retry.
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(
                Throw.Exception(SqlExceptionCreator.CreateSqlException("Blah", 8506, 20)));

            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //retry command for each retry.
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(
                Throw.Exception(SqlExceptionCreator.CreateSqlException("Blah", 8506, 20)));
            Expect.Once.On(con).Method("Dispose");

            Expect.Exactly(1).On(con).GetProperty("State").Will(Return.Value(ConnectionState.Closed));
            //Open connection once.
            Expect.Once.On(con).Method("Open");
            Expect.Exactly(1).On(cmd).SetProperty("Connection");
            //retry command for each retry.
            Expect.Exactly(1).On(cmd).Method("ExecuteReader").WithNoArguments().Will(Return.Value(rdr));
            Expect.Once.On(rdr).Method("Read").Will(Return.Value(true));
            Expect.Once.On(rdr).Method("Close");
            Expect.Once.On(con).Method("Dispose");
        }
        bool exceptionThrown = false;
        try
        {
            using (CreatorScope scope = new CreatorScope(delegate { return con; }))
            {
                db.ExecuteSingleCommand(cmd, work, myConnectionStrings, IsolationLevel.ReadCommitted, null);
            }
        }
        catch (OurException e)
        {
            Assert.AreEqual(8506, ((SqlException)e.InnerException).Number);
            exceptionThrown = true;
        }
        Assert.IsFalse(exceptionThrown);
        Assert.IsTrue(workCalled == 1);
        mocks.VerifyAllExpectationsHaveBeenMet();
    }
}

 

Data access code and Delegates

So it’s been a while since I posted about the “cool problem” … Largely it has to do with time.  However before I present the solution let me talk a little more about what we are doing.

Each time we execute a stored procedure from within our data access code we do it under it’s own transaction (if a transaction is needed).  Here are some examples of what we do:

UserProxy uProx = new UserProxy(connectionstrings);
uProx.UpdateUser(userInformation);
uProx.LookupUser(userinformation, delegate (IDataReader) {  /*do datareader stuff */});

Each of these statements executes under it’s own connection logic to the database.    What we would like to do is be able to wrap these connections under a transaction.  We could use a TransactionScope:

using (TransactionScope scope = new TransactionScope(options))
{
   //Do our database stuff
}

This is all fine and good but there are a few caveats.  Most of the time we use the transaction scope around our code we end up using the DTC. (and in our case we are hitting the same DB so we’d like to avoid that if possible).  Our code starts to become cluttered with Using transactionScope statements, And if we have to extend (which we do in this case) the access to the database to include error handling logic due to mirroring failovers then we’d have to affect every piece of code that accesses the database in a transaction scope to handle an Exception, and then retry the sequence of statements within the scope, once we’ve determined that the failure is of the type that a failover retry might succeed.Enter our particular solution.

Using the concept of a batch we realized that an anonymous delegate could represent the work we needed to do and then we could submit that delegate to our Command execution engine and it could handle the retries due to errors.  Since we wanted to use the same engine for single statements as well as batches we had to figure that into the equation.  So now our usage model looks something like this:

DatabaseExecutor.Execute(IsolationLevel.Snapshot,connectionstrings, delegate (){ UserProxy.CreateUser(info); UserProxy.UpdateUser(info); UserProxy.ReadInfo(info, delegate(IDataReader reader) {/*read the data*/})});

The next post will share the unit tests and code for the DatabaseExecutor.

 

Linux installed

So I’ve been thinking a lot lately about other operating systems (I’m running Vista and XP at home) it seems that XP over the last few years has been requiring MORE and MORE pc resources.  (must be all the security updates) AND Vista is just a resource pig in general.  So I took a pc that I have and installed Etch (a debian version of Linux).  Man it was super easy.  And it runs like a top on the pc whereas with XP it was gasping along. 

What next?  well I’m thinking of hosting my own blog on that computer.  (this blog is great and all but it’s not as customizeable as I would like). 

Now the question is what blog software should I run