Summary
One of the biggest problems I used to find in unit testing is that most of the products I work on contain mostly methods that access a database. I found that most objects and methods work with data and it’s very tedious to try and mock or fake the database component. If you’ve followed this blog for some time you’ll know that I have an easy and effective way around this problem. I have a framework that I developed that enables me to load a SQLLocalDB instance, replicate the tables, views, stored procedures and even constraints before a unit test is executed. In this blog post, I’m going to demonstrate how to unit test a method that contains a call to a stored procedure. I’m going to show two different methods you can use to perform this operation.
What Are We Testing. The Full Database Method.
First, we need to determine what we’re really testing. If you go to my GitHub account here, and download the NHibernate mapping generator, you can use this to extract all the table, view and stored procedure definitions from a selected database (you can ignore or delete the NHibernate ORM stuff if you’re not going to use NHibernate). This solution contains a project called “Helpers” that contains a framework for setting up a SQLLocalDB instance and can create a database using the source you generate when you run the NHibernate generator tool. In order to use this framework, you can then create unit tests that stuff information into the SQLLocalDB database instance, execute your method, then read the results from the database (or test results returned from the method under test).
Let’s create an example..
First, open up sql server and create a table and a stored procedure.
CREATE TABLE [dbo].[person](
[id] [int] IDENTITY(1,1) NOT NULL,
[first] [varchar](50) NULL,
[last] [varchar](50) NULL,
CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[ReadPersonNames]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT first, last FROM person
END
GO
Insert some data into your person table:
INSERT INTO person (first,last) VALUES ('Mary','Jane')
INSERT INTO person (first,last) VALUES ('Joe','Cool')
Now, download the NHibernate Mapping Generator program from GitHub (click here). There are several projects inside the solution. Make sure that the project named “NHibernateMappingGenerator” is set as the starting project. Then hit F5 (or click the run/start button). In the drop-down list box, select your database instance (MS SQL Server). A list of databases inside that server will display in the check list box below the drop-down. Then check the database that you created the table and stored procedure above, then click the generate button. When the generate button turns disabled, then you’re done. Exit from the program. You might receive a “File Modification Detected” error, this is normal. Just click “Reload” and you’ll see your database added to the NHIbernateDataLayer project.
Create a new console project. Add an existing project to your solution and navigate to the helpers project (or you can copy the helpers from the NHibernate solution into your solution). Make sure you add a reference to the helpers project from your main project and add a “using Helpers;” at the top.
Next, add a unit test project to your solution. In your unit test project you will need to reference the Helpers and your console project. Also, include the using statements at the top.
Add this code to your main program:
class Program
{
static void Main(string[] args)
{
var personSelector = new PersonSelector();
DataSet data= personSelector.ReadNames();
foreach (DataRow row in data.Tables[0].Rows)
{
Console.WriteLine(row["first"].ToString() + " " +
row["last"].ToString());
}
Console.ReadKey();
}
}
public class PersonSelector
{
public DataSet ReadNames()
{
using (var db = new ADODatabaseContext("Server=YourServer;
Initial Catalog=sampledata;Integrated Security=True"))
{
return db.StoredProcedureSelect("sampledata.dbo.ReadPersonNames");
}
}
}
As you can see, I’m just using ADO to read the records from the person table using the stored procedure read method defined in the Helpers project. There are no parameters to this stored procedure, it just reads the first and last names of the entire table. You should be able to execute this tiny program and see the first and last names of each record listed (Mary Jane and Joe Cool if you used my insert statements earlier). If your database is not named “sampledata” then you’ll need to change that in the code above.
Now we’ll need to add a database context project to your console application. This will contain the cs files created by the NHibernate mapping generator. First, create a new class library project named “Data”. Inside the NHibernateDataLayer project you’ll find a subdirectory with the name of your database, inside that will be a folder named “TableGeneratorCode”, and it will contain a cs file named “sampledataTableGeneratorCode”. Copy that file into your “Data” project. You’ll have to include the helpers project and add your using statement.
Next, you’ll need to copy over the stored procedure generator code. This will be in the NHibernateDataLayer project, in the folder named after your database, in a subfolder named “StoredProcedures” and there will be a cs file for each stored procedure that was found in your database. Grab the file for the one stored procedure named “ReadPersonNames.cs”. Copy that file into your “Data” project. Don’t forget to include the Helpers using clause.
Now for the unit tests. You’ll need to create a class that will initialize the SQLLocalDB. I usually call this source “AssemblyCommon.cs”. You’ll need an assembly cleanup and initialize method. Before you start, add a reference to the Helpers and Data projects, then add these using statements:
using Helpers;
using Data;
Now add in the code below:
[TestClass]
public class AssemblyCommon
{
[AssemblyInitialize]
public static void ClassStartInitialize(TestContext testContext)
{
UnitTestHelpers.Start("storedprocinstance", new string[] { Data.sampledataTables.DatabaseName });
// create tables
UnitTestHelpers.CreateAllTables(Data.sampledataTables.TableList, Data.sampledataTables.DatabaseName);
ReadPersonNames.Instance.CreateStoredProcedure(UnitTestHelpers.InstanceName);
}
[AssemblyCleanup]
public static void ClassEndCleanup()
{
UnitTestHelpers.End();
}
}
The assembly initialize code will start up an instance named “storedprocinstance”. You can put a breakpoint at the end of the AssemblyInitialize method and then startup SQL Server Management Studio and go to a database named “(localdb)storedprocinstance” and see the database that is running. This can be handy for troubleshooting your unit test data.
Next, go to your unit test cs file and add a TestCleanup method:
[TestCleanup]
public void Cleanup()
{
UnitTestHelpers.TruncateData();
}
This will reset your database between unit tests without tearing down the whole database and rebuilding it.
You’ll have to add a reference to System.Data and a using statement at the top (which will also need a reference to System.XML, but you won’t need a using statement for that one). Then add the following unit test method:
[TestMethod]
public void TestMethod1()
{
using (var db = new ADODatabaseContext("TEST", "sampledata"))
{
db.ExecuteNonQuery("INSERT INTO person (first,last) VALUES
('Mary','Jane')");
db.ExecuteNonQuery("INSERT INTO person (first,last) VALUES
('Joe','Cool')");
}
var personSelector = new PersonSelector();
DataSet data = personSelector.ReadNames();
Assert.AreEqual(2, data.Tables[0].Rows.Count);
}
This unit test starts with the instance already in place. The person table is already created and the stored procedure exists. All you need to focus on is your unit tests. In the sample above, I opened an ADO context into the database and inserted two records (you can use Entity Framework or any other ORM for your unit tests, treat it like a normal SQL server). Next, I instantiate the object and call my method that I wish to test. Then I check the result with an assert. In this case, I’m just checking to see if two rows were inserted.
What Are We Testing? The Fake Database Method.
In this example, I’m going to show you how to setup the same unit test, except I will not be using the person table. Why would I do this? In or previous “toy” example, there is one stored procedure that calls one table and selects data. Very simple. In a real database or a legacy system, there will be stored procedures that call dozens of tables containing data that is related to each other and possibly tables that are not contained in the stored procedure. You might not need to setup all that data just to test the method you want to test. Realistically, your method only sees the results from your stored procedure depending on variables that it passes to that procedure.
What I’m going to do in this unit test setup is I’m going to create a fake unit test that is named the same as the unit test that the method under test expects. Except I’m going to fake the output with a simple select statement that uses no tables. Then I’m going to execute the method and see if my results are as expected. Then in another unit test, I will create a new fake stored procedure with the same name (by dropping any currently created stored procedures in the database and replacing it).
Use the solution created in the sample above. First, add a directory in your unit test project and name it “TestData”, then add two text files to this directory named “TestFakeSproc1.sql” and “TestFakeSproc2.sql”. You’ll need to select these two files and then in the properties window, set the files to “Embedded Resource”:
This will ensure that the test data that you are going to create will be embedded in the unit test dll. This will prevent any directory issues that you’ll encounter if you’re using an automated deployment product like TeamCity.
Now, add some SQL to the first sql file:
USE [sampledata]
GO
IF exists (SELECT * FROM sys.objects WHERE name = N'ReadPersonNames' AND type = N'P')
BEGIN
DROP PROCEDURE ReadPersonNames
END
GO
CREATE PROCEDURE [dbo].[ReadPersonNames]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
'Lisa' AS first,
'Smith' AS last
WHERE
1 = 1
END
GO
Now add the first unit test method:
[TestMethod]
public void TestFakeSproc1()
{
UnitTestHelpers.ExecuteSQLCode("UnitTestPersonSelector.TestData.TestFakeSproc1.sql");
var personSelector = new PersonSelector();
DataSet data = personSelector.ReadNames();
Assert.AreEqual(1, data.Tables[0].Rows.Count);
Assert.AreEqual("Lisa", data.Tables[0].Rows[0]
["first"].ToString());
}
The full pathname to an embedded resource will be the namespace followed by the subdirectory name followed by the file name. If you don’t get this right, then you’ll get an error at the streamreader portion of the ExecuteSQLCode() method.
In this unit test, the stored procedure that is created will just return one row of data that is hard-coded. No tables were read and the method will receive exactly one row of data no matter what the inputs are. The point of this unit test is to test the method itself given the pre-planned data that will be produced by the stored procedure. Now let’s do another fake stored procedure just to show how this works for multiple unit tests.
Inside TestFakeSproc2.sql, copy this text:
USE [sampledata]
GO
IF exists (SELECT * FROM sys.objects WHERE name = N'ReadPersonNames' AND type = N'P')
BEGIN
DROP PROCEDURE ReadPersonNames
END
GO
CREATE PROCEDURE [dbo].[ReadPersonNames]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
'Lisa' AS first,
'Smith' AS last
UNION
SELECT
'Joe' AS first,
'Cool' AS last
UNION
SELECT
'Adam' AS first,
'West' AS last
WHERE
1 = 1
END
GO
Now create this unit test:
[TestMethod]
public void TestFakeSproc2()
{
UnitTestHelpers.ExecuteSQLCode("UnitTestPersonSelector.TestData.TestFakeSproc2.sql");
var personSelector = new PersonSelector();
DataSet data = personSelector.ReadNames();
Assert.AreEqual(3, data.Tables[0].Rows.Count);
}
This method will count the number of rows as 3. You can setup your select statement to return anything you want to simulate.
Download the Sample Code
You can go to my GitHub account and download the entire functional unit testing sample by clicking here.