I recently had the task of creating a report where they wanted one of the columns to list out all the users associated with a given report line item in a single comma separated string. My initial thoughts were, that this would involve some type of cursor or at least a loop of some description, which I'd then have to attach to the main result set. However, it seems there's a very nice little trick you can use that will solve this issue without the use of cursors or loops!

With the use of the COALESCE keyword you can create a list in a single line of SQL (except for declarations). I'll use the AdventureWorks database to demonstrate the technique. Assume  you need to get a list of Countries and the regions they contain, in a report similar to the one below: More...

This is the second part of the series which discusses the issues around modeling M:1 (many-to-one) relationships. If you've not read the first part, then its advisable that you read that first before continuing with this part. To keep up to date with this and other blog's you might consider registering with the RSS feed.

Part I: The Problem

Part III: BusinessKey as a value type

Suggested Solution

Having covered the issues with M:1 relationships and the three different types I'll now present a potential solution which provides the necessary foreign key information, but in a more domain model fashion. I'll also point out yet another issue with the original logical model when it comes to actually using it, which I'll also offer up a solution for. More...

Designing a domain model can be a tough job and if you're lucky enough to start from scratch then it becomes even tougher once you apply it to the physical database model. Over the next few posts I' like to discuss the issues we encounter when modeling this relationship type, examine the way its 'normally' done and then present an alternative that I believe presents a more domain model centric view of the world. The series will be broken up into the following sections, with this being the first. To keep up to date with this and other blog's you might consider registering with the RSS feed.

Part I: The Problem

Part II: Suggested Solution

Part III: BusinessKey as a value type

The main issue when designing a domain model is that they don't have these oddities called foreign keys, as the relationships are known by association. However, once you turn your domain model into a database schema we have to start adding all these extra keys, which get worse when you have a M:M (many-to-many) relationship! In this post I'd like to discuss how we normally model the M:1 (many-to-one) relationship and its pitfalls. More...

I've just released another sample for the Database Explorer API, this time using the built in features of Visual Studio 2008 (should work with VS 2005 with a download of the SDK). Visual Studio 2008 comes with a built in code generator known as T4. While the built in editing experience leaves a bit to be desired the free T4 editor by Clarius Consulting goes a long way to making it workable. If you download the editor, it indicates that the time bomb expires in Jan 1, 2008; this however isn't the case as they're still working on the next release.

This sample creates a fairly decent (not perfect by any means) set of entities which you can then easily drop on to a class designer and get a good feel for how your domain model looks. It has support for all but the recursive relationship type, which could be added as the API does support this. This sample is provided to show how much you can do with both T4 and the API in only a small amout of code. I've included a screen shot from the Adventure Works sample, which as you can see supports many relationship types. The sample download includes the results run against the AdventureWorks sample database.

To download the new sample visit the projects CodePlex page.

Remember to provide as much feed back on the samples and API as you can. Enjoy!

I'm in the process of getting up to speed with DSL tools, and while there's a whole lot of info out there, at the end of a long day I'd prefer to watch a video on a new subject rather than pour over the written docs, but trying to locate all the video's can be difficult, and even more if you want to view them in the right order. Well this is my effort to make things a little easier. Unless otherwise stated these videos's relate to Visual Studio 2008 edition of DSL tools, as I discover more DSL video's I'll add them to the list. If you know of a good one I've missed, leave a comment and I'll get it added. More...

For anyone that's read my blog entries you've seen me make mention to a framework that I've been working on. Well today marks the release of the first beta of part of that framework. The Database Explorer API, is a database schema API that is designed to make accessing database schema information easy and intuitive. More...

While I don't normally get involved with deploying web applications (we normally have 'people' for that) however I did have to deploy the application you're using now. Which brings me to the blog post I recently found which discusses the top 10 best practices for ASP.NET which I've linked to on my toolbox page. The main one that concerned was of concern to me was forgetting to switch off debug mode.

   1: <system.web>

   2:   <compilation debug="false" />

   3: </system.web>

This setting appears to have a large impact on production web sites so I'd check out the detail in the this post to get all the details. Also you might want to check the config files of the production apps you work on or blog if you host your own; lets just say we may need to update a few of ours!

I went along to the .NET developers association in Redmond on Monday where Ken Levy demonstrated the extensibility tools of VS.NET 2008 (which also relates to VS 2005 to some extent). It seems that MS have decided to offer the VS 2008 shell license free! If you're anyone who's looking at providing third party tools (related or not to Visual Studio) and need a good IDE host, you've just saved yourself a bucket load of time. Also, it runs in two modes, integrated (merges with your instance of VS 2008) or isolated where it acts as its own (customisable too I was led to believe) IDE without the need for VS 2008. More...

This article was originally published in the April 2003 issue of Visual Studio Magazine. Their online version has been archived, so it has been reproduced here. The articles code requires updating for .NET 2.0 which will be the subject of a future post.

ASP.NET has brought about a huge change in how we as developers think about designing a web site. We all know that ASP.NET now gives us the opportunity to create reusable custom controls which we can drag and drop onto our pages, which can even remember the information entered between postbacks, using the viewstate mechanism built into ASP.NET. This has proven to be a huge advantage in designing our pages, by allowing us to bring our more traditional programming model to the Web for the first time. ASP.NET also allows us to add these same controls dynamically at runtime. More...

In many application be they web or otherwise it can be important to secure parts of the configuration files incase the file is compromised by a hacker or you simply don't want anyone to know what the true values are. It turns out that this is fairly easy to do using the ASP.NET IIS Registration Tool (Aspnet_regiis.exe) (obvious right?). To illustrate the point I'll show you a typical config file and what's required to encrypt parts of it. More...