Testing SQL Stored Procedures using LINQ

LINQ (Language Integrated Query) is new set of technologies that ship with the .NET Framework 3.5 (which is included as part of Visual Studio 2008) which allow you to access and manipulate data sources directly from a .NET language in a way that is consistent across different data sources. I’ve been looking at LINQ and have discovered that using LINQ is a great way to test SQL stored procedures. For example, suppose you have a database named dbMovies which contains a stored procedure named usp_GetMoviePrice. The stored procedure accepts a movie ID as input and returns the price of the movie. Testing such a stored procedure using normal ADO.NET techniques is possible but somewhat awkward. But here’s a glimpse of how easy the task becomes using LINQ:
Console.WriteLine("\nBegin experiment");
var dbc = new dbMoviesDataContext();
decimal? actualPrice = null;
string movieID = "002";
decimal expectedPrice = 22.22M;
dbc.usp_GetMoviePrice(movieID, ref actualPrice);
if (actualPrice == expectedPrice)
Console.WriteLine("\nEnd experiment");
Very, very clean compared to alternate techniques. I am writing up an article which fully explains testing SQL stored procedures using LINQ for Microsoft’s MSDN Magazine, and the article should appear in my Test Run column (http://msdn.microsoft.com/msdnmag/find/default.aspx?phrase=Test%20Run) in early 2008. 
This entry was posted in Software Test Automation. Bookmark the permalink.