Page 1 of 2

Desperately trying to pass data from one iteration to next

Posted: Wed Sep 12, 2012 8:46 pm
by carsonw
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!

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

Posted: Wed Sep 12, 2012 9:50 pm
by carsonw
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.
					}
				}

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

Posted: Thu Sep 13, 2012 10:32 am
by artur_gadomski
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.

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

Posted: Thu Sep 13, 2012 5:46 pm
by carsonw
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) :)

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

Posted: Thu Sep 13, 2012 6:40 pm
by Ciege
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

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

Posted: Thu Sep 13, 2012 11:44 pm
by carsonw
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.

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

Posted: Fri Sep 14, 2012 7:46 am
by artur_gadomski
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.

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

Posted: Fri Sep 14, 2012 8:27 am
by Support Team
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

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

Posted: Tue Sep 18, 2012 6:22 pm
by carsonw
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.

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

Posted: Tue Sep 18, 2012 10:09 pm
by IanF
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.

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

Posted: Wed Sep 19, 2012 4:51 pm
by Support Team
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

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

Posted: Thu Sep 20, 2012 1:03 am
by IanF
Ranorex

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

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

Posted: Thu Sep 20, 2012 4:55 pm
by Support Team
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

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

Posted: Fri Sep 21, 2012 7:24 pm
by carsonw
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.

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

Posted: Tue Sep 25, 2012 11:54 am
by Support Team
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