DataTables in QTP

When you test your applications, you may want to check how the application performs the same operations with multiple sets of data. For example, suppose you want to check how your Web site responds to ten separate sets of data. You could create ten separate tests, each with its own set of data. But thats not feasible and code length is going to very large. Your sets of data can be as large as 1000 values. So writing same code lines for each value of test data is not a good practice. Alternatively, you can create Data Table parameters so that your test runs ten times, each time using a different set of data. 

DataTable is just like Microsoft Excel file. The DataTable contains a global sheet and a separate sheet for each action in the test. So we two types of sheets in data table.

1. Global sheet

2. Local sheet

Global sheet is available to all actions in the test and local sheet is available to the corresponding action.  The best practice is to use local data sheet while parameterizing the test.

Types of DataTable:
Design Time DataTable: While creating editing the test scripts, you enter data directly in the data table  displayed there. You can view data table by selecting View >> Data Table option. This data table is called Design Time DataTable.

Runtime DataTable: The data table used while running the test is called Runtime DataTable. The data in the runtime data table may be same as design time data table and/or can be entered by importing data from excel sheet, text file etc. The runtime data table can be seen in the Test Results window when run session is ended.

DataTable Parameter:

Each column in global/local sheet of datatable is called a Parameter. We can rename the parameter just by double clicking on the column header and giving the name to it. Data can be entered in the column/parameter simply by clicking on the cell and entering the value.

Parameterizing the test:

Instead of using hardcoded values in the script, we can store the test data in the data table and give reference of same in the script which help perform the same operations on the AUT with multiple sets of data. This all is done through data table methods and properties.

DataTable Methods:

These methods are applied on either DataTable object or DTSheet object or DTParameter object. Here are the methods and their properties:

DataTable Object - Methods

Add Sheet:
Adds the specified sheet to the run time data table.
DataTable.AddSheet(“My Sheet”)

DeleteSheet:
Deletes the specified sheet from the run time data table.
DataTable.DeleteSheet “My Sheet”

Export:
Exports the complete data table to the specified path.
DataTable.Export(“C:\abc.xls”)

ExportSheet:
Exports the current sheet to the specifed path.
DataTable.ExportSheet “C:\abc.xls”,1

GetCurrentRow: (for global sheet)
Retrieves the current row number of the global data sheet.
Row=DataTable.GetCurrentRow
Reporter.ReportEvent 1, “Row Number”, row

GetRowCount: (for global sheet)
Retrieves the number of rows in the longest column in the first sheet(global data sheet) in the run time data table.
Rowcount=DataTable.Get Sheet(“My Sheet”).GetRowCount
Reporter.ReportEvent 2, “There are” &rowcount, “rows in the data sheet”

GetSheet:
Retrieves the specified sheet in the run time data table.
DataTable.Getsheet(“My Sheet”).AddParameter(“Time”,”08:00”)

GetSheetCount:
Retrieves the total number of sheets in the run time data table.
Sheetcount=DataTable.GetSheetCount
Reporter.ReportEvent 0, “sheet number”, “There are” &sheetcount “sheets in data table”

Import:
Import the specified Microsoft Excel file to the run time data table.
DataTable.Import(“C:\abc.xls”)

ImportSheet:
Imports the specified sheet of Microsoft Excel file to the run time data table.
DataTable.ImportSheet”C:\abc.xls”, 1, ”Name”

SetCurrentRow:
Sets the specified row as the current(active) row in the run time data table.
DataTable.SetCurrentRow(2)

SetNextRow:

Sets the row next to the current row as the new current row in the run time data table sheet.

DataTable.GetSheet(“My Sheet”).SetNextRow

SetPrevRow:

Sets the row above the current row as the new current row in the run time data table sheet.

DataTable.SetPrevRow

DataTable Object - Properties

GlobalSheet Property:Description: Returns the first sheet in the datat table.
DataTable.GlobalSheet.AddParameter “Time”,”08:00”

LocalSheet Property:
Description: Returns the current (active) sheet of the data table.
MyParam=DataTable.LocalSheet.AddParameter “Time”,”09:00”

RawValue Property:
Description: Returns the raw value of the cell in the specified parameter of the current row of the data table. The raw value is the actual string written in the cell before the cell is computed, such as the actual text from a formula.
FormulaVal=DataTable.RawValue(“Date”,”ActionA”)

Value Property:
Description: Data table default property. Retrieves or sets the value of the cell in specified parameter and the current row of the data table.
DataTable.Value(2,3)=”New York”

DTParameter - Methods

DtSheet.AddParameter:   (Adds a new column.)

DataTable.GetSheet(“dtGlobalSheet”).AddParameter “NewColumnName”,”FirstRowVaule”

DtSheet.GetParameter:

Description:  Retrieves the specified parameter from the run time data table sheet.

DataTable.GetSheet(“My Sheet”).GetParameter(“Destination”)

DtParameter – Properties

Name Property:

Description: Retrieves the name of the parameter in run time data table.

ParamName=DataTable.LocalSheet.AddParameter(“NewColumnName”,”Row1Value”).Name

RawValue Property:

Description: The raw value of the cell in the current row of the parameter in the run time data table. The raw value is the actual string written in the cell before the cell is computed, such as the actual text from a formula.

The following example uses the RawValue property to find the formula used in current row of the Date column in the ActionA sheet of the run time data table.

The statement below runs the Value:=Now()

FormulaVal=DataTable.GetSheet(“ActionA”).GetParameter(“Date”).RawValue

Value Property:

Parameter default property. Retrieves or sets the value of cell in the current row of parameter in the run time data table.

DataTable.GetSheet(“ActionA”).GetParameter(“Destination”).Value=”New York”

ValueByRow Property:

Retrieves the value of the cell in the specified row of the parameter in the run time data table.

DataTable.GetSheet(“ActionA”).GetParameter(“Destination”).ValueByRow(4)

DTSheet Methods

AddParameter Method:

Add the specific parameter(column) to the sheet in Run Time data table, sets the value of the first row to the specified value, and returns the parameter so that you can directly set or retrieve properties of new parameter in same statement.

Variable=DataTable.AddSheet(“My Sheet”).AddParameter(“Time”,”08:00”)

DeleteParameter Method:

Deletes the specified parameter from the sheet in run time data table.

DataTable.GetSheet(“My Sheet”).DeleteParameter(“Time”)

GetCurrentRow Method:

Returns the row number of the current(active) row in the run time data table sheet.

Rownum=DataTable.GetSheet(“My Sheet”).GetCurrentRow

Reporter.ReportEvent 1, “Row Number”, rownum

GetParameter Method:

Returns the specified parameter from the run time data table sheet.

DataTable.GetSheet(“My Sheet”).GetParameter

GetParameterCount Method:

Returns the total number of parameters(columns) from the run time data table sheet.

Paramcount=DataTable.GetSheet(“My Sheet”).GetParameterCount

Reporter.ReportEvent 2, “There are”, &paramcount, “columns in this sheet”

GetRowCount Method:

Returns the total number of rows in the longest column in the run time data table sheet.

rowcount=DataTable.GetSheet(“My Sheet”).GetRowCount

Reporter.ReportEvent 2, “There are”, &rowcount, “active rows in this sheet”

SetCurrentRow Method:

Sets the specified row as the current row in the run time data table sheet.

DataTable.GetSheet(“My Sheet”).SetCurrentRow(2)

SetNextRow Method:

Sets the row next to the current row as the new current row in the run time data table sheet.

DataTable.GetSheet(“My Sheet”).SetNextRow

SetPrevRow Method:

Sets the row above the current row as the new current row in the run time data table sheet.

DataTable.GetSheet(“My Sheet”).SetPrevRow

DTSheet Properties:

Name Property:

Returns the name of the run time data table sheet.

Sheetname=DataTable.LocalSheet.Name

Reporter.ReportEvent 1, “The active sheet is”, sheetname

Object Repository

As you know QTP works on the principal of identifying the objects in the application under test and performing the specified action on that objects. But to work on that objects, the objects should be stored in the object repository.


Object Repository is we can say the heart of QTP where it stores the objects of application under test. While running the test, QTP compares the object available in object repository(called Test Objects) with the objects in application(called RunTime Object). If properties of both test object and runtime object macthes then the specified action is performed on that object.


Types of Object Repositories:
Object Repositories are of two types:
1. Local OR:  By default each action has a local object repository. Local OR is action specific i.e. the objects added to local object repository are available to that action only thats why the local object repository is also called Per-Action Object Repository.When we are recording, the objects are added to the local object repository of that action. Extension for local OR is (.mtr). 
2. Shared OR: Shared object repository can be used by any action in current test and/or any other test. To make a shared object repository:
A. Open object repository window.
B. Select File >> Export Local Objects
C. Select location where you want to save shared object repository.
D. Click on SAVE button.
Now the shared object repository is saved at specified location with all objects of your local object repository. Extension for shared OR is (.tsr)


How to add object to Object Repository:
Objects are added to object repository automatically when we are recording the test in QTP. All the objects that we use while recording gets added to object repository. For example while recording if we click on a link, button then that link/button object gets added to object repository similarly when we enter a value in a text box(like username, password) that text box object gets added to object repository.
But if we are not using recording then we can add object manually. Here are the steps:
1. Open Object Repository from Resources >> Object Repository 
2. Click on the "Add objects to local" icon from toolbar.
3. Now QTP is minimized and application is on front. Click on any object in application.
4. Object selection window is displayed. Select the required object.
5. Click on OK button.
Now the object selected in Object Selection Window is added to your local object repository.


How to associate shared OR with an Action:
1. Open  the test and select the action with which you want to associate shared OR.
2. Go to Edit >> Actions >> Action Properties
3. Select "Associated Repositories" tab
4. see that by default Local Object Repository is associated with action.
5. Click on "+" button.. 
6. Click on "...." button to browse for shared OR.
7. Browse and select shared OR..
8. Click on Ok button.
Now shared OR is associated with the action. If all object of local OR are available in shared also then we can remove those objects from local OR and still your script will work.


Merging two Shared Object Repositories:
Shared object repositories are used so that the same objects can be used by multiple test. We can create a single shared OR by merging multiple shared ORs but at a time we can merge only two shared object reporisories.
1. Select Resources >> Object Repository Manager
2. Now in OR Manager, Select Tools >> Object Repository Merge Tool
3. Browse and Select two shared ORS, one as primary and other as secondary.
4. Click on OK button.
5. Now the merged object repository's objects are displayed in right side .
6. Select File >> Save option. Select location and click SAVE.
Now the merged object repository is saved at specified location. It can be associated with an action in same way as described above.


Editing Shared Object Repository:
Now you have associated a shared objecr repository to the an action. But if you see that object repository now you will find that the objects in shared OR are disabled. You do not have permission to edit the shared OR. But we need to add few more objects to the object repository. This is also possible with help of Object Repository Manager(ORM).
1. Select Resources >> Object Repository Manager
2. Select  File >> Open
3. Browse the Shared OR you want to edit. Its not editable now.
4. Select File >> Enable Editing option.
5. Now you can add objects with help of "Add Objects" icon as described in "How to add object to Object Repository"  section above.


Important Points:
1. Object Repository is centralized place to add object of AUT.
2. Local object repository is given preference by QTP. If both local and shared OR are associated with action then QTP looks for the object in Local OR first and if not found in local then it will look in the shared OR.
3. Shared OR enhances the reusability of the objects so while automating the test we should use shared object repository.
4. If an object with the same name and description is located in more than one shared object repository associated with the same action, the object definition is used from the first occurrence of the object, according to the order in which the shared object repositories are associated with the action.
5. When a Reusable action is called then it brings its associated object repositories also.