Recently i worked in one of the projects that required 1000 + SQL test cases to be executed. Since each of these test cases involved running Sql queries against a source, we were unable to leverage any OOB feature of VSTS for automation.  The time spent in creating 1:1 test methods in VSTS using Test List editor and mapping them with TFS test cases and executing the sql queries is a time consuming process. Also TFS and Test methods will never be in sync in the course of the project and needs manual intervention

Integration of  Sql Queries in TFS Test cases with VSTS:

We have come with a code snippet which reads the test cases written in TFS and extracts the query and coverts each test case automatically into a Test method.The code dynamically reads all the test queries under a specific project node in TFS and convert them into Test methods so that they are visible as individual tests in Test List Editor as shown below

We have used the following namespaces exposed by TFS and accessed the test case work items for a specific project :

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
 


The work items are filtered by adding some of the below parameters in app.config file:

<add key="TFSServer" value=""/>
<add key="TFSSprintPath" value="')"/>
<add key="Project" value=""/>
<add key="Title" value="Console"/>

We can pull the specific test cases from TFS using the below piece of code.

public void FetchTestQueries_VSTF()
      {
          // get the Uri to the project collection to use           
          Uri tempURI = new Uri(ConfigHelper.ConfigTFSServer);
          var collectionUri = tempURI;
          int arrlength = 1000;
          int rid = 0;
          string[] QueryStore = new string[arrlength];
          string[] TitleID = new string[arrlength];
          int[] TestCaseID = new int[arrlength];

          try
          {              
                  // get the work item store from the TeamFoundationServer
                  Console.WriteLine("Connecting to {0}...", collectionUri);

                  // get a reference to the team project collection
                  using (var projectCollection = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(collectionUri))
                  {
                      // get a reference to the work item tracking service
                      var workItemStore = projectCollection.GetService<WorkItemStore>();

                      string wiqlQuery = " SELECT [System.Id], [System.WorkItemType]," +
                                               " [System.State], [System.AssignedTo], [System.Title]" +
                                               " FROM WorkItems " +
                                               @" WHERE [System.TeamProject] = '"+ConfigHelper.ConfigProjectName+"' AND [System.IterationPath] In " + ConfigHelper.ConfigTFSSprintPath + "" +
                                               @"AND [System.WorkItemType] = 'Test Case' AND [System.Title] CONTAINS '" + ConfigHelper.ConfigTitle + "' ORDER BY [System.WorkItemType], [System.Id]";

                      // execute the query and retrieve a collection of workitems
                      var workItems = workItemStore.Query(wiqlQuery);

                      // write out the heading
                      Console.WriteLine("Query: {0}\n\n{1}\t{2}", wiqlQuery, "Id", "Query");

                      //For each workitem fetch testcaseid,title and the query                                               
                      foreach (WorkItem workItem in workItems)
                      {                           

                          WorkItem wrkItm = workItem.Store.GetWorkItem(workItem.Id);
                          object QueryField = wrkItm.Fields["Queries"].Value;
                          Console.WriteLine("{0}\t{1}", workItem.Id, QueryField);

                          try
                          {
                              if (QueryField.ToString() != "")
                              {
                                  TestCaseID[rid] = workItem.Id;
                                  TitleID[rid] = Regex.Replace(workItem.Title, @"\s", "");
                                  TitleID[rid] = TitleID[rid].Replace("-", "_");
                                  QueryStore[rid] = QueryField.ToString();
                                  QueryStore[rid] = QueryStore[rid].Replace("\n", " ");

                                  rid++;
                              }                              
                           }

                          catch (Exception e)
                          {
                              Console.WriteLine("Error: {0}", e.Message);
                          }                                                                                 
                      }              
                  }

In my next blog, let me cover the steps to convert each query into individual test methods, please stay tuned