dotNetProfessional

Turn multiple rows into a single string in SQL Server

garrymc — Fri, 27 Jun 2008 22:56:56 -0700

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:

To produce a list you can use the following SQL:

DECLARE @result varchar(max)

SELECT    @result = coalesce(@result + ', ', '') + Name

FROM    Person.StateProvince

WHERE    Person.StateProvince.CountryRegionCode = @CountryRegionCode

RETURN @Result

View Plain | Copy To Clipboard

The way this works is the COALESCE function returns the first non-null expression. For the first record this would be the empty string + Name as @Result would be null. However, the interesting thing is that because we keep concatinating to @Result a single value is returned from the whole result set. The only issue is that for this to work we need a variable to keep storing the information in. So if you want to apply this against another table that supplies the filter, you'll have to create a function that returns the string. As this is a scalar function (ie doesn't return a result set) its use within another query is extremely easy. Assuming a function for the above is called dbo.fnRegionsToString we are left with:

SELECT Name AS Country, dbo.fnRegionsToString(CountryRegionCode) States

FROM
         Person.CountryRegion

View Plain | Copy To Clipboard

The whole script which you can test against the AdventureWorks database is:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID(N'[dbo].[fnRegionsToString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

    DROP FUNCTION dbo.fnRegionsToString

GO

CREATE FUNCTION dbo.fnRegionsToString

    @CountryRegionCode varchar(5)

RETURNS varchar(Max)

AS

BEGIN

    DECLARE @result varchar(max)

    SELECT    @result = coalesce(@result + ', ', '') + Name

    FROM    Person.StateProvince

    WHERE    Person.StateProvince.CountryRegionCode = @CountryRegionCode

    RETURN @Result

END

GO

SELECT Name AS Country, dbo.fnRegionsToString(CountryRegionCode) States

FROM         Person.CountryRegion

View Plain | Copy To Clipboard

This makes for a very clean solution to an otherwise painful problem. So the next time you need to turn a lot of records into a single one, check to see if this technique can help.

Modeling many-to-one (M:1) entity relationships Part II

garrymc — Wed, 25 Jun 2008 19:00:13 -0700

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.

Remembering the tree types of M:1 relationships discussed previously, lets see how we can treat them differently:

Reference: As a reference table will typically (should only have one significant column; which must be unique!) only have one column which we can map to as a string just like we did in the logical model. The effect is that instead of having OrderTypeID we have OrderTypeName, both values are unique and therefore provide a 'key' to the OrderType table. As there's nothing else of real interest in the OrderType table, returning only a single field and merging it into the main entity poses little issue but does provide a much more meaningful value than some arbitrary key. In many cases this can avoid a second call to a service or database as we already have the pertinent information.
Enum: An enum table is also much like the reference table, the only difference being that we can also record all the known values as an enum and tie this enum to the entity exactly like we did in the logical model. When saving the data however, we don't attempt to save the enums number value but its name. That is we send Processing not 1, as the number may have little connection to the back end database, and again we want to remove our system keys as much as possible. Therefore the save is exactly the same as it would be for a reference type, we just represent the 'string' value differently as we know what the allowable ones are.
Entity: The entity type is the most interesting of the types. If you look at the original model vs the one produced by Linq to SQL, you might be thinking, well its basically the same what's wrong with that? Well a few things actually which is the topic of the next section.

Modeling M:1 Entities

As suggested, modeling reference and enum types are fairly straightforward just requiring an extra join to get the necessary information (which is worth it as the information is useful!). However, I've indicated that the seemingly correct entity M:1 has some issues. The issues I see are:

The parent entity still has the child key which is a system key, and is most likely useless to a business person.
If you've adopted the Linq to SQL pattern you have the same issues as already discussed with sync issues etc.
If you want to create a new child entity and associate it to the parent you either have to create the entity first (thereby getting its key) or using the Linq to SQL model and attach a new child entity object; which should work fairly well.
Alternative designs may remove the foreign key from the parent and rely on the child telling the parent what the key is. The issue with this is that you must have a full entity as the child even if you are only concerned with the parent. Also adding the association requires the full entity which may not be available at the time of creating a new entity.

In the past I've tended to treat entity M:1 relationships as reference relationship types with a single key from the entity being used as its business key. This works fairly well, that is until you get an entity that has a business key that maps to more than a single column, which unfortunately happens quite often. In these occasions, I tended to use the entities GUID if it has one or revert to the system key (hey I need something!). As I didn't want the burden of having to retrieve an entire entity just so I know the keys.

So while having the entire entity would be nice and does better represent our original model it poses a number of limitations especially if you just want to associate a pre-existing child entity to a parent. What would be good is to have just the keys, but then be able to upscale to the full entity when the use case requires it. This is exactly what I propose as the solution to this issue. Instead of having a single value to represent a complex entities M:1 relationship we have what I'm calling a business key entity which only knows about the unique business key for that entity and it acts like a value type. The parent then has a relationship with this business key entity not the full entity. Initially I thought an interface could be used to avoid having a separate business entity but as it turns out you can't serialize a property that has an interface as its return type. That basically means in order to support polymorphism (ie the ability to upscale) we have to use implementation inheritance. The original model is demonstrated below using this new technique:

For those who prefer to look at code rather than diagrams, here's how the class for the OrderDetail class and Product business key classes look. The other classes follow a similar pattern, if you are keen to see how all the code looks, leave an email and I'll try bundle it all up:

   1: public partial class OrderDetailBusinessKey

   2: {

   3:     private System.Int32 _orderDetailID;

4:

   5:     //  Business key constructor

   6:     public OrderDetailBusinessKey(System.Int32 orderDetailID)

   7:     {

   8:         _orderDetailID = orderDetailID;

   9:     }

  10:     public OrderDetailBusinessKey(){}

11:

  12:     public System.Int32 OrderDetailID

  13:     {

  14:         get {return _orderDetailID;}

  15:         set {_orderDetailID = value; }

  16:     }

  17: }

18:

  19: [Serializable]

  20: public partial class OrderDetail

  21:     : OrderDetailBusinessKey

  22: {

  23:     #region Private Variables

  24:     private OrderBusinessKey _order;

  25:     private ProductBusinessKey _product;

  26:     private System.Int32 _quantity;

  27:     private System.Decimal _price;

  28:     private System.Decimal _total;

  29:     // Variables for relationships

  30:     #endregion

31:

  32:     #region Constructors

  33:     public OrderDetail()

  34:     {

  35:         this.Initialize();

  36:     }

37:

  38:     /// <summary>

  39:     /// This code initializes the domain value types for the generated properties

  40:     /// </summary>

  41:     protected virtual void Initialize()

  42:     {

  43:         _order = new bl.OrderBusinessKey();

  44:         _product = new bl.ProductBusinessKey();

45:

  46:         // Call the Initialize routine in the partial class

  47:         this.ExtendedInitialize();

  48:     }

  49:     #endregion

50:

  51:     #region Public Properties

52:

53:

  54:     public OrderBusinessKey Order

  55:     {

  56:         get { return _order; }

  57:         set { _order = value; }

  58:     }

59:

  60:     public ProductBusinessKey Product

  61:     {

  62:         get { return _product; }

  63:         set { _product = value; }

  64:     }

65:

  66:     public System.Int32 Quantity

  67:     {

  68:         get { return _quantity; }

  69:         set { _quantity = value; }

  70:     }

71:

  72:     public System.Decimal Price

  73:     {

  74:         get { return _price; }

  75:         set { _price = value; }

  76:     }

77:

  78:     public System.Decimal Total

  79:     {

  80:         get { return _total; }

  81:         set { _total = value; }

  82:     }

  83:     #endregion

  84: }

85:

  86: public partial class ProductBusinessKey

  87: {

  88:     private System.String _productName;

89:

  90:     //  Business key constructor

  91:     public ProductBusinessKey(System.String productName)

  92:     {

  93:         _productName = productName;

  94:     }

  95:     public ProductBusinessKey(){}

96:

  97:     public System.String ProductName

  98:     {

  99:         get {return _productName;}

 100:         set {_productName = value; }

 101:     }

 102: }

View Plain | Copy To Clipboard

At first glance this might seem overly complex, but if you think about what's happening its not. The perceived complexity comes from the fact that every 'entity' now has a base business key class. While I've shown them all on the diagram, in general they wouldn't need to be seen, just knowing that the property is type ProductBusinessKey would be enough without actually showing the relationship. However, looking at the model, we can see that all but the primary key IDs have been eliminated from our domain model and it looks surprisingly similar to what our original logical model looked like. This approach provides a number of benefits despite its slightly more complex model:

Flexible persistence: You can add either an existing entity to a parent via its business key or the entire entity. The code used to persist the relationship can simply check what its type is to determine if a full entity needs updating/saving or just an association is required based on the business key. Also you don't have the duplication of keys, so you can write Order.Status = Status.Shipped or even OrderDetail.Product.ProductName = "Product B" (assuming you are using the BusinessKey not the full entity) which seems very intuitive.
Eliminate foreign key IDs: Although its true that some entities don't have business or natural keys. In these situations, you're left with either using a GUID if available or the system primary key.
Entities are more useful: Your entities now having gotten rid of meaningless keys (from the business perspective) can do more without requesting more data from the server. Ie if you wanted to list the OrderDetails chances are you could get away with displaying OrderDetail.Product.ProductName to the user and not have to worry about retrieving more detail about a product unless specifically requested.
SOA friendly: This design is more friendly as the entities resemble documents more than they represent a table, as such they make for a much friendlier XML representation which is now human readable ie instead of seeing IDs for products you'll see the actual product names.

So what's the downside of the approach? From what I can see the main downside is the amount of effort required to make it all work. There are a lot more classes that need creating and you do have to do some extra joins in your selects too; however I believe it reduces complexity overall as the model is more natural and cleaner to work with. However, these issues can be mitigated via code generation, and the good news is that I'll be releasing something fairly soon (notice the vague reference) that implements this model. The sample above was completely code generated. If anyone is interested in being an alpha/beta tester, send me an email via the contacts page or leave a comment.

While this might look like the end of the discussion, there are still a few things that need addressing including inheritance and the concept of the business key as a value type. These will be covered in the last part of this series, so check back soon.

This discussion is to obtain feedback on how others feel about the approach I'm taking with my models and open a discussion on how best to model these M:1 relationships. I look forward to any feedback, positive or negative on the approach.

Modeling many-to-one (M:1) entity relationships Part I

garrymc — Sun, 22 Jun 2008 13:06:18 -0700

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.

The Problem

When designing a domain model (entity model) in UML its unlikely that you'd include foreign key relationships as part of your design as this is a database concept rather than a domain concept. If we take a very simple and arguably contrived example (its very basic just to highlight the concepts I want to discuss) of an Order it might look something like this (note I'm using the VS class designer which doesn't support multiplicities hence I have to use two associations to highlight relationships):

In this example we have three different types of M:1 relationships being modeled, which are:

Entity - The OrderDetail --> Product and Oder-->Customer are M:1 relationships which map to other entities within the business domain.
Reference - The Order.OrderType property is one that is restricted by a known set of values. However, this set of values can change over the life time of the application. That is its data driven and so the current values can't be coded directly within the application. Also a reference value is usually defined as a single field with no other business properties.
Enum - The Order.Status property is also restricted by a known set of values. However, unlike a reference type the values don't or can't change. These values typically drive logic which is set during design, adding a new value after deployment wouldn't add value as the application wouldn't know what to do with it.

Now that I've defined the different M:1 relationship types, they would typically be modeled in the database all the same way as foreign key constraints to their respective tables. The following diagram shows the ER (Entity Relationship) diagram of the physical database model.

So the next step in modeling our sample is to create the actual business entities that we can use in a real application, taking into account how our data is physically stored so that we can map our logical to the physical as easy as possible. This of course is where things typically start to deviate from our nice logical model. We have to take these foreign keys into consideration as the database needs them, so typically we end up with something more like the following figure than our original logical model. The following was created using Linq for SQL, which like most O/RMs (the Entity Framework will improve on this) will create something like the following:

You'll notice from this model that it more resembles the database model than it does our original logical model. We have an extra entity in the OrderType which we'd probably have in our logical too, to allow for maintenance but we didn't need to tie this object to our Order entity. The changes that were made to our original model can be summarized as:

Simple types are now complex types, namely instead of a simple enum or string we now have full entities with their associated complexities.
Our 'domain' entities now have system keys liberally scattered throughout to support persistence back to the database.

We also have the following issues as a result of this:

We expose a lot of internal information about our database when we expose foreign keys, exposing this data presents a security risk as a hacker may attempt to corrupt your data by altering the primary or foreign keys. This becomes a bigger issue when you expose your data via web services.
In the case of Linq to SQL we also have duplication of foreign key fields, namely we have both an OrderTypeID (foreign key field) and an object reference to the OrderType entity. This just looks plain ugly! Also lets say you want to change the OrderType to a different one, if you change the OrderTypeID from 1 to 2, and you did have an OrderType entity associated at the time, you'd be out of sync! The entity version of the OrderType wouldn't match the change you've just made. In the Linq to SQL architecture you're not even allowed to change the foreign key once its been set! You have to change the OrderType entity which will then update the Order.OrderTypeID. Which only goes to highlight the problems.

So now that I've expressed the issue and the potential problems with this type of design which is an all to common pattern, with the exception of the double foreign key reference that Linq to SQL uses . The question becomes why? The reasons as I see it are two fold:

Effort, it takes some effort to design more closely to the original logical model
Architectural/Technical in that we need those damn keys, so the issue is how do we make a nicer model without loosing the essential information we need?

The next part of the series will examine what we can do to fix this situation and bring our model back in line with what we originally intended.

Create a full entity class diagram with a T4 template

garrymc — Wed, 26 Mar 2008 05:42:02 -0700

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!

Learning DSL tools with Videos!

garrymc — Fri, 21 Mar 2008 08:35:20 -0700

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.

Presenter: Hilton Giesenow

How Do I: Create And Use T4 Templates?

Presenter: Dylan Miles

How Do I: Get Started with the Visual Studio Domain Specific Languages?

How Do I: Create and Customize My Domain Specific Language?

How Do I: Use Advanced Customization?

How Do I: Generate Code from my Domain Specific Language Using DSL Tools?

How Do I: Create a Multi Project Template?

For those interested in DSL tools and need a rich database schema API to go with it, I've developed the Database Explorer API which provides a rich model to access your database schemas. This API is part of the larger dnp.Framework which will also include a Domain Model API amongst other things. Register for the RSS feed to keep updated on any updates.

Database Explorer API beta released

garrymc — Mon, 17 Mar 2008 17:10:56 -0700

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.

The API has support for schemas, tables, views, indexes, constraints, relationships ie 1-1, 1-M, M-M, M-1 and recursion. While its original goal was as a replacement for the SchemaExplorer API provided with the Code Smith product, its uses are most definitely not limited to code generation. The API has no dependencies on any other libraries except the ones required to run your database. So if you're a user of MyGeneration, ArchAngel or some other code generation tool, or even if you want to use it in a windows form application you'll only have to reference the dll's to get started; oh and its free too!

The API is fairly complete but does lack support for stored procedures, functions, and retrieving contents of views. However, these are slated for a future release and any feed back on which are more important would be appreciated.

At present the API is in beta and only supports SQL Server 2005, however the provider model being used can easily be used to support other databases. If you are working with another database I'd be happy to work with you to get that database supported.

I've tried to supply a reasonable about of documentation and a few samples (which I'll blog about in more detail over the next week or so) to help get you up and running fast, because I know if it takes more than 10 minutes to get a feel for something, its probably too hard. For those not familiar with Code Smith I've just completed a sample using a console app so you can see it works as easy without Code Smith. The sample is the same as the one detailed in the API overview so check that out for more details.

As this is a beta I welcome all feed back, positive or negative (though keep it constructive) and I intend to keep releasing updates regularly. To keep updated, I'd recommend registering with the rss feed. The following links provide you with more information about the current release and what to expect in future releases.

dnp.Framework - Provides a summary of the framework and its components. You can also download the framework here.

Database Explorer API Overview - provides an overview of the API and a covers one of the samples within the API.

Database Explorer API - Provides more detailed information about the API including a class diagram. (work in progress)

Download: Database Explorer - Console Sample.zip (39.42 kb)

Did you forget something when you deployed your ASP.NET App?

garrymc — Sat, 15 Mar 2008 04:54:28 -0700

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>

View Plain | Copy To Clipboard

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!

Visual Studio Extensibility (VSX) gets serious!

garrymc — Thu, 13 Mar 2008 02:51:39 -0700

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.

If you're not so inclined to use the VS Shell, you may want to try adding your own custom extensions to VS to provide addition features MS haven't gotten around to yet. The API which used to be only available to partners (at a large cost - which is now free!) allows you to extend the IDE in just about any way you want. The VSX site provides links to numerous samples and they seem quite committed to building a large community around this technology, so I'd keep an eye on their Visual Studio Gallery where the community will post their extensions.

Other improvements seem to be in the area of DSLs (Domain Specific Languages) where they now provide DSL tools to build your own DSLs. This is an area I believe will become more important as people begin to realize that a lot of what we do is based on a known pattern, with customizations for a given problem.

I'll be looking into how DSLs can be leveraged with the framework I'm currently developing that code gens your stored procs, entities and services. Anyway for those who are interested in learning more about these new tools I'd recommend you visit the following sites:

http://msdn.com/vsx - The Visual Studio Extensibility home page

http://blogs.msdn.com/klevy - Ken Levy's blog

Also MS sponsored CoDe magazine for a special focus on VSX which allows you to download the full magazine which has a heap of VSX information. You can download the PDF here.

Oh should mention that the demo's are quite impressive, take a look at the World of War Craft editor (with its own language) using the VS Shell.