Unit Testing using LocalDB

 

I recently wrote some code with the typical data access layer as an interface to SQLServer.  The code I wrote doesn’t include a UI and has various operations occurring in its pipeline including authentication, model validation, data aggregation and so forth.  So now that the code is written it’s time for Unit Testing right?

Anybody that has had to write Unit Tests has at some point encountered issues with testing scenarios that include some kind of server.  A typical server issue when associated with a Unit Test is the need to build and/or reset the state of the server so that data is “just right” so that the Unit Test can perform its job.  That server might be IIS, SQLServer, SharePoint or something else but as soon as any server is introduced into the mix there is an immediate desire to pull back and start mocking things to remove the server issues. 

Unfortunately that means (when dealing with a database server) that stored procedures, table/column definitions and things like unique or check constraints, SQL data access layer and transaction/nested transaction commit/aborts won’t get tested.

Desiring to test these things along with everything else in the pipeline I thought that LocalDB might be the answer to my issues.  This article is about implementing that.

Generating/Updating the LocalDB Database

 

To start the process I created everything I need in a local SQLServer instance.  This makes it easy to implement and test since there are very good tools to do this.

Once I had all of it working in my local SQLServer I created a Visual Studio 2013 SSDT ‘SQL Server Database Project’.  The great thing about this project is how it makes it so easy to move changes from one database to another.  In my case, every time I made a change to the database, I needed to move the changes to a the VS database project (source control), SQL Azure database AND to my Unit Test LocalDB.  Doing all of this for any change takes less than 5 minutes each time.

One quirk I ran into with the ‘SQL Server Database Project’ is that schema compare wouldn’t connect to the LocalDB that was in my Unit Test project.  I eventually ended up leaving the LocalDB in the APP_DATA folder in a Web project where I would make the updates and then file copy the database into the Unit Test project.

The database files are stored at the root of the Unit Test project.  I would prefer to store them in a folder but idiosyncrasies with the [DeploymentItemAttribute] and changes in behavior on how the Unit Test runs in Release versus Debug caused me to just leave it in the root of the project and configure the [DeploymentItemAttribute] to also copy the database files into the root of the test location rather than a folder.

Configure the LocalDB Database

 

Mark the database files as ‘Content’ and ‘Copy Always’ and attribute each Unit Test with:

	[DeploymentItem(@"MyDatabase.mdf")]
	[DeploymentItem(@"MyDatabase_log.ldf")]
  

The connection string I left in the app.config for the Unit Test project and it presented the next challenge.  When a Unit Test is run the actual on disk location of the database file will vary.  Combine this with the need to use AttachDbFilename in the LocalDB connection string and you could create some interesting code pulling out connection string, figuring out directories and using string.Format to doctor the connection string before use.  However the location in the code that actually pulls the connection string from the configuration was deep within the SQL data layer and I didn’t want to try to modify the code to work with both Unit Test and Production.  Thankfully I found the answer to this at http://stackoverflow.com/questions/12244495/how-to-set-up-localdb-for-unit-tests-in-visual-studio-2012-and-entity-framework.  Combining using ‘|DataDirectory|’ in the connection string in the app.config with the following code in each test class solved that problem.

	[ClassInitialize]
	public static void ClassSetup(TestContext context)
	{
	    AppDomain.CurrentDomain.SetData(
		"DataDirectory",
		Path.Combine(context.TestDeploymentDir, string.Empty));
	}

Reset Database State for Each Test

 

So now the Unit Test can use the LocalDB but I also need to reset the database state before each Unit Test runs.  I could figure out a scenario like detaching the database (if it’s attached), recopying the database files and reattaching it before each test but I thought it would be easier to just use the same database and in [TestInitialize] I could just truncate all of the tables. 

Unfortunately all of the tables have identity columns, foreign keys, check constraints and all of the usual things you find in a database.  This meant I couldn’t run a SQL script in [TestInitialize] to just truncate all of the tables.

I then decided I’d delete all of the rows from each table and use DBCC CHECKIDENT to reset the identity columns so I could guarantee row ids in objects that were inserted into the SQL tables.  This led me down an interesting path.

Look at the documentation on DBCC CHECKIDENT and you’ll find the following in the documentation

image

The highlighted text is inconsistent with the behavior of SQLServer 2014 and LocalDB v11.0.  I didn’t test with any other versions of SQLServer or LocalDB so I don’t know if they have these issues as well but the actual behavior (you can decide for yourself which SQL is obeying the documentation as it’s still not clear to me) when using “DBCC CHECKIDENT(‘MyTable’, RESEED, 0)” after ‘DELETE FROM MyTable’ is:

  • SQLServer 2014 – The next row inserted has a row id of 1
  • LocalDB v11.0 – The next row inserted has a row id of 0!!!!

What?  I didn’t even know it was possible to have a Row ID of 0.  After many trials and tribulations and wondering if I needed to rethink using LocalDB I came up with the following SQL script that is run in [TestInitialize] (it runs before every test):

    BEGIN TRY
	BEGIN TRAN T1

	DECLARE @ID_TO_CHECK BIGINT

	DELETE FROM MyTable
	DBCC CHECKIDENT('MyTable', RESEED, 0)

	SAVE TRANSACTION T2
	INSERT INTO MyTable(Title) VALUES('test')
	SELECT @ID_TO_CHECK = MAX(MyTableId) FROM MyTable
	IF (@ID_TO_CHECK > 0)
	BEGIN
		ROLLBACK TRANSACTION T2
		DBCC CHECKIDENT('MyTable', RESEED, 0)
	END
	ELSE
		DELETE FROM MyTable

	-- Do more tables

	COMMIT TRAN T1
    END TRY
    BEGIN CATCH
	DECLARE @Error INT
	DECLARE @ErrorMessage NVARCHAR(4000)
	DECLARE @ErrorSeverity INT
	DECLARE @ErrorState INT

	SELECT @ErrorMessage = ERROR_MESSAGE(), 
		   @ErrorSeverity = ERROR_SEVERITY(), 
		   @ErrorState = ERROR_STATE()

	ROLLBACK TRAN T1
	RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
    END CATCH

This makes sure that when the test runs the next row that is inserted into the table will have a Row ID of 1 (NOT 0!!).

One thought on “Unit Testing using LocalDB

  1. Pingback: Unit Testing ASP.NET WebAPI Controllers | //InterKnowlogy/ Blogs

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>