Data Driven Testing using Selenium in C#

Data Driven Testing

Data-driven is a test automation framework which  focused on separating the test scripts logic and the test data from each other. It allows us to have a single test script which can execute tests for all the test data in the table. The test data set is kept in the external files or resources such as Excel Sheets, SQL Database, XML files etc.The test scripts connect to the external resources to get the test data. By using this framework we could easily make the test scripts work properly for different sets of test data. This framework significantly reduces the number of test scripts compared to a modular based framework.

Advantages of using Data Driven Test Framework

We adopt Data Driven Framework when we have to execute the same script with multiple sets of test data.If we run the same test with multiple test data sets manually it's time-consuming and error-prone.

Advantages->

Re-usability of code

Improves test coverage

Faster Execution

Less maintenance

Permits better error handling

Data driven framework in selenium webdriver using Excel files 

Here in my sample code selenium webdriver opens the browser and search for “Selenium”.We read the url and the search word from the excel file.

1. Create Excel file “TestData.xlsx” in the desired folder of the PC.

        Fill the data in the excel file like below.

 2. Install ExcelDataReader 

      Reference->Manage Nuget Packages->Browse->ExcelDataReader   

      

3. To open the Excel sheet and read data from it within  Selenium test script, I use following piece of code.

     #region Excel 

    public class ExcelLib
    {
        static List<Datacollection> dataCol = new List<Datacollection>();
        public class Datacollection
        {
            public int rowNumber { get; set; }
            public string colName { get; set; }
            public string colValue { get; set; }
        }
        public static void ClearData()
        {
            dataCol.Clear();
        }
        private static DataTable ExcelToDataTable(string fileName, string SheetName)
        {
            // Open file and return as Stream
            using (System.IO.FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
            {
                using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
                {
                    excelReader.IsFirstRowAsColumnNames = true;
                    //Return as dataset
                    DataSet result = excelReader.AsDataSet();
                    //Get all the tables
                    DataTableCollection table = result.Tables;
                    // store it in data table
                    DataTable resultTable = table[SheetName];
                    //excelReader.Dispose();
                    //excelReader.Close();
                    // return
                    return resultTable;
                }
            }
        }
        public static string ReadData(int rowNumber, string columnName)
        {
            try
            {
                //Retriving Data using LINQ to reduce much of iterations
                rowNumber = rowNumber - 1;
                string data = (from colData in dataCol
                               where colData.colName == columnName && colData.rowNumber == rowNumber
                               select colData.colValue).SingleOrDefault();
                //var datas = dataCol.Where(x => x.colName == columnName && x.rowNumber == rowNumber).SingleOrDefault().colValue;
                return data.ToString();
            }

            catch (Exception e)
            {
                //Added by Kumar
                Console.WriteLine("Exception occurred in ExcelLib Class ReadData Method!" + Environment.NewLine + e.Message.ToString());
                return null;
            }
        }
        public static void PopulateInCollection(string fileName, string SheetName)
        {
            ExcelLib.ClearData();
            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 col = 0; col < table.Columns.Count; col++)
                {
                    Datacollection dtTable = new Datacollection()
                    {
                        rowNumber = row,
                        colName = table.Columns[col].ColumnName,
                        colValue = table.Rows[row - 1][col].ToString()
                    };
                    //Add all the details for each row
                    dataCol.Add(dtTable);
                }
            }

        }
    }
    #endregion

      4. Show the path of the excel file  and send “Row number” and “Column Name” in parameter.

  class Program
    {
       public static String ExcelPath = @"";
        static void Main(string[] args)
        {
            IWebDriver driver = new ChromeDriver();
            ExcelLib.PopulateInCollection(ExcelPath, "Data");
            driver.Navigate().GoToUrl(ExcelLib.ReadData(2, "Value"));//Fetching data from Excel
            var searchbox = driver.FindElement(By.Id("lst-ib"));
            searchbox.SendKeys(ExcelLib.ReadData(3, "Value"));//Fetching data from Excel

            searchbox.SendKeys(Keys.Enter);
            driver.Close();
        }

5. Output of the code