Passing variables to a SQL-connection

Mudhenn · Post by **Mudhenn** » Mon Jun 27, 2016 2:31 pm

Hello

I have the following problem: I need to check some numbers on a webpage. First I pass an identifier and then I have to check the results. Problem is, that there are not only different results but also a different amount of them.
So I searched the Forum and found that one part of my solution would be using an sql-server / database.
Actually I have one Table with the identifier and an unique ID to that

Table: dbo.DUS
Fields:
DU_ID - the unique ID
DU_TEXT - the identifier

which gives "the main loop" for the test.

And then a second one with the data to check, which creates the "sub-loop" beneath each "main loop"

Table: dbo.Timecodes
Fields:
DU_ID - Linked to the unique ID above

Feldname
Timecode } these three fields have to get verified
Bildname

I also created a stored procedure named dbo.usp_TimeCodesPerDU which accepts one parameter called @DU_IDPara. This basically does this:

Code: Select all

SELECT * from dbo.Timecodes where DU_ID = @DU_IDPara

Then I created an connector to my SQL-Database in Ranorex called SQL_TC_nach_DU.

Now I need to somehow use this data in my module. Actually I have successfully made an module which checks the data coming from that connection using the default-value for @DU_IDPara but I am somewhat stuck how to integrate the changing of the connection-string into my module.
I found some postings from @krstcs but was not able to follow him, I think because of him using some classes(?) or other elements in his codes that I don't have or know.

If anyone has a not too fancy code-snippet ideally with some explanations, that would be great!

Thanks in advance!

Best regards,
Frank

PS: If you need any informations to understand better what I want to do please let me know!

krstcs · Post by **krstcs** » Mon Jun 27, 2016 3:30 pm

Hey Frank!

Let's see if I can clear it up for you...

Each SQL Data Connector in Ranorex has a string property called "Query". All you need to do is change that query string to the SQL query you want to use the next time the connector is accessed by a connected test case.

Create a new Ranorex code module (you can use a recording module, but I use code modules so I don't have to work around the recording action table UI). Then add DU_ID as a module variable (right-click anywhere in the code and select "Insert New Module Variable"). You will bind this later.

Now, put the following code in the "Run()" method of the new code module, after the existing lines.

Code: Select all

using Ranorex.Core.Data;

...

string dataCacheName = "<Put the name of your data connector here>";
string newQueryString = string.Format("EXEC usp_TimeCodesPerDU {0}", DU_ID); //assuming your unique ID is an int value, otherwise you need to wrap the {0} with single quotes like: '{0}'

DataCache dc = DataSources.Get(dataCacheName);
((SqlDataConnector)dc.Connector).Query = newQueryString;
dc.Load();

That's all the code you need. It updates the query string with the runtime value each time the parent test case loops, and then reloads the connector, refreshing the data for Ranorex to use the next time that data connector is referenced in a test case.

Now, drop this module into your test suite right above the test case that uses this data connector (I do it right above the related connector in order to make it easier to see the relationship).

For example, assuming you named the new module "TimeCodesPerDU":

Code: Select all

TEST_SUITE
--TestCase1 => SQLConnector1 as "SELECT * FROM dbo.DUS" (should return DU_ID as a column)
----TimeCodesPerDU => Bind "DU_ID" to parent data connector
----TestCase2 => SQLConnector2 as "EXEC usp_TimeCodesPerDU"
------<Run the tests here>

I would recommend that you add a default value to @DU_IDPara in your stored procedure. I usually use 0 for int types. This will return no rows, but will still return the columns so you can bind the connector to your module variables without having the actual data.

If you have questions, feel free to PM me.

EDIT: Fix code issues

Mudhenn · Post by **Mudhenn** » Tue Jun 28, 2016 12:16 pm

Hello and thank you very much for your help, I've got it finally to work for me

Two annotations:
1. There is one little error in the code

Code: Select all

((SqlDataConnector)dc.Connector).Query = queryString;

should be

Code: Select all

((SqlDataConnector)dc.Connector).Query = newqueryString;

2. I had to add

Code: Select all

using Ranorex.Core.Data;

in the beginning of the code before the namespace-part to use "DataCache".

But besides that little glitches you were really a great help, also the second part with the structure was vital for me.

Thank you very much again!

best regards,
Frank

krstcs · Post by **krstcs** » Tue Jun 28, 2016 2:09 pm

Yeah, sorry, I copy-pasted from my code and forgot those parts. I'll edit my post to reflect it.

Ranorex Forum

Passing variables to a SQL-connection

Passing variables to a SQL-connection

Re: Passing variables to a SQL-connection

Re: Passing variables to a SQL-connection

Re: Passing variables to a SQL-connection