We have had a few questions about being able to data bind web tests to either formats that we do not support or to something other than a table, such as a select statement.  This post should walk you through one way of creating custom data binding.  The method provided in this post is to create one class which will manage the data and then create a web test plug-in which will add the data into the web test context.  To use this example, you would need to set your connection string and modify the sql statement. 

Here is the class which manages the data.  This class is a singleton which will be called from the plug-in.  The initialize method will make the call to your data source.  In this example, I am just creating a select statement against a database.  The GetSqlSelectStatement is the method which will return the SQL statement to be executed.  The plug-in will fetch data by calling GetNextRow.  This method will return a set of Name, Value pairs.  The name is the column name and the value is the column value.  Each time the method is called, it will get the current row of the dataset and then advance the current position counter.  If the end of the dataset is reached, it will start from the beginning again.  To customize this class, you would usually only need to change the SQL statement return from GetSqlSelectStatement.

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Data;

using System.Globalization;

using System.Data.OleDb;

 

namespace CustomBinding

{

    public class CustomDS

    {

        //singleton instance

        private static readonly CustomDS m_instance = new CustomDS();

 

        //keep track of next row to read

        private int m_nextPosition = 0;

       

        //Is the Datasource initialized

        private bool m_initialized = false;

 

        //Datasource object

        private DataSet m_datasource;

 

        //Data table object

        private DataTable m_dataTable;       

 

        //object for locking during initialization and reading

        private static readonly object m_Padlock = new object();

 

        #region Constructor

        //constructor

        private CustomDS()

        {

 

        }

        #endregion

 

        #region Properties

        public bool Initialized

        {

            get

            {

                return m_initialized;

            }

 

        }

 

        public static CustomDS Instance

        {

            get

            {

                return m_instance;

            }

        }

        #endregion

 

        #region public Methods

         

        public void Initialize(string connectionString)

        {

            lock (m_Padlock)

            {

                if (m_datasource == null && Initialized == false)

                {

                    //load the data

                    //create adapter

                    OleDbDataAdapter da = new OleDbDataAdapter(GetSqlSelectStatement(), connectionString);

 

                    //create the dataset

                    m_datasource = new DataSet();

                    m_datasource.Locale = CultureInfo.CurrentCulture;

 

                    //load the data

                    da.Fill(m_datasource);

                    m_dataTable = m_datasource.Tables[0];                   

 

                    //set the manager to initialized

                    m_initialized = true;

 

                }

            }

        }

 

        public Dictionary<String, String> GetNextRow()

        {

            if (m_dataTable != null)

            {

                //lock the thread

                lock (m_Padlock)

                {

                    //if you have reached the end of the cursor, loop back around to the beginning

                    if (m_nextPosition == m_dataTable.Rows.Count)

                    {

                        m_nextPosition = 0;

                    }

 

                    //create an object to hold the name value pairs

                    Dictionary<String, String> dictionary = new Dictionary<string, string>();

 

                    //add each column to the dictionary

                    foreach (DataColumn c in m_dataTable.Columns)

                    {

                        dictionary.Add(c.ColumnName, m_dataTable.Rows[m_nextPosition][c].ToString());

                    }

                    m_nextPosition++;

 

                    return dictionary;

 

                }

            }

 

            return null;

        }

 

        private string GetSqlSelectStatement()

        {

            return "Select * from Customers";

        }

 

       

#endregion

    }

}

 

The next class is the Web Test Plug-in.  Web Test plug-ins are called once for each iteration of the web test.  This plug-in first checks to see if the data source has been initialized.  If it has not been initialized, it will call the initialize method of the CustomDS class.  The initialize method only takes the connection string.  This plug-in checks to see if a context parameter was set on the web test that indicates what the connection string should be.  If no connection string was set, it will use the default string defined in this class.  After the data is initialized, it calls GetNextRow to get the data for the current iteration.  Once it gets the data, it adds each column to the context.  Now the data is available to use in the web test.

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.VisualStudio.TestTools.WebTesting;

 

namespace CustomBinding

{

    public class CustomBindingPlugin : WebTestPlugin

    {

        string m_ConnectionString = @"Provider=SQLOLEDB.1;Data Source=dbserver;Integrated Security=SSPI;Initial Catalog=Northwind";

        public override void PostWebTest(object sender, PostWebTestEventArgs e)

        {

           

        }

 

        public override void PreWebTest(object sender, PreWebTestEventArgs e)

        {

            //check to make sure that the data has been loaded

            if (CustomDS.Instance.Initialized == false)

            {

                //look to see if the connection string is set as a context parameter in the web test

                //if it is not set use the default string set in this plugin

                if (e.WebTest.Context.ContainsKey("ConnectionString"))

                {

                    m_ConnectionString = (string)e.WebTest.Context["ConnectionString"];

                }

                CustomDS.Instance.Initialize(m_ConnectionString);

            }

 

            //add each column to the context

            Dictionary<string, string> dictionary = CustomDS.Instance.GetNextRow();

            foreach (string key in dictionary.Keys)

            {

                //if the key exists, then update it.  Otherwise add the key

                if (e.WebTest.Context.ContainsKey(key))

                {

                    e.WebTest.Context[key] = dictionary[key];

                }

                else

                {

                    e.WebTest.Context.Add(key, dictionary[key]);

                }

            }          

        }

    }

}

 

After adding these classes to you project, you can then set the web test plug-in on the web test.  You can do this by clicking the Set Web Test Plug-in button on the web test toolbar.  If you want or need to have multiple plug-ins, you will need to use a coded web test.  You can have multiple plug-ins in coded test but not the declarative test.

The way to access a value from the plug-in is to surround the variable with {{…}}.  If you wanted to bind one of the query string parameters to a column called CustomerName, you would set the value of the query string parameter to {{CustomerName}}

 

Here are the same samples in VB:

Imports System

Imports System.Collections.Generic

Imports System.Collections.Specialized

Imports System.Data

Imports System.Globalization

Imports System.Data.OleDb

 

Namespace CustomBinding

 

    Public Class CustomDS

 

        'singleton instance

        Private Shared ReadOnly m_instance As CustomDS = New CustomDS()

 

        'keep track of next row to read

        Private m_nextPosition As Integer = 0

 

        'Is the Datasource initialized

        Private m_initialized As Boolean = False

 

        'Datasource object

        Private m_datasource As DataSet

 

        'Data table object

        Private m_dataTable As DataTable

 

        'object for locking during initialization and reading

        Private Shared ReadOnly m_Padlock As Object = New Object()

 

 

        'constructor

        Private Sub New()

 

        End Sub

 

 

        Public ReadOnly Property Initialized() As Boolean

            Get

                Return m_initialized

            End Get

        End Property

 

 

        Public Shared ReadOnly Property Instance() As CustomDS

            Get

                Return m_instance

            End Get

        End Property

 

 

        Public Sub Initialize(ByVal connectionString As String)

 

            SyncLock (m_Padlock)

 

                If (m_datasource Is Nothing) And (Initialized = False) Then

 

                    'load the data

                    'create adapter

                    Dim da As OleDbDataAdapter = New OleDbDataAdapter(GetSqlSelectStatement(), connectionString)

 

                    'create the dataset

                    m_datasource = New DataSet()

                    m_datasource.Locale = CultureInfo.CurrentCulture

 

                    'load the data

                    da.Fill(m_datasource)

                    m_dataTable = m_datasource.Tables(0)

 

                    'set the manager to initialized

                    m_initialized = True

 

                End If

            End SyncLock

        End Sub

 

 

        Public Function GetNextRow() As Dictionary(Of String, String)

 

            If Not m_dataTable Is Nothing Then

 

                'lock the thread

                SyncLock (m_Padlock)

 

                    'if you have reached the end of the cursor, loop back around to the beginning

                    If m_nextPosition = m_dataTable.Rows.Count Then

                        m_nextPosition = 0

                    End If

 

                    'create an object to hold the name value pairs

                    Dim dictionary As Dictionary(Of String, String) = New Dictionary(Of String, String)()

 

                    'add each column to the dictionary

                    For Each c As DataColumn In m_dataTable.Columns

 

                        dictionary.Add(c.ColumnName, m_dataTable.Rows(m_nextPosition)(c).ToString())

                    Next

                    m_nextPosition += 1

 

                    Return dictionary

 

                End SyncLock

            End If

 

            Return Nothing

        End Function

 

 

        Private Function GetSqlSelectStatement() As String

 

            Return "Select * from Customers"

        End Function

 

    End Class

End Namespace

 

 

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports Microsoft.VisualStudio.TestTools.WebTesting

 

Namespace CustomBinding

 

    Public Class CustomBindingPlugin

        Inherits WebTestPlugin

 

        Dim m_ConnectionString As String = "Provider=SQLOLEDB.1;Data Source=dbserver;Integrated Security=SSPI;Initial Catalog=Northwind"

 

        Public Overrides Sub PreWebTest(ByVal sender As Object, ByVal e As PreWebTestEventArgs)

 

            'check to make sure that the data has been loaded

            If CustomDS.Instance.Initialized = False Then

 

                'look to see if the connection string is set as a context parameter in the web test

                'if it is not set use the default string set in this plugin

                If e.WebTest.Context.ContainsKey("ConnectionString") Then

 

                    m_ConnectionString = e.WebTest.Context("ConnectionString").ToString()

                End If

                CustomDS.Instance.Initialize(m_ConnectionString)

            End If

 

            'add each column to the context

            Dim dictionary As Dictionary(Of String, String) = CustomDS.Instance.GetNextRow()

            For Each key As String In dictionary.Keys

 

                'if the key exists, then update it.  Otherwise add the key

                If e.WebTest.Context.ContainsKey(key) Then

 

                    e.WebTest.Context(key) = dictionary(key)

                Else

 

                    e.WebTest.Context.Add(key, dictionary(key))

                End If

            Next

        End Sub

 

 

        Public Overrides Sub PostWebTest(ByVal sender As Object, ByVal e As PostWebTestEventArgs)

            'do nothing

        End Sub

    End Class

End Namespace