Summary
In this post, I’m going to show how to use a stored procedure in a SQLLocalDB MS Test setup. Technically this is an integration test, so excuse the fact that I might call it a unit test out of habit.
The Technical Details
What I’m going to do is copy the code from a SQL Server stored procedure, and paste it into a class/method string or property. Next I’m going to write a unit test helper method that will create the stored procedure in my SQLLocalDB test instance. Finally, I’m going to run a unit test that executes a method containing a call to the stored procedure and verify it worked correctly.
If your familiar with stored procedures and unit tests, you’re probably scratching your head right now and thinking “what would be the purpose.” My purpose in accomplishing this task is to get legacy code into a test harness so I can refactor my code and replace ugly stored procedures with LINQ code, or possibly clean up any stored procedures I’m forced to keep.
Now you’re thinking that if I change a stored procedure, my unit test will still work, even though my run-time code might not. That’s a problem. However, I have created a map class generator for NHibernate. What I plan to do is enhance this generator to scrape the text out of the stored procedures in a database and put them into my project containing all my ORM mappings. That way, I can regenerate my class mappings and synchronize the stored procedures all at once. This will make sure that my unit tests are always using the same stored procedures that are defined in the database. This will be demonstrated in a future blog post.
The Session Factory
I’m going to start by demonstrating how you can modify your session factory to make it execute in dual mode. Which mode is used will depend on the calling assembly. If the calling assembly is from a unit test assembly, then we’ll use the SQLLocalDB context. If the calling assembly is not a unit test assembly, then we’ll assume that we’re supposed to connect to the real database. I have commented the session factory code from the code shown below, you can download the demo application to see the real code.
public class MSSQLSessionFactory
{
private static ISessionFactory _sessionFactory;
private static ISessionFactory SessionFactory
{
get
{
if (_sessionFactory == null)
{
if (UnitTestHelpers.UnitTestHelpers.IsInUnitTest)
{
// unit test context
}
else
{
// production context
}
}
return _sessionFactory;
}
}
public static ISession OpenSession()
{
return SessionFactory.OpenSession();
}
}
The code in my demo could use some folding and refactoring, but this is just to demonstrate the concept. So I’m keeping it simple for now.
The Stored Procedure
I scraped the stored procedure code from my database and pasted it into a string. Eventually, I’ll need a method that can do that for me. The code for this object looks like this:
public class StoredProcedureObjects
{
public string Name { get { return "pSetClassForTeacher"; } }
public string Code {
get
{
return @"USE [facultydata]
GO
/****** Object: StoredProcedure [dbo].[pSetClassForTeacher] Script Date: 8/9/2014 6:06:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pSetClassForTeacher]
@TeacherId int,
@ClassId int
AS
BEGIN
SET NOCOUNT ON;
UPDATE class SET teacherid=@TeacherId WHERE id=@ClassId
END";
}
}
public void CreateStoredProcedure(string databaseName)
{
SqlConnection db = new SqlConnection(
"server=(localdb)\" + databaseName +
"testinstance;" +
"Trusted_Connection=yes;" +
"database=" + databaseName + "; " +
"Integrated Security=true;");
// first, drop the stored procedure if it already exists
db.Open();
string sp = @"if exists (select * from sys.objects
where name = N'" + Name + @"' and type = N'P')
begin
drop procedure " + Name + @"
end";
SqlCommand myCommand = new SqlCommand(sp, db);
myCommand.ExecuteNonQuery();
// need to read the text file and create the stored
// procedure in the test database
string[] TSQLcommandList = Regex.Split(Code, "GO");
foreach (var tsqlCommand in TSQLcommandList)
{
myCommand = new SqlCommand(tsqlCommand, db);
myCommand.ExecuteNonQuery();
}
db.Close();
}
}
First, you should notice that we need to split the stored procedure query by the “GO” statements. Technically the sql code above is a collection of separate queries and we need to execute these individually.
Next, I have taken the liberty of changing the “ALTER PROCEDURE” to “CREATE PROCEDURE”. Later, I’m going to do a string replace when I generate the stored procedure text while reading it from the database.
Last, I have written code to drop the stored procedure first, if it already exists. Technically, we would like to create all the stored procedures in the database at assembly start time and just use them for all tests. There should be no expectation that a unit test will be changing while unit tests are in progress, so there’s no need to delete and create stored procedures for each unit test.
The Unit Test
The unit test method looks like this:
[TestMethod]
public void test_stored_procedure_object()
{
StoredProcedureObjects sp = new StoredProcedureObjects();
sp.CreateStoredProcedure("facultydata");
using (var db = MSSQLSessionFactory.OpenSession())
{
var teacher = new Teacher
{
name = "Smith"
};
db.Save(teacher);
teacher = new Teacher
{
name = "Johnson"
};
db.Save(teacher);
string[] classList = new string[] { "MTH 100",
"CHM 101", "ENG 200", "PHY 230"};
foreach (var aClass in classList)
{
var MyClass = new Class
{
name = aClass,
teacherid = 1
};
db.Save(MyClass);
}
db.Flush();
}
TeacherRecordObjects teacherRecordObjects = new
TeacherRecordObjects();
teacherRecordObjects.SetClassForTeacher(2,3);
using (var db = MSSQLSessionFactory.OpenSession())
{
var query = (from c in db.Query<Class>()
where c.id == 3
select c).FirstOrDefault();
Assert.AreEqual(2, query.teacherid);
}
}
The first thing this unit test does is create the stored procedure. Next I have some insert queries to insert two teachers and 4 classes. This is my test data setup. Then I call the SetClassForTeacher method with a parameter that I can check for after the method executes. Last, I open another database context and read the results and test for equality.
Summary
There are a lot of pieces necessary to making this work properly. You need to be able to check which project called the context and select the correct database context. You need to be able to create a stored procedure in SQLLocalDB. You need to be able to truncate data in your SQLLocalDB after your unit test has concluded (so your results don’t mess up the next unit test). Finally, you need to handle the case where someone will try and create a stored procedure from another unit test without realizing that it already exists.
You can download the sample code here: