There are many ways to define the specifications for your application. Since we are discussing doing this in an agile way, the specifications come in a scenario. Below is the description from a scenario for our first report. It includes personas Katie (a sales representative) and Jay (her supervisor).
Description:
Jay wants to be able to see sales totals for his sales force for any time period he might specify. For instance, Jay might want to run a report for sales for the week from 1 January to 7 January 2002. He also wants to be able to see the total for each sales rep.
This seems like a simple request. For the purposes of this article, I based the report on the Northwind database that comes standard with SQL Server 2000.
We have the definition for the report, which gives us the basis for the first test. To make sure that the report returns the right information, let us compare the reports result to a straight SQL query utilizing a dataset. Let’s start by using a stored procedure that is included with the Northwind database.
Since I’m basing the report on the Sales by Year stored procedure, let’s create a test to get data directly from the database. This result will then be compared with the actual report. From the code listing below, take out the actual SQL and insert it in the test with the same filter criteria you would pass from the report into the stored procedure.
Listing 2: Sales by Year Stored Procedure
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure "Sales by Year"
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID,
"Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM
Orders INNER JOIN "Order Subtotals" ON Orders.OrderID
= "Order Subtotals".OrderID WHERE Orders.ShippedDate
Between @Beginning_Date And @Ending_Date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The name of this first test should be something descriptive, so I chose SalesByYearResults. I start this test by using a straight query with SQL similar to that used in the stored procedure. So the initial code looks like the code in Listing 2:
Listing 3: SalesByYearResults Test
[Test]
public void SalesByYearResults()
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db
= DatabaseFactory.CreateDatabase();
string sqlCommand = "SELECT Orders.ShippedDate, Orders.OrderID,
[Order Subtotals].Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID =
[Order Subtotals].OrderID WHERE Orders.ShippedDate Between
'01/01/1998' And '12/31/1999'";
DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
DataSet productsDataSet = db.ExecuteDataSet(dbCommandWrapper);
Assert.IsTrue(productsDataSet.Tables[0].Rows.Count>0, "No results were returned");
}
This simple test proves that our code returns data. Each iteration of the test builds toward the ultimate test. So now, let us add the stored procedure to this test. Since the report is based on this stored procedure, this step helps verify that the results from running a stored procedure with the same filters as the straight SQL expression give the same results. I will then be sure that any problems encountered when I test the report against a straight SQL expression are related to the Crystal Report, not the stored procedure. I added the code in Listing 3 to the code in Listing 2.
Listing 4: Testing the Stored Procedure
sqlCommand = "Sales By Year";
dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);
dbCommandWrapper.AddInParameter("@Beginning_Date",
DbType.DateTime, System.Convert.ToDateTime("01/01/1998"));
dbCommandWrapper.AddInParameter("@Ending_Date", DbType.DateTime,
System.Convert.ToDateTime("12/31/1999")); DataSet salesDataSetSP =
db.ExecuteDataSet(dbCommandWrapper); Assert.IsTrue(salesDataSetQuery.
Tables[0].Rows.Count == salesDataSetSP.Tables[0].Rows.Count,
"Does not contain the same data.");
This test brings back a green bar in the nUnit interface. Let’s add some Crystal code to our tests to continue developing our application.
