Desperately trying to pass data from one iteration to next

Ranorex Spy, Recorder, and Studio.
carsonw
Posts: 178
Joined: Tue Nov 08, 2011 10:01 pm

Desperately trying to pass data from one iteration to next

Post by carsonw » Wed Sep 12, 2012 8:46 pm

I've brought this up a few different times with a couple of solutions being suggested. I knew that, in time, this would bite us but I was able to leave it for a while.

Now, it's causing us problems and I have two different solutions, neither of which work. Essentially, I am trying to update my test data on the fly so I can reuse that data in later iterations.

Here is what we are trying to accomplish, imagine this is a simple data table in Excel:

Simple Sheet example:

Code: Select all

     A                       B
1  Comment                OrderID
2  Create Order / Get ID   ???
3  Verify Order ID           =B2
During itreation 2, I get my order ID which I may need to reuse throughout one or more iterations during the test.
To get this information, I have references in my Excel sheet to cell, B2.

In Ranorex, I have successfully done the following, but neither accomplish my goals for different reasons.

Solution #1
Updated the actual Excel Datasheet Cell #B2 with the data. Opened the sheet during run time and confirmed both B2 and B3 have the correct data.

Didn't Work Because...
I could never get Ranorex to reload the data from Excel. I tried all kinds of ways to this, here are a couple:

Code: Select all

TestCase.Current.DataContext.ReloadData();
TestCase.Current.DataContext.ReloadData(true);
TestCase.Current.DataContext.Source.Load()
Solution #2 (Much Preferred)
I tried a different approach after that - rather than updating the sheet, I updated the TestCase.Current.DataContext.Source.Rows with the new data instead. This is preferred because it doesn't mess with our "saved" "hardcopy" of the data.

Didn't Work Because...
For some reason, the references are lost. So, I was able to update cell B2, but the value in cell B3 remained the same as it was when the data was originally loaded - as if the reference to cell B2 never existed. Somehow this works in QTP without a hitch.

I tried the above methods reload the data, and somehow it loaded the original data, not sure from where because when I changed the sheet it wasn't reloaded then so it must be held elsewhere.

Code I Used
Here is the code I used for solution 1 (note, it uses a package called EPPlus (http://epplus.codeplex.com/):

Code: Select all

				int rowtoUpdate = TestCase.Current.DataContext.CurrentRowIndex;
				Ranorex.Core.Data.ExcelDataConnector connector = (Ranorex.Core.Data.ExcelDataConnector)TestCase.Current.DataContext.Source.Connector;
				ExcelPackage package = new ExcelPackage(new FileInfo(connector.FileName));
			
				ExcelWorksheet sheetToBeUpdated = package.Workbook.Worksheets[connector.WorksheetName];
				ExcelRow headerRow = sheetToBeUpdated.Row(1);							
				
				int columnID = -1;
				
				for(int i = 1; i < sheetToBeUpdated.Cells.Count(); i++)
				{
					if ((string)sheetToBeUpdated.Cells[1, i].Value == columnToUpdate)
					{
						columnID = i;
						break;
					}					
				}
				
				if(columnID == -1)
				{
					throw new InternalTestException("Column: " + columnToUpdate + " was not found in " + connector.WorksheetName + " at " + connector.ResolvedFileName);
				}
							
				sheetToBeUpdated.SetValue(rowtoUpdate+1, columnID, updatedValue);
				package.Save();
Here is the code I used for solution 2:

Code: Select all

int columnIndex = -1;
				
				foreach(Ranorex.Core.Data.Column dataColumn in TestCase.Current.DataContext.Source.Columns)
				{
					if (dataColumn.Name == columnToUpdate)
					{
						columnIndex = dataColumn.Index;
						break;
					}
				}
				
				int rowCounts = TestCase.Current.DataContext.Source.Rows.Count;
				int currentRowIndex = TestCase.Current.DataContext.CurrentRowIndex;
				
				Ranorex.Core.Data.RowCollection testCollection = TestCase.Current.DataContext.Source.Rows;
				
				for (int i = 0; i < testCollection.Count; i++)
				{
					Reporting.LogInfoMessage("Row #: " + i.ToString() + string.Join(",", testCollection[i].Values));
				}
				
				
				TestCase.Current.DataContext.Source.Rows[TestCase.Current.DataContext.CurrentRowIndex-1].Values[columnIndex] = updatedValue;
As a side note, one thing that confuses things a bit is that TestCase.Current.DataContext.CurrentRowIndex will return a row index of "1", but the equivalent row in TestCase.Current.DataContext.Source.Rows is actually row 0. Took a bit of debugging to figure that one out.

Somehow, I need this to work. I will go through the forums and try and find other solutions, but my preference would be something along the lines of solution #2 so the sheet itself does not have to change.

Thanks!

carsonw
Posts: 178
Joined: Tue Nov 08, 2011 10:01 pm

Re: Desperately trying to pass data from one iteration to next

Post by carsonw » Wed Sep 12, 2012 9:50 pm

Ok, I got solution 1 to work by adding the following:

Code: Select all

foreach (Ranorex.Core.Data.DataCache cache in TestSuite.Current.DataConnectorCaches)
				{					
					if(cache.Connector.Name == connector.Name)
					{
						cache.Load();
					}
				}	
That seemed to do it! However, it still requires me to save the sheet, which I'm hoping to avoid.

Instead, is there a way I can take the excel sheet I have in memory and load it into the cache:

Code: Select all

sheetToBeUpdated.SetValue(rowtoUpdate+1, columnID, updatedValue);
				//package.Save();    --Don't save the sheet, but the sheet still has the value in memory.
				
				foreach (Ranorex.Core.Data.DataCache cache in TestSuite.Current.DataConnectorCaches)
				{					
					if(cache.Connector.Name == connector.Name)
					{
						//cache.LoadDataFromsheetToBeUpdatedSomehow <-- if I could do this, then I could reload the data cache from the sheet I have in memory without having to actually save the sheet.
					}
				}

User avatar
artur_gadomski
Posts: 207
Joined: Mon Jul 19, 2010 6:55 am
Location: Copenhagen, Denmark
Contact:

Re: Desperately trying to pass data from one iteration to next

Post by artur_gadomski » Thu Sep 13, 2012 10:32 am

Have you considered Solution 3: reorganize your tests?
We code all our tests so I don't have much experience with Ranorex Test Cases and Iterations but shouldn't iteration do exactly same things just with different data? If something special happens in iteration 2 that then affects all other iterations that it seems more like Suite setup step and not iteration.
I know reorganizing all tests might be a lot of work but if trying to solve all problems takes more time then it's time well spent.

Also maybe using SQL Database might be a solution instead of Excel.

carsonw
Posts: 178
Joined: Tue Nov 08, 2011 10:01 pm

Re: Desperately trying to pass data from one iteration to next

Post by carsonw » Thu Sep 13, 2012 5:46 pm

Hi - yes we have been reorganizing our data to avoid this requirement as much as possible, but we're getting to a point where it's unavoidable.

We did consider using SQL for our test cases as well (I was an advocate of this), but the team preferred to stick with excel because it's easier to manipulate and organize mass amounts of data (which I can see).

Our tests are quite large, having hundreds of iterations is not uncommon.

Our tests activities are generally driven by the test data using switch cases (so theoretically anyone could write the tests, even without any kind of code experience).

So, iteration 1 might be: login
2: Open customer
3: book order
4: release payment
5: log off
6: repeate with different customer
X 300 times with different data/variations.

But, in order to release the payment for the order booked in iteration 3, I need to know that order number. That's a very simple example. Imagine I want to refund that order, edit it, and then release it?

Those are done in separate iterations to give the test a lot of functionality.

For example, I don't want a single iteration that books, edits, releases, refunds because I'll need functionality for all those combinations which would make the test confusing.

Or, for the sake of speed / efficiency, I might book 100 orders through a web service, where the payments have to be released in a windows client. In order to release them, I need those order IDs. The easiest way to do that is to update excel which contains the cell references.

It's easier to use excel for this because when writing the cases you can see the references and it's a little easier to manage.

Hopefully there's a way to do what I'm asking, it's been my only consistent complaint about Ranorex so far - the excel support could be improved (or show me how to do what I'm asking and then we can have that support implemented ourselves) :)

User avatar
Ciege
Ranorex Guru
Posts: 1335
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Desperately trying to pass data from one iteration to next

Post by Ciege » Thu Sep 13, 2012 6:40 pm

You can try using Excel through Interop and do everything you want quite easily yourself...

Take a look (if you haven't already) of this Excel framework of methods that include reading and writing of single cells, ranges, named ranges, etc...
http://www.ranorex.com/forum/my-excel-f ... t3265.html
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!

Ciege...

carsonw
Posts: 178
Joined: Tue Nov 08, 2011 10:01 pm

Re: Desperately trying to pass data from one iteration to next

Post by carsonw » Thu Sep 13, 2012 11:44 pm

Thanks for the response! Yes I had a look at your excel methods - quite a lot of work there!

However, and please correct me if I'm wrong, I don't think it uses the data connector, does it? The solution you have seems to pull all of it's data to/from excel and updates the sheets etc. from there - but there's no using / update the data connector that Ranorex uses - is that correct or did I miss something (which is entirely possible)?

We like using the data connector, because the module bindings are a handy feature and we like the test suite interface and that set up. I think that's a great way of managing data and we don't want to lose or bypass that functionality.

What we're really after is getting a way for the connector to reload the data from the excel sheet - I've got it figured out for how to do it by physically updating the file, but I'd much rather just do it in memory (by reloading the sheet object into the cache somehow), but I don't know how to do that given the methods associated with the DataCache (or is it the DataConnector, I'm not sure).

So in my second post the code sample that I gave - that's really what we're after:

Code: Select all

  foreach (Ranorex.Core.Data.DataCache cache in TestSuite.Current.DataConnectorCaches)
           {               
               if(cache.Connector.Name == connector.Name)
               {
                  //cache.LoadDataFromsheetToBeUpdatedSomehow <-- if I could do this, then I could reload the data cache from the sheet I have in memory without having to actually save the sheet.
               }
            }
Really appreciate the help/insights/suggestions from the community though, but so far it's just not what we're after.

User avatar
artur_gadomski
Posts: 207
Joined: Mon Jul 19, 2010 6:55 am
Location: Copenhagen, Denmark
Contact:

Re: Desperately trying to pass data from one iteration to next

Post by artur_gadomski » Fri Sep 14, 2012 7:46 am

How about: make a temporary copy of Test data, use this file as data source, save into it all you want. This way you still keep the original test data but you're able to 'update' it.

Also look into RobotFramework or other keyword test framework. As far as I understood you created your own keyword framework using Ranorex Iteration and data connection and it's not very flexible.

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Desperately trying to pass data from one iteration to next

Post by Support Team » Fri Sep 14, 2012 8:27 am

Hi,

You can for instance manipulate the data from the connector in the setup region of the specific test case with the following code:
var dataConn = DataSources.Get("YourConnector");
dataConn.Rows[1].Values[1]="test";
this code can just manipulate the data from the connector of the actual testcase, this code won't work if you want to edit the values of a data connector of another test case.

I hope this helps,
Regards,
Markus
Ranorex Support Team
.
Image

carsonw
Posts: 178
Joined: Tue Nov 08, 2011 10:01 pm

Re: Desperately trying to pass data from one iteration to next

Post by carsonw » Tue Sep 18, 2012 6:22 pm

To support: I did try what you're suggesting, but that doesn't solve the problem (explained in my original post).

Yeah, we're probably going to be stuck with making a temporary copy and going from there depending on how support answers the following question:

Question for support then - somehow the connector is loading the data from the spreadsheet at launch. Is there way I can simply reload the data from the worksheet being held in memory (I have the worksheet object available to me)?

This would solve all my problems! :D

Thanks!

Carson.

User avatar
IanF
Posts: 60
Joined: Thu May 24, 2012 12:37 am
Location: Brisbane, Australia
Contact:

Re: Desperately trying to pass data from one iteration to next

Post by IanF » Tue Sep 18, 2012 10:09 pm

Dynamic data seems to be a concept that Ranorex simply don't understand. Since reading your post I have been playing with this myself because I will have the same issues as you going forward.

The soultion is either Ranorex wake up and make data dynamic...

or

You use another data handling process. The ExcelFramwork files mentioned here are pointer in the right direction but they are seriously wanting as a dynamic data tool. There is some work being done to port some Excel data handling code I used in QTP. When its at a point worth sharing I will post it here.
Ian Fraser

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Desperately trying to pass data from one iteration to next

Post by Support Team » Wed Sep 19, 2012 4:51 pm

Hello,

A working copy of the excel file used in the Data Connector is located in your output directory of your project.
It is possible to modify this file during the runtime. You have to place the code which changes the excel file before the test case which uses the data connector.
For example:
Excel.Application excelDataconnectorSourceFile = new Excel.ApplicationClass();       	
excelDataconnectorSourceFile.Visible = true;
string workbookPath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelFile.xlsx";
Excel.Workbook excelWorkbook = excelDataconnectorSourceFile.Workbooks.Open(workbookPath);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
excelDataconnectorSourceFile.Cells[1,4] = "Insert value during runtime";
excelWorkbook.Save();
excelWorkbook.Close();
I hope this is what you want to do.

Regards,
Bernhard
Ranorex Support Team
.
Image

User avatar
IanF
Posts: 60
Joined: Thu May 24, 2012 12:37 am
Location: Brisbane, Australia
Contact:

Re: Desperately trying to pass data from one iteration to next

Post by IanF » Thu Sep 20, 2012 1:03 am

Ranorex

How hard would it be to have in the "Add New Action" a selection that wrote variables out to a data file?
Ian Fraser

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Desperately trying to pass data from one iteration to next

Post by Support Team » Thu Sep 20, 2012 4:55 pm

Hello Ian,

I already added a request to our feature request list. We will discuss internally if we will implement this feature in one of our future releases. Thank you for the suggestion!

Regards,
Bernhard
Ranorex Support Team
.
Image

carsonw
Posts: 178
Joined: Tue Nov 08, 2011 10:01 pm

Re: Desperately trying to pass data from one iteration to next

Post by carsonw » Fri Sep 21, 2012 7:24 pm

Support Team wrote:Hello,

A working copy of the excel file used in the Data Connector is located in your output directory of your project.
It is possible to modify this file during the runtime. You have to place the code which changes the excel file before the test case which uses the data connector.
For example:
Excel.Application excelDataconnectorSourceFile = new Excel.ApplicationClass();       	
excelDataconnectorSourceFile.Visible = true;
string workbookPath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelFile.xlsx";
Excel.Workbook excelWorkbook = excelDataconnectorSourceFile.Workbooks.Open(workbookPath);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
excelDataconnectorSourceFile.Cells[1,4] = "Insert value during runtime";
excelWorkbook.Save();
excelWorkbook.Close();
I hope this is what you want to do.

Regards,
Bernhard
Ranorex Support Team

This doesn't work unless you also reload the data connectors (I mentioned in my first post). This is all well and good but it STILL doesn't answer my question as to whether or not I can just feed the data to the connector myself. I have the worksheet in memory, it's all there, I just need a way to give it to the connector. Thus far, I see no way of doing that. This is my preference to having to update the file itself, which I'd rather not do (original or copy I don't want a test to stall because of a locked file or whatever, especially when I can just hold the object in memory and deal with it there).

If that were possible, then I'd have complete control over the data management. I appreciate all the various ideas, but I've already explored them, explained why they don't solve our problem, and am trying to get a clear answer as to whether or not I actually want to do is possible (and if so how). If it's not, then I'd like to request it as a feature.

All we would need is a way to feed the columns and rows to the data connector, it must already be doing this somehow, I just can't access the methods.

User avatar
Support Team
Site Admin
Site Admin
Posts: 11709
Joined: Fri Jul 07, 2006 4:30 pm
Location: Graz, Austria

Re: Desperately trying to pass data from one iteration to next

Post by Support Team » Tue Sep 25, 2012 11:54 am

Hello,

Do you want to use the data connector in another test case then the test case you changed the data? If this is the case it is not possible to use the modified data of the previous data connector because the content will be loaded in each test case you bind the data connector. The data connector will load the data from the original data source. It should work if you want to manipulate the data of the current test case. Is it possible to send us a solution with this issue?

Thank you!

Regards,
Bernhard
Ranorex Support Team
.
Image