QC DATA PULLER using C#

• Download source - 515.25 KB

Abstract

Using this application, a user can generate a report of a test case execution from QC (Quality center) into an HTML web page with a fancy pie chart and a tabular format using C# and OTA API expose from QC (Quality Center).

Introduction

Before jumping to the main point, let me clarify that there would be so many articles available on the internet to generate Test cases execution report using VB.NET but using C# for the same you will find very little information. I have developed this app using C#.NET. In this app, you will get feasibility to generate a report as an HTML web page from QC and the HTML page would contents the pie chart of test cases executed with tabular format along with this you can email this report to your whole team member. Apart from that, you can store the result back to QC location for future use.

What are the New Features I have Provided?

This application is a whole bundle package, just one *.exe runs and the result would be in your inbox.

Using this, you will be able to:

1. Generate test cases report.
2. Draw Pie chart based on report.
3. Store the result in HTML webpage.
4. Email this report using any email service provider (like Outlook, etc.)
5. Save the result to local hard drive or back to QC location.
6. Be an early morning manager ready with the report.

Refer to the below graph for the best explanation:

Background

As I am into C# automation and not very good in VB script and in QTP, I thought of using C# for this purpose. There isn't much help available on the internet for OTA of QC for C#.NET and working in a big organization, the people at the top are more interested in data, fancy report irrespective of your oral conversation and they believe more in the report. So I thought of attracting the C# Automation guys also towards QC (Quality center) API reports and finally at one points of time, there would be enough information available on the net.

Last but not least, this is my small effort to achieve this.

What is OTA API?

You will get loads of information available on the net for the same. Just Google it or Bing it, you will get enough information. But still I would prefer to give you some basic information for the same.

• The Open Test Architecture (OTA) API is a COM library that enables you to:
• integrate external applications with Quality Center
• interact with the Quality Center application without having to use the GUI front-end
• interact with the QC databases bypassing DBA
• The API functions are accessible through COM-compatible programming languages, such as Visual Basic, VBScript, C++, C#, etc.
• The API has one entry point - TDConnection object

Implementation

I have used C# programming language to achieve my purpose. To talk with the QC, I have included TDAPIOLELib DLL Provided by mercury labs. We have used console application from Visual Studio IDE to develop this application so that we can easily schedule this into Windows scheduler for running at regular time intervals. To store all the settings and inputs values, I have used app.config file to store the configuration value. Overall, I have used a very easy and direct approach to achieve my task so that even the person who is new to C# can understand this.

The HTML report would appear something like this:

Using the Code

Part 1: App.config

Before going deep into the code, I would love to explain the App.config file which I have used to store all the inputs settings. So that you will get an idea like prerequisite:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<!-- QC server location to access the QC website-->
<add key="QCserverURL" value="QCServerURL"/>
<!-- QC domain name -->
<add key="QCdomainName" value="Your Domain Name"/>
<!-- the name of the project for which you want to 
retrieve the test case execution report-->
<add key="QCprojectName" value="Your project Name"/>
<!-- user name through which you have to login in to QC-->
<add key="username" 
value="<a href="mailto:jawed.ace@gmail.com%22/%3E">jawed.ace@gmail.com</a><!--"/>
 Password to login in to QC-->
<add key="pwd" value="myPassword"/>
<!-- Folder names provided in to QC from which you want to generate report. 
Multiple location should be separated by semicolon(;)-->
<add key="QCFolderLocationForTestCase" 
value="Root\Integration Team\Anna-GR-4\ePrint;Root\Integration Team\Anna-GR-4\ SIPs"/>
<!-- the title of the pie chart, will be appear before putting the graph-->
<add key="ChartTitleForTestCasesReport" value=" eprint ; Sips"/>
<!-- location where you want to store all the pie chart generate by code-->
<add key="locationToSavePieChartImages" value="C:\QCReportImages\"/>
<!-- Test case status names-->
<add key="TCStatusNames" value="Not Completed;No Run;
Failed;Passed;Accepted Failure;Blocked;N/A;Repair;Unsupported"/>
<!-- location to store the HTML file or report generated-->
<add key="htmlWebPageLocation" value="C:\\QcReportIntegrationTeam.html"/>
</appSettings>
</configuration>

Part 2: Main Program

This will be our main idea to put forward to work as per our requirement or how we want to handle the code to work for us.

Declaring namespace:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.IO;
using System.Threading;
using System.Diagnostics;
using System.Net;
using System.Drawing;
using System.Drawing.Drawing2D;
using TDAPIOLELib;

To add TDAPIOLELib namespace, follow the below steps:

1. Go to your solution explorer
2. Click on References
3. Select add references
4. On opening window clicked on Browser tab
5. Go to the location C:\Program Files\Common Files\Mercury Interactive\Quality Center
6. Select OTAClient.dll and click on Ok button, if you don’t have OTAClient.dll, then you need to download it from the web.

   Refer to the below figure for details:

   

   Fig 4. Adding OTAClient.dll
7. Go to your main program, add the namespace as shown in the below figure:

   

   Fig 5. Adding TDAPIOLELib name space.

   Now, you ready to use class and function provided by OTA API. We start our programming here after.

   We will read all the settings provided in App.config file into our local strings so that we can use it in the program here and there.

   //Read all the inputs value from App.config file and store 
   //into variable for further use.
   //variable to store UserName to login in to QC
   string username = ConfigurationSettings.AppSettings["username"].ToString();
   //Variable to store Password
   string pwd = ConfigurationSettings.AppSettings["pwd"].ToString();
   //Names of the test Cases similar to QC
   string strTestCaseStatus = 
   ConfigurationSettings.AppSettings["TCStatusNames"].ToString();
   //Folder Name for test cases uploaded in to QC(Root\\Integration Team\\Anna-GR-3)
   string testSetFolderPath = 
   ConfigurationSettings.AppSettings["QCFolderLocationForTestCase"].ToString();
   //Title of the Pie Chart
   string chartTitleTestCasesReport = 
   ConfigurationSettings.AppSettings["ChartTitleForTestCasesReport"].ToString();
   //Location on to local derive where we want to store Pie chart
   string strLocationToSavePieChartImages = 
   ConfigurationSettings.AppSettings["locationToSavePieChartImages"].ToString();
   //Server Location of QC
   string strServerURL = ConfigurationSettings.AppSettings["QCserverURL"].ToString();
   //Domain name for QC access
   string strDomainName = ConfigurationSettings.AppSettings["QCdomainName"].ToString();
   //Project name to access from QC
   string strProjectName = 
   ConfigurationSettings.AppSettings["QCprojectName"].ToString();
   //Local derive location to store HTML web page for result
   string strHtmlWebpageLocation = 
   ConfigurationSettings.AppSettings["htmlWebPageLocation"].ToString();

Now we are ready with all the input to start our programming.

1. First login to the QC.

   //Declare an object of TDCoonection 
   TDConnection qctd = new TDConnection();
   //Pass the QC server URL
   qctd.InitConnectionEx(strServerURL);
   //Connect with the QC
   qctd.ConnectProjectEx(strDomainName, strProjectName, username, pwd);

2. Go to the Test lab folder from where you have to read the test cases.
3. Collect all the test cases and their corresponding status in dataset.
4. Using data view the count of various test status like passed, failed, etc.

Class Diagram



Fig 6. Class Diagram

The code is as explained below:

if (qctd.Connected)
{
    //Decaler test set factory
    TestSetFactory testSetFactory = (TestSetFactory)qctd.TestSetFactory;
    //Define filter
    TDFilter filter = (TDFilter)testSetFactory.Filter;
    //Go through each test set folder
    string[] testCasesFolderNames = testSetFolderPath.Split(';');
    //get the chart title names
    string[] arrChartTitleTestCasesReport = chartTitleTestCasesReport.Split(';');
    int chartTitleCount = 0;
    foreach (string testFoldername in testCasesFolderNames)
    {
        if (testFoldername!=null)
        {
            filter["CY_FOLDER_ID"] = "\"" + 
            testFoldername + "\"";
            //get all the list of test cases
            List testSets = (List)testSetFactory.NewList(filter.Text);
            //make sure that the list of test cases is not null
            if (testSets != null)
            {
                //Read all the testcases status from appconfig files.
                string[] arrValueNames = strTestCaseStatus.Split(';');
                //Define dataset to hold all the values for testcases execution
                DataSet dsTC = new DataSet();
                dsTC.Tables.Add("TestCaseExecutionReport");
                dsTC.Tables[0].Columns.Add("TestName");
                dsTC.Tables[0].Columns.Add("TestStatus");
                //Read the Test cases status one by one from 
                //the folder location provided by the user.
                foreach (TDAPIOLELib.TestSet tst in testSets)
                {
                    string nameOfTestSuit = tst.Name;
                    TSTestFactory tstf = (TSTestFactory)tst.TSTestFactory;
                    List ftest = (List)tstf.NewList(" ");
                    //integer value to hold the total status count
                    int[] TCStatus = new int[arrValueNames.Length];
                    try
                    {
                        //trace trough each test cases to get the status
                        foreach (TDAPIOLELib.TSTest test in ftest)
                        {
                            //store that value in to dataset
                            dsTC.Tables[0].Rows.Add
                            (test.Name, test.Status);
                        }
                        //Get the total row
                        int totalRow = dsTC.Tables[0].Rows.Count;
                        //Initialize the value to short the dataset 
                        //to get the status count 
                        int TSrowValue = 0;
                        //read the dataset to get the total status count
                        foreach (string TCfilterValue in arrValueNames)
                        {
                            if (TCfilterValue != null)
                            {
                                DataView dvDataTC = new DataView(dsTC.Tables[0]);
                                dvDataTC.RowFilter = "TestStatus like '" + 
                                TCfilterValue + "'";
                                TCStatus[TSrowValue] = dvDataTC.Count;
                                TSrowValue++;
                            }
                            // TCStatus[TCStatus.Length] = totalRow;
                        }
                        //local variable to use for Pie chart 
                        //images name
                        string strImageNames = nameOfTestSuit + "_" + 
                        DateTime.Now.ToString("yyyyMMddHHmmss") + ".png";
                        //Gee the string for chart title
                        string chartTitle=string.Empty;
                        if(arrChartTitleTestCasesReport.Length==1)
                        {
                            chartTitle = arrChartTitleTestCasesReport[0] + 
                            " " + nameOfTestSuit;
                        }
                        else
                        {
                            chartTitle = arrChartTitleTestCasesReport
			[chartTitleCount] + " " + nameOfTestSuit; ;
                        }
                        //Call a method to create pie chart
                        Bitmap btMapImages = GeneratePieChart.pieChart
                        (TCStatus, arrValueNames, chartTitle);//Save that pie chart 
						// in a particular location
                        btMapImages.Save(strLocationToSavePieChartImages + 
                        strImageNames, System.Drawing.Imaging.ImageFormat.Png);
                        //call a method to create html webpage 
                        //and put all the report over there
                        CreateHtmlPage.saveHtmLPage(TCStatus, nameOfTestSuit, 
                        strLocationToSavePieChartImages + strImageNames, 
                        arrValueNames, totalRow, strHtmlWebpageLocation);
                        //Clear the data set so that all the store rows value 
                        //will get deleted.
                        dsTC.Clear();
                    } //end of try block
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    } //end of catch block
                }
            } //end of checking List for test cases
        }//end of checking for multiple folder
        //increase the count
        chartTitleCount += 1;
    }//end of for each loop
}//end of checking Connection

5. Now we will call pie chart function to draw pie chart for the various test status. We will pass various parameters to the pie chart method to draw our pie chart like: test status count, test status names, chart title.

   For the code to draw pie chart, download the code and go through the code. You can use your own code for that purpose too. There is so much code available on the net to draw a pie chart.

6. The pie chart method will return the images as bitmap. We will save images to our local location to use this into HTML web page report, else you can directly use this to HTML page but for record purposes, I am saving this to our local location.

   

   Fig 7. Pie chart sample

   //Call a method to create pie chart
   Bitmap btMapImages = GeneratePieChart.pieChart(TCStatus, arrValueNames, chartTitle);
   //Save that pie chart a particular location
   btMapImages.Save(strLocationToSavePieChartImages + 
   	strImageNames, System.Drawing.Imaging.ImageFormat.Png);

7. After that we will call a method to put all the data into an HTML web page with tabular format as shown in the below diagram:

   

   Fig 8. Format into HTML web page

   For the code, please go through the code uploaded on this website. I have provided comments at each line so that you can understand better and in a nice way.

8. Call your method to send out the report as attached or embedded into the email through Outlook or any email client.
9. The ready report would be available in your mail box.
10. You can add this *.exe to your Windows scheduler to run daily or weekly basis and to send out reports to your team members or to whomever you want.

    Is this not great!!!

11. So being automation engineers, this is our responsibility to make manual processes easy and automatic so that we can save time, effort and of course money.

You can go through my blog to get more ideas and to see the work in progress.

http://jawedm.blogspot.com

Happy automation using C#.....

Feel free to provide your comments and suggestions. Thanks!!