How to do a SQL Connector in a UserCode Module

[kdreiling]

I am seeking advice on how to embed a SQL Connector into a UserCode Module and then call it in a recording that uses parameter driven Excel spreadsheet.

Example: The recording is searching for a member that exists in our database. I want to validate the members termination date is greater than today. My thought is to pass a parameter into a user code module to ensure the members coverage is still active. If the coverage is inactive, I want to move to the next iteration or the next test case.

So the recording clicks on a field passes a parameter and then clicks a search button. Before I click search, I want to take that parameter and validate the members termination date is greater than today using a SQL Connector.

If anyone has some suggestions, I would appreciate direction or offer a better solution.

[krstcs]

You don't need to go to the hassle of creating a Ranorex SQL Data connector for this. (NOTE: I EXCLUSIVELY use SQL data connectors in my tests and have posted several techniques for manipulating the SQL Query at runtime.)

In your usercode module (or usercode backer for your test module) you just need to create a SQL DB connection.

Code: Select all

using System.Data.SqlClient; //make sure to add this in the using directives section and add a reference in the project


//in your Run() method...
string sqlConnString = "Server={0};Database={1};User Id={2};Password={3};Connection Timeout={4};";

SqlConnection sqlConn = new SqlConnection();

sqlConn.ConnectionString = string.Format(sqlConnString, "<dbServer>", "<dbName>", "<dbUserID>", "<dbPassword>", "<dbConnectionTimeoutInSeconds>");

SqlCommand sqlComm = new SqlCommand();

sqlComm.Connection = sqlConn;
sqlComm.CommandText = "<your sql statement>";

//to return only 1 value:
object result = sqlComm.ExecuteScalar();

If you need to return a table of values, you would be better off using a data adapter and loading the resulting records from the query into a .NET DataTable.

[Tzahi]

Hi kdreiling,

I would highly recommend to you to create a DB class (so you can use it anywhere at the test).
And manage the connection string at the app.config file.
You can create instance at the above user code and check relevant things (I do it a lot in my tests).

good luck

[slovelace]

Trying to use the SQL connector above. I get an error saying Incorrect syntax near 'vendoridOR'. Any suggestions?

/*
* Created by Ranorex
* User: eone
* Date: 7/7/2016
* Time: 8:51 AM
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/
using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Drawing;
using System.Threading;
using WinForms = System.Windows.Forms;


using Ranorex;
using Ranorex.Core;
using Ranorex.Core.Testing;


namespace SmartConnect
{
/// <summary>
/// Description of UserCodeModuleEx1.
/// </summary>
[TestModule("E2874BFB-AF90-4A35-99A9-03A80185A77C", ModuleType.UserCode, 1)]
public class UserCodeModuleEx1 : ITestModule
{
/// <summary>
/// Constructs a new instance.
/// </summary>
public UserCodeModuleEx1()
{
// Do not delete - a parameterless constructor is required!
}

/// <summary>
/// Performs the playback of actions in this module.
/// </summary>
/// <remarks>You should not call this method directly, instead pass the module
/// instance to the <see cref="TestModuleRunner.Run(ITestModule)"/> method
/// that will in turn invoke this method.</remarks>
void ITestModule.Run()
{
Mouse.DefaultMoveTime = 300;
Keyboard.DefaultKeyPressTime = 100;
Delay.SpeedFactor = 1.0;

//in your Run() method...
string sqlConnString = "Server={0};Database={1};User Id={2};Password={3};Connection Timeout={4};";

SqlConnection sqlConn = new SqlConnection();

sqlConn.ConnectionString = string.Format(sqlConnString,"XXX\\XXX", "TWO", "XX", "XXX", "180");

SqlCommand sqlComm = new SqlCommand();

sqlComm.Connection = sqlConn;
sqlConn.Open();
sqlComm.CommandText= @"select * from TWO.dbo.PM00200 where
VENDORID="+"JOHNSON0001"+"select vendorid"+
"OR VENDORID="+"DARVING0001"+
"OR VENDORID="+"FRANS0001"+
"OR VENDORID="+"JCYCLE001";



//to return value:
object result = sqlComm.ExecuteScalar();


}
}
}

[krstcs]

I think there is an extra "select" statement in your query, on the second line of your query code. In addition, you need to put the string inside single quotes when doing SQL queries. And, make sure that there are actual spaces between words in the SQL query.

I would suggest doing it all in one line using the string.Format() method to fill in the parameters, it will make it easier to see issues:

Code: Select all

string query = string.Format("select * from TWO.dbo.PM00200 where VENDORID='{0}' OR VENDORID='{1}' OR VENDORID='{2}' OR VENDORID='{3}'", "JOHNSON0001", "DARVING0001", "FRANS0001", "JCYCLE001");

I would suggest doing some reading about SQL basics.

[slovelace]

Thank you. Do you have any suggestions on where to put the sqlComm.CommandText?

[Tzahi]

HI slovelace,

do you try running 2 queries?
does it run on sql manager?
do you pass parameters to the query (I see many +)?

SqlCommand sqlComm = new SqlCommand(Put Here Your Command);

add try catch (SqlException) for ExecuteScalar.

[krstcs]

The "query" string is the command text. You can just replace my "string query =" with "sqlComm.CommandText =".

Like this:

Code: Select all

sqlComm.CommandText = string.Format("select * from TWO.dbo.PM00200 where VENDORID='{0}' OR VENDORID='{1}' OR VENDORID='{2}' OR VENDORID='{3}'", "JOHNSON0001", "DARVING0001", "FRANS0001", "JCYCLE001");

[slovelace]

Thank you.

[krstcs]

You're welcome! Again, I would highly recommend some reading on .NET and SQL before you get too far into it. It will help keep you from getting yourself into trouble. And keep asking questions here!

[kdreiling]

Dear krstsc:

I wanted to thank you for your comment on this. I was able to complete the SQL Connection (after much trial and error) to resolve my issue. Wanted to share this code in case it could add value for others later. Notice I changed the User Name to Integrated Security so the connection string used my active directory id.

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = "server='[servername];database=[DBName];Integrated Security=True;Max Pool Size=200;Connect Timeout=60";
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlConn.Open();
sqlComm.CommandText= "Select Max([ColumnName]) from [TableName] where [ColumnName] = '" + [VariableName] + "' and [ColumnName] > GetDate()";


////to return only 1 value:
object result = sqlComm.ExecuteScalar();

if(result!=null)

[kdreiling]

Greetings:

So as stated previously, my SQL Connector worked like a charm, but a new caveat came up that make the test case fail, and that was even though I was looking for the most current active member, I wasn't handling the fact that a member could be termed all together. So now instead of trying to retrieve one single value back in an SQL Connection, I want to return one column of rows of valid data by passing two parameters in and then be able to pick one or more rows in a test case, and in subsequent test cases pick other rows.

So now my sql connector looks like this, but the problem is in the sqlcomm.ExecuteScalar(). How does one write the results to a table and in the Data Source pick a range from the table? Is this even possible?

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = "server=NDSTELLA;database=MMHDevSQL;Integrated Security=True;Max Pool Size=200;Connect Timeout=60";
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlConn.Open();
sqlComm.CommandText= "Select Top 20 CCXRefID from [TableName] where [ColumnName] = '" + varParm1 + "' and [ColumnName] = '" + varParm2 + "';


////to return only 1 value:
object result = sqlComm.ExecuteScalar();

if(result!=null)

{

varCCXRefID = '#' + result.ToString();

Report.Log(ReportLevel.Info, "Keyboard", "Key sequence from variable '$varCCXRefID' with focus on 'OPTAMUM_Entry_Points.Member_Search_Group_Note.PayerClientID'.", repo.OPTAMUM_Entry_Points.Member_Search_Group_Note.PayerClientIDInfo);
repo.OPTAMUM_Entry_Points.Member_Search_Group_Note.PayerClientID.PressKeys(varCCXRefID);

}

sqlConn.Close();

[krstcs]

As I said in my original post, if you want to return a SINGLE VALUE you would use ExecuteScalar.

If you want to return a TABLE of VALUES you would probably want to use a SQL Data Adapter and fill it. You can do a search for it and find out how to adapt what you have.

The SQLDataAdapter would fill a .NET DataTable object with the results of the query execution.