Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Selenium Database Test In C#

In every web application's back end, there is a database like SQL Server, My SQL, Oracle or other types of database support functions. When using Selenium to automate UI testing, we need to make sure any user data entered from the UI should be captured in the database. We can use database retrieve statement to retrieve data and then use the data to verify what is entered by the user.

Considering the example of a user registration. User registers himself/herself with a username and password. By establishing a DB connection and retrieving data from the DB, we can validate the username entered in the UI should match the username in the database.



Test Case: verify that user login information should be saved in the database when a user successfully registered in a site.

In this screenshot below, first two users were in the database. A new test user is entered from the application UI, so we will verify the username.

Step 1: create a console application project in visual studio. Add all Selenium .Net 4.0 Web Driver binaries in the reference. Add config file "app.config", class "SiteElementsByInfo.cs" and class "SiteModel.cs". Program.cs is the main executable file.

Step 2: write the following information in the app.config file

Code Snippet
  1. <?xmlversion="1.0"?>
  2. <configuration>
  3.   <connectionStrings>
  4.       <addname="UserDBConnectionString"providerName="System.Data.SqlClient"connectionString="Data Source=192.168.1.9;Initial Catalog=UserInfo;User Id=sa;Password=Pass@word;"/>
  5.   </connectionStrings>
  6. <startup><supportedRuntimeversion="v4.0"sku=".NETFramework,Version=v4.0"/></startup>
  7. </configuration>

Step 3: write the following code in the SiteElementsByInfo.cs class

Code Snippet
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using OpenQA.Selenium;
  6.  
  7. namespace SeleniumMasterDatabaseTesting
  8. {
  9.     class SiteElementsByInfo
  10.     {
  11.         protected static IWebDriver driver = null;
  12.  
  13.         public static By GetUsernameFieldByInfo()
  14.         {
  15.             return By.Id("login_login_username");
  16.         }
  17.  
  18.         public static By GetRegisterLinkByInfo()
  19.         {
  20.             return By.LinkText("Register");
  21.         }
  22.         public static By GetFirstnameInputTextFieldByInfo()
  23.         {
  24.             return By.XPath("//table/tbody/tr[contains(@id,'register')]//label[contains(text(),'First')]//following::input[1]");
  25.         }
  26.         public static By GetLastnameInputTextFieldByInfo()
  27.         {
  28.             return By.XPath("//table/tbody/tr[contains(@id,'register')]//label[contains(text(),'First')]//following::input[2]");
  29.         }
  30.         public static By GetEmailInputTextFieldByInfo()
  31.         {
  32.             return By.XPath("//table/tbody/tr[contains(@id,'register')]//label[contains(text(),'First')]//following::input[3]");
  33.         }
  34.  
  35.         public static By GetInstitutionSelectByInfo()
  36.         {
  37.             return By.XPath("//select[contains(@id,'register')]");
  38.         }
  39.  
  40.         public static By GetRegisterButtonByInfo()
  41.         {
  42.             return By.XPath("//input[@value='Register']");
  43.         }
  44.         public static By GetRegistrationConfirmationMessageByInfo()
  45.         {
  46.             return By.CssSelector("p");
  47.         }
  48.  
  49.     }
  50.  
  51. }

Step 4: write the following code in the SiteModel.cs

Code Snippet
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using OpenQA.Selenium;
  6.  
  7. namespace SeleniumMasterDatabaseTesting
  8. {
  9.     class SiteModel:SiteElementsByInfo
  10.     {
  11.  
  12.         public static IWebElement GetElement(By by)
  13.         {
  14.             return driver.FindElement(by);
  15.         }
  16.     }
  17. }

Step 5: write the following code in the Program.cs class

Code Snippet
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using OpenQA.Selenium;
  6. using OpenQA.Selenium.Firefox;
  7. using OpenQA.Selenium.Support.UI;
  8. using System.Diagnostics;
  9. using OpenQA.Selenium.Chrome;
  10. using NUnit.Framework;
  11. using System.Data.SqlClient;
  12. using System.Configuration;
  13.  
  14. namespace SeleniumMasterDatabaseTesting
  15. {
  16.     class Program:SiteModel
  17.     {
  18.         private static String testUrl;
  19.         static void Main(string[] args)
  20.         {
  21.             //start new test
  22.             Program program = new Program();
  23.             //set up test
  24.             program.setup();
  25.             //register new user
  26.             program.RegisterNewUser();
  27.             //end test
  28.             program.TearDown();
  29.             //validate user in DB
  30.             var temp = program.VerifyUserInDatabase("newuser");
  31.             Assert.AreEqual("newuser", temp);
  32.             
  33.         }
  34.  
  35.         public void setup()
  36.         {
  37.             try
  38.             {
  39.                 //set test url
  40.                 testUrl = "http://demo.mahara.org/register.php";
  41.                 //set firefox browser
  42.                 driver = new FirefoxDriver();
  43.                 //set browser time out time to 60 seconds
  44.                 driver.Manage().Timeouts().SetPageLoadTimeout(TimeSpan.FromSeconds(120));
  45.                 
  46.                 
  47.             }
  48.             catch (Exception e)
  49.             {
  50.                 //add message to the console
  51.                 Console.WriteLine(e.Message);
  52.                 Console.WriteLine("Cannot start Web Driver and Launch the browser");
  53.             }
  54.         }
  55.  
  56.         public void RegisterNewUser()
  57.         {
  58.             //navigate to the registration page
  59.             driver.Navigate().GoToUrl(testUrl);
  60.             //wait until find the first name text field
  61.             WaitForElementVisible(SiteElementsByInfo.GetFirstnameInputTextFieldByInfo(),60);
  62.             //enter first name
  63.             SiteModel.GetElement(SiteElementsByInfo.GetFirstnameInputTextFieldByInfo()).SendKeys("new");
  64.             //wait until find last name text field
  65.             WaitForElementVisible(SiteElementsByInfo.GetLastnameInputTextFieldByInfo(), 60);
  66.             //end last name
  67.             SiteModel.GetElement(SiteElementsByInfo.GetLastnameInputTextFieldByInfo()).SendKeys("user");
  68.             //wait until find email text field
  69.             WaitForElementVisible(SiteElementsByInfo.GetEmailInputTextFieldByInfo(), 60);
  70.             //enter email address
  71.             SiteModel.GetElement(SiteElementsByInfo.GetEmailInputTextFieldByInfo()).SendKeys(" This email address is being protected from spambots. You need JavaScript enabled to view it. ");
  72.             //wait until find institution select drop down menu displayed
  73.             WaitForElementVisible(SiteElementsByInfo.GetInstitutionSelectByInfo(), 60);
  74.             //select demo for the institution
  75.             var institutionSelect=new SelectElement(SiteModel.GetElement(SiteElementsByInfo.GetInstitutionSelectByInfo()));
  76.             institutionSelect.SelectByText("Demo");
  77.             //wait until find the register button
  78.             WaitForElementVisible(SiteElementsByInfo.GetRegisterButtonByInfo(), 60);
  79.             //click on the register button
  80.             SiteModel.GetElement(SiteElementsByInfo.GetRegisterButtonByInfo()).Click();
  81.             //wait until find the confirmation message
  82.             WaitForElementVisible(SiteElementsByInfo.GetRegistrationConfirmationMessageByInfo(),60);
  83.             var confirmationText=SiteModel.GetElement(SiteElementsByInfo.GetRegistrationConfirmationMessageByInfo()).Text;
  84.             Console.WriteLine(confirmationText);
  85.             //assert confirmation message
  86.             Assert.AreEqual("You have successfully registered. Please check your email account for instructions on how to activate your account", confirmationText);
  87.         }
  88.  
  89.         //tear down
  90.         public void TearDown()
  91.         {
  92.             driver.Close();
  93.         }
  94.         //define a method that keep checking if an element is displayed until time reaches 60 seconds
  95.  
  96.         private void WaitForElementVisible(By by, int timeOutInSeconds)
  97.         {
  98.             Stopwatch stopwatch = new Stopwatch();
  99.             stopwatch.Start();
  100.             try
  101.             {
  102.                 var wait = new WebDriverWait(driver, TimeSpan.FromSeconds(timeOutInSeconds));
  103.                 wait.Until(ExpectedConditions.ElementIsVisible(by));
  104.             }
  105.             catch (Exception)
  106.             {
  107.                 Console.WriteLine("Time elapsed: {0}", stopwatch.Elapsed.Seconds);
  108.             }
  109.             finally
  110.             {
  111.                 stopwatch.Stop();
  112.             }
  113.         }
  114.  
  115.         public string VerifyUserInDatabase(string usernameToVerify)
  116.         {
  117.             var user = String.Empty;
  118.             var con = ConfigurationManager.ConnectionStrings["UserDBConnectionString"].ToString();
  119.  
  120.             using (SqlConnection myConnection = new SqlConnection(con))
  121.             {
  122.                 string oString = "Select * from userlogin where username=@newusername";
  123.                 SqlCommand oCmd = new SqlCommand(oString, myConnection);
  124.                 oCmd.Parameters.AddWithValue("@newusername", usernameToVerify);
  125.                 myConnection.Open();
  126.                 using (SqlDataReader oReader = oCmd.ExecuteReader())
  127.                 {
  128.                     while (oReader.Read())
  129.                     {
  130.                         user = oReader["username"].ToString();
  131.                      }
  132.  
  133.                     myConnection.Close();
  134.                 }
  135.             }
  136.             return user;
  137.         }
  138.     }
  139. }

 Step 6: run the console application and verify the result. Assertion passed in the test.