I started using VSTS Database Edition back when it was called Data Dude and available as a CTP download. Since then I have slowly embraced all its features and I now use it as my complete database development solution from schema management, to data generation, and finally deployment.

DB Edition has its quirks but you learn to understand them and work with them and each new version has new features to improve the workflow. However, as I have been using DB Edition each day, an idea has been steadily stewing in my head for where I'd like to see DB Edition go next. My thought process in a nut-shell follows...

A Database Project allows you to define your schema and data generation and, from a Visual Studio context menu, deploy the database to a chosen server as a new database or as an upgrade to an existing database. You can also use the DatabaseTestService in a test project to deploy the database and test data for automated testing. And finally you can use the SqlDeploy MSBuild task to deploy the database as part of a continuous integration build.

However, all of these methods of deployment require the relative path to the Database Project and all its SQL scripts and some settings in a configuration file. This causes problems in deployed test runs and Team Build test runs where the relative path to Database Project often changes. It also restricts using multiple test databases in a single test project due to the way the configuration file works.

I propose that, at build time, DB Edition could package into a .NET assembly, the full definition of the Database Project along with some standard bootstrap code but minus the deployment configuration. Test projects could then include a reference to the DB assembly and make calls into the bootstrap code, perhaps something in the form:

MyDBAssembly.Schema.DeployTo(someConnectionString, someOptions);
MyDBAssembly.SomeDataGenPlan.DeployTo(someConnectionString);

The DB assembly will be treated as a dependency like any other references would and will naturally be moved around wherever the primary assembly gets deployed and all the necessary information will always be available to perform a completely new database deployment or to perform a schema upgrade on any compatible existing database instance.

If the DB assembly happened to double as a console application, it could be used for ad hoc command-line deployments or even included in batch, MSBuild, or PowerShell scripts for automated deployments.

I am contemplating several ways to hack a feature like this into DB Edition myself but I'm hoping someone else has already done it or maybe the DB Edition team already has it on the cards for Rosario.

I've used several third party Check In Policies for Team Foundation Server with both TFS2005 and 2008 and I've dabbled in writing my own too. One thing I noticed with most of them, is that they don't appear to respond to actions in the VS Pending Changes window as readily as the standard Microsoft policies.

I recently followed Jim's example Option Strict Check In Policy to write my own policy to prevent checking in code with the DataSet Designer ConnectionState Bug. The policy would always correctly evaluate when clicking the Check In button but the Policy Warnings tab didn't always update automatically when Source Files list changed.

I decided it was time to dig deeper and find out why the standard policies work nicely when compared to the custom ones. After a few minutes with Reflector I discovered there were a few more things to do beyond the instructions in the MSDN article to create a responsive custom policy.

The PolicyBase class, from which your custom policy should inherit, gets passed an instance of IPendingCheckin to its Initialize method which it persists and is made available to your subclass via a protected PendingCheckin property. The IPendingCheckin instance exposes several other objects with events that you can handle to be notified when changes relevant to your policy occur.

The methodology that worked for me was to override Initialize and register the event handler and override Dispose also to remove the handler at the end. All the handler does, after checking the base class isn't disposed, is to call the custom policy's Evaluate function and raise the base's PolicyStateChanged event.

Sample code for a policy dependent on the Source Files list, like the Option Strict policy and the ConnectionState Bug policy, follows:

Public Overrides Sub Initialize(ByVal pendingCheckin As IPendingCheckin)
    MyBase.Initialize(pendingCheckin)
    AddHandler MyBase.PendingCheckin.PendingChanges.CheckedPendingChangesChanged, _
        AddressOf CheckedPendingChangesChanged
End Sub

Private Sub CheckedPendingChangesChanged(ByVal sender As Object, ByVal e As EventArgs)
    If Not MyBase.Disposed Then
        Dim failures = Evaluate()
        MyBase.OnPolicyStateChanged(failures)
    End If
End Sub

Public Overrides Sub Dispose()
    RemoveHandler MyBase.PendingCheckin.PendingChanges.CheckedPendingChangesChanged, _
        AddressOf CheckedPendingChangesChanged
    MyBase.Dispose()
End Sub

Almost two months ago, at the February Adelaide Geek Dinner, I was expressing my frustration at one of my Visual Studio solutions taking too long to build and how I would like Visual Studio to build using multiple processors just like the new MSBuild /m parameter.

Paul Stovell made the comment that even with the improvement that multi-core builds was giving me, my solution really shouldn't be taking that long to build. Given that Paul wasn't familiar with my particular project layout and I naturally didn't have a copy with me, the conversation quickly went onto other topics.

However, Paul's comment stayed with me for days after, bugging me every time I waited for the latest build to complete. Then, while staring at the VS Output window during a build, I noticed that most of the time seemed to be spent running FxCop on each project.

I decided to rebuild the solution but this time disabling code analysis via the appropriate build switch. I watched the build time drop from 40 seconds to just 10 seconds by skipping the FxCop process.

Excellent! But given that our entire team runs with Option Strict On, Treat Warnings As Errors, and the Code Analysis Check-in Policy, how could I possibly revert to such a lax build process for the sake of decreased build time?

The answer is to disable Code Analysis in each project's settings (and unfortunately the check-in policy too) but leave it enabled in the Team Build script so it runs and gets reported via the continuous integration build that runs after each check-in. Luckily we've also been running with FxCop for so long that we tend to avoid writing code that would cause violations in the first place.

I met with Paul over the weekend and mentioned my success with better build times. When I told him I had been running FxCop with every build, he just laughed, amazed.

I just finished reading Martin Fowler's book, P of EAA. Fowler does a great job of presenting various ways to approach solving each given enterprise architecture problem in an object-oriented way and explains the circumstances that will suit each solution and where each solution starts to fall down. This is a welcome change from books and blogs that preach The One True Way.

As Ayende has blogged recently, the patterns described in the book made a lot of sense of code like that in the NHibernate framework and at the same time convinced him that there is no real reason to re-implement those patterns. While I agree that implementing all the required structures yourself is both a large task and a good reason to "buy" an existing system, learning to code something like this yourself can really help to improve your understanding.

Fowler also opened my eyes as to how various classes in the .NET Framework relate to these patterns, and therefore, what methods for interacting with such classes work well. One example, is how ADO.NET's DataSet compares to the Unit Of Work. Rather than having a DataSet fill from a database, then exist for the life of an application and commit back to the database at the end, instead the life of a DataSet should correspond closely to that of a particular business transaction.

The book also contains a good mix of both Java and C# examples, and highlights how, with different built-in tools in these two development environments, each of these languages implements a pattern with its own strengths. My only concern was that much of the discussion leaned toward web application scenarios. My preferred domain, smart client applications, have a different set of issues to solve but admittedly the book is about architecture patterns, not about smart client presentation patterns, so I can't complain.

I can recommend this book as a good read for anyone writing large business applications.

K Scott Allen just recently posted the final article in a series of five about managing the development and deployment of a relational database alongside your code in a team environment.

He highlights some common failing points and good solutions to tricky obstacles, most of which I've faced throughout my career and learned about the hard way. I haven't quite achieved the same level of streamlined schema management that Scott has but it's good to know I'm on the right track.

Visual Studio for Database Professionals projects have started to be included in our product source control and we are pushing this great but still relatively new tool to automate as much of our schema management as possible. No matter how good the tools are though, I'm not the only one who feels at least a few developers on any team "need to step up and get comfortable with SQL".

In fact, getting comfortable with effective use of PowerShell, in addition to SQL, has helped to get reams of lookup data (ie post codes, etc) into repeatable T-SQL scripts and also to manage deployment of schema changes to multiple sites.

I've also just ordered Refactoring Databases: Evolutionary Database Design by Scott Ambler and Pramod Sadalage. The book focuses on applying the same incremental refactoring techniques used in code to evolve a database schema over time with minimal upset to existing systems. I expect to find some real insight into database development in this book.

Ambler has recently recorded some good interviews on both .NET Rocks and OnSoftware and Pramod has an e-book on Recipes For Continuous Database Integration worth investigating.

In my last post about the Adelaide Geek Dinner, I intended to schedule the coming dinner for January. Unfortunately, if I scheduled it for the last weekend in January, that would coincide with the Australia Day long weekend and I imagine several people already have plans.

Therefore, the next dinner will instead be Saturday 2nd February, but once again at Cafe Buongiorno, Rundle Street, starting at 6:30pm. I have sent invitations via email to all the people who attended last time and also to those who couldn't make it.

If you didn't receive an invitation and would like to attend, contact me and I'll arrange it. Please RSVP by Thursday 24th January so I can ensure enough seats will be reserved at the restaurant.

I look forward to discussing all the interesting things that have been happening since the November dinner.

A few weeks ago I was trying to put together a quick regular expression for converting an entire English sentence into Pascal (or Headed Camel) Case. What would have been rather trivial in Perl was a little ugly in C#.

Tonight, in a typical midnight haze, I realised I could use a lambda expression to tidy it up a bit. Here is the result, short and simple, presented in unit test form:

[TestMethod]
public void ShouldRemoveSpacesAndCapitaliseWords()
{
   const string original = "Should remove spaces and capitalise words";
   const string expected = "ShouldRemoveSpacesAndCapitaliseWords"; 

   Regex rx = new Regex(@"(\s*)\b(\w)");
   string result = rx.Replace(original, m => m.Groups[2].Value.ToUpper()); 

   Assert.AreEqual(expected, result);
}

I was trying to renew my Thawte Personal Email Certificates this week because they expire very soon. Unfortunately I was having zero luck requesting a new certificate via Thawte's certificate management website.

Whenever I reached the stage to choose a Cryptographic Service Provider, the VBScript that is supposed to fill the drop down list would fail with a generic "424 Object required" error and the process would go no further. I tried several PCs but all my machines at home and work are Vista with IE7 and Thawte's list of supported software does not list Vista or IE7.

I downloaded the Internet Explorer 6 Application Compatibility VPC image from Microsoft and was able to complete the certificate renewal then export the private key to a file and move it out of the virtual machine and install it on my workstation.

I tried IE7 on Vista 32-bit, 64-bit, non-admin, Administrator, Protected Mode On and Off, and tried adding the Thawte website to my trusted sites. None of this helped. I eventually decided to download the IE7 version of the App Compat VPC to see if Vista or IE7 is the problem.

I found that apart from a security warning, which helped me to track down the ultimate reason, the Thawte website works fine with IE7 on Windows XP. The reason is that the XEnroll.dll certificate enrolment control was replaced in Vista for a more secure CertEnroll.dll.

As usual, I'm disgusted that it has been eleven months since Vista went RTM and Thawte still haven't addressed this issue. It's even worse because the Betas and Release Candidates of Vista were around for even longer and these distributions exist so third party software and service providers can test their systems to be ready soon after launch.