Visual Studio 2010 gave the development community a nice new way to manage our databases. The Database Project and Database Server Projects have made what used to be a headache of managing a series of migration scripts into a simpler point and click database comparison deployment process. In addition to the Database Project comes the Database Unit Test. You can easily create a Unit test from the Schema View Window in the Database Project. Once you have a Unit Test though you might run into something I struggled with at first.
I have a Pre-Test Script, Test Script, and a Post-Test Script. If I use these scripts the way you would expect it would look like this. The Pre-Test script defines variables such as IDs I will use and then populates tables. The Test script actually calls a stored procedure and asserts on some expected result. Then the Post-Test Script deletes all that test data the Pre-Script created. Looks straight forward but… Since each script is executed in a different context I can’t simply declare a variable in the Pre-Test script and use it in any other script. There is a way to do this so let’s just jump into it.
The code behind on the Database Unit test gives us access to the actual calls to each script. There is where we can define DbParameters and then using the "DatabaseTestAction.Execute" overload pass them to the execution step for each script. The only caveat is that a DbParameter can only be accessed once per execution context so keep that in mind. I choose to have a function new up my parameters so I could reuse them easily.
System.Diagnostics.Trace.WriteLineIf((((testActions.PretestAction) != null)), "Executing pre-test script…");
//Execute the test script
System.Diagnostics.Trace.WriteLineIf((((testActions.TestAction) != null)), "Executing test script…");
System.Diagnostics.Trace.WriteLineIf((((testActions.PosttestAction) != null)), "Executing post-test script…");
Guid specialID = Guid.NewGuid();
p = new System.Data.SqlClient.SqlParameter();
p = this.ExecutionContext.Provider.CreateParameter();
p.Direction = ParameterDirection.Input;
p.ParameterName = "@specialID";
p.Value = this.specialID;
Now in the script where you want the parameter to be used you just use is like it had been defined. Don’t include your own declare statement, just use it using the "@" prefix.
(ID, FirstName, LastName,
Then your actual Test Script executes and you do your assertions. Now clean up.
WHERE ID = @specialID
Pretty simple eh? Since the argument on the Execute takes an array you can supply as many parameters as your test needs. Hope this helps.