How to do a SQL Connector in a UserCode Module

Best practices, code snippets for common functionality, examples, and guidelines.
kdreiling
Posts: 9
Joined: Mon Sep 24, 2012 9:45 pm
Location: Kansas City

How to do a SQL Connector in a UserCode Module

Post by kdreiling » Wed Jan 06, 2016 9:48 pm

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
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: How to do a SQL Connector in a UserCode Module

Post by krstcs » Wed Jan 06, 2016 11:03 pm

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.
Shortcuts usually aren't...

Tzahi
Posts: 16
Joined: Sun May 17, 2015 2:24 pm

Re: How to do a SQL Connector in a UserCode Module

Post by Tzahi » Thu Jan 07, 2016 10:55 am

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
Best Regards
Tzahi Caspi

slovelace
Posts: 3
Joined: Mon Jul 11, 2016 3:55 pm

Re: How to do a SQL Connector in a UserCode Module

Post by slovelace » Mon Jul 11, 2016 4:13 pm

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
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: How to do a SQL Connector in a UserCode Module

Post by krstcs » Mon Jul 11, 2016 4:54 pm

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.
Shortcuts usually aren't...

slovelace
Posts: 3
Joined: Mon Jul 11, 2016 3:55 pm

Re: How to do a SQL Connector in a UserCode Module

Post by slovelace » Mon Jul 11, 2016 5:13 pm

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

Tzahi
Posts: 16
Joined: Sun May 17, 2015 2:24 pm

Re: How to do a SQL Connector in a UserCode Module

Post by Tzahi » Mon Jul 11, 2016 6:17 pm

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.
Best Regards
Tzahi Caspi

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: How to do a SQL Connector in a UserCode Module

Post by krstcs » Mon Jul 11, 2016 9:42 pm

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");
Shortcuts usually aren't...

slovelace
Posts: 3
Joined: Mon Jul 11, 2016 3:55 pm

Re: How to do a SQL Connector in a UserCode Module

Post by slovelace » Mon Jul 11, 2016 9:44 pm

Thank you.

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: How to do a SQL Connector in a UserCode Module

Post by krstcs » Mon Jul 11, 2016 9:56 pm

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! :D
Shortcuts usually aren't...

kdreiling
Posts: 9
Joined: Mon Sep 24, 2012 9:45 pm
Location: Kansas City

Re: How to do a SQL Connector in a UserCode Module

Post by kdreiling » Wed Jul 20, 2016 11:17 pm

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
Posts: 9
Joined: Mon Sep 24, 2012 9:45 pm
Location: Kansas City

Re: How to do a SQL Connector in a UserCode Module

Post by kdreiling » Fri Aug 12, 2016 7:44 pm

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
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: How to do a SQL Connector in a UserCode Module

Post by krstcs » Mon Aug 15, 2016 4:52 pm

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.
Shortcuts usually aren't...