imporved method of using SQL Load Test

Oct 1, 2008 at 4:19 PM

First of all I would like to say Thank you so much for your hard work to get this project.

I was able to use your base code and added XML test data feed to improve the readability and

Make it user friendly.

If you copy the following code any one can just add there SQL queries to a XML file and run the test without going through the Trace file or running it through the command.

 

You can name this anyname.cs for example I have it named as QALtest.cs

//------------------------------------------------------------------------------

// This Script designed to Load Test SQL Server with SQL queries on XML file

// Created By Andrew Antoneypillai

//

//------------------------------------------------------------------------------

 

namespace SQLTEST

{

    using Microsoft.VisualStudio.TestTools.UnitTesting;

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Xml;

   

    [TestClass()]

    public partial class SQLTESTTests

    {

       

        private TestContext _testContext;

       

        private SqlConnection _connection;

       

        public TestContext TestContext

        {

            get

            {

                return this._testContext;

            }

            set

            {

                this._testContext = value;

            }

        }

       

        [TestMethod()]

        public void SQLTEST()

        {

           

 

            string filename = "C:\\Documents and Settings\\antoneypillai\\My Documents\\Visual Studio 2008\\Projects\\TestProject7\\TestCases.xml";

            string name = string.Empty;

            string commandText = string.Empty;

 

            DataSet testCases = new DataSet();

            testCases.ReadXml(filename);

 

            for (int j = 0; j < testCases.Tables[0].Rows.Count; j++)

            {

                name = testCases.Tables[0].Rows[j][0].ToString();

                commandText = testCases.Tables[0].Rows[j][1].ToString();

 

                ExecuteStatement(name, commandText);

            }

   

        }

    

 

        private void ExecuteStatement(string name, string commandText)

        {

            _testContext.BeginTimer(name);

            try

            {

                SqlCommand cmd = null;

                try

                {

                    cmd = new SqlCommand();

                    cmd.Connection = _connection;

                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = commandText;

                   // cmd.ExecuteNonQuery();

                }

                finally

                {

                    if ((cmd != null))

                    {

                        cmd.Dispose();

                    }

                }

            }

            finally

            {

                _testContext.EndTimer(name);

            }

        }

 

    }

}

 

 

 

 

Keep this file as the same from SQLLoadTest project. Just change the Data Connection and save it as SQLTEST.stubs.cs

//------------------------------------------------------------------------------

// <auto-generated>

//     This code was generated by a tool.

//     Runtime Version:2.0.50727.832

//

//     Changes to this file may cause incorrect behavior and will be lost if

//     the code is regenerated.

// </auto-generated>

//------------------------------------------------------------------------------

 

namespace SQLTEST

{

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using Microsoft.VisualStudio.TestTools.UnitTesting;

   

   

    public partial class SQLTESTTests

    {

       

        [TestInitialize()]

        public void InitializeTest()

        {

            _connection = new SqlConnection("Server=myserver\\mysql02inst01;Database=testdata;Trusted_Connection=True;");

            _connection.Open();

        }

       

        [TestCleanup()]

        public void CleanupTest()

        {

            _connection.Dispose();

        }

    }

}

 

 

Create a following XML file.

</TestCase>

 

  <TestCase>

    <CommandText>SELECT SlotID - 1 FROM RSCUT WHERE SlotID BETWEEN 2 AND 52 AND    SlotID - 1 NOT IN (SELECT SlotID FROM RSCUT)UNION SELECT SlotID + 1 FROM RSCUT WHERE SlotID BETWEEN 1 AND 52 AND    SlotID + 1 NOT IN (SELECT SlotID FROM RSCUT) UNION SELECT 1 WHERE (SELECT COUNT(*) FROM RSCUT WHERE SlotID BETWEEN 1 AND 52) = 0 ORDER BY 1</CommandText>

    <Comment></Comment>

  </TestCase>

 

 

<TestCase>

    <name>SQLTEST_</name>

    <CommandText>SELECT b.AccountCode, c.AccountCode, d.AccountCode FROM RSACCOUNT a LEFT OUTER JOIN RSACCOUNT b ON a.Level1AccountID = b.AccountID LEFT OUTER JOIN RSACCOUNT c ON a.Level2AccountID = c.AccountID LEFT OUTER JOIN RSACCOUNT d ON a.Level3AccountID = d.AccountID WHERE a.AccountID = 10218</CommandText>

    <Comment></Comment>

  </TestCase>

 

 

That’s it just add these file to your load test and let it rip. J

 

Hope this help you guys out.

Thanks

Antoneypillai