locked
excel with DataBase Verification in CodedUI RRS feed

  • Question

  • In coded-UI, I want to parameterize value through Excel sheet, before providing test data for parameterization I want to verify that ,test data in Excel sheet is present in database.

    Is there is any way to do the above by executing the coded-UI(vb.net) script?

    please do needful

    example:

    tb1 in excel sheet             tb1 in Database

    emp1                                emp1

    emp2                                 emp2

    Thanks in advance


    ajay

    Thursday, May 29, 2014 4:52 PM

Answers

  • Yes it is possible however, it is not part of Coded UI Test Framework. What you need to do is before acting with the DataRow, you need to check for its existence in your DB using ADO.NET:

    //Not compiled code
    [TestMethod]
    [DataSource("....")]
    public void TestMethod1()
    {
        if (VerifyData(this.TestContext.DataRow))
        {
            // Data exists, proceed
            //this.UIMap.Methods calls
        }
        else
        {
            //what to do otherwise??
        }
    }
    
    string connectionStringDB = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=true";
    string queryString = "IF EXISTS (SELECT Id FROM EmployeeTable WHERE EmployeeId=@EmployeeId) SELECT 1 ELSE SELECT 0";
    
    private bool VerifyData(DataRow row)
    {
        bool value = false;
        using (SqlConnection connection =
        new SqlConnection(connectionStringDB))
        {
            // Create the Command and Parameter objects.
            SqlCommand command = new SqlCommand(queryString, connection);
            command.Parameters.AddWithValue("@EmployeeId", row[0].ToString());
    
            // Open the connection in a try/catch block.  
            // Create and execute the DataReader, writing the result 
            // set to the console window. 
            try
            {
                connection.Open();
                value = bool.Parse(command.ExecuteScalar().ToString());
            }
            catch
            {
                // Handle...
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
            }
        }
        return value;
    }

    TejasJ.

    • Proposed as answer by Amanda Zhu Tuesday, June 3, 2014 1:43 AM
    • Marked as answer by Amanda Zhu Monday, June 9, 2014 1:36 AM
    Friday, May 30, 2014 4:09 AM