QTP框架开源第一波之数据驱动

lanbingkafei

Test Automation Framework –Creating Test data tables
1        论坛有字数限制，这一部分省略……
2        CONVENTIONS FOR CREATING TEST DATA SOURCES
•        Store the required external test data files (created using the template) in corresponding module-specific sub-folders in the “Test_Data” folder.
Note:  In Quality Center environment, please ensure that the sub-folder structure that is maintained in the “Test_Data” and “Test_Scripts” folder is identical.
•        In each Test data table, the data for only one automated test script should be provided.
•        The name of the test data table should be the same as that of the automated test script for which the data is stored.
•        The names of each of the columns in the table should be unique.
•        Each data table must have an “Execute” column:
o        Allowed values in the “Execute” column are “Yes” and “No”.
o        These values determine whether the data in the row is used during script execution.
3        PROCEDURE FOR CREATING TEST DATA FILES IN EXCEL FORMAT
Steps to execute: For creating one or multiple Test data sources in Excel format.
a)        Copy the template “Test_Data_Source_Template.xls” from the “Test_Templates” folder to the required sub-folder in “Test_Data” folder.
b)        After placing it in the desired sub-folder in the “Test_Data” folder, the test data file should be renamed as per requirement.
c)        If required, repeat the above steps for creating multiple test data sources that can hold the test data for automated test scripts of multiple modules.
Note: Refer the “instructions” worksheet available in the “Test_Data_Source_Template.xls” file for the conventions to follow when creating test data tables in the test data source files.
4        PROCEDURE FOR CREATING TEST DATA TABLES IN EXTERNAL FILES
Example scenario:
A data file named “Login_Data” is used to store the test data for the “Login” functionality of the “Flight Test” application.
What is the procedure for creating the test data table that meets the following requirements?
•        It should have columns named “Agent Name”, “Password”, “Expected Result” and “Execute”
•        Both the Agent Name and Password columns in the table stores alphanumeric data.
•        The Expected Result column stores the title text of the next window displayed upon successful login.
•        Five rows of test data are to be stored in the table.
Steps to execute:
a)        Open the test data file named Login_Data.xls stored at c:\MyProject\Test_Data\Login\.
b)        Rename the worksheet “Sheet_Name1” to “LoginData”.
c)        In the “LoginData” worksheet, select the row where the column names of the table are to be entered and type the names of the required columns as shown here.
d)        . Because the requirement is to store five rows of test data values in the table, select the range of cells comprising the column headers and five additional rows below them.
e)        From the Insert menu, select Name and then Define.
f)        On the Define Name window, in the Names in Workbook field, type LoginTest, and click OK. This step creates a table that can hold five rows of test data in the corresponding columns.
g)        Verify that the required table was created. From the list of table names in the upper-left corner, select the name of the table you just created (LoginTest). The range of cells for that table will be selected as shown here.
h )        Define the format of the data in the columns. We’ll format the Agent Name column as an example.
Select the range of cells in the rows below the Agent Name header. From the Format menu, select “Cells”.
i)        On the Format Cells window, select Text from the Category list, and click OK.
Note: In instances where different rows of the same column may hold numeric and text values, it is recommended that you select the Text format to avoid runtime errors.
Adding Test data to rows in Test data table
a)        In the rows, under the corresponding column enter the required data. Under the “Execute” column, if you want the automated test script to use this row of test data for execution at runtime, enter the value “Yes” else enter the value “No”.
b)        Repeat the above steps for all required test data values.
Note : If you have added Test data rows that are less than the number of the rows of the overall table, this will not impact the result of execution in any way. But if you have added rows of test data that are beyond the range of the table, the extra rows will not be considered for execution. In this scenario, you will have to expand the range of the table in the following way.
Delete the table by using “Insert -> Name – Define” and then select the required table name from the list of values and then click on “Delete” button. After this, select all the extra rows of test data (there is no need to delete the data that already exists) and create the table by following the process as explained earlier.
j)Because one worksheet can include multiple tables, it is recommended that you put the name of the table above the corresponding column headers as shown here. This approach helps to identify tables containing the data of an automated test script.  
Repeat the same process to add multiple test data tables in the same worksheet or other worksheets of the Test data source in excel format. But in creating multiple tables of data, where each table has data corresponding to its automated script, you have to take care to ensure that the ranges of cells that you select for each table do not overlap.

==============以上是制作数据表格的方法，虽然没办法传图片，按照这个步骤，也可操作======
===================以下是访问刚才制作的数据表格的代码===================================

1. 'Connect to the External Data file that contains the required test data table.
   
2. Call Connect_Test_Data_Source( "ORDER","Order.xls")
   
3. 
   
4. ' After the above function call is executed, the variable g_objResultSet will have reference to the returned recordset.
   
5. 
   
6. 'Get the count of test data rows in the test data table that have been selected for execution
   
7. total_DataRows_Selected =g_objResultSet.Recordcount
   
8. 
   
9. 'Validate whether the test data table has atleast one row of test data selected for execution.
   
10. 
    
11. If total_DataRows_Selected < 1 Then
    
12.         Reporter.ReportEvent micFail, "Validate for Input Data","Please do provide the input data for the execution"
    
13.         ExitTest
    
14. End If
    
15. 
    
16. 'Iterate the test for all the selected rows of test data
    
17. For Row_Index =1 to total_DataRows_Selected
    
18.         For DummyLoop =1 to 1
    
19. 'The dummy loop Used to establish the following control. In case the script encounters a failure for a particualar data row. instead of aborting the test
    
20. 'the user can use ExitFor to exit the current loop and still continue the test for the remaining rows of test data.
    
21.         Reporter.ReportEvent  micDone, "Data Iteration : " &Row_Index,"Scenario is executing for " & Row_Index & " data set"
    
22.                 Username =Get_DataValue(Row_Index,"USERNAME")
    
23.                 Password = Get_DataValue(Row_Index,"PASSWORD")               
    
24.                 'TODO:Your automation code
    
25. Next

复制代码

1. '============================================================================================================================
   
2. 'Function Name    : Connect_Test_Data_Source
   
3. 'Description           :  Establishes the Connection with the Specified Test Data source & assigns the recordset to the global variable - g_objResultSet
   
4. 'Applicable Objects: None
   
5. 'Arguments            : FolderName, FileName in which the Test Data is present
   
6. 'Return value        : None
   
7. 'Example                            : Connect_Test_Data_Source("c:\Flight Reservation","Transaction.xls")
   
8. '============================================================================================================================
   
9. Function Connect_Test_Data_Source(FolderName,FileName)
   
10.            'Download the datafile from QC to Local drive
    
11.                 'Call Downloadfile_From_QC(FolderName,FileName,Environment.Value("TEST_DATASOURCE_LOCATION"))
    
12.            TestID = Environment.value("TestName")
    
13.            Set g_objConnection=CreateObject("ADODB.Connection")
    
14.            Set g_objResultSet =CreateObject("ADODB.RecordSet")
    
15.            If QualityCenter_Environment="Yes" Then
    
16.                    If FolderName <> "" Then
    
17.                            RetVal=Downloadfile_From_QC(arrFolders(0) & "\Test_Data\"  & FolderName ,strLocalPath & "\Test_Data\" & FolderName &"\" ,FileName)
    
18.                    Else
    
19.                                 RetVal=Downloadfile_From_QC(arrFolders(0) & "\Test_Data\"  & FolderName ,strLocalPath & "\Test_Data\"  ,FileName)
    
20.                    End If           
    
21.                         If RetVal<>"PASS" Then
    
22.                                 Reporter.ReportEvent micFail,"Download File from QC",RetVal
    
23.                                 ExitTest
    
24.                         End If
    
25.                         g_objConnection.ConnectionString ="DBQ=" & strLocalPath & "\Test_Data\" & FolderName &"\" & FileName &";Driver=Driver do Microsoft Excel(*.xls)"
    
26.            Else
    
27.            g_objConnection.ConnectionString ="DBQ=" & arrFolders(0) & "\Test_Data\"  & FolderName &"\"& FileName &";Driver=Driver do Microsoft Excel(*.xls)"
    
28.            End If
    
29. 
    
30.            g_objConnection.Open
    
31.                 g_objResultSet.Open "select * from " & TestID & " where Ucase(Execute) ='YES'",g_objConnection,1,1
    
32. End Function

复制代码

lanbingkafei

回复 10# hsjzfling


    哈哈，我喜欢竞争••••你或许是我的动力，你共享一套框架的百分之五十，我奉陪你一套，我不太喜欢直说不做•••

hsjzfling

打住吧，不要越跑越偏，背离了原题了。共享也是为了能够让更多的人能够学到知识，增强技能，而不是让观众看戏的吧~讨论也是能让大家都有所收获，可以多些更深的认识。

hsjzfling

回复 9# lanbingkafei


  LZ既然坚持知识传递才能体现价值，为嘛自己还藏着掖着只共享部分代码或偶尔来个冰山一角呢
同时是不是说LZ没共享不传递的那一大部分就不能体现价值，那要它作甚~~~

PS：看到回复的时候并没有那个注释，后来编辑的吧……

lanbingkafei

回复 8# hsjzfling


    就怕你误会，还特意注明了一下……   你想多了，你我都共享……还是这句话：知识是用来传递的，传递才能体现知识的价值

hsjzfling

LZ共享也知道只共享40%，1楼中的也只是拿出冰山一角，这样的残品框架就算拿出100套对于其它学习者来说有什么太多意义呢，并不能让学习者们有更有价值的收获，有违分享的初衷了，伤十指不如断一指，让学习者们弄透彻一套才是关键所在。

淡定，斗气就无聊了。

若觉得本人评论有失偏颇，可以指出反驳；无私分享的行为是值得肯定的，若不能接受本人的评论，本人收回以上所有文字，不打扰LZ无私为大家奉献就是了

lanbingkafei

回复 6# hsjzfling


    框架思想基本大同小异……  只是实现细节上不同而已！！！这位仁兄，我已经共享出来一套基本框架了，你是否也把你的共享出来，咱们不止是说说而已，你共享几套框架，我也共享几套，另外，如果我这边没有你的框架多或者没你的好，我会把我的C++单元测试、代码安全测试、性能诊断和调优经验的视频也共享出来…… 可敢和我比试一下？（不带有任何挑衅意思，勿误解，只是为开源做贡献）   知识是用来传递的，传递才能体现知识的价值，你说不是吗？ 咱俩不但要说，而且要做，为测试行业做点贡献，我觉得你也赞同我的观点！！！

hsjzfling

回复 4# lanbingkafei

不好意思，上次只是大致扫了几眼，没注意到上面第一段那几行代码……从这里看出只是框架支持数据驱动功能，数据驱动并为集成到框架中去，具体的差别你可以拿自己的框架和QTP工具本身已经做好的功能做下比较。至于LZ回复中的第一句话，用ADO三个字母概括下就可以了……

这个框架大体上还是和我四五年前写的框架结构类似，细节上肯定诸多不同，但思路上都是大同小异，应该也属于现阶段使用比较广泛的类型。在这边留言也是希望LZ能更多的为大众分享。当然如果能有让我眼前一亮的东西，我也会不吝给大家一些惊喜。

lanbingkafei

回复 3# hsjzfling


    另外回复这位仁兄，你看到数据驱动框架是整个框架的冰山一角……  我只是共享最基础部分，并不会把整个框架共享出来……  论坛并不适合讨论， 欢迎加入TIB自动化测试北京群：195522961

lanbingkafei

回复 3# hsjzfling


   我把excel模拟成数据库来用，你可以自己按照步骤操作一遍，所有的数据可以自定义成datatable，之后可用用标准的SQL语句对这个excel里的数据进行操作，比如：update、select等操作，如果你那边有什么好的思想，可以共享一下。另外，如果没在实践中用到这些，是感觉不到这个数据驱动的优点和缺点，希望大家都评论，同时，可以指出一些缺点，并同时共享自己优化后的代码和思想……

hsjzfling

总的来说是值得肯定的，不过仅目前看到的部分，还不足够让人眼前一亮哦~
思路都还是中规中矩的，支持从QC中读取Excel数据表，通过ADO的方式访问Excel把需要执行的数据集返回到g_objResultSet对象中去，不过只是实现数据驱动的前半部分，只是读取数据集。单纯看这里，还不能确定究竟是数据驱动框架呢还是只是支持使用数据驱动的框架。关联用例与数据、执行数据的部分的框架结构会更加重要。

lanbingkafei

回复 1# lanbingkafei


   公司网络缘故，图片暂时无法上传&……