MSDN Magazine - April 2008 - (Page 99)

Testing SQL Stored Procedures Using LINQ Dr. JameS mccaffrey he need to test a program that accesses and manipulates a backend SQL Server® database is very common. In many such cases, the application interacts with the back-end data through the use of SQL stored procedures. In this type of scenario, you can think of the stored procedures as auxiliary methods of the system under test; and they must therefore be tested just like any other module in the system. Although there are several approaches you can take when testing SQL stored procedures, I have discovered that using LINQ dramatically simplifies test automation. In this month’s column, I show you how to test SQL stored procedures using LINQ—specifically, using the LINQ to SQL provider. I’ll assume you have intermediate level C# and SQL skills but no LINQ experience. If you examine Figure 1, you’ll see that I have written a small C# console application test harness that demonstrates the key activities you must perform when testing SQL stored procedures. I am testing a simple but representative stored procedure named usp_ DeleteMovie, which resides in a database of movie-related data named dbMovies. For each test case, I initialize a test bed database, call usp_DeleteMovie with a particular movie ID, and then check to see if the resulting state of dbMovies is correct. What is not apparent in Figure 1 is that I am using LINQ, which makes it easier to create my test harness than it is when I use ADO.NET and T-SQL. In the following sections, I will describe the dummy dbMovies database and usp_DeleteMovie stored procedure I am testing. I’ll also show you the code and explain the LINQ techniques used, and then I’ll explain how you can modify and extend my examples in order to meet your own needs. Before digging into the harness, let’s look at the sample database of the system under test so that you’ll understand the test automation. In a typical development environment, developers, testers, and database architects usually work with their own local copies of the back-end database. So, let’s assume that you have access to a SQL script that creates the current version of the database under test. A realistic database script would be long and complex, but the sample database creation script in Figure 2 is simple in order to keep the main concepts clear. My database is named dbMovies and contains two tables, tblMain and tblPrices. The tblMain table has a movie ID column (which is T the primary key), a title column, and a column that stores the duration of the movie (in minutes). The tblPrices table has a movie ID column and a column that stores the price of the movie. My database has three stored procedures: usp_DeleteMovie, usp_ GetMovieDataByPrice, and usp_GetMoviePrice. I preUsing LINQ dramatically cede the names of my stored simplifies test automation. procedures with usp_ to indicate that these are userIt is easier to create a test defined stored procedures harness using LINQ than it is as opposed to system stored using ADO.NET and T-SQL. procedures (sp_) or extended stored procedures (xp_). The points of note are that usp_DeleteMovie changes the state of the database but does not explicitly return a value; usp_GetMovieDataByPrice returns one or more SQL rowsets but does not change the state of dbMovies; and usp_GetMoviePrice returns two scalar values (one by a return value and one by an out-type parameter) but does not change database state. These represent the three most common situations when testing The System under Test Figure 1 Testing a Stored Procedure Using LINQ april2008 99

Table of Contents Feed for the Digital Edition of MSDN Magazine - April 2008

MSDN Magazine - April 2008 Contents Toolbox CLR Inside Out Basic Instincts Cutting Edge Foundations Test Run Service Station Windows with C++ Going Places { End Bracket }

MSDN Magazine - April 2008

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.