Sachin Rekhi's Ramblings

Database Unit Testing Whitepaper Published

2007-03-17T01:48:00Z

I wanted to let you all know that I just published my detailed whitepaper on database unit testing.

Summary: Microsoft Visual Studio 2005 Team Edition for Database Professionals is the first toolset to deliver a comprehensive framework for performing database unit tests. This product brings to the data community the same first-class capabilities for performing unit tests that many application developers enjoy today. This paper explains exactly what database unit testing is, shows you how to develop database unit tests by using the framework, and walks you through how to leverage its rich capabilities to develop an effective strategy for performing database unit tests inside your organization. (21 printed pages)

You can check it out here!

http://msdn2.microsoft.com/en-us/library/bb381703(VS.80).aspx

Sachin Rekhi

Reporting Survey

2007-01-19T02:23:00Z

As many of our Team System customers know, a lot of the value that comes from the Visual Studio Team System is in the reporting functionality, providing you direct visibility into the health of your development projects. We’ve tried to create a set of out of box reports to allow you to immediately realize the value of all the data Team System collects for you.

However, we are looking for your feedback to improve the reporting functionality in future editions. Please take 10-15 mins to give us feedback on what you would like to see in terms of reporting in Visual Studio Team System.

http://www.zoomerang.com/survey.zgi?p=WEB22632D8HGS3

Sachin

New DB Pro Case Study

2007-01-18T22:15:00Z

Check out this great case study that was recently published showing exactly how Team Edition for Database Professionals has significantly helped Gulf Coast Seal. I love seeing these success stories popping up for DB Pro, especially so quickly after RTM!

http://www.microsoft.com/casestudies/casestudy.aspx?casestudyid=200529

Sachin

Programmatic deployment sample

2007-01-02T22:30:00Z

Jamie Laflen, Tech Lead for database unit testing, has put together another great sample showing you how to programmatically deploy your database project using the database unit testing framework.

The scenario here is that you can control the deployment of the database project to happen whenever you want it to during the database unit testing lifecycle. This gives you much more flexibility compared to the automatic deployment supported through the database unit testing UI. This can also be used for deploying your database project outside of the context of database unit testing in a programmatic way.

The sample is in the form of a console application that supports deployment of your database project through a command-line argument specifying the configuration file.

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

// This file is part of:

// Visual Studio Team Edition for Database Professionals

// This source code is intended only as a supplement to Microsoft

// Development Tools and/or on-line documentation. See these other

// materials for detailed information regarding Microsoft code samples.

// THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY

// KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

// PARTICULAR PURPOSE.

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

using System;

using System.Collections.Generic;

using System.Text;

using System.Diagnostics;

using System.IO;

using System.Configuration;

using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Configuration;

using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting;

namespace DeployDBSample

{

class Program

{

FileInfo _configFile;

[STAThread()]

static void Main(string[] args)

{

try

{

ConsoleTraceListener l = new ConsoleTraceListener();

l.TraceOutputOptions = TraceOptions.Timestamp;

Trace.Listeners.Add(l);

Trace.AutoFlush = true;

Debug.AutoFlush = true;

Program p = new Program();

p.Run(args);

}

catch (Exception ex)

{

Trace.TraceError("Unhandled exception {0}{1}", Environment.NewLine, ex);

}

public Program()

{

}

void Run(string[] args)

{

if (ValidArgs(args) == false)

{

PrintUsage();

return;

}

// Load the config section

string sectionName = "DatabaseUnitTesting";

ExeConfigurationFileMap map = new ExeConfigurationFileMap();

map.ExeConfigFilename = _configFile.FullName;

Configuration config = ConfigurationManager.OpenMappedExeConfiguration(map, ConfigurationUserLevel.None);

DatabaseUnitTestingSection section = config.GetSection(sectionName) as DatabaseUnitTestingSection;

if (section == null)

{

Console.WriteLine("Could not load section {0}", sectionName);

return;

}

// If the Database deployment section is defined, attempt to deploy the DBProject

if ( section.DatabaseDeployment != null

&& string.IsNullOrEmpty(section.DatabaseDeployment.DatabaseProjectFileName) == false)

{

// Deploy the database project

HelperService s = new HelperService();

s.Deploy(

section.DatabaseDeployment.DatabaseProjectFileName,

section.DatabaseDeployment.Configuration,

section.PrivilegedContext.Provider,

section.PrivilegedContext.ConnectionString);

}

#region Console app helpers

void PrintUsage()

{

string exeName = this.GetType().Assembly.Location;

string usage = string.Format("Usage: {0} configFile", exeName);

Console.WriteLine(usage);

}

bool ValidArgs(string[] args)

{

bool isValid = true;

if (args == null || args.Length != 1)

{

return false;

}

// The first argument is the configuration file

_configFile = new FileInfo(args[0]);

if (_configFile.Exists == false)

{

isValid = false;

}

return isValid;

}

#endregion

class HelperService : DatabaseTestService

{

public void Deploy(

string dbProjectFileName, string projectConfig,

string providerName, string connectionString)

{

DeployDatabaseProject(dbProjectFileName, projectConfig, providerName, connectionString);

}

Take it for a test drive!

Jamie Laflen

Sachin Rekhi

Disabling triggers to support data generation

2007-01-02T21:47:00Z

Jamie Laflen, Tech Lead for database unit testing, has developed a code sample to show you how to automatically disable triggers to support data generation.

The scenario is that you have delete or insert triggers defined on your database that inhibit proper test data generation. So this sample code will allow you to modify your database unit test setup such that before generating test data, the triggers will be disabled and then re-enabled after the data has been appropriately generated. This is an important scenario that customers have been running into when attempting to generate data for, say, the AdventureWorks database that has delete triggers defined.

To use the sample code, replace your existing DatabaseSetup.cs in your test project with the code found below. You will have to update the namespace, etc. to be appropriate for your test project. The sample code uses SMO to determine what insert and delete triggers exist in your database and then appropriately disables them. Make sure you thus add the appropriate reference to SMO to your test project.

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

// This file is part of:

// Visual Studio Team Edition for Database Professionals

// This source code is intended only as a supplement to Microsoft

// Development Tools and/or on-line documentation. See these other

// materials for detailed information regarding Microsoft code samples.

// THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY

// KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

// PARTICULAR PURPOSE.

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

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Configuration;

using System.Data.SqlClient;

using System.Diagnostics;

using Microsoft.VisualStudio.TestTools.UnitTesting;

using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

namespace TestProject1

{

[TestClass()]

public class DatabaseSetup

{

[AssemblyInitialize()]

public static void IntializeAssembly(TestContext ctx)

{

// Setup the test database based on setting in the

// configuration file

DatabaseTestClass.TestService.DeployDatabaseProject();

ConnectionContext triggerCtx = DatabaseTestClass.TestService.OpenPrivilegedContext();

List<Trigger> disabledTriggers =

DisableTriggersForPopulate(triggerCtx.Connection as SqlConnection);

try

{

DatabaseTestClass.TestService.GenerateData();

}

finally

{

EnableTriggers(disabledTriggers);

triggerCtx.Dispose();

}

/// <summary>

/// Disables all Insert and Delete triggers defined and enabled on tables

/// in the database connected to by the supplied connection string.

/// <remarks>

/// This disables all triggers by enumerating all tables (and their triggers)

/// in the entire database. This can have a negative performance, if performance

/// becomes an issue, a script that directly disables/enables triggers should be

/// used instead.

/// </remarks>

/// </summary>

/// <param name="targetConn"></param>

/// <returns></returns>

private static List<Trigger> DisableTriggersForPopulate(SqlConnection targetConn)

{

if (targetConn == null)

{

throw new ArgumentNullException("targetConn");

}

ServerConnection serverConn = new ServerConnection(targetConn);

Server targetServer = new Server(serverConn);

Database db = targetServer.Databases[targetConn.Database];

// Loop through all tables and disable all Insert/Delete triggers

List<Trigger> disabledTriggers = new List<Trigger>();

foreach (Table currentTable in db.Tables)

{

foreach (Trigger currentTrigger in currentTable.Triggers)

{

if (currentTrigger.IsEnabled)

{

if (currentTrigger.Insert || currentTrigger.Delete)

{

currentTrigger.IsEnabled = false;

currentTrigger.Alter();

Debug.Print("Disabled {0}", currentTrigger.Name);

disabledTriggers.Add(currentTrigger);

}

return disabledTriggers;

}

/// <summary>

/// Enables all supplied triggers

/// </summary>

/// <param name="triggers"></param>

private static void EnableTriggers(List<Trigger> triggers)

{

if (triggers == null)

{

return;

}

foreach (Trigger t in triggers)

{

t.IsEnabled = true;

t.Alter();

Debug.Print("Enabled {0}", t.Name);

}

Hope that helps!

Jamie Laflen

Sachin Rekhi

Database Unit Testing Whitepaper Draft

2006-12-20T23:02:00Z

So I’ve spent the last week authoring a detailed whitepaper on Team Edition for Database Professional’s database unit testing framework. I thought I would post the draft online here prior to publishing it to MSDN.

Summary: Team Edition for Database Professionals is the first toolset to deliver a comprehensive database unit testing framework, bringing the same first-class unit testing capabilities many application developers enjoy today to the data community. This paper explains exactly what database unit testing is, shows you how to go about developing database unit tests using the framework, as well as walks you through how to leverage its rich capabilities to develop an effective database unit testing strategy inside of your organization. (23 pages)

Please let me know any feedback that you have!

Sachin Rekhi

Team Edition for Database Professionals v1.0 in now available!

2006-12-08T00:31:00Z

The first edition of Team Data is now available for download on MSDN. So if you are an MSDN subscriber, go get it now!

I just wanted to say that I’m very proud of what this team has accomplished and am super excited to get this pivotal release out to our customers. Please use the tool and let us know what you think! We’d love your feedback as we start to begin planning on the next wave of functionality we want to bring to the Database Professional.

You can also get the trial edition here. So give it a whirl!

Sachin Rekhi

Common Regular Expressions

2006-11-29T21:06:00Z

Gert Drapers has a great post on taking full advantage of the regular expression generator. He shows you some very useful regular expressions that you’ll want to take advantage of in creating meaningful test data using the data generation feature in Team Edition for Database Professionals.

Check it out!

Sachin Rekhi

Process Guidance Incorporating DB Pro Available!

2006-11-18T22:54:00Z

The other major announcement we made this week was that the MSF Process Guidance Templates have been updated to incorporate the new database professional roles and this updated guidance is available today!

We have made updates to both the MSF Agile and MSF for CMMI process guidance templates to incorporate the new database developer and database administrator roles that are specifically targeted by the upcoming Team Edition for Database Professionals.

As I have said all along, Microsoft is building a database development solution that consists of two key components. The first is the great toolset we have integrated into Visual Studio to enable managed database development. Equally important is the updated process guidance describing just how your organization can implement an efficient and repeatable process to improve the efficiency, mitigate the risk of, and manage database changes.

You can find the updated MSF Agile guidance here and the updated MSF for CMMI guidance here. Check out Randy Miller’s blog post for details on how to install the new guidance.

These process guidance updates are only the first phase in rich guidance we hope to deliver to our customers. Expect white papers, articles, and such from the product team in the near future.

And of course, always come back to this blog for the latest tips, tricks, and best practices on database development.

Sachin Rekhi

Team Data Ship Date: November 30, 2006

2006-11-18T22:44:00Z

We officially announced this week at the SQL Pass conference our RTM date for Team Edition for Database Professionals: November 30^th this year!

Our customers have been clamoring for this release and we are incredibly amazed with what we have built in the past year. We are super excited to get this in the hands of our customers by the end of the month.

Check out Cameron’s blog post for more details about the announcement.

Sachin Rekhi