Every developer needs to test their own code or have it tested by someone else. Many developers aren’t great at testing their own work. The main reason is that we tend to test only the "happy path" through the functionality that we wrote. We often avoid testing the boundaries of our code, such as invalid inputs, exceptions that might occur, etc. One way to become a better tester is to start writing unit tests. Although it takes more time up-front to write unit tests, it saves a ton of time if you have to regress test changes to existing features.

Starting with Visual Studio 2008, Microsoft added a unit testing framework into Visual Studio. One of the great features of the unit testing tools in Visual Studio is the ability to use a data-driven approach. There are also several third-party testing frameworks you can use. In this article, you’ll to learn to retrieve values to be used for testing from SQL Server. Using a data-driven approach can significantly reduce the unit tests you must write.

Why You Should Test Your Code

We all know that we need to test code prior to putting it into production. Testing ensures that the code you write works as expected. It’s not enough to check that it works as expected, but you must also check it under varying circumstances and with different inputs. For example, what if someone takes the database offline? Will your code recover gracefully from this exception? Does your code inform the user that there’s a problem with the database connection in a friendly and easily understood manner? All of these questions need to be answered in the testing of your code. You need to simulate these conditions so you can test your exception code.

Performing the act of testing often helps you improve the quality of your code. As you think about the various scenarios that can go wrong, you start to add additional code to handle these scenarios. This leads to your code being more robust and ultimately more maintainable and user-friendly. You’ll find that taking time to test your code will make you a better programmer in the long run. Your boss and your end-users will appreciate the extra effort as well.

Automated Testing

Instead of you or a QA person doing all the testing, try to automate as much as possible, called unit testing. Unit testing means that you write a program, or more likely, a series of programs, to test each line of code and ensure that it operates properly under all circumstances. Unit testing has become very prevalent in today’s professional software shops.

Although this sounds like a lot of work, and it is more work up front, you’ll more than make up that time by avoiding multiple regression tests that have to be done by a human. You can even automate the set-up and tear-down of databases, files, etc. With the correct architecture, you can automate almost all of the various inputs a human tester would have to enter by hand.

You also save the time that you normally eat up when you do your own testing by pressing F5 to start debugging in Visual Studio, waiting for the compile process, and clicking through a half dozen menus to finally get to the point where you want to start testing. As you know, this can sometimes take 15 seconds to a minute, depending on how large your application is. By unit testing instead, you can run many tests in just a couple of seconds. This adds up to saving many hours over your complete development cycle.

Automated tests are repeatable, unlike tests run by humans who might forget to test something. You end up with more of your code tested because things won’t be forgotten. Because you’re forced to think of how to test your code while you’re writing it, you write better code. You also save time on the set-up and the tear down of the tests because these tasks can also be automated. As you can see, there are many advantages to an automated approach over human testing.

Of course, there are disadvantages to the automated approach as well. First, it does take more time up-front to develop these tests. Automated tests are only as good as the person who develops the tests. The tools to test user interfaces are not always great, and they require that you purchase third-party testing tools. Then again, if you’re using good n-tier techniques, MVC, MVP, MVVM, or similar design patterns, there should be very little UI for you to test anyway.

The FileExists Method

For this article, you’re going to build a method that checks to see whether a file exists. You’re then going to build unit tests to check each type of input that you can pass to this method. Next, you’re going to replace all of those unit tests with a single unit test that retrieves the various inputs from a database table.

To start, create a new Class Library project in Visual Studio using C#. Set the name of this class library project to MyClasses. Rename the Class1.cs file created by Visual Studio to FileProcess. Add a method in this class called FileExists, as shown in the following code snippet:

public bool FileExists(string fileName) {
  if (string.IsNullOrEmpty(fileName)) {
    throw new ArgumentNullException("fileName");
  return File.Exists(fileName);

This is a very simple method, yet it requires at least three unit test methods to ensure that this method works with all possible inputs. The three possible values you can pass to the fileName parameter are:

  • A file name that exists
  • A file name that does not exist
  • A null or empty string

Create a Test Project

Right-mouse click on your MyClasses solution and choose Add -> New Project. From the list of templates, click on the Visual C# -> Test -> Unit Test Project. Set the Name to MyClassesTest. Click the OK button. Rename the UnitTest1.cs file to FileProcessTest.cs. You’re going to test the method in your MyClasses class library, so you need to add a reference to that project. Right-mouse click on the References folder in the MyClassesTest project and select MyClasses. Add the following using statement at the top of the FileProcessTest.cs file.

using MyClasses;

It’s now time to start writing the unit tests to create each of the three possible inputs identified for this method. The first one is to test that a file exists. Add the code shown below to the FileProcessTest class. Feel free to change the drive letter, path, and file name to a file that exists on your computer.

public void FileExistsTestTrue() {
  FileProcess fp = new FileProcess();
  bool fromCall;
  fromCall =
  Assert.AreEqual(true, fromCall);

After adding this code, right-mouse click in your code window and choose Run Tests from the context-sensitive menu that appears. After the code runs, a Test Explorer window appears with the results of the test. If the file exists, the window should display something that looks like Figure 1.

Figure1: Test results appear in the Test Explorer window.

The next method to write tests for a file that doesn’t exist. Create a method in your FileProcessTest class to test this condition. Write the code shown in the following code snippet.

public void FileExistsTestFalse() {
  FileProcess fp = new FileProcess();
  bool fromCall;
  fromCall =
  Assert.AreEqual(false, fromCall);

Once again, run these tests and you should now see two passed tests in your Test Explorer window. Add the final test to the FileProcessTest class to test if you pass in a null value or a blank value to the FileExists method. An ArgumentNullException is thrown from the FileExists method if a null or blank value is passed to the method. There are two ways to handle this thrown exception. First, add an ExpectedException attribute after the TestMethod attribute on the method, as shown in the following code snippet.

public void FileExistsTestNullWithAttribute() {
  FileProcess fp = new FileProcess();

The second way to handle this exception is to wrap up the call to the FileExists method within a try…catch block. The catch block should check to see if the exception is an ArgumentNullException. If it is, don’t do anything to tell the unit test framework that this test succeeded. If no exception is thrown, there’s something wrong with the logic and you should call the Assert.Fail() method to signal to the unit test framework that this test failed. Let’s use this second method because this is what you’ll do when you go to a data-driven approach.

public void FileExistsTestNull() {
  FileProcess fp = new FileProcess();
  try {
  catch (ArgumentNullException) {
          // The test succeeded

Run the unit tests one more time, and you should now see three passed tests in the Test Explorer window. Using this approach to testing means that you need to create different methods for each individual file and return value combination that you wish to test. Extrapolate this to a method that has several parameters, multiple If statements or Switch statements, and you can see how the number of methods you must write can grow substantially. This is where a data-driven approach to passing parameters to your methods reduces the amount of test methods you need to create.

Create a Data-Driven Test

Now that you have a table, let’s create a test method that can be fed data from the FileProcessTest table you created previously. There are a few steps to get the data-driven test working.

  1. Create a table to hold parameter data.
  2. Add a reference to System.Data to your test project.
  3. Add a TestContext property to your test class.
  4. Add a DataSource attribute to your test method.
  5. Add a config file to hold a connection string and table name to use.

Create a Table to Hold Test Data

Because you created three test methods, you're going to need a table with three rows, one for each test. Each test passes one value, and returns a Boolean value, so the table needs at least two fields: FileName and ReturnValue. Add one more column named CausesException that you can set to a true or false value on whether you are expecting an exception to be thrown by the method you are testing. Create this table in a database with the script shown in Listing 1.

The data in this table that was created from the script matches the hard-coded values in the three-unit test methods. Table 1 shows the values that are now in your table. Feel free to use any specific file names and return values that make sense for your computer.

Add Properties and References

When the unit test framework creates an instance of a test class (a class marked with a [TestClases] attribute), it creates a TestContext object. This object contains properties and methods related to testing. One of the properties stores the data source information to allow you to access each row in the table you specify for the test. To access this TestContext object, you must create a property named TestContext in each of your test classes.

private TestContext _TestInstance;
public TestContext TestContext
    get { return _TestInstance; }
    set { _TestInstance = value; }

A DataTable is created from the table you create and a DataRow property is exposed on your TestContext property. In order to use this property, you must add a reference to the System.Data.dll. Go ahead and right-mouse click on your test project’s References folder and select Add Reference… from the menu. Then select Assemblies -> Framework, locate the System.Data assembly, and add it to your project.

Create Data-Driven Test Method

Add a new method to your FileProcessTest class that looks like the following code snippet. Don’t split the connection string across multiple lines, though; I had to format it to fit within the column width of this article. You also need to modify the name of your server and the database in which you created the FileProcessTest table.

   Integrated Security=SSPI",
public void FileExistsTestFromDB() {

Besides the [TestMethod] attribute, this method has a new attribute called [DataSource]. This attribute allows you to specify a data provider, a connection string, the name of the table in the data source, and how to loop through the records in the table.

The unit testing framework uses the information passed to the DataSource attribute to build a DataTable of the rows within the table specified in the DataSource. The framework begins looping through each row and for each row, sets the DataRow property and then calls your test method. You now retrieve the appropriate columns you need for your test within your method. Listing 2 shows the complete code for the FileExistsTestFromDB() method.

In the FileExistsTestFromDB method, you retrieve the values from the column using standard ADO.NET syntax. The next code snippet shows retrieving each of the columns you created in the table.

fileName = TestContext.DataRow["FileName"]
returnValue = Convert.ToBoolean(
causesException = Convert.ToBoolean(

After retrieving the values, you now call the Assert.AreEqual() method to compare the return value for the current row with the value returned from calling the FileExists() method, passing in the file name retrieved from the current row. If an exception occurs, it could be caused by two reasons. The first is that the Assert.AreEqual method raises an exception of the type AssertFailedException to inform the unit testing framework that this test failed. The second reason is that the FileExists method raises an exception in response to a bad file name being passed in. If this latter situation is the case, you need to check the value you retrieved from the column CausesException in the current row. If it’s a true value, the test succeeded; otherwise, the Assert.IsTrue causes an exception of the type AssertFailedException, which again, informs the unit test framework that the test failed.

Move the Connection String to a Config File

Just like any other class, a test class like FileProcessTest should follow the same best practices you would normally employ for your application code. In the DataSource attribute in Listing 2, there’s a hard-coded connection string and table name. This information is best put into a configuration file to make it easy to change. Microsoft gave us the flexibility to accomplish this. Create an App.config file in the test project and add the items within the <configuration> section shown in Listing 3. NOTE: The type= attribute should all be on one line. It is broken into multiple lines to format for this article.

Once this configuration file has been created, modify the DataSource on your FileExistsTestFromDB method to look like that shown in the code snippet below. The DataSource attribute is now a simple reference to the name attribute within the <dataSources> collection. You may have as many different data sources as needed by your tests.

public void FileExistsTestFromDB()

You should now be able to run your three unit tests from the information contained in your data table. Comment out the original tests you wrote in this article so the only test method left is the one with the DataSource attribute. Run the tests to ensure that you have everything configured correctly.

Architect Your Code for Testing

Correctly architecting an application will do wonders for re-usability, readability, upgradeability, maintainability, and testability. Take advantage of the design patterns that exist today, such as MVC, MVP, MVVM, or derivatives thereof. All of these patterns help you remove code from the user interface layer of your application. Removing code from the UI layer is the single best thing you can do to have a well architected, easily testable application.

Removing code from the UI layer is the single best thing you can do to have a well architected, easily testable application.

Your UI code should strive to just call or bind to properties and methods in classes. Each class you write should contain all of the logic for your application. By moving all application logic out of the UI and into classes, you make it easy to use the various unit testing tools in Visual Studio. Furthermore, these classes should be combined into assemblies to aid not only in testing, but also in re-usability.


If you find that you’re writing many different unit tests to test a single method that has a lot of inputs and outputs, the data-driven approach outlined in this article may be just what you are looking for. I recommend using a separate database for all your test tables. I also like creating a schema with the name of "tests," as I did in this article. Be sure to put all of your connection strings and data source names in an App.config file for the ultimate in flexibility. Good luck with your testing!