NUnit Test Data in CSV or Excel

     I'm always trying to find ways to build what I call "lightweight data driven tests" and I keep coming back to Excel or CSV as a way to accomplish this quickly.  Why? Because, from thought to execution you can quickly get a large number of tests completed while keeping them easy to maintain. Data driven testing is much like gold mining, some people spend a lot of money and only find dirt others get rich quick and everything between that.  The best case for this Pattern is when you want a third party to take some degree of ownership of the data, a PM, manual QA, customer service, Technical Account manager, Sales Engineer, or other. The example below will be in CSV in case you don't actually have Excel installed. But that won't matter because Excel and CSV are basically the same in code, Excel is just easier to work with.

     First off here is a sample test "TestCsvData" and a TestCaseSource "CSVDATA" that calls the "TestDataReader" class's "ReadCsvData" method to get its test data.

[TestCaseSource("CSVDATA")]
public void TestCsvData(string a, string b, string c)
{
    Assert.AreEqual(int.Parse(a) + int.Parse(b), int.Parse(c));
}

public IEnumerable<TestCaseData> CSVDATA
{
    get
    {
        List<TestCaseData> testCaseDataList = new TestDataReader().ReadCsvData(@"Data\CSVData.csv");
        if (testCaseDataList != null)
            foreach (TestCaseData testCaseData in testCaseDataList)
                yield return testCaseData;
    }
}

     Next we just need to make an OleDbConnection to the file and select the rows we want to use. For a CSV file the table is the file name and for a Excel file the spreadsheet is the table. The first row is treated as the column names, if you don't want to return all columns as test data then change the select statement .

class TestDataReader
{
    public List<TestCaseData> ReadCsvData(string csvFile, string cmdText = "SELECT * FROM [{0}]")
    {
        if (!File.Exists(csvFile))
            throw new Exception(string.Format("File name: {0}", csvFile), new FileNotFoundException());
        var file = Path.GetFileName(csvFile);
        var pathOnly = Path.GetFullPath(csvFile).Replace(file, "");
        var tableName = string.Format("{0}#{1}", Path.GetFileNameWithoutExtension(file), Path.GetExtension(file).Remove(0,1));

        cmdText = string.Format(cmdText, tableName);
        var connectionStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Text;HDR=Yes\"", pathOnly);
        var ret = new List<TestCaseData>();
        using (var connection = new OleDbConnection())
        {
            connection.ConnectionString = connectionStr;
            connection.Open();

            var cmd = connection.CreateCommand();
            cmd.CommandText = cmdText;
            var reader = cmd.ExecuteReader();

            if (reader == null)
                throw new Exception(string.Format("No data return from file, file name:{0}", csvFile));
            while (reader.Read())
            {
                var row = new List<string>();
                var feildCnt = reader.FieldCount;
                for (var i = 0; i < feildCnt; i++)
                    row.Add(reader.GetValue(i).ToString());
                ret.Add(new TestCaseData(row.ToArray()));
            }
        }
        return ret;
    }

    public List<TestCaseData> ReadExcelData(string excelFile, string cmdText = "SELECT * FROM [Sheet1$]")
    {
        if (!File.Exists(excelFile))
            throw new Exception(string.Format("File name: {0}", excelFile), new FileNotFoundException());
        string connectionStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", excelFile);
        var ret = new List<TestCaseData>();
        using (var connection = new OleDbConnection(connectionStr))
        {
            connection.Open();
            var command = new OleDbCommand(cmdText, connection);
            var reader = command.ExecuteReader();
            if (reader == null)
                throw new Exception(string.Format("No data return from file, file name:{0}", excelFile));
            while (reader.Read())
            {
                var row = new List<string>();
                var feildCnt = reader.FieldCount;
                for (var i = 0; i < feildCnt; i++)
                    row.Add(reader.GetValue(i).ToString());
                ret.Add(new TestCaseData(row.ToArray()));
            }
        }
        return ret;
    }
}

So why Excel? Its just easier than anything else.... 

• Excel was built for this type of data manipulation 
• Excel has lots of functionality to help you get work done quick such as pattern copy
• Using a file instead of DB makes the test suite more accessible to non-technical people
• SQL will require a lot more time to develop
• SQL will require a lot more time to maintain
• CSV not very good readability or data entry 

Generic methods like these return all the data as string parameters so its up to the test method to correctly type the data.

In the past I have had a hard time convincing people of the benefits of testing this way, I'll do my best here to allay their concerns and yours.
Concern: Putting the test data in Excel or CSV will somehow make the data become irrelevant especially over time. I think they have the perception that having the data in a file external to the test class will cause a data issue or slow down the time to find and fix issues with the data.
Answer: False, the data will become irrelevant no matter where you store it, so focus on changing/fixing it as soon as it happens regardless of data location.
Concern: By putting the test data in Excel or CSV the data will lose visibility, meaning the PM's, managers or manual testers won't have visibility to the data.
Answer: False, we are talking about moving the data from source code to Excel or CSV file and it will most likely be in an adjacent folder. That means the data is going to be in the same source control as before so no access control will change. Furthermore, you might grant write permissions to manual testers on an excel file to add more test cases where you won't want them touching source code.
Concern: Shouldn't we put this in SQL, reasons: (reporting, visibility, easy data entry, etc)
Answer: False, if you put this data in SQL you need to build a data access layer and this will make changing the data more difficult and rigid. Also, if you don't already have one, using SQL now will add a new dependency on SQL.  Rule of thumb SQL is great for enterprise software but not at all lightweight.
Concern: Excel or CSV  will not scale to meet the data needs, or will have poor performance.
Answer: If you have this problem with your test data, you have other more important problems. I believe if you could actually find enough relevant tests to make Excel or CSV perform poorly enough to bother you then you hit a testing gold mind.

Best use of webDriver.Quit() Close() Dispose() or it will eat your hard drive

Recently I found that some of VM's I share with coworkers were running out of hard drive space, I was surprised to find that the reason was webDriver.Quit() wasn't being called. My cohorts were running code or NUnit tests in the debugger and forcing the code execution to stop. Stopping execution this way was ensuring that [TearDown] and [TestFixtureTearDown] weren't being called, which is where we had placed the Quit() call. To put this in perspective one of my cohorts had over 18 Gb of data in his appData folder because of this.

What to do when done with the WebDriver
It's important to make sure to free up the driver at the end of the run by calling the Quit() method. Calling the Quit method will clean up temporary files created and in the case of RemoteDriver it will also close the session on the Selenium Server.

I Did some Googleing and didn't really find an answer that I thought I could trust, so I turned to the source code and found the following:

• webDriver.Close() - Close the browser window that the driver has focus of
• webDriver.Quit() - Calls dispose
• webDriver.Dispose() Closes all browser windows and safely ends the session

In summary ensure that Quit() or Dispose() is called before exiting the program, and don't use the Close() method unless you're sure of what you're doing. I found this unanswered question on StackOverflow

What happens if you don't Call WebDriver.Quit() before exiting the program
The result will be that files are abandoned in your appData folder. And in the case of the remote driver the Selenium Server will not have ended the session properly, causing a memory leak in the Selenium Server.

I wouldn't have guessed this to be a problem for several reasons, first I would have thought those files would be cleaned up when doing a disk clean up. That assumption was incorrect even though these files are being written to a temp folder its one that Disk cleanup doesn't work on. Second as I rarely hit the "Stop" button in the debugger and therefore my appData folders aren't blowing up like the housing market. The reason I don't like to stop execution like that is when you let execution continue it ensures we get a NUnit log and all teardowns take place, in our case that means un-managed objects are cleaned up screenshots and logs are written. Having these logs and screenshots make it easier to troubleshoot and do investigation.

Test Pattern, Given

The Idea was to simplify validation of dependent functionality and is really useful if your already following the Exception over Assert pattern.
The concept is to use the similar comparisons to Assert to reduce the amount of code written in validation. I didn't want to use Assert for this because any failed assertion will result in a failed test.

Below I have two tests that do the same thing, change a users name in a system.  If either test cannot login as the user the test will error (not fail), and if either test cannot change the users name the test will fail. In the first test we check the return value of the LoginAs() method and if its not true we throw a new exception. Int he second test we Given the same way we would use Assert and it validates the return value of  the LoginAs() method and it raises an exception if the condition doesn't pass. It's also quicker to write and easier to read.

Code:

[Test]
public void ChangeUserName_Exception()
{
    if(!LoginAs("Rick"))
        throw new Exception("Login failed as user");
    Assert.That(ChangeUserName("Rick Casady"));
}
 
[Test]
public void ChangeUserName_Given()
{
    Given.That(LoginAs("Rick"));
    Assert.That(ChangeUserName("Rick Casady"));
}

Here is a basic version of the Given class, it's good to have other comparison functions found in the Asset class like (NotNull(), AreEqual(), AreNotEqual(), ...)

Code:

public class Given
{
    static public void That(bool condition)
    {
        Given.That(condition, Is.True, null, null);
    }

    static public void That(object actual, IResolveConstraint expression, string message, params object[] args)
    {
        Constraint constraint = expression.Resolve();

        if (!constraint.Matches(actual))
        {
            MessageWriter writer = new TextMessageWriter(message, args);
            constraint.WriteMessageTo(writer);
            throw new Exception(writer.ToString());
        }
    }
}

Constraint and IResolveConstraint can be found in NUnit.Framework.Constraints

Functional Test Pattern, Exception over Assert

The Idea is that a test should only result in a fail status if the functionality being tested isn't working and if the dependent functionality inst working it should only result in a error status. You can achieve this by raising a exception when the dependent functionality isn't working and Asserting on the functionality being tested.

Code:

// if it's dependent functionality
if(!UserCreated("TestUser"))
    throw new Exception("User wasn't created");
// if it's functionality being tested
Assert.That(UserCreated("TestUser"));

Use Assertion as little as possible in a functional test,

In a test suite for users and roles you might have 2000 test cases, but you might have only 100 test that actually test creating a user and the rest of the tests will test other functionality above and beyond that. Those other tests will have will have pre-steps or setup that will add the users before the actual test.

• Create user (dependent functionality)
• Check property 
• Login as user (dependent functionality)
• Check that user is logged in
• Change user name (functionality being tested)
• Check new user name

Code:

[Test]
public void ChangeUserName()
{
    User responseUser = CreateUser("Rick");
    if(responseUser.Name != "Rick")
        throw new Exception("User has incorrect name");
    if(!LoginAs("Rick"))
        throw new Exception("Login failed as user");
    Assert.That(ChangeUserName("Rick Casady"));
}

Now when you get an NUnit test report and the create user functionality is broken your report will say 1999 tests errored and 1 test failed. If you gave your tests meaningful names you'll know exactly what broke. Your test name might be AddAUserWithDefultPermissions()

Exceptions should also be raised in any helper classes any time there is a test run that has no failures and has errors you'll know you need to write at least one new test.

Attach debugger to NUnit GUI test runner

Here are the steps to attach Visual Studio debugger to the NUnit GUI test runner process. Attaching the debugger will allow you set breakpoints and debug NUnit test code in Visual Studio IDE.

• Right click the Project in the solution explorer
• Select “Start external program”
• Right click the Project in the solution explorer
• Click properties
• Click the ellipses “…” button
• Find and select the NUnit GUI test runner “c:\yourpath\nunit-x86.exe”
• Type the name of your test dll in the “Command line arguments: “ textbox
• Run the project and if its configured correctly the NUnit GUI runner should appear with the test dll loaded

You can also install TestDriven.NET and it packaged with NUnit and NCover, it provides right click test run and other cool stuff but can be buggy sometimes.

I wrote this because today I ran across someone who decided to rewrite there test suite in MSTest because they couldnt debug or use break points with NUnit...  By the way, friend don't let friends use MSTest for functional tests. More than once I have run across developers or testers who didn't know how to do attach the debugger and its very simple. Perhaps the funniest occasion was on a 3 month contract with eTouch, the developers there created a separate command line (exe) project that executed the NUnit test runner passing in there dll.

To get the Break points & Edit and Continue to work in .NET 4.0 you need to edit the nunit-x68.config file (or witch ever config of the exe your using to execute the tests)

<startup>
    <requiredRuntime version="4.0.30319" />
</startup>