Click here to Skip to main content
15,867,453 members
Articles / DevOps / Testing

QC DATA PULLER using C#

Rate me:
Please Sign up or sign in to vote.
4.88/5 (10 votes)
3 Mar 2011CPOL6 min read 120.5K   3.1K   20   26
Using this application, user can generate report of test case execution from QC(Quality center) in to HTML web page with fancy pie chart and tabular format using C# and OTA API expose from QC (Quality Center).

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:

Image 1

Fig 1. QC data Puller Diagram

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

Click to enlarge image

Fig 2. OTA Object model

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:

Click to enlarge image

Fig 3. HTML web page as report

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
<?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:

C#
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:

    Click to enlarge image

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

    Image 5

    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.

    C#
    //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.
    C#
    //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.
  5. Class Diagram

    Click to enlarge image

    Fig 6. Class Diagram

    The code is as explained below:

    C#
    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
  6. 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.

  7. 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.

    Image 7

    Fig 7. Pie chart sample
    C#
    //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);
  8. 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:

    Image 8

    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.

  9. Call your method to send out the report as attached or embedded into the email through Outlook or any email client.
  10. The ready report would be available in your mail box.
  11. 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!!!

  12. 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!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
http://jawedm.blogspot.com

Contact me for Freelancing at jawed.ace@gmail.com
Free to go anywhere,ready to learn anything...

Comments and Discussions

 
QuestionI am using Interop.TDAPIOLELib.dll for QC ALM Integration But taking too much time . Pin
Member 1215462615-May-17 21:52
Member 1215462615-May-17 21:52 
AnswerRe: I am using Interop.TDAPIOLELib.dll for QC ALM Integration But taking too much time . Pin
indrajeet_pat12319-Aug-18 19:57
indrajeet_pat12319-Aug-18 19:57 
QuestionGetting Com error while connecting to QC server Pin
Member 126250708-Jul-16 3:40
Member 126250708-Jul-16 3:40 
Hi,

I'm experiencing below error while connecting to QC server. Could you please help on this.
C#
Retrieving the COM class factory for component with CLSID {C5CBD7B2-490C-45F5-8C40-B8C3D108E6D7} failed due to the following error: 800703e6.


OS: windows 7 64 bit
QC : 9.2
AnswerRe: Getting Com error while connecting to QC server Pin
Member 42059395-Dec-17 18:12
Member 42059395-Dec-17 18:12 
GeneralGreat stuff :) Pin
Member 114675599-Jul-15 11:28
Member 114675599-Jul-15 11:28 
QuestionVery effective kickstart! thanks ! Pin
raafproj11-Feb-15 0:01
raafproj11-Feb-15 0:01 
QuestionGetting Error while executing in line ["CY_FOLDER_ID"] Pin
Lesly Arun Franco24-Oct-14 4:01
Lesly Arun Franco24-Oct-14 4:01 
AnswerRe: Getting Error while executing in line ["CY_FOLDER_ID"] Pin
Member 116725577-May-15 4:58
Member 116725577-May-15 4:58 
QuestionQC test case updation on changing the data Pin
Anibha Agrawal9-Oct-14 8:13
Anibha Agrawal9-Oct-14 8:13 
QuestionC# QC Data Puller Filter Question Pin
scgamer115-Apr-13 16:54
scgamer115-Apr-13 16:54 
QuestionAny help on how to implement the feature in Bugzilla? Pin
Member 930928026-Jul-12 1:47
Member 930928026-Jul-12 1:47 
QuestionGood stuff! Pin
ABCD1122-Feb-12 11:57
ABCD1122-Feb-12 11:57 
QuestionSendemail.cs missing Pin
don_B24-Jan-12 6:33
don_B24-Jan-12 6:33 
AnswerRe: Sendemail.cs missing Pin
thomasbo19-Apr-13 17:22
thomasbo19-Apr-13 17:22 
GeneralMy vote of 5 Pin
Kanasz Robert1-Dec-11 2:52
professionalKanasz Robert1-Dec-11 2:52 
GeneralRe: My vote of 5 Pin
jawed.ace1-Dec-11 22:02
jawed.ace1-Dec-11 22:02 
QuestionRunning on a different system Pin
swankgd26-Jul-11 11:20
swankgd26-Jul-11 11:20 
QuestionQC OTA API for pulling data from 2 tables Pin
Member 80634116-Jul-11 5:45
Member 80634116-Jul-11 5:45 
QuestionTabular format directly on webpage instaed of creating a html page and save at perticular location [modified] Pin
siva naga maruthi4-Apr-11 5:14
siva naga maruthi4-Apr-11 5:14 
AnswerRe: Tabular format directly on webpage instaed of creating a html page and save at perticular location Pin
jawed.ace16-Apr-11 10:06
jawed.ace16-Apr-11 10:06 
GeneralRe: Tabular format directly on webpage instaed of creating a html page and save at perticular location Pin
siva naga maruthi22-Apr-11 16:08
siva naga maruthi22-Apr-11 16:08 
AnswerRe: Tabular format directly on webpage instaed of creating a html page and save at perticular location Pin
jawed.ace24-Apr-11 1:58
jawed.ace24-Apr-11 1:58 
GeneralMy vote of 5 Pin
Imtiyaz8424-Feb-11 19:47
Imtiyaz8424-Feb-11 19:47 
GeneralRe: My vote of 5 Pin
jawed.ace27-Feb-11 19:13
jawed.ace27-Feb-11 19:13 
GeneralRe: My vote of 5 Pin
sanjeev080124-Mar-11 8:13
sanjeev080124-Mar-11 8:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.