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.

private
DatabaseTestActions dbo_GetSpecialPeople;

 

[TestMethod()]

public
void dbo_GetSpecialPeople()

{


DatabaseTestActions testActions = this.dbo_GetSpecialPeople;


//Execute the pre-test script

System.Diagnostics.Trace.WriteLineIf((((testActions.PretestAction) != null)), "Executing pre-test script…");


ExecutionResult[] pretestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PretestAction, GetUnitTestParameters());

 

//Execute the test script

System.Diagnostics.Trace.WriteLineIf((((testActions.TestAction) != null)), "Executing test script…");


ExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction, GetUnitTestParameters());

 


//Execute the post-test script

System.Diagnostics.Trace.WriteLineIf((((testActions.PosttestAction) != null)), "Executing post-test script…");


ExecutionResult[] posttestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PosttestAction, GetUnitTestParameters());

}

 

Guid specialID = Guid.NewGuid();

 

private
DbParameter[] GetUnitTestParameters()

{


DbParameter[] p = new
DbParameter[1];

p[0] = new System.Data.SqlClient.SqlParameter();

p[0] = this.ExecutionContext.Provider.CreateParameter();

p[0].Direction = ParameterDirection.Input;

p[0].ParameterName = "@specialID";

p[0].Value = this.specialID;

 


return p;

}

 

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.

–Pre-Test

INSERT
INTO SpecialPeople

(ID, FirstName, LastName,
Description)

VALUES

(@specialID,
‘Justin’, ‘Mason’, ‘Who cares this will be deleted after the test is complete’)

 

Then your actual Test Script executes and you do your assertions. Now clean up.

–Post-Test

DELETE
FROM SpecialPeople

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.

 

Category: Applications