MSDN Magazine Launch Issue - February 15, 2008 - (Page 72) The database unit-testing API supports data-driven testing by allowing you to supply zero or more DbParameter instances to the Execute method. To understand how the supplied DbParameter instances are used, think of the SQL test script as parameterized SQL that directly references the DbParameter instances as variables in the code. An example is shown in Figure 9. As you can see in Figure 9, the test method is decorated with an attribute that informs the test harness that it expects to be called once for each row of data retrieved from the DecisionAllocationTestData table in the TestDB_DataDriven database on my local SQL Server Express instance. The DatabaseTestAction class represents an action in a test composed of a test script to execute and test conditions to evaluate the results. You’ll notice that I create five DbParameter instances representing the variables referenced in the test script; it is important to note that some of these parameters are output parameters. The CreateParameter method is just a small helper method that creates the instance and optionally populates it with data from the test context. The test script you see next is defined to reference the DbParameter instances I’m providing as arguments to the Execute method. If you’re familiar with ADO.NET code, you’ll see this is simply parameterized SQL. Next I call the Execute method on the default TestService instance, passing the DbParameter instances as arguments. After Figure 9 Supplying DbParameter Instances [DataSource (“System.Data.SqlClient”, “Data Source=(local)\\sqlexpress” + “;Initial Catalog=TestDB_DataDriven;Integrated Security=True”, “DecisionAllocationTestData”, DataAccessMethod.Sequential), TestMethod()] public void DataDriveTestSample() { // Setup and execute the test DatabaseTestAction test = new DatabaseTestAction(); DbParameter customerID = CreateParameter( “@customerID”, DbType.Int32, ParameterDirection.Input, “CustomerID”); DbParameter offerID = CreateParameter( “@offerID”, DbType.Int32, ParameterDirection.Input, “OfferID”); DbParameter decision = CreateParameter( “@decision”, DbType.StringFixedLength, ParameterDirection.Input, “Decision”); DbParameter actualVault = CreateParameter( “@vault”, DbType.Int32, ParameterDirection.Output); DbParameter actualBroker = CreateParameter( “@broker”, DbType.Int32, ParameterDirection.Output); DbParameter actualMarket = CreateParameter( “@market”, DbType.Int32, ParameterDirection.Output); test.SqlScript = @” declare @rc int execute @rc = AllocatePositionsByDecision @customerID, @offerID, @decision, @vault out, @broker out, @market out “; DatabaseTestClass.TestService.Execute( execution I verify the results. This is simply a matter of retrieving the expected values from the DataRow and comparing them with the output parameters. The example in Figure 9 demonstrates that the database unittesting API can be targeted directly rather than using the designer. There I am using data retrieved from a test table to drive execution of a stored procedure; the stored procedure being tested uses the first three input values to calculate the number of shares that will be retrieved from various locations to fulfill the customer’s decision. This calculation is complex, depends only on the inputs, and has simple outputs. Therefore it makes sense to write this test as data-driven rather than duplicating the same test many times varying only the data. At this point, I have covered several different ways you can modify or use the existing API to leverage transactions or create data-driven tests. The database unit-testing API can be extended or modified in several ways, though, by adding extra test conditions or by creating your own test service. So now let’s look at ways you can extend the framework. Custom Test Conditions The database unit-testing feature comes with six test conditions that can be used to verify the execution of your unit test. Although these test conditions are sufficient for basic use, most users will base.ExecutionContext, base.PrivilegedContext, test,customerID, offerID, decision, actualVault, actualBroker, actualMarket); // Verify the results int expectedVault = (int) base.TestContext.DataRow[“ExpectedVault”]; int expectedBroker = (int)base.TestContext.DataRow[“ExpectedBroker”]; int expectedMarket = (int)base.TestContext.DataRow[“ExpectedMarket”]; Assert.AreEqual(expectedVault, actualVault.Value, “Vault incorrect”); Assert.AreEqual(expectedBroker, actualBroker.Value, “Broker incorrect”); Assert.AreEqual(expectedMarket, actualMarket.Value, “Market incorrect”); } private DbParameter CreateParameter(string paramName, DbType paramType, ParameterDirection paramDirection) { return CreateParameter(paramName, paramType, paramDirection, null); } private DbParameter CreateParameter(string paramName, DbType paramType, ParameterDirection paramDirection, string dataColumnName) { DbParameter newParameter = base.ExecutionContext.Provider.CreateParameter(); newParameter.DbType = paramType; newParameter.Direction = paramDirection; newParameter.ParameterName = paramName; if (paramDirection == ParameterDirection.Input || paramDirection == ParameterDirection.InputOutput) { newParameter.Value = base.TestContext.DataRow[dataColumnName]; } } return newParameter; 72 msdnmagazine Database Unit Testing
For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.