I have inherited a Web API 2 project written in C#/.NET that uses ADO.NET to access an SQL Server database.
The data access layer of the project contains many methods which look similar to this:
public class DataAccessLayer
{
private SqlConnection _DBConn;
public DataAccessLayer()
{
_DBConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
}
public string getAllProductsAsJSON()
{
DataTable dt = new DataTable();
using (SqlConnection con = _DBConn)
{
using (SqlCommand cmd = new SqlCommand("SELECT productId, productName FROM product ORDER BY addedOn DESC", con))
{
cmd.CommandType = CommandType.Text;
// add parameters to the command here, if required.
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return JsonConvert.SerializeObject(dt);
}
}
}
// ... more methods here, but all basically following the above style of
// opening a new connection each time a method is called.
}
Now, I want to write some unit tests for this project. I have studied the idea of using SQL transactions to allow for insertion of mock data into the database, testing against the mock data, and then rolling back the transaction in order to allow for testing against a "live" (development) database, so you can have access to the SQL Server functionality without mocking it out completely (e.g. you can make sure your views/functions are returning valid data AND that the API is properly processing the data all at once). Some of the methods in the data access layer add data to the database, so the idea is that I would want to start a transaction, call a set of DAL methods to insert mock data, call other methods to test the results with assertions, and then roll back the entire test so that no mock data gets committed.
The problem I am having is that, as you can see, this class has been designed to create a new database connection every single time that a query is made. If I try to think like the original developer probably thought, I could see how it could make at least some sense to do this, considering the fact that these classes are used by a web API, so a persistent database connection would be impractical especially if a web API call involves transactions, because you then do need a separate connection per request to maintain separation.
However, because this is happening I don't think I can use the transaction idea to write tests as I described, because uncommitted data would not be accessible across database connections. So if I wrote a test which calls DAL methods (and also business-logic layer methods which in turn call DAL methods), each method will open its own connection to the database, and thus I have no way to wrap all of the method calls in a transaction to begin with.
I could rewrite each method to accept an SQLConnection as one of its parameters, but if I do this, I not only have to refactor over 60 methods, but I also have to rework every single place that such methods are called in the Web API controllers. I then have to move the burden of creating and managing DB connections to the Web API (and away from the DAL, which is where it philosophically should be).
Short of literally rewriting/refactoring 60+ methods and the entire Web API, is there a different approach I can take to writing unit tests for this project?
EDIT: My new idea is to simply remove all calls to con.Open(). Then, in the constructor, not just create the connection but also open it. Finally, I'll add beginTransaction, commitTransaction and rollbackTransaction methods that operate directly upon the connection object. The core API never needs to call these functions, but the unit tests can call them. This means the unit test code can simply create an instance, which will create a connection which persists across the entire lifetime of the class. Then it can use beginTransaction, then do whatever tests it wants, and finally rollbackTransaction. Having a commitTransaction is good for completeness and also exposing this functionality to the business-logic layer has potential use.
