Test run: Using LINQ to test SQL stored procedures

It is common to test programs that access and manipulate back-end SQL server® databases. In such cases, the application basically interacts with the backend data by using SQL stored procedures. At this point, you can consider the stored procedure as an auxiliary method for the system being tested, and therefore must test it as any other module in the system.

Although there are a number of methods to use when testing SQL stored procedures, I find that using LINQ can greatly simplify test automation. In this month's column, I'll show you how to test SQL stored procedures using LINQ, especially with LINQ to SQL providers. I'll assume that your C # and SQL skills are intermediate, but you don't have LINQ experience.

If you look at Figure 1, you'll find that I've written a small C # console application Test tool that shows the important actions that you have to perform when you test your SQL stored procedures. I'm going to test a simple but representative stored procedure called usp_DeleteMovie, which resides in a database named Dbmovies that stores movie-related data.

Figure 1 Testing a Stored Procedure Using LINQ

For each test instance, I initialize a test platform database, invoke usp_DeleteMovie with a special movie ID, and then check that the Dbmovies's result status is correct. It is not clear from figure 1 that I am using LINQ, and it is easier to use it to create test tools than using Ado.net and T-SQL. In the following sections, I will describe the virtual Dbmovies database and the usp_DeleteMovie stored procedures to be tested. I'll also show you the code and explain the LINQ techniques used, and then show you how to modify and extend my example to meet your needs.

Pending test System

Before delving into the tools, let's look at the sample database of the test system so that you understand test automation. In a common development environment, developers, testers, and database architects often use their own local back-end database replicas. Therefore, suppose you have access to the SQL script that created the current version of the database to be tested. The actual database script can be very long and complex, but the sample database creation script in Figure 2 is very simple, which is to highlight the key concepts.