How do I validate data in my database during a web test

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)

How to change the display name of my Find Text validation rule

Problem I can’t easily tell what my Find Text validation rule is searching for in my Web Test. Solution Change the DisplayName value in the Web Test xml to something more meaningful. From Solution Explorer right click on the Web Test and select Open With… then select XML (Text) Editor and click OK.  Once the file is open search the file for DisplayName="Find Text".  Replace Find Text with something more meaningful.  Save the file and close it.  Now right click on the Web Test from Solution Explorer and select Open With… and select the Web Test Editor (Default).  Now expand the Validation Rules folder under the desired web request.  The Find Text validation rule label will be the value you typed in. Explanation For some reason the creator of this validation run did not expose the DisplayName property so it could be change from the Properties Window.  However, the value is there and can be changed from the xml file.  This is very helpful when you have several Find Text validation rules on the same request.

How to Xcopy deploy using TFS 2010/2012

Problem I need my VS2010/VS2012 build to perform an “Xcopy deployment” of my ASP.NET application to an existing virtual directory. Solution Customize the build template to use the CopyDirectory activity to copy the ASP.NET application to the virtual directory. Explanation One of the benefits of ASP.NET development is the simply “Xcopy deployment”. ASP.NET applications require no changes to the registry and have no special installation requirements for the hosting server.  Therefore, you can use the drag-and-drop feature in Microsoft Windows Explorer, File Transfer Protocol (FTP), or the DOS Xcopy command to copy files from one computer to another. The only prerequisite of this technique is the virtual directory in IIS must already be created and configured. The goal of this build is to not have to install any special features or extensions in IIS to facilitate deployment of my ASP.NET application.  I want my environments to match production as close as possible and I never intend on installing IIS Extensions in production. When you configure a build definition that builds and ASP.NET application the binaries directory and drop location contain a folder named _PublishedWebsites.  Each ASP.NET application built during the build will have a sub directory that contains all the files needed for the application. To perform an “Xcopy deployment” we simply need to identify the source and destination directories.  We are going to store this information in arguments passed to the build.  To begin open the DefaultTemplate.xaml file in VS2010 and click the Arguments button at the bottom of the workflow designer to show the workflow arguments.  Add two string arguments  VDir and SiteDir. Now let’s add a nice coat of polish on our arguments.  Click the ellipses next to the default value of the Metadata argument to show the Process Parameter Metadata Editor window.  Click the Add button and enter in the following information and click OK. Parameter Name – VDirDisplay Name – Virtual DirectoryCategory – DeployDescription – The full UNC path to the virtual directory to copy the website too. Editor – leave blankRequired – leave unchecked View this parameter when – Always show the parameter and Parameter Name – SiteDirDisplay Name – Site DirectoryCategory – DeployDescription – The sub directory of _PublishedWebsites to copy from.Editor – leave blankRequired – leave uncheckedView this parameter when – Always show the parameter   We are simply going to add a CopyDirectory activity that uses the arguments we just created to perform the copy.  To begin we must locate the correct area of the build template to add our CopyDirectory activity.  I find the quickest way to do this is to click the Collapse All button at the top of the workflow designer window.  Now double click on the words Double-click to view on all of the following activities: 1.    Run On Agent2.    Try Compile, Test, and Associate Changesets and Work Items 3.    Sequence4.    Compile, Test, and Associate Changesets and Work Items5.    Try Compile and Test6.    Compile and Test7.    For Each Configuration in BuildSettings.PlatformConfigurations8.    Compile and Test for Configuration When we configured the metadata for our arguments we left the Required checkbox unchecked.  This will allow users of this build template to leave the values for VDir and SiteDir blank if they are not building an ASP.NET application or simply do not want to deploy them.  Therefore, we need to check the value of the arguments to determine if we need to perform the copy or not.  In the toolbox expand the Control Flow tab and drag and drop the If activity right above the If Not Disable Test activity.  Click the double down arrows in the title bar of the if to show its contents. In the Condition text box enter the following: Not String.IsNullOrWhiteSpace(VDir) From the toolbox drag and drop a Sequence activity from the Control Flow tab onto the Then side of the If.  Change the DisplayName of the sequence to Deploy ASP.NET Application. We add a sequence here so that we can use multiple activities. Click the double down arrows in the title bar of the sequence to show its contents.  This is where we are going to add the activities needed to copy the ASP.NET application to the virtual directory in IIS. Now we can add the CopyDirectory activity and set the properties to deploy our ASP.NET application during the build.  To get started simply drag and drop the CopyDirectory activity from the Team Foundation Build Activities tab into the Deploy ASP.NET Application sequence.  With the CopyDirectory activity selected set the following values in the properties window: •    Destination – Vdir•    Source  - BinariesDirectory + "\_PublishedWebsites\" + SiteDir Now save your xaml file, check in your changes and queue a new build. You can download a copy of the final file below. TFS2010 DeployTemplate.xaml (55.16 kb) TFS2012 DeployTemplate.11.1.xaml (75.68 kb)

I get an unresolved reference to the object [#aspnet_Permissions]

Normal 0 false false false EN-US X-NONE X-NONE Problem When I import a database that uses ASP.NET Providers I get an unresolved reference to the object [#aspnet_Permissions]. Solution Modify the script to define the shape of the temp table by adding the additional SQL above the declaration of the cursor. IF(OBJECT_ID('tempdb.#aspnet_Permissions') IS NULL) BEGIN CREATE TABLE #aspnet_Permissions  ( Owner     sysname,  Object    sysname,    Grantee   sysname,    Grantor   sysname,    ProtectType char(10),    [Action]    varchar(60),    [Column]    sysname ) END Or just download the file below. Explanation The "#aspnet_Permissions" temp table must be created by ASPNET at the same time as the membership database is created. Because the table is not created in this script or referenced by this project the table appears as an external reference that DBPro cannot resolve. By adding the code the table can be resolved by the DBPro project and will not create the table if it already exist on the database server you are deploying too. aspnet_Setup_RestorePermissions.proc.sql (1.14 kb)

sysobject warnings in my DBPro projects

Problem My DBPro project references sysobjects and is causing build warnings for example: SQL04151: Procedure: [dbo].[aspnet_AnyDataInTables] has an unresolved reference to object [dbo].[sysobjects].    Solution Add the following DB reference to your database project: C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas\master.dbschema If you are targeting 2005 SQL Server use: C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\2005\DBSchemas\master.dbschema

I need to build a project that is not supported by MSBuild

Problem My solution contains a .vdproj and it is not supported by MSBuild. Solution Call devenv from team build using InvokeProcess for .vdproj projects. Explanation This customization can be extended to build any project types not supported by MSBuild (vb6, power builder, fortran, VC++ 6, etc).  Any project that can be built from the command line can also be built using TFS 2010 Build using this technique. We are simply going to add a switch statement that uses the extension of the project to determine if we pass it to MSBuild or perform the build ourselves.  Let’s start by opening DefaultTemplate.xaml from the BuildProcessTemplates folder.  To begin we must locate the correct area of the build template to add our switch statement.  I find the quickest way to do this is to click the Collapse All button at the top of the workflow designer window.  Now double click on the words Double-click to vew on all of the following activities: 1.    Run On Agent2.    Try Compile, Test, and Associate Changesets and Work Items 3.    Sequence4.    Compile, Test, and Associate Changesets and Work Items5.    Try Compile and Test6.    Compile and Test7.    For Each Configuration in BuildSettings.PlatformConfigurations8.    Compile and Test for Configuration9.    If BuildSettings.HasProjectsToBuild10.    For Each Project in BuildSettings.ProjectsToBuild11.    Try to Compile the Project12.    Compile the Project The Compile the Project Sequence contains the Convert Server Path to Local Path and Run MSBuild for Project activities.  We are going to add our switch around Run MSBuild for Project.  In the toolbox expand the Control Flow tab and drag and drop the Switch<T> activity right above the Run MSBuild for Project activity.  When prompted for type, select String and click OK. Our switch is going to test the extension of the current project being built. If the extension is .vdproj we are going to call devenv ourselves to build the setup project.  If the extension is anything else we are going to simply let MSBuild build it.  So the first thing we need to do is find the extension of the project being built.  The Convert Server Path to Local Path activity stores the project path in localProject which we can use to find the extension.  Click the double down arrows in the title bar of the Switch<String> activity to show the expression and cases of the switch.  In the Expression text box enter the following: (New System.IO.FileInfo(localProject)).Extension.ToLower()This line of code uses an instance of the FileInfo class to gain access to the extension of the current project being built.  We call ToLower to make sure the case of the extension is known for our comparison.Now click the word Default to display the activity area of that case.  Drag the Run MSBuild for Project into the default case. At this point the template works exactly as it did before we touched it. After our customizations you will still be able use this template for all your current builds that use the default template. Now it is time to add the case for the .vdproj projects. Click the words Add new case. In the Case Value combo box type .vdproj without ”’s.  The switch already knows that what you are going to type is a string so you DONOT type the “’s.  If you do it will fail because “.vdproj” is not equal to .vdproj. From the toolbox drag and drop a Sequence activity from the Control Flow tab onto the .vdproj case.  Change the display of this sequence to Build Setup Project.  We add a sequence here so that we can use multiple activities. Click the double down arrows in the title bar of the sequence to show it's contents.  This is where we are going to add the activities needed to build the setup projects. Before we can add the InvokeProcess activity to build the setup projects we first need to know the command line.  Below is an example of the command line to build a setup project.  Notice we must provide the configuration to build "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv" "C:\Builds\1\Build Sandbox\HelloWorld\Sources\HelloWorld\Setup\Setup.vdproj" /build Release Now we can add the InvokeProcess activity and set the properties to build this command line during the build.  To get started simply drag and drop the InvokeProcess activity from the Team Foundation Build Activities tab into the Build Setup Project sequence.  With the InvokeProcess activity selected set the following values in the properties window: •    Arguments - """" + localProject + """ /build " + platformConfiguration.Configuration•    FileName - """C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv""" The rest of the values can remain with their default values.  Pay close attention to the number of “’s and white space.  Because localProject may contain spaces we must quote the string. The same is true for the path to devenv.exe.  Whenever I use an InvokeProcess activity I always expand it and add a WriteBuildMessage under Handle Standard Output with the Message set to stdOutput and a WriteBuildError under Handle Error Output with the Message set to errOutput.  This will allow that information to be show in the build output.  At this point if we check in our changes and add our .vdproj file as an Items to Build from the Process tab of the build definition the setup project will be built.  Be sure and remove the .vdproj project from the solution or you will continue to get errors from MSBuild. However, it will not be in our drop location.  The reason for this is because only items that are in the binaries folder get copied to the drop location.  The final piece is to add a CopyDirectory activity under the InvokeProcess to copy the contents of the output folder of the setup project to the binaries folder. With the CopyDirectory activity selected set the following values in the properties window: •    Destination - BinariesDirectory•    Source - (New System.IO.FileInfo(localProject)).DirectoryName + "\" + platformConfiguration.Configuration + "\" Your Build Setup Project sequence should look like this:  Now save your xaml file, check in your changes and queue a new build. You can download a copy of the final file below. TFS2010 DefaultTemplate.xaml (59.14 kb) TFS2012 DefaultTemplate.11.1.xaml (81.14 kb)

One lab machine will not connect to test controller!

Problem I have a lab environment configured with 4 machines. It is running in Network Isolation, and has workflow and test capability enabled.  The testing capability fails only on one machine.  Solution Adjust the binding order of your network adapters so that external adapter is first.  To do this bring up the Network Connections window and click on the Advanced menu.  If the Advanced menu is not showing press the Alt key to display it. From the Advanced Menu selected Advanced Settings.  The Advanced Setting dialog will open. Use this dialog to move the external network adapter to the top of the list. Explanation If you are getting a message like the one below: "The machine is not ready to run tests because of the following error: Unable to connect to the controller on '<TestControllerFQDN>:6901'. Reason: No such host is known. Make sure the test controller is online and the firewall on the test controller is not blocking the connection." The chances are if you try to ping the Test Controller machine from the lab machine it will fail.  If the internal network adapter binds first it may cause issues when trying to resolve network names.  Simply move the external network adapter to the first position.

My build agent just stopped working!

Problem My build agent state in Team Foundation Administrative Console states Ready but the icon shows stopped and I can't queue builds. Solution Either log in as the Service Account used to run the build agent or log in using any account and right click on IE and run as the Service Account.1. Open Internet Explorer and go to Tools –> Internet Options2. Choose the Connections tab3. Click the “LAN settings” button4. Uncheck the “Use a proxy server for your LAN” checkbox.  Explanation One of my builds collects code coverage results on a web application.  To do so the IE connection settings are configured to use a proxy while the data is collected and then returned to the original configuration afterwards.  However, if you are forced to stop a build the proxy will stay configured and lead to this problem. The key is to log in or run IE as the Service account to uncheck the proxy check box on the connection setting of IE.

I can't run VMM and I am an admin

Problem When I run VMM I am given the error I don't have permissions to run VMM on localhost. Solution You have to have a current VMM administrator to add you to the Administrator role in VMM under Administration tab. Start VMM Click the Administration tab Select User Roles Double Click Administrator under Profile Type Click the Members tab Click Add… and add the user