In my previous post, we discussed on how to extract the Sql queries written in TFS Test cases.

Now we shall discuss about to how to convert those test queries into individual Test methods so that they are visible as individual tests in VSTS Test List Editor.

We open up a temp text file and start creating the following header text which would be common for all test methods:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Configuration;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Microsoft.Data.Schema.UnitTesting;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
namespace YourSolnName

{
    [TestClass()]
public class TestMethodGeneration
{

Now we need iterate thru the array which we has all the test cases metadata such as Test Case ID,Test Case Title,Sql Query and start creating the test methods using them.

Below code can be used to perform the above operation:

FileStream FS1 = new FileStream(ConfigHelper.ConfigTFSTestCaseDump, FileMode.Truncate, FileAccess.Write, FileShare.Write);
                StreamWriter SW1 = new StreamWriter(FS1);
                long offset = 0;
                SW1.BaseStream.Seek(offset, SeekOrigin.End);
                string HeaderText = "";
                HeaderText = "" +
                            "using System;\n" +
                            "using System.Collections.Generic;\n" +
                            "using System.Data;\n" +
                            "using System.Data.Common;\n" +
                            "using System.Configuration;\n" +
                            "using Microsoft.VisualStudio.TestTools.UnitTesting;\n" +
                            "using Microsoft.Data.Schema.UnitTesting;\n" +
                            "using Microsoft.TeamFoundation.Client;\n" +
                            "using Microsoft.TeamFoundation.WorkItemTracking.Client;\n" +
                            "using System.Data.SqlClient;\n" +
                            "using System.IO;\n" +
                            "using System.Text.RegularExpressions;\n" +
                            "namespace EIP3._0\n" +
                            "{\n" +
                                "\t[TestClass()]\n" +
                                "public class TestMethodGeneration\n" +
                                "{\n";
                SW1.WriteLine(HeaderText);
                SW1.Close();

                //arr1.Length
                for (int i = 0; i < rid; i++)
                {

                        FileStream FS = new FileStream(ConfigHelper.ConfigTFSTestCaseDump, FileMode.Append, FileAccess.Write, FileShare.Write);
                        StreamWriter SW = new StreamWriter(FS);
                        string TestName = string.Format("TestCase_{0}_{1}", TestCaseID[i], TitleID[i]);
                        string CategoryName = "XBLTEMP2";
                        SW.Write("\n[TestCategory("+'"'+CategoryName+'"'+")]\n");
                        SW.Write("\n[TestMethod]\n");
                        SW.Write(string.Format("\npublic void {0}()\n", TestName));

                        string StaticText = "";

                        StaticText = "{\n\n" +
                        "string spQuery;\n" +
                        "String Connstr = ConfigHelper.ConfigConnectionString;\n" +
                        "System.Data.SqlClient.SqlConnection myconnection = new System.Data.SqlClient.SqlConnection(Connstr);\n" +
                        "myconnection.Open();\n" +

                        "SqlCommand cmd = myconnection.CreateCommand();\n" +
                        "spQuery =" + '"' + QueryStore[i] + '"' + ";\n" +
                        "cmd.CommandType = CommandType.Text;\n" +
                        "cmd.CommandText = spQuery;\n" +
                        "try \n{\nSqlDataReader RS = cmd.ExecuteReader();\nif (RS.HasRows)\n{\nAssert.Fail();\n}\n" +
                        "else\n{\n    CreateLogger(" + '"' + TestCaseID[i] + '"' + "," + '"' + TitleID[i] + '"' + "," + '"' + "Pass" + '"' + ",null,null);\n}\n}\n" +
                        "catch (Exception e)\n{\nCreateLogger(" + '"' + TestCaseID[i] + '"' + "," + '"' + TitleID[i] + '"' + "," + '"' + "Fail" + '"' + ",e.Message, e.StackTrace);\n\n" +
                        "\nAssert.Fail();\nConsole.WriteLine(e.Message);\n" +
                        "}\n" +
                        "}\n\n";
                        SW.Write(StaticText);
                        SW.Close();                 


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

Having created all the test methods, now we need to create some logging mechanism (Logger method) to capture the results of our Test Methods. In our implementation, we have used a SP to log the results and we pass the parameters to the SP using the below code.

Use the below code for logging

finally
            {
                FileStream FS = new FileStream(ConfigHelper.ConfigTFSTestCaseDump, FileMode.Append, FileAccess.Write, FileShare.Write);
                StreamWriter SW = new StreamWriter(FS);
                string StaticText = "";

                StaticText = "\npublic void CreateLogger(string TestCaseId,string Title,string TestResult,string Message,string Trace)\n{\n\n" +
                "String Connstr = ConfigHelper.ConfigConnectionString;\n"+
                "System.Data.SqlClient.SqlConnection myconnection = new System.Data.SqlClient.SqlConnection(Connstr);\n"+
                "myconnection.Open();\n"+
                "SqlCommand cmd = myconnection.CreateCommand();"+
                "cmd.CommandType = CommandType.StoredProcedure;"+
                "cmd.CommandText = "+ '"' + "TestResults_Logging" + '"' +";"+
                "\nSqlParameter prm1 = new SqlParameter();"+
                "\nprm1 = cmd.Parameters.Add(" + '"' + "@TestCaseID" + '"' + ", SqlDbType.VarChar, 500);" +
                "\nprm1.Value = TestCaseId;"+
                "\nSqlParameter prm2 = new SqlParameter();" +
                "\nprm2 = cmd.Parameters.Add(" + '"' + "@Title" + '"' + ", SqlDbType.VarChar, 500);" +
                "\nprm2.Value = Title;" +
                "\nSqlParameter prm3 = new SqlParameter();" +
                "\nprm3 = cmd.Parameters.Add(" + '"' + "@TestResult" + '"' + ", SqlDbType.VarChar, 500);" +
                "\nprm3.Value = TestResult;" +
                "\nSqlParameter prm4 = new SqlParameter();" +
                "\nprm4 = cmd.Parameters.Add(" + '"' + "@ExceptionMessage" + '"' + ", SqlDbType.VarChar, 500);" +
                "\nprm4.Value = Message;" +
                "\nSqlParameter prm5 = new SqlParameter();" +
                "\nprm5 = cmd.Parameters.Add(" + '"' + "@LogTrace" + '"' + ", SqlDbType.VarChar, 500);" +
                "\nprm5.Value = Trace;" +
                "\ncmd.ExecuteNonQuery();\n}\n";
                SW.WriteLine(StaticText);
                SW.WriteLine("}\n");
                SW.WriteLine("}");
                SW.Close();
                //Reading the test from TFSTestCaseDump.txt and writing into TestMethodGeneration.cs
                StreamReader SR1 = new StreamReader(ConfigHelper.ConfigTFSTestCaseDump);
                string TextCode = "";
                TextCode = SR1.ReadToEnd();

                FileStream FS1 = new FileStream(ConfigHelper.ConfigFileTestMethodGeneration, FileMode.Truncate, FileAccess.Write, FileShare.Write);
                StreamWriter SW1 = new StreamWriter(FS1);
                SW1.WriteLine(TextCode);
                SW1.Close();

Encapsulate the entire piece of code in a test method like FetchTestQueries_VSTF(). After running this test method, please refresh hit the refresh button in your Test List editor so that the newly created test methods are compiled and visible