Donovan Brown | All posts tagged 'Validation-Rule'

Problem: The only way I can verify the success of my web test is to read a value from a database. Solution: Create a custom validation rule that can validate the value in the database. Code: using System; using System.ComponentModel; using System.Data.SqlClient; using Microsoft.VisualStudio.TestTools.WebTesting; namespace TestUtil { [DisplayName("SQL Validation Rule")] [Description("Executes the query and compares the first column of the first row to the Expected value.")] public class SQLValidationRule : ValidationRule { public SQLValidationRule() { IgnoreCase = true; } [Description("The query to execute and extract the first column from. In Select * from x where column={0}. You can leave the where clause if it is not needed.")] public string Query { get; set; } [DisplayName("Connection String")] [Description("The full connection string to the database")] public string ConnectionString { get; set; } [DisplayName("Where Clause Context Parameter Name")] [Description("The name of an optional context parameter to use if there is a where clause in the query.")] public string WhereClauseContextParameterName { get; set; } [DefaultValue(true)] [DisplayName("Ignore Case")] [Description("When set to true the case of the word is not used")] public bool IgnoreCase { get; set; } [DisplayName("Expected Value")] [Description("The value to compare the first column too.")] public string ExpectedValue { get; set; } public override void Validate(object sender, ValidationEventArgs e) { string where = null; if(!string.IsNullOrEmpty(WhereClauseContextParameterName)) where = e.WebTest.Context[WhereClauseContextParameterName].ToString(); string result = ExecuteQuery(ConnectionString, Query, where); e.IsValid = string.Compare(result, ExpectedValue, IgnoreCase) == 0; } private string ExecuteQuery(string connectionString, string query, string where) { SqlConnection conn = new SqlConnection(connectionString); string cmdText = string.Format(query, where); SqlCommand cmd = new SqlCommand(cmdText, conn); try { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if(dr.Read()) return dr.GetValue(0).ToString(); } catch(Exception e) { System.Diagnostics.Debug.WriteLine(e.Message); } finally { conn.Close(); } return null; } } } Explanation: Creating a custom validation rule for web test is extremely simple. Simply create a new public class that derives from Microsoft.VisualStudio.TestTools.WebTesting.ValidationRule and override the Validate method. If you create the class in your test project it will become immediately available the next time you try to add a validation rule to a web request. If you created the class in a separate class library simply add a reference to that class library in your test project. This particular validation rule has the five following properties: Query – This is the query to be executed on the database connection. Only the first column of the first record is used in the comparison of this validation rule. The query can have a single where condition in the where clause that uses a context parameter value. For example “Select Name from Table1 where ID={0}”. At runtime the validation rule will look up the value of the provided context parameter and replace {0} with the value stored in the context parameter. Using a context parameter is completely optional. Connection String – The connection string to a SQL Server database. This can also be a context parameter entered in {{ContextParameterName}} format. Otherwise you may simple enter a literal string. Where Clause Context Parameter Name – The context parameter to be used to replace the {0} if any of the query. Ignore Case – Identifies if case should be ignored or not during the string comparison. Expected Value – The value to compare the first column of the first row too. This can also be a context parameter entered in {{ContextParameterName}} format. Otherwise you may simple enter a literal string. You can download the file below. SQLValidationRule.cs (2.48 kb)