SQL 2008 T-SQL MERGE Operator

I’ve had some opportunity to play around with a few of SQL 2008’s new features and I really like the new MERGE operator and wrote my own code sample.
-- create the source and target tables
declare @sourceTable table
(
id int primary key identity(1,1) not null,
firstName nvarchar(255) not null,
lastName nvarchar(255) not null
)

declare @targetTable table
(
id int primary key not null,
firstName nvarchar(255) not null,
lastName nvarchar(255) not null
)

-- populate the source
insert @sourceTable
select 'John', 'Doe' union
select 'Jane', 'Doe' union
select 'Adam', 'Smith' union
select 'Alex', 'Smith'

-- view the contents of both tables
select * from @sourceTable
select * from @targetTable

-- use the MERGE statement to populate the target from the source
merge @targetTable t -- target
using (select id, firstName, lastName from @sourceTable) s -- source
on t.id = s.id
when matched then update set t.firstName = s.firstName, t.lastName = s.lastName
when not matched by target then insert values (id, firstName, lastName)
when not matched by source then delete;

select * from @sourceTable
select * from @targetTable

-- Jane Doe and Alex Smith got married and Jane decided to hyphenate her last name to Doe-Smith
update @sourceTable set lastName = N'Doe-Smith'
where firstName = N'Jane' and lastName = N'Doe'

select * from @sourceTable
select * from @targetTable

-- source and target now have different values for the 3rd record
-- we can use the same code to match up target and source
merge @targetTable t -- target
using (select id, firstName, lastName from @sourceTable) s -- source
on t.id = s.id
when matched then update set t.firstName = s.firstName, t.lastName = s.lastName
when not matched by target then insert values (id, firstName, lastName)
when not matched by source then delete;

select * from @sourceTable
select * from @targetTable

-- remove Adam Smith from the source table
delete from @sourceTable where id = 1

select * from @sourceTable
select * from @targetTable

-- once again we'll use the same merge statment to match up our temporary source and target
merge @targetTable t -- target
using (select id, firstName, lastName from @sourceTable) s -- source
on t.id = s.id
when matched then update set t.firstName = s.firstName, t.lastName = s.lastName
when not matched by target then insert values (id, firstName, lastName)
when not matched by source then delete;

select * from @sourceTable
select * from @targetTable

C# 3.0 – Part II :: Object & Collection Initializers

In part I, the feature discussed was auto-implemented properties. Here we’ll make use of the classes defined in part I to demonstrate object initializers.

Notice that no constructor was declared in the classes defined. The compiler creates a default constructor (empty constructor), but this isn’t new in 3.0. What is new, is a way to set the properties of the object as it is initialized.

// declare and initialize an instance of the Address class
Address homeAddress = new Address()
{
// set the properties using C# 3.0 object initializer feature
Street = "123 Some Street",
City = "Newport Beach",
State = "CA",
PostalCode = "92626",
AddressType = AddressType.Home
};

We can then use the homeAddress instance to instantiate a Person object, also defined in part I.

Person person = new Person()
{
ID = 1,
FirstName = "John",
LastName = "Doe",
Addresses = new List<Address>(){ homeAddress }
};

Notice that we’ve initialized the collection of addresses of the person instance, as the object itself is initialized. Collection Initializers is also a new feature of C# 3.0. Instead of calling the Add method of a collection, we can simply list the objects, similar to that of arrays.

We can even combine the initialization of objects and collections into one big block of code.

Person person = new Person()
{
ID = 1,
FirstName = "John",
LastName = "Doe",
Addresses = new List<Address>()
{
new Address()
{
Street = "123 Some Street",
City = "Newport Beach",
State = "CA",
PostalCode = "92626",
AddressType = AddressType.Home
}}};

Probably the most interesting to me are the collection initializers. I’ve often have had to create lookup dictionaries for one reason or another. Typically the process is declare and instantiate the dictionary and then populate with lookup values.

Dictionary<int, string> numberLookup = new Dictionary()<int, string>();
numberLookup.Add(1, "one");
numberLookup.Add(2, "two");

Instead, we can instantiate like so:

Dictionary<int, string> numberLookup = new Dictionary()<int, string>()
{
{ 1, "one" },
{ 2, "two" }};

C# 3.0 – Part I :: Auto-Implemented Properties

I’ve been playing around with VS 2008 and looking at some of the new features implemented for the next version of C#. Here’s some features that I found to be time saving.

Auto-Implemented Properties
Encapsulating properties has always been a pain, even with code snippets. C# 3.0 allows the declaration of backing fields with auto implemented properties, as shown below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Sample
{
public class Person
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public List<Address> Addresses { get; set; }
}

public class Address
{
public string Street { get; set; }
public string City { get; set; }
public string State { get; set; }
public string PostalCode { get; set; }
public AddressType AddressType { get; set; }
}

public enum PhoneType
{
Home,
Work,
Fax,
Mobile
}

public enum AddressType
{
Home,
Work
}
}

I added a an enumeration and List of addresses to play with collections later. A caveat to the auto-implemented properties is that you can’t have complex get or set accessors and mutators. You’re probably thinking what’s the point? You can get the same thing with making the backing fields themselves public. I agree that the use is limited; however, there is that option to make the mutator private in order to make the property read only. The feature isn’t ground breaking but it does save some time when you’re making some simple container class.

Special Folders

Ever wonder where certain special folders map to? There’s the enumeration System.Environment.SpecialFolder and the special folders in a Visual Studio setup project to name a couple. When you’re using them in your code or setting up a deployment project, how do you know that the special folder you’re using is actually what you want?

Well I got tired of running the app to see if it will break or making a small application to test what folder does it actually map to. Hence I wrote a small app and iterateded through the enumeration to get the folder paths using
Environment.GetFolderPath(Environment.SpecialFolder)
Environment.SpecialFolder

Enumeration Value	Vista (32 bit)
Desktop	C:\Users\{username}\Desktop
Programs	C:\Users\{username}\AppData\Roaming\Microsoft\Windows\Start Menu\Programs
Personal	C:\Users\{username}\Documents
Favorites	C:\Users\{username}\Favorites
Startup	C:\Users\{username}\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup
Recent	C:\Users\{username}\AppData\Roaming\Microsoft\Windows\Recent
SendTo	C:\Users\{username}\AppData\Roaming\Microsoft\Windows\SendTo
StartMenu	C:\Users\{username}\AppData\Roaming\Microsoft\Windows\Start Menu
MyMusic	C:\Users\{username}\Music
DesktopDirectory	C:\Users\{username}\Desktop
Templates	C:\Users\{username}\AppData\Roaming\Microsoft\Windows\Templates
ApplicationData	C:\Users\{username}\AppData\Roaming
LocalApplicationData	C:\Users\{username}\AppData\Local
InternetCache	C:\Users\{username}\AppData\Local\Microsoft\Windows\Temporary Internet Files
Cookies	C:\Users\{username}\AppData\Roaming\Microsoft\Windows\Cookies
History	C:\Users\{username}\AppData\Local\Microsoft\Windows\History
CommonApplicationData	C:\ProgramData
System	C:\Windows\system32
ProgramFiles	C:\Program Files
MyPictures	C:\Users\{username}\Pictures
CommonProgramFiles	C:\Program Files\Common Files

Validating Embeded Digital Signatures

I had a recent project that had a feature of being able to update itself as determined by a web service. The service provided information as to the URI of the files to be updated. The client component grabs the files, in zip format, downloads it and extracts and either replaces files in the application or executes it; replacement and/or execution determined by the meta-data from the service. As a precaution, we wanted to ensure that the files executed really are from us. To verify, we implemented a rule that only digitally signed dll’s and exe’s would be allowed to run by our updater.

The challenge was being able to verify the digital signatures. Initially I was using the System.Security.Cryptography.X509Certificates.X509Certificate class to validate the certificate. The idea is to validate the certificate chain to the root CA and verify the publisher.

What I ran into was that the .NET classes only successfully validated .NET assemblies, but not all. The final solution was to create a wrapper class that calls the WinVerifyTrust method of wintrust.dll.