Consecutively reading data from Excel in C#/NUnit test cases, returns blank data for 2nd Case

Question

I'm trying to write a parameterized NUnit test that executes twice. Each time it runs, it references a different row in a spreadsheet and gets the username and password based on int rowNum.

    class Test
    {
        //Run the test twice
        [Test,TestCase(1),TestCase(2)]
        public void T101_LoginTestSuite_Valid(int rowNum)
        {
            Console.WriteLine(TestContext.CurrentContext.Test.MethodName); //Test Name 
            Console.WriteLine("Row number "+rowNum);// Value of rowNum

            ExcelDataFactory.GetTestDataSet(TestContext.CurrentContext.Test.MethodName);

            //Print out the credentials
            Console.WriteLine(ExcelDataFactory.ReadData(rowNum,"username")); 
            Console.WriteLine(ExcelDataFactory.ReadData(rowNum, "password"));
        }
    }

Here is the excel

The first test case gets username and password correctly.

However the second test case returns blank (If I run this individually it will work!)

Below is the ExcelDataFactory code:

    class ExcelDataFactory
    {
        //Get data from excel
        private static DataTable ExcelToDataTable(String filename, String sheetName)
        {
            //Open file and returns as Stream
            FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);

            //CreateOpenXmlReader via ExcelReaderFactory 
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx

            //Return as DataSet and set the frist row as column name
            DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            });

            DataTableCollection table = result.Tables;


            DataTable resultTable = table[sheetName];

            //Close FileStream
            stream.Close();

            //Return
            return resultTable; 
        }

        //Put data into a collection 
        static List<DataCollection> dataCollection = new List<DataCollection>();

        public static void PopulateInCollection(string fileName, String sheetName)
        {
            DataTable table = ExcelToDataTable(fileName,sheetName);

            //Iterate through the rows and columns of the Table
            for(int row = 1; row <= table.Rows.Count; row++)
            {
                for (int column = 0; column < table.Columns.Count; column++)
                {
                    DataCollection dataTable = new DataCollection()
                    {
                        rowNumber = row,
                        columnName = table.Columns[column].ColumnName,
                        columnValue = table.Rows[row - 1][column].ToString()
                    };
                    //Add all the details for each row
                    dataCollection.Add(dataTable);
                }
            }
        }

        //Find the correct excel file and sheet
        public static void GetTestDataSet(String testName)
        {
            String[] testNameSplit = testName.Split('_');
            String filePath = MyProps.Default.TestData //Add path
                    + testNameSplit[1]
                    + "."
                    + "xlsx";
            PopulateInCollection(filePath, testNameSplit[0]);
        }

        public static string ReadData(int rowNumber, string columnName)
        {
            try 
            {
                //Retriving Data using LINQ to reduce amount of iterations
                string data = (from collectionData in dataCollection
                               where collectionData.columnName == columnName && collectionData.rowNumber == rowNumber
                               select collectionData.columnValue).SingleOrDefault();

                //var data   = dataCollection.Where(collectionData => collectionData.columnName == columnName && collectionData.rowNumber == rowNumber).SingleOrDefault().columnValue; 
                return data.ToString();
            } 
            catch (Exception e)
            {
                e.StackTrace.ToString();
                return null;
            }
        }

    }  

    class DataCollection
    {
        public int rowNumber { get; set; }

        public string columnName { get; set; }

        public string columnValue { get; set; }
    }

I suspect that the ExcelDataFactory.GetTestDataSet method is called in the wrong place, but I really am stumped as to why this is happening. Any ideas would be greatly appreciated.

you're storing the data of the excel in a static member? so if two things call that same function, what are you expecting to happen? You should be *returning* data from function calls most likely, not appending to some static member that will be polluted by multiple invokes. — Kritner, Apr 17 '20 at 12:25
Okay so, I should return a new instance of List dataCollection instead of it being static? — David Cunningham, Apr 17 '20 at 12:39
yes, I would think so. Do you understand what `static` means? that may be a good thing to do a bit of research on. I personally try to avoid statics as much as possible, and when i *do* use them, i make sure they're deterministic functions. — Kritner, Apr 17 '20 at 13:28

score 0 · Answer 1 · answered Apr 17 '20 at 13:37

I did some quick changes to ExcelDataFactory class, I removed the static references and now PopulateInCollection method returns a List that is declared and initialized at the start of the class.

using ExcelDataReader;
using NUnit.Framework;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Wizuda_Selenium_Test_Automation
{
    class ExcelDataFactory
    {
        List<DataCollection> dataCollection = new List<DataCollection>();

        private static DataTable ExcelToDataTable(String filename, String sheetName)
        {
            //Open file and returns as Stream
            FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);

            //CreateOpenXmlReader via ExcelReaderFactory 
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx

            //Return as DataSet and set the frist row as column name
            DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            });

            DataTableCollection table = result.Tables;


            DataTable resultTable = table[sheetName];

            //Close FileStream
            stream.Close();

            //Return
            return resultTable; 
        }

        //static List<DataCollection> dataCollection = new List<DataCollection>();

        public List<DataCollection> PopulateInCollection(string fileName, String sheetName)
        {
            dataCollection = new List<DataCollection>();

            DataTable table = ExcelToDataTable(fileName,sheetName);

            //Iterate through the rows and columns of the Table
            for(int row = 1; row <= table.Rows.Count; row++)
            {
                for (int column = 0; column < table.Columns.Count; column++)
                {
                    DataCollection dataTable = new DataCollection()
                    {
                        rowNumber = row,
                        columnName = table.Columns[column].ColumnName,
                        columnValue = table.Rows[row - 1][column].ToString()
                    };

                    //Add all the details for each row
                    dataCollection.Add(dataTable);
                }
            }

            return dataCollection;
        }

        public string ReadData(int rowNumber, string columnName)
        {
            try 
            {
                //Retriving Data using LINQ to reduce amount of iterations
                string data = (from collectionData in dataCollection
                               where collectionData.columnName == columnName && collectionData.rowNumber == rowNumber
                               select collectionData.columnValue).SingleOrDefault();

                //var data   = dataCollection.Where(collectionData => collectionData.columnName == columnName && collectionData.rowNumber == rowNumber).SingleOrDefault().columnValue; 
                return data.ToString();
            } 
            catch (Exception e)
            {
                e.StackTrace.ToString();
                return null;
            }
        }

        public void GetTestDataSet(String testName)
        {
            String[] testNameSplit = testName.Split('_');
            String filePath = MyProps.Default.TestData //Add path
                    + testNameSplit[1] //LoginTestSuite
                    + "."
                    + "xlsx";              //T101
            PopulateInCollection(filePath, testNameSplit[0]);
        }
    }  

    class DataCollection
    {
        public int rowNumber { get; set; }

        public string columnName { get; set; }

        public string columnValue { get; set; }
    }
}

I updated the test to create new instance of ExcelDataFactory

        [Test,TestCase(1),TestCase(2)]
        public void T101_LoginTestSuite_Valid(int rowNum)
        {
            ExcelDataFactory excelDataFactory = new ExcelDataFactory();

            Console.WriteLine(TestContext.CurrentContext.Test.MethodName);
            Console.WriteLine("Row number "+rowNum);
            excelDataFactory.GetTestDataSet(TestContext.CurrentContext.Test.MethodName);

            Console.WriteLine("username= "+ excelDataFactory.ReadData(rowNum,"username"));
            Console.WriteLine("password= "+ excelDataFactory.ReadData(rowNum, "password"));
        }

And now the test passes

I guess I need to go back and re-learn about the use static methods, thanks Kritner

Although you have found a solution, I want to point out that the order of execution of your two cases is not guaranteed. Not sure if that matters to you. — Charlie, Apr 17 '20 at 14:26
At this point in time it doesn't matter.I am going to try and keep each test self contained in its own method, so that they are independent of one another. However if there is an approach to defining a test order in C#/NUnit or on test design, I would welcome any advice. — David Cunningham, Apr 17 '20 at 15:10
Generally, keeping the tests independent is your best course. For the rare case where order matters, NUnit has an OrderAttribute. — Charlie, Apr 18 '20 at 18:12

Consecutively reading data from Excel in C#/NUnit test cases, returns blank data for 2nd Case

1 Answers1