Florian Reischl

Domain Objects And Many To Many Relations

2011-09-28T10:14:00.000-07:00

Today I want to discuss different kinds of many to many relations and cases where it can make sense to transform them into different structures when they become loaded from a database into an object graph of a business layer. For sake of straightforwardness this post focuses on Microsoft Entity Framework and O/R-Mappers in general. Let me know, if one is interested in how to handle many to many relations in native ADO.NET.

This is the third post of a blog series about designing domain objects in a business layer and the second part that gives attention to transformation of table structures into object structures. The first post "Tables Are No Domain Objects" gave an introduction to this series and showed some reasons why it can make sense to abstract O/R-Mappers in layers above the data access layer (DAL). In the second part "Tables Are No Domain Objects: Table Relation Transformations Part 1" we discussed foreign key fields, aggregations and status objects.

Basics

A many to many relation is given when two objects are related to each other and each of them can be referenced to more than one object (rows in the database) on the other side.

An example for a many to many relation is the relation between articles and their categories. Each category can be related to many articles, like a category "food" that references apples, pies and meat. On the other hand, an article "apple" can be categorized as "food" and "healthy".

In an object model a many to many relation is exposed by two domain objects where each contains a collection of objects of objects of the other type. Since databases don't provide complex column types, like lists many to many relations are realized by putting an intermediate link table between the tables.

Simple Many To Many Relations

A simple many to many relation is given whenever the link table consists of nothing but the foreign keys which point to the rows of the two tables to be related to each other.

When working with native ADO.NET many to many relations are always a bit tricky but can, for sure, be handled. I'll focus on O/R-Mappers for now.

When working with a common O/R-Mapper simple many to many relations are usually automatically transformed by the mapper. The link table stays hidden inside of the mapper each of our two objects can provide a list of objects of the other type.


public partial class Article {
   public IList<Category> Categories { get; set; }
}

public partial class Category {
   public IList<Article> Articles { get; set; }
}

The ORM knows all values to be inserted into our link table and doesn't need to annoy clients of our business layer with this table. If you are at the beginning of a project and your O/R-Mapper does not support simple many to many relations, I'd suggest to consider another mapper.

Complex Many To Many Relations

A complex many to many relation is given when the link table contains any additional columns which are not the foreign keys of our domain objects base tables.

With this link table an O/R-Mapper like Entity Framework run into trouble. It is unable to fill our creation date column without an intermediate domain object that does nothing but hold the additional column. Our two domain objects will look like this.


public partial class Article {
   public List<ArticleCategory> ArticleCategories { get; set; }
}

public partial class Category {
   public List<ArticleCategory> ArticleCategories { get; set; }
}

This might be fine for EF but usually that's not how we want to work with our objects in the main part of our system. Often columns like a creation date are only used for support or reporting purposes and we don't want to think about the odd ArticleCategory object when adding new operations features.

Without some refining of our domain objects we will be forced to implement every access of an articles categories like this.


Article article = GetArticle();
var categories = from acl in article.ArticleCategories
                 select acl.Category;

// process the article and its categories

It is not only unnatural to need to always access the intermediate object to get what we are really looking for but also a causes a tight coupling between our domain objects and the underlying database table structure. Worst thing would be if we started up with a simple many to many relation between articles and categories and a new requirement causes the need of the creation date column - and the resulting ArticleCategory object. Without some architectural effort we might have to refactor larger parts of our existing source code. Luckily, there are a few things we can do.

The easiest way to hide the relation object is to define the ArticleCategories property as private and provide a few methods that give us the opportunity to directly work with the referenced entities.

public partial class Article {
   public IEnumerable<Category> GetCategories() {
      return ArticleCategories.Select(acl => acl.Category);
   }

   public void AddCategory(Category category) {
      ArticleCategory categoryLink = new ArticleCategory();
      categoryLink.CreationDate = DateTime.Now;
      categoryLink.Article = this;
      categoryLink.Category = category;
      ArticleCategories.Add(categoryLink);
   }

   public void RemoveCategory(Category category) {
      var categoryLink = ArticleCategories.Where(
                           item => category.Equals(item.Category)).FirstOrDefault();
      if (categoryLink != null)
         ArticleCategories.Remove(categoryLink);
   }
}
// =========================================
// sample usage
Article article = GetArticle();

var categories = article.GetCategories();
// process categories

article.AddCategory(GetCateory());

Apart from the fact that we provide a more natural access to our categories, this also causes an architecture that is robuster for possible future changes - like additional fields in our link table.

If we want to go one step further we can provide an even more sophisticated interface to access our (indirectly) referenced domain objects. Unfortunately we cannot use a simple List<T> and copy all categories into it because our ArticleCategories list would not become affected by any add/remove calls. This makes also impossible to use a simple LINQ query that transforms the ArticleCategory objects into categories.

However, what we can do is implement a custom IList<T> that transforms a list of objects of one type into other objects by utilizing a provided delegate. In our case we need to transform a list of ArticleCategory objects into categories.

The following snipped shows how such a list could work.

public class TransformationList<T, TResult> : IList<TResult> {
   private IList<T> _list;
   private Func<T, TResult> _transform;
   private Func<TResult, T> _factory;

   // Constructor that creates a read-only version of the list
   public TransformationList(IList<T> list, 
                             Func<T, TResult> transformation)
      : this(list, transformation, null) {
   }
   // Constructor that creates a writable version of the list
   public TransformationList(IList<T> list, 
                             Func<T, TResult> transformation, 
                             Func<TResult, T> factory) {
      _list = list;
      _transform = transformation;
      _factory = factory;
   }

   // Indexer access
   public TResult this[int index] {
      get { return _transform(_list[index]); }
      set {
         EnsureWritable();
         _list[index] = _factory(value);
      }
   }

   // Count property works like a proxy
   public int Count { get { return _list.Count; } }

   // The list is read-only if no factory method provided
   public bool IsReadOnly { get { return _factory != null; } }

   // Ensures that the list is writable and uses the factory method to create a new item
   public void Insert(int index, TResult item) {
      EnsureWritable();
      _list.Insert(index, _factory(item));
   }

   // Read-only method uses the transformation method
   public bool Contains(TResult item) {
      return _list.Where(i => item.Equals(_transform(i))).Any();
   }

   // ensure that the list is writable
   private void EnsureWritable() {
      if (IsReadOnly)
         throw new InvalidOperationException("List is read only");
   }

   // and so forth...
}

The second constructor, which gets a second delegate as factory method makes the list writable and enables us to add new objects from outside without knowing that another, hidden object becomes materialized inside of our transformation list.

This (reusable!) class makes us able to provide a our articles categories with a nice IList<Category> property.

public partial class Article {
   private IList<Category> _categories;

   public IList<Category> Categories {
      get {
         if (_categories == null)
            _categories = 
               new TransformationList<ArticleCategory, Category>(
                     ArticleCategories, 
                     (acl) => acl.Category,
                     (c) => AddCategory(c));
         return _categories;
      }
      set { _categories = value; }
   }

   public ArticleCategory AddCategory(Category category) {
      ArticleCategory acl = new ArticleCategory();
      acl.CreationDate = DateTime.Now;
      acl.Article = this;
      acl.Category = category;
      ArticleCategories.Add(acl);
      return acl;
   }
}
// =========================================
// sample usage
Article article = GetArticle();

foreach (var category in article.Categories) {
   // process categories
}

article.Categories.Add(GetCateory());

Conclusion

Simple many to many relations are usually easy to work with, but even if an O/R-Mapper shows some weakness in its mapping features, we are still able to provide a reasonable interface to clients of our business layer and its domain objects.

Outlook

In the next part of this series we will look at version controlled data, what challenges they can could cause and ways to get them handled.

Tables Are No Domain Objects: Table Relation Transformations Part 1

2011-09-06T12:41:00.000-07:00

This is the second part of a blog series 'Tables Are No Domain Objects'. In this post we will discuss where database relations are good candidates to be accessed in a different manner when they are represented by domain objects of our business layer.

The most obvious kind of a database table relation is a one (A) to many (B) relation where rows in table B hold a foreign key column that points to a unique key (usually the primary key) of table A. Most data access layers, based on an O/R-Mapper or custom mappings, do a good job to map this kind of database relations into objects, but there are some cases where it can make sense to transform those relations into a different structure or provide a different access than given by our database.

Foreign Key Fields And Transparent Database Relations

Before we step into more specific types of relations, there is one very basic thing where each of us should think about when starting to design a new business layer. In a database relations are always represented by foreign keys but when data are loaded into a object structure we can use object references, so we don't really need those foreign key fields as part of our objects. For instance, a sales order line object does not need to hold the ID of its parent sales order, it can hold a object reference of the sales order. One good reason to keep foreign key fields present our in domain objects is to have some additional logging and debugging information. However, we should never use those fields to implement any business logic on them, instead all business logic should always be implemented on the corresponding object references. (Very rare exceptions prove the rule though.)

This was already discussed in the previous blog post but should be recalled for sake of completeness. O/R-Mapper like Entity Framework or NHibernate provide a powerful query interface to access data, but using those queries in our business layer or presentation layers will cause a tight coupling between our source code and the database structure. Apart from other issues, discussed in the other post, queries like this can become a issue if we ever need to refactor our database structure or domain objects.


var orders = from o in efContext.Orders
             where o.CustomerId == currentCustomer.Id
             select o;

foreach (var order in orders) {
   // process order
}

Instead of this it is usually much safer to provide strong typed access methods out of our data access layer.


var orders = myDataContext.Orders.GetForCustomer(currentCustomer);

foreach (var order in orders) {
   // process order
}

Please read the previous post (Tables Are No Domain Objects Part 1) to see further issues, especially when using Entity Framework.

Aggregations

In general I'm not as restrictive as other architects, who say it is always a bad solution to access any related objects of a current object reference, but when it comes to aggregations it can sometimes be dangerous to be done from outside of the class that holds the objects to be aggregated.

One of the most common examples for a an aggregation that we should consider to encapsulate is when we have to calculate the price of a sales order that is based on the price of its line items.


SalesOrder order = GetOrder();
decimal orderPrice = 
   order.SalesOrderLines.Sum(line => line.ArticlePrice * line.ItemCount);

From the very beginning of a new system this could work pretty nice. The problem is, what if the calculation of the sales orders price ever changes? Salespeople are creative to find new ways to sell the companies products and usually it is only a matter of time when discount features become required. Discounts can be a special offer for specific articles or article categories, a graduated discount depending on the orders all round price or many other types. Now we can run into trouble if we do a outside calculation of a sales orders price. A better solution is to put the aggregation into the sales order class.


public partial class SalesOrder {
   public decimal GetPrice() {
      return SalesOrderLines.Sum(line => line.ArticlePrice * line.ItemCount);
   }
}

For now we only encapsulated the calculation that we have done from outside (what already avoids a duplication of logic to multiply with the sales lines item count) but when it comes to discounting we don't need to scan our whole source code to find all places where an orders price is calculated. We only have to adapt the body of our SalesOrder.GetPrice method and the rest of the system doesn't even notice the new calculation.

(The approach to do money calculations with decimal becomes part of a subsequent blog of this series.)

Status Objects

Status objects are special kinds of domain objects that describe the current status of their parent objects. They usually exist in a collaboration of their parent domain object and a description object that describes the current status.

In addition to providing a current status of their parent object, status objects are often used to log an operational history of an object since they are usually not deleted or updated after their first creation. Since each new status object can change the state of its parent they are often very important rules for the processing of an object.

As an example, say we have a parent SalesOrder domain object that can hold a list of SalesOrderStats objects where each of the status is described by a referenced SalesOrderStatusDescription object. Now what if we want to know if an orders current status is "Closed"? Without some design effort we would have to do something like this.


public partial class SalesOrderStatusDescription {
   // status description code constants
   public const string ClosedCode = "Closed";
   // ...
}
// =========================================
// sample usage
SalesOrder order = GetOrder();

bool isClosed = (from status in order.SalesOrderStatus
                 where status.OrderId = order.Id
                 orderby status.CreationDate descending
                 select status.SalesOrderStatusDescription.Code)
                 .First()
                 == SalesOrderStatusDescription.ClosedCode;

Apart from the fact that this causes is tight coupling between three different domain objects and their base tables, it would be crap if we always would have to do so in upper layers, just to get an objects current state.

A first thing we can do to prettify this is introduce an enum that either represents the possible codes of our SalesOrderStatusDescription objects or represents the possible foreign key values pointing to the SalesOrderStatusDescription primary keys. Since we would need to always load the descriptions to parse the code fields, we will do the foreign key solution, what causes a lower database utilization. Yes, I know we should try to never base any functionality on foreign key values but I tend to see this as one of the valid exceptions. Our descriptions IDs are usually immutable and it does not make a big difference if our source code is coupled to the Code column of the status description or its primary key.


public enum SalesOrderStatusCode : int {
   Created = 1,
   Approved = 2,
   Delivered = 3,
   Payed = 4,
   Closed = 5,
}

Next step we can do is add a new property to our sales order status that represents the value of the enum. Unfortunately Entity Framework does not provide native support for enums, so we need to do workaround by casting the foreign keys value.


public partial class SalesOrderStatus {
   public SalesOrderStatusCode Code {
      get { return (SalesOrderStatusCode)SalesOrderStatusDescriptionId; }
      set { SalesOrderStatusDescriptionId = (int)value; }
   }
}

Okay, now we are able to shorten the previous snippet a little bit, but without one more method we would still need to traverse the list of all existing status whenever we want to know the current one. Since the current status of an object is usually a widely needed information we should add a method to our sales order that encapsulates the traversing returns the code of the current status.


public partial class SalesOrder {
   public SalesOrderStatusCode GetCurrentStatusCode() {
      return (from status in SalesOrderStatus
              orderby status.CreationDate descending
              select status.Code)
              .First();
   }
}
// =========================================
// sample usage
SalesOrder order = GetOrder();
bool isClosed = order.GetCurrentStatusCode() == SalesOrderStatusCode.Closed;

This interface is much niftier and will make life much easier in client code.

As an optional, last step we could add a IsClosed method to our order. I use to do this only for the most important states of an object though.


public partial class SalesOrder {
   public bool IsClosed() {
      return GetCurrentStatusCode() == SalesOrderStatusCode.Closed;
   }
}
// =========================================
// sample usage
SalesOrder order = GetOrder();
bool isClosed = order.IsClosed();

Now our sales order provides a really handy interface that helps us to concentrate on other things when implementing features that need to work with the sales order status.

Last but not least, we should add a corresponding method set the new status of an order.


public partial class SalesOrder {
   public void SetStatus(SalesOrderStatusCode code) {
      SalesOrderStatus status = new SalesOrderStatus();
      status.CreationDate = DateTime.Now;
      status.Code = code;
      status.SalesOrder = this;
      SalesOrderStatus.Add(status);
   }
}

There is one line in this method that could cause problems. Setting the CreationDate with by using the local hosts time is only safe if we are sure that all client PCs are configured with the same time server, otherwise we can get deflections of the creation date of new status. Since the creation date is essential for these objects this could cause issues in production. One thing we can do is to use the time from a central server, like the database server, instead of trusting the clients.

Since there are usually much more places where we need to know the current status of an object than places where a status of an object becomes changed I tend to add less strong typed set methods like SetPayed().

As we have seen, due to their importance and the complicated access, status objects are usually good candidates to be handled in a very different way than they are stored in our database and some architectural effort to get them into a more fashionable, object-oriented structure can be a good investment.

Performance Tuning. Since this series concentrates on designing our domain objects I kept this until now, but our current solution requires to always retrieve all existing status objects from the database server, what causes a unneeded network traffic and database utilization. We should consider to add a method to our data access layer that loads only the current status, description ID or description code, instead of loading all status objects if not yet loaded anyway. This is another important reason why we should encapsulate the get method, since we need to change only one place.

Outlook

In the next post we will continue the discussion of table relation transformations.

We will have a look at many to many relations where we might need to handle the weakness of O/R-Mappers.

As last part of the discussion about table relation transformations we will have a look at versioned data.

Tables Are No Domain Objects Part 1

2011-09-01T14:12:00.000-07:00

This is the first part of a blog series where I want to discuss ways of how to design rich domain objects provided by the Business Logic Layer of a software system.

In most business applications we have to work with data stored in a database or provided by web services. For sake of straightforwardness, we will focus on database based storages. However, when working with a skinny data web service layer that provides its data almost (or completely) in a database table style, some of the things we'll discuss here might still fit.

Today where we are armed with shiny O/R-Mappers like NHibernate, Active Record or Microsoft Entity Framework. Some of those even provide a nice wizard that only needs a database connection to create a full set of domain objects. It seems like we can immediately start to create our first front end dialog/view/page and use those classes.

Cool, this "software architecture" and layering, people are talking about, is a wizard. Now we've got it. Thanks for participating the discussion, we are finished. Or, probably not... ;-)

Sounds funny? Unfortunately quite a few applications are based on this kind of "software architecture" and "business layers". When talking about smaller projects, this might even work, but if a system becomes more complex, say with with some thousands lines of code (how about millions of lines?) this approach can become an early dead approach. A table-like domain object is often not perfect structured when we need to implement our business logic or when displaying in a rich presentation layer.

Sure, we cannot cover everything that might need be considered when designing domain objects and structuring a business layer in a blog series, so we will concentrate on a few topics that I find more important and discuss some examples for each of them. At current stadium of software industry I don't even think that anybody is at a point that (s)he knows everything about architecting a business layer - or any other layer. If we look at the last 10 years of software industry we see way to many changes of minds. Every new pattern, technology or practice preaches itself to be the golden calf. I'd say, as long as there is not a full decade without big changes in architectural and technical approaches, we will not know that we reached the end - and I guess we are far away from the beginning of this decade.

With this knowledge about imperfection, we, still, should always do best to design a powerful and usable business layer. The business layer is the heart of all applications. It should encapsulate as much business logic as possible to avoid duplication of source code. After this encapsulation of logic, the highlight becomes to provide this logic by an easy to use interface for those (us?) who will consume it in a front end application like a windows UI or web UI, but also a web or windows services.

Here is a short overview of the topics we will cover in this series.

Before we dig deeper into designing of domain objects we will startup in this post with a look at O/R-Mappers, if actually used, and reasons why we should consider to keep them inaccessible to other layers than our Data Access Layer.

In a following post we will talk about Table Relations and Class References. We will look at database relations like aggregates or status objects that might be candidates to be represented in a different manner when they are loaded into a domain object hierarchy.

When talking about Field Aggregations I will try to show groups of database columns that can make sense to be arranged in referenced helper classes or structs instead of keeping them directly in our entities.

We will discuss Data Type Transformations and see some cases where it can be helpful when data, received from a database, are transformed into a different type or one database column can contain structural information.

In the last part of this series we will talk about Reusable Entities. There are some types of domain objects that appear in many projects. Some of them are good candidates to be implemented in base libraries reuse them in other projects, other types often appearing entities are harder or (almost) impossible to be reused.

How to Safely Use an O/R-Mapper

I know, this topic is not directly related to designing domain objects. However, since O/R-Mapper are often used as central component to access an underlying database this topic makes much sense to me to start with.

We should always consider to abstract the native instance of the O/R-Mapper and I will try to show a few reasons here. First thing, when talking about designing domain objects, is we might run into architectural restrictions caused by restrictions of the ORM. If we directly use the mapper class in our whole system might not be able to design our objects as we would like to.

Apart from the architectural reasons we should look a little closer at some other reasons why we should consider to avoid being tightly coupled with the O/R-Mapper.

(Examples and issues here are based on Microsoft Entity Framework. Some of them will also apply to other ORMs and others might not.)

Let's do a jump start with a very simple domain model that contains only one entity SalesOrder.

Here is a very simple example to use Microsoft Entity Framework with this domain model that already contains a few pitfalls.


using (var ctx = new Model1Container()) {
   DateTime since = DateTime.Today.AddMonths(-1);
   var orders = from o in ctx.SalesOrders
                where o.CreationDate >= since
                select o;

   foreach (var order in orders) {
      // set approved
   }

   // do something else

   foreach (var order in orders) {
      // create invoices
   }
}

The worst issue of this piece of code is one that is especially related to the architecture of Entity Framework. The used LINQ query to get sales orders does actually not return a real result of objects, instead it only returns an instance of an EF implementation of an IQueriable<SalesOrder>. This IQueriable can be seen as an object version of a SQL statement, whenever we start to traverse through the result we will fire another database query. This causes unnecessary database load and (worse) can cause different results for each loop. If a new order becomes created between the first and the second foreach-loop, we will get this order in our second loop and the order becomes invoiced before it was approved. A simple solution is to put the LINQ query into braces and call the IEnumerable<T>.ToList() extension method. This copies the results into a List<T> and all list operations become offline from now. Problem is, if we ever forget the call of ToList() we will again run into this trap.

A much safer way to use an O/R-Mapper is to create an own data storage class that wraps the EF container and provide custom methods for the needed data. At this point we stand on a crossroad where we have to decide between implementing a Gateway (Martin Fowler, Patterns of Enterprise Application Architecture) and provide all data access methods directly from our data storage class or implementing Repositories (Martin Fowler, PoEAA) for each of our domain object types. When working in smaller projects a Gateway is usually the better solution because it is easier to implement. Drawback of this pattern is, a Gateway class can become large when there are dozens or hundreds of access methods. The Repository approach needs more effort to be set up, since we need one for each type of domain object but keeps classes more well-arranged. Due to our large domain model I decided to use a Repository.


// Custom data store class that wraps the EF container
public class DataStore : IDisposable {
   private Model1Container _efContainer;
   private SalesOrderRepository _salesOrders;
      
   public DataStore() {
      _efContainer = new Model1Container();
   }

   public SalesOrderRepository SalesOrders {
      get { return _salesOrders 
                   ?? (_salesOrders = new SalesOrderRepository(_efContainer)); }
   }

   public void Dispose() {
      _efContainer.Dispose();
   }
}
// ========================================================================
// custom repository to provide access to sales orders
class SalesOrderRepository {
   Model1Container _efContainer;

   internal SalesOrderRepository(Model1Container efContainer) {
      _efContainer = efContainer;
   }

   public IEnumerable<SalesOrder> GetCreatedSince(DateTime date) {
      return (from o in _efContainer.SalesOrders
               where o.CreationDate >= date
               select o).ToList();
   }
}
// ========================================================================
// ========================================================================
// sample usage
using (var data = new DataStore()) {
   DateTime since = DateTime.Today.AddMonths(-1);
   var orders = data.SalesOrders.GetCreatedSince(since);

   foreach (var order in orders) {
      // set approved
   }

   // will always be the same results
   foreach (var order in orders) {
      // create invoices
   }
}

Now we need to remember only once that we need to add this odd ToList() method to avoid the previously described issues. Unfortunately this was only one, EF related, issue of directly accessing a O/R-Mapper from higher layers.

Another issue is the SalesOrder.IsCanceled property. In most parts of our software we might not want/need to work with canceled orders. Alike fields in other objects could be IsActive, IsDeleted and so forth. By encapsulating our O/R-Mapper we only need to change a few access methods to eliminate unwanted access to canceled orders. Sure, there are still parts of the system that might want to work with those orders but we should consider to provide them by more descriptive methods like GetCreatedWithCanceledSince or simply GetCanceledSince, depending on what is required.

A non-abstracted O/R-Mapper access does usually also mean a tight coupling between our database and source code. If we ever need to change the structure of tables we can only hope that our ORM is able to handle the new structure without changing the domain model and, if it is able to handle the new mapping, we can hope that this will still give us the advantage that we aimed with our database changes. There can be database changes that will most likely make it impossible for most O/R-Mappers to stay able to handle them. Say we need to introduce some EAV/CR tables to move some rarely needed columns out of our main tables. When using a custom data store class we can relatively easy adopt an internal hybrid of keep using the ORM while doing other mappings with native ADO.NET features.

What if not all data we need to work with come from our own database? I've just seen a thread MSDN Software Architecture Forums "Centralizing Duplicate Functionality and Data", where employees data had to be taken from one single source for all software systems of the company. Most O/R-Mapper support only one database but we can hold several different versions of them when working with a wrapping data storage class. Even if some of the data we work with are not provided by a database, say by a web service, we can still provide one homogeneous source of our data to the other layers.

As a last reason for now why to abstract an O/R-Mapper we should keep the possibility of horizontal scaling in mind. With one database server we have only one option to get better performance from it, buying a bigger box. Though, there is a point where vertical scaling reaches its end. Most software systems consist of say 80% read and only 20% write operations. With a strong data access layer we are able to set up several read-only accessed database servers to handle all the read operations while all write operations are directed to the master server.

There are still more reasons why we should consider to keep our O/R-Mapper inaccessible for main parts of the system but this might become part of another blog entry.

Upcoming Posts

I will add a link to all related posts here.

Tables Are No Domain Objects Part 2: Table Relation Transformations Part 1
Tables Are No Domain Objects Part 3: Domain Objects And Many To Many Relations
Tables are no Domain Objects Part 4: Field Aggregations
Tables are no Domain Objects Part 5: Data Type Transformations
Tables are no Domain Objects Part 6: Reusable Entities

Streaming SQL Server VARBINARY(MAX) In Chunks With ADO.NET

2011-08-29T13:00:00.000-07:00

A few years ago it was unthinkable to store BLOB data like large documents directly inside of a database. The most common way to store those large objects in combination with a database was to save the data to the file system and just store the UNC path in our database. Today storing BLOB data directly inside of a database became a common requirement in many projects. This new way to save binary data brings up a some advantages and drawbacks to think about.

The probably most important advantage is security. If saving BLOB data in file system we not only have to manage security on database level but also restrict access to the file system, to avoid unauthorized access. Another advantage is integrity. A UNC file path, stored in a database becomes easily invalid if someone delete the file. This can happen by maleware attacks but also by bugs in a software that changes one side but not the other one. A file name doesn't provide any way to enforce referential integrity. Sometimes it is tricky to keep things synchronized, especially when working with transactions.

On the other hand there is especially one drawback, when storing BLOBs inside of the database. If data are really large, say larger than 10MB or 100MB, it becomes tricky to save and read them without allocating huge amounts of data on server and client side. Where smaller data can easily be pushed or pulled in one chunk we don't want the client or server to handle 100MB as one big bunch of memory.

Missing Class in ADO.NET Framework?

On SQL Server 2008 when working with VARBINARY(MAX) based on FILESTREAM we can use the ADO.NET SqlFileStream class to stream data from and to SQL Server. Unfortunately this class does not work if data are stored inside of a VARBINARY(MAX) column and not everyone wants to activate FILESTREAM - out of other reasons because of the integrity problem.

SqlBinaryData Class

To become able to stream data stored in a VARBINARY(MAX) column, I wrote a helper class called SqlBinaryData that provides a simple but powerful interface.


public class SqlBinaryData : IBinaryData {

   //...

   

   /// <summary>

   /// Creates a new readable instance of a <see cref="Stream"/> for the BLOB column

   /// </summary>

   /// <returns>A readable stream.</returns>

   public Stream OpenRead() {

      return new SqlBinaryReader( // ...

   }



   /// <summary>

   /// Creates a new writable instance of a <see cref="Stream"/> for the BLOB column

   /// </summary>

   /// <returns>A writable stream.</returns>

   public Stream OpenWrite(bool append) {

      return new SqlBinaryWriter( // ...

   }



   // ...

}

Since the class works with four additional internal classes (about 700 lines of code) I will only focus on the most important parts here. You can find a ZIP archive containing the whole source code and my unit tests downloadable at the end of this post.

The OpenRead method creates an instance of an internal SqlBinaryReader class that implements a readable version of a System.IO.Stream and returns it to the consumer. The OpenWrite method creates an instance of an internal SqlBinaryWriter class that implements a writable version of System.IO.Stream and returns it. The other two classes, SqlBinaryInfo and SqlBinaryMetaData are primary used to provide database connections, handle possible SQL injections and provide metadata.

Metadata Evaluation And Caching

Metadata, like primary key column and table names are established from SQL Server when first needed. The first time when this data is needed is when a stream becomes read or written. Until this there will be no database connection opened to avoid unnecessary resource allocation and database utilization.

All metadata are cached for the current application domain. If an instance of a SqlBinaryData needs database metadata that have already been established for the same table, binary column and connection string this information will be reused from an internal cache. If metadata for an unknown binary column is requested from two threads it is possible that one of the threads becomes suspended until metadata are allocated by the second thread. This will likely not cause any problems but should be known.

If a database schema might change over the life time of a process and the primary key column becomes changed by name or type it is possible to clear the internal cache by calling the static method SqlBinaryData.ClearMetaDataCache(). A reading or writing stream that is already active while the cache becomes cleared will keep working with its previously requested metadata what might cause an error, any new creation of a stream, even from an already existing SqlBinaryData instance causes a reload of metadata from database.

Metadata are allocated by utilizing SQL Server INFORMATION_SCHEMA views, so should be accessible with most common database user rights.

Table and schema name are taken, and so validated, from INFORMATION_SCHEMA.TABLES view.


-- If table schema is specified

SELECT QUOTENAME(TABLE_NAME), QUOTENAME(TABLE_SCHEMA), TABLE_SCHEMA

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = @tableName

   AND TABLE_SCHEMA = @schemaName



-- If table schema is not specified

SELECT QUOTENAME(TABLE_NAME), QUOTENAME(TABLE_SCHEMA), TABLE_SCHEMA

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = @tableName

   AND TABLE_SCHEMA = OBJECT_SCHEMA_NAME(OBJECT_ID(@tableName))

Primary key column information is taken from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view.


SELECT QUOTENAME(COLUMN_NAME)

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = @schemaName

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(CONSTRAINT_SCHEMA)

         + '.' + QUOTENAME(CONSTRAINT_NAME))

      ,'IsPrimaryKey') = 1

If no primary key was found an exception will be thrown. If the primary key consists of more than one column an exception will be thrown since this version of SqlBinaryData class does not support composite primary keys.

Binary column information is taken from INFORMATION_SCHEMA.COLUMNS view.


SELECT QUOTENAME(COLUMN_NAME), DATA_TYPE, CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tableName

   AND TABLE_SCHEMA = @schemaName

   AND COLUMN_NAME = @columnName

If the returned column is not of type VARBINARY(MAX) an exception will be thrown.

SQL Injection

To avoid possibility of SQL injection all provided table and column names are quoted by utilizing the SQL Server QUOTENAME function, as shown above. All values, like the value of the primary key to stream binary data from or to, are passed to SQL Server as SqlParameter objects to avoid SQL injection and let SQL Server reuse cached execution plans.

SqlBinaryReader Class

Internal class that is responsible for streaming read access to BLOB. As already noted, it implements a System.IO.Stream to provide a well known standard behavior.


class SqlBinaryReader : Stream { //...

The most important public method the Read method, that copies data into a specified array:


public override int Read(byte[] buffer, int offset, int count) {

   // first read

   if (_buffer == null)

      ReadChunk();



   int done = 0;



   while (count != done) {

      // read buffered data into provided buffer

      done += ReadInternal(buffer, offset + done, count - done);

      // end of DB data reached

      if (_buffer.Length < _info.BufferSize)

         break;

      // read next chunk from database if needed

      if (done < count)

         ReadChunk();

   }



   return done;

}

The called method ReadInternal gets data out of the internal buffer into the specified one. The ReadChunk method handles the database access and is shown here:


private void ReadChunk() {

   SqlBinaryMetaData metaData = GetMetaData();

   // create an internal database connection if not yet available

   if (_cn == null)

      _cn = _info.CreateConnection(GetMetaData().ConnectionString);



   // create an internal data reader

   if (_reader == null) {

      string sql =

         string.Format("SELECT {0} FROM {1} WHERE {2} = @pk",

                        metaData.BinaryColumn,

                        metaData.QualifiedTableName,

                        metaData.PkColumn);

      using (var cmd = new SqlCommand(sql, _cn)) {

         cmd.Parameters.Add(_info.CreatePkParam());

         // open the reader with sequencial access behavior to enable 

         // streaming data from database

         _reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

         _reader.Read();

      }

   }



   int read = (int)_reader.GetBytes(0, _position, _buffer, 0, _buffer.Length);

   if (read != _buffer.Length)

      Array.Resize(ref _buffer, read);

   _offset = 0;

}

The internal instance of an SqlDataReader is created with option CommandBehavior.SequentialAccess what causes ADO.NET to stream data out of SQL Server instead of pulling all data in one chunk. The reader and its parent connection are cleared from memory when the stream instance becomes disposed.

Data will always be streamed with the specified buffer size, even if the caller requests a larger count of bytes in one call. This is to avoid a too high memory utilization on server side. So it is still possible to get 10MB of data in one single call of Read method, even if this might cause 10 database calls if buffer size is specified for 1MB.

SqlBinaryWriter Class

Internal class that is responsible for streaming write access to a VARBINARY(MAX) column of a tables row. As well as the SqlBinaryReader it implements a System.IO.Stream.


class SqlBinaryWriter : Stream {

The two important public methods of the write stream are Write and Flush method.

The Write method streams the provided data in chunks into the internal buffer. Whenever the internal buffer is full, it calls the Flush method which is responsible to writing the current chunk of data to the server.


public override void Write(byte[] buffer, int offset, int count) {

   if (_failedState)

      throw new InvalidOperationException("Stream is in a failed state");



   int done = 0;

   while (done != count) {

      int chunk = Math.Min(_internalBuffer.Length - _internalOffset, count - done);

      // push a chunk of bytes into the internal buffer

      Array.Copy(buffer, offset + done, _internalBuffer, _internalOffset, chunk);

      _internalOffset += chunk;

      // if internal buffer is full, flush to database

      if (_internalOffset == _internalBuffer.Length)

         Flush();

      done += chunk;

   }

}

To avoid unneeded memory copying on server side new data are appended to the database field by using the VARBINARY(MAX).WRITE method (see UPDATE (Transact SQL)) which performs a partial update of existing data instead of a complete reallocation of all data.


public override void Flush() {

   if (_internalOffset == 0)

      return;

   if (_failedState)

      return;



   SqlBinaryMetaData metaData = GetMetaData();



   using (SqlConnection cn = _info.CreateConnection(metaData.ConnectionString))

   using (var tran = cn.BeginTransaction()) {

      try {

         // handle NULL value and "append" configuration

         PrepareValue(cn, tran);

         // UPDATE SchemaName.TableName 

         // SET BinaryColumn.WRITE(@buffer, @offset, @count) 

         // WHERE PkColumn = @pk

         string sql =

            string.Format("UPDATE {0} SET {1}.WRITE(@buffer, @offset, @count) WHERE {2} = @pk",

                          metaData.QualifiedTableName,

                          metaData.BinaryColumn,

                          metaData.PkColumn);

         using (var cmd = new SqlCommand(sql, cn)) {

            cmd.Transaction = tran;



            var bufferParam = cmd.Parameters.Add("@buffer", SqlDbType.VarBinary, _info.BufferSize);

            var offsetParam = cmd.Parameters.Add("@offset", SqlDbType.Int);

            var countParam = cmd.Parameters.Add("@count", SqlDbType.Int);

            cmd.Parameters.Add(_info.CreatePkParam());



            byte[] buffer;

            if (_internalOffset == _internalBuffer.Length)

               buffer = _internalBuffer;

            else {

               // avoid bumping not needed data over network

               buffer = new byte[_internalOffset];

               Array.Copy(_internalBuffer, buffer, _internalOffset);

            }



            bufferParam.Value = buffer;

            // VARBINARY(MAX).WRITE works with a zero based index

            offsetParam.Value = _position;

            countParam.Value = _internalOffset;

            // write chunk

            int affected = cmd.ExecuteNonQuery();

            _info.AssertOneRowAffected(affected);

            _position += _internalOffset;

            _internalOffset = 0;

         }

         tran.Commit();

      }

      catch {

         _failedState = true;

         tran.Rollback();

         throw;

      }

   }

}

The PrepareValue method, called from Flush handles the preparation of the database field for the first call. If the stream was created with option "append" false it resets any existing data to initial value "0x". If the stream was created with "append" true it determines the current length of the data in the binary field or sets it to "0x", if its value is currently NULL.

How To Use

Here is an example that shows how to use the class to streaming write and read data.


SqlBinaryData data = 

   SqlBinaryData.CreateLongPrimaryKey(ConnectionString, "TestBlob", "Data", 3L, 5);



byte[] expected = Guid.NewGuid().ToByteArray();

using (var writer = data.OpenWrite(false)) {

   writer.Write(expected, 0, 4);

   writer.Write(expected, 4, expected.Length - 4);

}



byte[] actual = new byte[expected.Length];

using (var reader = data.OpenRead()) {

   reader.Read(actual, 0, 3);

   reader.Read(actual, 3, actual.Length - 3);

}



Assert.IsTrue(expected.SequenceEqual(actual));

To create a new instance of a streaming providing SqlBinaryData class you can use the constructor. The parameter pkParam is used as specification of the primary key column.


public SqlBinaryData(string connectionString, string tableName, string tableSchema,

                     string binaryColumn, SqlParameter pkParam, object pkValue, 

                     int bufferSize);

In case of an INT, BIGINT or UNIQUEIDENTIFIER primary key column, you can also use one of the static factory methods of SqlBinaryData (each one with and without specifying a table schema name):


public static SqlBinaryData CreateIntPrimaryKey(string connectionString, 

      string tableName, string tableSchema, string binaryColumn, int pkValue, int bufferSize);



public static SqlBinaryData CreateIntPrimaryKey(string connectionString, 

      string tableName, string binaryColumn, int pkValue, int bufferSize);



public static SqlBinaryData CreateLongPrimaryKey(string connectionString, 

      string tableName, string tableSchema, string binaryColumn, long pkValue, int bufferSize);



public static SqlBinaryData CreateLongPrimaryKey(string connectionString, 

      string tableName, string binaryColumn, long pkValue, int bufferSize);



public static SqlBinaryData CreateGuidPrimaryKey(string connectionString, 

      string tableName, string binaryColumn, Guid pkValue, int bufferSize);



public static SqlBinaryData CreateGuidPrimaryKey(string connectionString, 

      string tableName, string tableSchema, string binaryColumn, Guid pkValue, int bufferSize);

Transactions

Since the classes create their database connections inside of the class you cannot directly use a SqlTransaction to scope your DML operations. However, since the ADO.NET data provider for SQL Server supports transaction scoping it is still possible to ensure an isolated processing by using the System.Transactions.TransactionScope.


using (TransactionScope tran = new TransactionScope()) {

   using (var cn = CreateConnection()) {

      // do stuff

   }



   var binaryData = CreateBinary(10L, 16);

   using (var stream = binaryData.OpenWrite(false)) {

      byte[] toWrite = Guid.NewGuid().ToByteArray();

      stream.Write(toWrite, 0, toWrite.Length);

   }



   using (var cn = CreateConnection()) {

      // do more stuff

   }

}

Server Side Resource Allocation

Since we can specify the exact buffer size to work with and chunks are always taken in this size we know that we are fine on client side. The other side to look at is what resources are allocated on server side. We will do this by imitating the classes behavior in SQL Server Management studio and checking results in SQL Server Profiler.

Here is the setup up for the following tests.


SET NOCOUNT ON;



IF (OBJECT_ID('TestBlob') IS NOT NULL)

   DROP TABLE TestBlob;



CREATE TABLE TestBlob (

   Id INT NOT NULL PRIMARY KEY CLUSTERED

   ,Data VARBINARY(MAX)

);



INSERT INTO TestBlob VALUES (1, 0x);

INSERT INTO TestBlob VALUES (2, 0x);

GO

First, let's UPDATE a row, with 50MB of binary data in one single batch.


-- one big update with 50MB of data

DECLARE @data VARBINARY(MAX) = 

   CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARBINARY(MAX), NEWID()), 65536 * 50));



UPDATE TestBlob SET Data = @data WHERE Id = 1;

A look into SQL Server Profiler shows, as expected, a huge resource allocation.

Now, let's get the same amount of data into SQL Server by using the WRITE method and sending in 10 chunks of 5MB. To keep the test valid we have to remember the current position to write at. To keep this information available over the scope of a batch we can use the the CONTEXT_INFO() which allows to store up to 128 bytes of custom information for the current session.


SET CONTEXT_INFO 0x; -- clear the context

GO

-- 10 updates, each with 5MB

DECLARE @offset INT = 

   ISNULL(CONVERT(INT, SUBSTRING(CONTEXT_INFO(), 1, 4)), 0);

--PRINT @offset;

DECLARE @data VARBINARY(MAX) = 

   CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARBINARY(MAX), NEWID()), 65536 * 5));

DECLARE @data_length INT = DATALENGTH(@data)



UPDATE TestBlob SET Data.WRITE(@data, @offset, @data_length) WHERE Id = 2;

SET @offset += @data_length;



SET CONTEXT_INFO @offset;

GO 10 -- count of batch execution loops

Another look into Profiler shows, we get 10 statements with a really nice resource allocation.

Unfortunately I cannot provide a valid test result for read operations using the SqlDataReader.GetBytes method at the moment. This is caused by an issue of SQL Server Profiler when trying to trace BLOB data (see MS Connect MSFT:EDW - Profiler displays "Trace Skipped Records" for large (>530Kb) batch). I don't want to show test results with too small binary data since measuring inaccuracy might be too high. If you can tell me a way of how to get valid test results for this I would be happy if you let me know.

Restrictions

The current version of this class supports only single column primary keys. An exception will be thrown if a primary key consists of more than one column. Let me know, if there is a need of a version that supports composite primary keys.

It is required that the table containing the binary data has a primary key, the class doesn't work with heap tables. An exception is thrown if an accessed table does not have a primary key.

The class supports only columns from type VARBINARY(MAX). If there is a need for a VARCHAR(MAX)/NVARCHAR(MAX) based version, let me know.

The class does not handle any possible concurrent client sessions to avoid unwanted database locks. If data, accessed by a current instance of a read or write stream become modified by another user, the processed data will become most likely corrupted on client or even server side. To secure write operations use a TransactionScope surrounding the writing stream.

IBinaryData Interface

The SqlBinaryData class implements an interface called IBinaryData. This interface is not required for using this class, but I use it for unit testing purposes in business layers of a project where it allows me to work with files of a directory instead of data from a database. Feel free to remove it.

Attachments

Here you can find ZIP archive containing all classes of SqlBinaryData and a NUnit based test class.

The sources SqlBinaryData.zip
The unit tests SqlBinaryDataTest.cs
The SQL script to do the server side memory allocation tests SqlBinaryData_DbResourceAllocationTest.sql

ADO.NET Connection Pooling, Internals and Issues

2011-08-16T07:16:00.000-07:00

Description

The creation of a new database connection is a relatively expensive process on server side. The server needs to allocate new memory, create a new session, allocate all needed client information, authenticate the client and so on. On the other side, it does not make sense to keep keep connections open for the whole lifetime of a application, since this causes a higher session management overhead on server side due to many concurrent connections.

To handle those two issues, ADO.NET uses connection pooling. This means, the well known SqlConnection object from .NET framework is just a wrapper for the real, internal database connection. Whenever a new SqlConnection becomes opened it asks the connection pool for an existing, and currently not used, internal connection. If an existing, free connection is available ADO.NET wont create a new database connection but reuse the existing one. As soon as the connection is not needed any more it becomes sent back into the connection pool. If a connection is not used for a longer time it becomes automatically closed by the ADO.NET connection pooler, to release resources on SQL Server. To determine if an internal connection can be used for a current request by a SqlConnection, ADO.NET compares several (not all) connection string information. This includes authentication information, database name, transaction context enlisting configuration and so forth.

This blog post focuses on ADO.NET SqlClient and SQL Server. Some of the information might be different when working with other database servers or other data providers.

Myth Close and Dispose

Many books and web resources preach that it is important to always Close and dispose a connection, to ensure that the internal connection will be sent back to the connection pool. Until .NET 3.5 even MSDN stated this, but this became fixed version 4.0. Both ways, calling the Close method or by disposing the SqlConnection will actually send the connection back to the pool. Since the using-directive provides a language integrated way to dispose a component as soon as it goes out of scope, I usually prefer this over calling the Close method in a try-catch-finally block.

Here's a little sample that shows that both ways cause a correct connection pooling:

string cnStr = "server=(local);database=Sandbox;trusted_connection=sspi;";

string sql = "SELECT connect_time FROM sys.dm_exec_connections WHERE session_id = @@SPID";

SqlConnection cn1 = new SqlConnection(cnStr);



// close the connection but don't dispose it

cn1.Open();

using (SqlCommand cmd = new SqlCommand(sql, cn1)) {

   DateTime loginTime = (DateTime)cmd.ExecuteScalar();

   Console.WriteLine("New connection: {0}", loginTime.ToString("HH:mm:ss.fff"));

}

cn1.Close();



// dispose but no close

Thread.Sleep(500);

using (SqlConnection cn = new SqlConnection(cnStr)) {

   cn.Open();

   using (SqlCommand cmd = new SqlCommand(sql, cn)) {

      DateTime loginTime = (DateTime)cmd.ExecuteScalar();

      Console.WriteLine("After Close only: {0}", loginTime.ToString("HH:mm:ss.fff"));

   }

}



// result afer dispose but no close

Thread.Sleep(500);

using (SqlConnection cn = new SqlConnection(cnStr)) {

   cn.Open();

   using (SqlCommand cmd = new SqlCommand(sql, cn)) {

      DateTime loginTime = (DateTime)cmd.ExecuteScalar();

      Console.WriteLine("After disposing only: {0}", loginTime.ToString("HH:mm:ss.fff"));

   }

}



cn1.Dispose();

Console.ReadLine();

By returning the connect_time column from sys.dm_exec_connections for our current session id we retrieve the creation time of the connection from SQL Server.

As we see, in all cases we reuse the same, internal connection to SQL Server.

How a Pooled Connection becomes Reset

Since ADO.NET does not know what happened in a previous session of a pooled connection, when it is returned to a new instance of a SqlConnection, it has to reset the connection before it can be safely reused for another session. When resetting a connection all created temporary objects become dropped, all previously allocated resources are freed and the initially connected database will be set - if it was changed in the previous session. This resetting is done by calling the SQL Server procedure sp_reset_connection.

This alludes some issues. The ADO.NET connection pooler does not reset the connection when it is sent back to the pool, but when it is returned to a new opening SqlConnection, as shown in the next topics.

Temporary Tables and Named Constraints

When working with temporary tables in SQL Server, there is still an issue, names of temporary tables only need to be unique within the current connection, but names of constraints need to be unique for all current connections. Since SQL Server 2005 and following versions it is possible to define constraints without specifying a name for them. Previous versions did not support this feature and the syntax to specify a name for a table constraint is, for sure, still valid. When executing the following SQL statement in two concurrent connections you get an error message:


CREATE TABLE #t1 (

   Id INT NOT NULL CONSTRAINT PK_TEMP PRIMARY KEY CLUSTERED

)

Only the first connection is able to create the temp table, the second connection gets the following error:

Msg 2714, Level 16, State 5, Line 1

There is already an object named 'PK_TEMP' in the database.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

This also affects ADO.NET connection pooling. Say, we've got two different processes, the first creates a new entry in a database and the second one listens for those entries to process them. When the first process finishes its work without an explicit DROP of the temporary table, the connection stays open and the temporary table will stay in database until the connection is reused. If the second process tries to create the same temporary table it will receive the same exception.

Global Temporary Tables

Same problem as described for constraints of usual temporary tables applies to global temporary tables. Their name needs to be unique over all concurrent SQL Server connections. If one process creates a global temp table and does not explicitly drop it, when closing/disposing the SqlConnection, the table will stay on the server until the current process opens another connection to this server (and database). This behavior can cause confusing errors in a production system.

Release of Temporary Allocated Data

For sure, since temporary tables stay available until a connection becomes reused, this also implies that all data, stored in those tables, stay allocated until the process exists or reuses the connection.

If we have a process that creates a temporary table and fills it with hundreds of thousands of rows those data will stay in tempdb for probably longer than expected.

One way to ensure all allocated resources and temp tables become cleaned as soon as the connection is closed would be to deactivate connection pooling. However, we should always try to avoid this. The cleanest way to avoid all previously described issues is to always, explicitly drop temporary objects.

Pooled Connection became Invalid

If an connection becomes invalid while it is in the connection pool, it will still be returned for next usage. This could be caused by network problems, a restart of the SQL Server service, a manual KILL of the connection on database side or several other reasons.

Here is a little example that can be debugged to show this:


string cnStr = "server=(local);database=Sandbox;trusted_connection=sspi;";

         

using (SqlConnection cn = new SqlConnection(cnStr))

using (SqlCommand cmd = new SqlCommand("SELECT @@SPID", cn)) {

   cn.Open();

   Debug.Print("SPID: {0}", cmd.ExecuteScalar());

}



// at this point, restart the service or KILL the connection on server side



using (SqlConnection cn = new SqlConnection(cnStr))

using (SqlCommand cmd = new SqlCommand("SELECT @@SPID", cn)) {

   cn.Open();

   Debug.Print("SPID: {0}", cmd.ExecuteScalar());

}

When debugging this code and restarting the SQL Server service after the first connection became disposed and before the second connection becomes opened you will get an exception. In my case (since I'm working with a local instance of SQL Server):


A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

At the time of this post, there is no built-in support of ADO.NET for SQL Server that tells the resource pool to verify the connection before it is returned to the SqlConnection. However, if occasional network issues or other problems, that cause an invalid internal connection, sometimes happen in your environment, you can implement your custom connection validation in a factory method that creates your connections.


string cnStr = "server=(local);database=Sandbox;trusted_connection=sspi;";

SqlConnection cn = new SqlConnection(cnStr);

cn.Open();



try {

   using (SqlCommand cmd = new SqlCommand("DECLARE @i INT", cn)) {

      cmd.ExecuteNonQuery();

   }

}

catch (SqlException ex) {

   if (ex.Message.StartsWith("A transport-level error has occurred ")) {

      cn = new SqlConnection(cnStr);

      cn.Open();

   }

   else

      throw;

}

return cn;

As you see, the connection sends a tiny SQL statement to the server before it is returned to the caller. If a transport-level exception occurs, the internal connection becomes removed from pool and another new connection will be created and returned. You can replace the "DECLARE @i INT;" SQL statement with an empty stored procedure called usp_VerifyConnection. This might be slightly faster (not tested, though), but will definitely look less confusing in SQL Server traces ;-).

Control the Connection Pool over Connection String

The SqlConnection.ConnectionString provides several attributes that allow to control the behavior of ADO.NET pooling.

Connection Lifetime can be used to specify a maximum life time for a connection in seconds. When a connection is returned to the pool this value will be compared to the (client side) creation time of the connection. If the connections lifetime is timed out, it will be closed and not put into pool. Attention, this implies if a connection with connection is sent back into the pool before its lifetime elapsed and not used for a long time, it will still stay opened and reused for one more time!


// connection string with a lifetime of 10 seconds

string cnStr = "server=(local);database=Sandbox;trusted_connection=sspi;connection lifetime=10";

string sql = "SELECT connect_time FROM sys.dm_exec_connections WHERE session_id = @@SPID";



// create a new connection with 10 seconds lifetime and send it back to the pool

using (SqlConnection cn = new SqlConnection(cnStr))

using (SqlCommand cmd = new SqlCommand(sql, cn)) {

   cn.Open();

   DateTime connectTime = (DateTime)cmd.ExecuteScalar();

   Debug.Print("Connect time: {0}", connectTime.ToString("HH:mm:ss.fff"));

}



// wait 15 seconds

Thread.Sleep(15000);

// aquire a new connection and release it

using (SqlConnection cn = new SqlConnection(cnStr))

using (SqlCommand cmd = new SqlCommand(sql, cn)) {

   cn.Open();

   DateTime connectTime = (DateTime)cmd.ExecuteScalar();

   Debug.Print("Connect time: {0}", connectTime.ToString("HH:mm:ss.fff"));

}



// create another connection

using (SqlConnection cn = new SqlConnection(cnStr))

using (SqlCommand cmd = new SqlCommand(sql, cn)) {

   cn.Open();

   DateTime connectTime = (DateTime)cmd.ExecuteScalar();

   Debug.Print("Connect time: {0}", connectTime.ToString("HH:mm:ss.fff"));

}

And here the output of this test:


Connect time: 12:47:07.617

Connect time: 12:47:07.617

Connect time: 12:47:22.837

As we see, even if the specified connection lifetime is elapsed, the second connection will get the pooled connection. As long as the elapsed connection is not sent back to the pool it will stay active. Only the third connection retrieves a new created connection, since the connections lifetime was already elapsed as it was sent to the pool.

The default value for Connection Lifetime is zero, what means a maximum lifetime.

Min Pool Size and Max Pool Size can be used to specify a minimum/maximum count of pooled connections. The default value for Min Pool Size is zero, the default value of Max Pool Size is 100. When trying to use more concurrent connections than specified value of Max Pool Size you will get an exception.

The Pooling attribute (default 'true') says if connection pooling shall be used for the created connection or not. If you use a SqlConnection with pooling 'false' the database connection will always be newly created and closed after using it.


// connection string with "pooling=false"

string cnStr = "server=(local);database=Sandbox;trusted_connection=sspi;pooling=false";

string sql = "SELECT connect_time FROM sys.dm_exec_connections WHERE session_id = @@SPID";



// create a new connection and print the current session id

using (SqlConnection cn = new SqlConnection(cnStr))

using (SqlCommand cmd = new SqlCommand(sql, cn)) {

   cn.Open();

   DateTime connectTime = (DateTime)cmd.ExecuteScalar();

   Debug.Print("Connect time: {0}", connectTime.ToString("HH:mm:ss.fff"));

}



// create a new connection and print the current session id

using (SqlConnection cn = new SqlConnection(cnStr))

using (SqlCommand cmd = new SqlCommand(sql, cn)) {

   cn.Open();

   DateTime connectTime = (DateTime)cmd.ExecuteScalar();

   Debug.Print("Connect time: {0}", connectTime.ToString("HH:mm:ss.fff"));

}

And here is the output:


Connect time: 13:41:32.790

Connect time: 13:41:33.050

As we see, the connect time on server side is different for both connections where it would be equal if we would have reused the same internal connection for the second SqlConnection.

Enlist (default 'true') attribute is a more special than the other connection pooling related attributes of connection string. If you don't work with distributed systems and/or Systems.Transactions namespace, you wont need this attribute. The Enlist attribute is important if you need to control how to span transactions over more than one transactional sub-system (like database servers, WCF services, ...). If Enlist is set to 'true' and a parent transaction context is available for the static property System.Transactions.Transaction.Current, ADO.NET will automatically register the SQL connection and its transaction in this transaction context. If you commit your database transaction (implicit or explicit) the transaction will stay pending until the parent transaction becomes committed or rolled back. Today, the most common way to manage those parent transactions is utilizing the TransactionScope class.

A hypothetical system, that might use transaction scopes, is a order management system that automatically creates a shipping order by utilizing a suppliers web-service when an order becomes created or approved. Here we need to update/create new data in a local database and create a shipping order over a web-service within one transaction. If both systems support (and allow) transactions a transaction scope can be used to ensure that all data are either written or not.

If Enlist is set to 'false', a new connection will not be enlisted to an existing transaction scope and behave like no transaction scope was available. All database transactions become committed, independent if the parent transaction will fail or succeed.

Control the Connection Pool over static SqlConnection methods

In addition to the control mechanisms provided by the connection string, ADO.NET SqlConnection provides two static methods to partially, or completely clear the current pool.

SqlConnection.ClearPool(SqlConnection) clears all pooled connections that match the provided connection - by its connection string.

SqlConnection.ClearAllPools clears all currently pooled SQL Server connections.

Pool Fragmentation

There are two more possible issues with ADO.NET connection pooling. Though, I find both are well described at SQL Server Connection Pooling (ADO.NET), so I just quote the paragraphs for sake of completeness.

Pool Fragmentation Due to Integrated Security (Windows Authentication)

Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server. This is a side effect of a particular Web application architecture that developers must weigh against security and auditing requirements.

Pool Fragmentation Due to Many Databases

Many Internet service providers host several Web sites on a single server. They may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. The connection to the authentication database is pooled and used by everyone. However, there is a separate pool of connections to each database, which increase the number of connections to the server.

This is also a side-effect of the application design. There is a relatively simple way to avoid this side effect without compromising security when you connect to SQL Server. Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQL USE statement to change to the desired database. The following code fragment demonstrates creating an initial connection to the master database and then switching to the desired database specified in the databaseName string variable.
// Assumes that command is a SqlCommand object and that

// connectionString connects to master.

command.Text = "USE DatabaseName";

using (SqlConnection connection = new SqlConnection(

  connectionString))

  {

    connection.Open();

    command.ExecuteNonQuery();

  }

Two short things to the second quote. I would not restrict this possible issue to web applications. This kind of pool fragmentation can also happen on application servers that work with many databases on same database server. I don't know why MSDN suggests to use a explicit SqlCommand to change the database, instead of using the SqlConnection.ChangeDatabase instance method.

Sources

Generic C# Resource Pool

2011-08-10T03:05:00.000-07:00

I guess this becomes another almost code-only blog.

Definition

A resource pool is a class that manages a synchronized access of (theoretically) infinite callers of a restricted count of resource items.

One reason to use a resource pool could be a integrated sub system that is restricted by its maximum count of concurrent accessing threads. This could be a web service with a restricted count of sessions. Another reason for a resource pool is a resource that becomes working slow when it becomes accessed by to many concurrent connections. This could be a web-service, a database or a farm of processing servers. A third reason for a resource pool could be a resource that takes long to become initialized, but can be reused. This could be database connections, XSLT transformation classes, custom XML serializers or many other classes.
Some well known .NET framework implementations of resource pools are:

The ADO.NET connection pooling mechanism
The System.Reflection.TheadPool class

Implementation

A few month ago I wrote myself a generic resource pool class that helps me to get those kinds of bottle necks managed.

Here is my implementation of a generic, thread safe C# resource pool. All source code files, as well as some NUnit tests, are also attached as downloadable files at bottom of this post.

// ===================================================================

// PoolItem<T>

// ===================================================================



/// <summary>

/// Represents an item in the <see cref="ResourcePool{T}"/>

/// </summary>

/// <typeparam name="T">The type of the resource to be hold</typeparam>

public sealed class PoolItem<T> : IDisposable where T : class {

   internal PoolItem(ResourcePool<T> pool, T resource) {

      _pool = pool;

      _resource = resource;

   }



   private T _resource;

   private readonly ResourcePool<T> _pool;



   public static implicit operator T(PoolItem<T> item) {

      return item.Resource;

   }



   /// <summary>

   /// Gets the resource hold by this resource pool item

   /// </summary>

   public T Resource { get { return _resource; } }



   /// <summary>

   /// Disposes this instance of an resource pool item and sends the resource back

   /// to the pool

   /// </summary>

   public void Dispose() {

      _pool.SendBackToPool(_resource);

      _resource = null;

   }

}





// ===================================================================

// ResourcePool<T>

// ===================================================================



/// <summary>

/// Generic pool class

/// </summary>

/// <typeparam name="T">The type of items to be stored in the pool</typeparam>

public class ResourcePool<T> : IDisposable where T : class {

   /// <summary>

   /// Creates a new pool

   /// </summary>

   /// <param name="factory">The factory method to create new items to

   ///  be stored in the pool</param>

   public ResourcePool(Func<ResourcePool<T>, T> factory) {

      if (factory == null)

         throw new ArgumentNullException("factory");

      _factoryMethod = factory;

   }



   private readonly Func<ResourcePool<T>, T> _factoryMethod;

   private ConcurrentQueue<PoolItem<T>> _freeItems = 

      new ConcurrentQueue<PoolItem<T>>();

   private ConcurrentQueue<AutoResetEvent> _waitLocks = 

      new ConcurrentQueue<AutoResetEvent>();

   private ConcurrentDictionary<AutoResetEvent, PoolItem<T>> _syncContext =

      new ConcurrentDictionary<AutoResetEvent, PoolItem<T>>();



   public Action<T> CleanupPoolItem { get; set; }



   /// <summary>

   /// Gets the current count of items in the pool

   /// </summary>

   public int Count { get; private set; }



   public void Dispose() {

      lock (this) {

         if (Count != _freeItems.Count)

            throw new InvalidOperationException(

               "Cannot dispose the resource pool while one or more pooled "

               + "items are in use");



         foreach (var poolItem in _freeItems) {

            Action<T> cleanMethod = CleanupPoolItem;

            if (cleanMethod != null)

               CleanupPoolItem(poolItem.Resource);

         }



         Count = 0;

         _freeItems = null;

         _waitLocks = null;

         _syncContext = null;

      }

   }



   /// <summary>

   /// Gets a free resource from the pool. If no free items available this method 

   /// tries to create a new item. If no new item could be created this method 

   /// waits until another thread frees one resource.

   /// </summary>

   /// <returns>A resource item</returns>

   public PoolItem<T> GetItem() {

      PoolItem<T> item;



      // try to get an item

      if (!TryGetItem(out item)) {

         AutoResetEvent waitLock = null;



         lock (this) {

            // try to get an entry in exclusive mode

            if (!TryGetItem(out item)) {

               // no item available, create a wait lock and enqueue it

               waitLock = new AutoResetEvent(false);

               _waitLocks.Enqueue(waitLock);

            }

         }



         if (waitLock != null) {

            // wait until a new item is available

            waitLock.WaitOne();

            _syncContext.TryRemove(waitLock, out item);

            waitLock.Dispose();

         }

      }



      return item;

   }



   private bool TryGetItem(out PoolItem<T> item) {

      // try to get an already pooled resource

      if (_freeItems.TryDequeue(out item))

         return true;



      lock (this) {

         // try to create a new resource

         T resource = _factoryMethod(this);

         if (resource == null && Count == 0)

            throw new InvalidOperationException("Pool empty and no item created");



         if (resource != null) {

            // a new resource was created and can be returned

            Count++;

            item = new PoolItem<T>(this, resource);

         }

         else {

            // no items available to return at the moment

            item = null;

         }



         return item != null;

      }

   }



   /// <summary>

   /// Called from <see cref="PoolItem{T}"/> to free previously taked resources

   /// </summary>

   /// <param name="resource">The resource to send back into the pool.</param>

   internal void SendBackToPool(T resource) {

      lock (this) {

         PoolItem<T> item = new PoolItem<T>(this, resource);

         AutoResetEvent waitLock;



         if (_waitLocks.TryDequeue(out waitLock)) {

            _syncContext.TryAdd(waitLock, item);

            waitLock.Set();

         }

         else {

            _freeItems.Enqueue(item);

         }

      }

   }

}

As you see, the ResourcePool<T> returns PoolItem<T> objects that hold a reference of the pooled resources. The pool does not need to become initialized with any resource items, new resource items become lazy initialized when (if) needed. Therefore the pool requires a factory method provided at construction. This method becomes called whenever a new resource is requested and no free resources are currently available. If the factory method returns null the requesting thread becomes suspended until another thread releases a used resource.

Usage

The pool can be used out of the box.

class MyResource {

   public void DoSomething() { }

}



[Test]

public void SampleUsage() {

   ResourcePool<MyResource> pool = new ResourcePool<MyResource>(CreateResource);



   using (var poolItem = pool.GetItem()) {

      MyResource resource = poolItem.Resource;

      resource.DoSomething();

   }

}



private static MyResource CreateResource(ResourcePool<MyResource> pool) {

   return pool.Count < 3 ? new MyResource() : null;

}

However, if used at many positions in your system I'd suggest to wrap it into a custom pool class. This pool can return a wrapper of the real resources that provide a more specific interface to the consumer. This wrapper can hold an internal reference to a PoolItem<T> and implement IDisposable to free the resources back into the pool when not needed anymore.

// ==========================================================

// The real resource

class InternalResource {

   public void DoSomething() { }

}



// ==========================================================

// The external wrapper returned to the consumer

class MyResource : IDisposable {

   private PoolItem<InternalResource> _poolItem;



   public MyResource(PoolItem<InternalResource> poolItem) {

      _poolItem = poolItem;

   }



   public void DoSomething() {

      _poolItem.Resource.DoSomething();

   }



   public void Dispose() {

      _poolItem.Dispose();

      _poolItem = null;

   }

}



// ==========================================================

// The custom pool

class MyPool {

   private ResourcePool<InternalResource> _pool;



   public MyPool() {

      _pool = new ResourcePool<InternalResource>(CreateResource);

   }



   public MyResource GetItem() {

      return new MyResource(_pool.GetItem());

   }



   private InternalResource CreateResource(ResourcePool<InternalResource> pool) {

      return pool.Count < 3 ? new InternalResource() : null;

   }

}



// ==========================================================

// Sample usage

[Test]

public void SampleUsage() {

   MyPool pool = new MyPool();



   using (MyResource resource = pool.GetItem()) {

      resource.DoSomething();

   }

}

Here are the files for download:

I hope the pool can help you to solve a few of your issues, as it did to me.

Object reference not set to an instance of an object

2011-08-03T14:09:00.000-07:00

We all know, and love, this meaningful message from somewhere inside of a system.
Luckily I can say, in my last projects I became able to almost eliminate those failures - without a 100% unit test code coverage. (Those outside there who say, 100% code coverage by unit tests is a must must, please don't flame me. I'm a big fan of well tested software, but I guess I'm not yet ready for 100% coverage ;-).)

My solution for this old enemy was much easier than some might think. It was done by a simple coding convention. Don't get me wrong, I don't like hundreds of rules about how to write code. In my current project we've got less than 10 project internal coding conventions. Developing large software systems is some kind of a creative work and I think to many rules can eliminate the creativity of developers. I say every experienced developer should be able to write code that others can read and should be able to read code of others.

Our new rule to get rid of null reference exceptions was simply:

Get-methods never return null. They return a valid value or it throws an exception.

This does not mean that our system throws thousands of exceptions per minute. It also means that it is still possible to unsuccessfully try to get data from a method without an exception, we just do this different.

In old projects, code like this was a main part of daily business:


Customer customer = dataSource.GetCustomerById(1234);

if (customer != null) {

   customer.DoSomething();

}

This is nice code and works perfectly. Though, what if someone (me?) ever forgets to check if the method returned <null>? In this case it becomes only a question of time to see our old friend in a log file or - even worse - in a dialog shown to the user.
To avoid try-catch blocks over and over and avoid NullReferenceExceptions we simply established the TryGet pattern used by .NET classes like the Dictionary<T,V>. Instead of a possibly null returning Get-method we use a TryGet-method that returns a bool value indicating if our try to get something was successful or not.


public bool TryGetCustomerById(int id, out Customer customer) {

   using (var connection = CreateOpenConnection())

   using (var command = CreateProcCommand(connection, "GetCustomerById")) {

      command.Parameters.Add("@id", SqlDbType.Int).Value = id;



      using (var reader = command.ExecuteReader(CommandBehavior.SingleRow)) {

         if (reader.Read()) {

            customer = new Customer();

            customer.Id = id;

            customer.Name = (string)reader["Name"];

         }

         else {

            customer = null;

         }

      }

   }

   return customer != null;

}

Plus, if really needed, a Get-method that utilizes the TryGet-method and throws an exception if the search was unsuccessful.


public Customer GetCustomerById(int id) {

   Customer customer;

   if (!TryGetCustomerById(id, out customer))

      throw new EntityNotFoundException(

         string.Format("Cannot find customer with id {0}", id));

   return customer;

}

However, in many cases we don't need the Get-methods any more and in a few other cases we don't need the TryGet-methods since something must be wrong if the requested information is not available and a (meaningful) exception is a must.

This simple change to a TryGet-method changes our consuming library to something like this:


Customer customer;

if (dataSource.TryGetCustomerById(123, out customer)) {

   customer.DoSomething();

}

Some might argue "we still need the check for a result", and they are correct. But, this is a psychological thing. Calling a method that is called Try... clearly tells the consumer, that this call can return without a result. Even more. Sometimes, when it is okay if the method does not return a value, a developer might leave out the if-condition at the point where (s)he calls a TryGet-method. In those cases I saw other developers asking "But you call a Try-method and you don't check for the null value, are you sure??".

In cases where a method can return more than one item, like GetCustomersForCountry, we say it is valid to return an empty list. This is because of two reasons, an empty list is not null and most of the time those calls are followed by a loop that will not be entered for an empty list. However, if we call a method that returns a list and we only need the first item we should consider to add an additional method that returns only one item (like GetFirstCustomerForCountry). Another solution is an extension method "IEnumerable<T>.TryGetFirst(out T item)" and we are back in our style.

For sure, the samples I used here, to get data from some kind of data source are only one of many parts in a system where we find Get-methods. We use this pattern for all methods we write, independent if it works with a data source or any other objects. And we are doin' good with it.

As I said, every now and then we still see a NullReferenceExcption, but I guess there have been about 2 or 3 over the last 5 months of intensive development and testing. All in development or test environment, none in production. Plus, if this happens, we know there are only two options how to fix it. Either add the missing if-clause around the call of a TryGet-method (didn't happen over the last year) or change the null returning Get-method.

Probably I'm the only one who ever had problems with null reference exceptions but, for some reason, it feels like I'm not ;-). I can say we are truly on a point where we can call this old problem (almost) gone.

How to Use 3rd-Party Components

2011-07-30T12:38:00.000-07:00

Long time ago since my last blog, but I had to concentrate on some other things. Guess it's time to get back here :-).

After two failed tries of two, more advanced topics, I decided to write about some more basic stuff.

Let's talk about how to correctly use 3rd-Party components in your software? However, even if this topic is some kind of basic for advanced developers and architects, it's a very important practice in my opinion.

What Means 3rd-Party Component Here?

Sure, everybody has a idea of "3rd-Party Components", but I'm not just talking about guerilla frameworks. I'm talking about every component that is not, either part of the stable part of the .NET framework or part of your own project.

This includes:

Web-Services. In a SOA environment even those of the own project to truly live the loose coupling concept
Data Access Layers (DAL) like Entity Framework or NHibernate
New or still changing and complex parts of the .NET framework, like Workflow Foundation (WF) or Windows Communication Foundation (WCF) on client side
Independent, true 3rd-party frameworks like log4net, Microsoft Enterprise Library (MEL) and any other library from the web or another ISV

This does not include:

.NET framework core components
Technologies like Windows Presentation Foundation (WPF) or Silverlight, since the whole application (a GUI) does not use the technology as a component but is completely based on it. It would be way to much (mostly senseless) effort to tailor an own framework over an existing one that is basis for the complete application
WCF on server side
Helper libraries of the company or project you are working at

A brief explanation for the difference of WCF on client and server side:

When using WCF on server side, the whole process is based on this technology (like WPF in case of GUIs). If you ever need to change the technology, you will usually have to rewrite the whole service, independent of how lose you are coupled to WCF or not.

When using WCF on client side, the consumed service is usually just one component that could be replaced. E.g. you could replace a data web-service or a service that does some calculations with a local DLL. In this case it should be possible to replace the consumed web-service with any other technology, or even another web-service library.

Dangerous Ways to Consume 3rd-Party Components

I'm sure everybody has already seen things source code like...Log4net


public class MyApp {
   private static readonly ILog _log = LogManager.GetLogger(typeof(MyApp));

   static void Main(string[] args) {
      BasicConfigurator.Configure();

      _log.Info("Entering application.");
      Bar bar = new Bar();
      bar.DoIt();
      _log.Info("Exiting application.");
   }
}

Entity framework


using (var context = new MyEntityFrameworkContext()) {
   var customer = context.Customers.Where(c => c.Code == "ALFKI").First();
}

or MEL Unity (sample from MEL help)


IUnityContainer myContainer = new UnityContainer();
myContainer.RegisterType<ILogger, FileLogger>();
myContainer.RegisterType<ILogger, FastFileLogger>("FastLogger");
// ...
ILogger myLogger = myContainer.Resolve<ILogger>();

At first sight, this all looks good, pragmatic and straight forward. However, this approach can bring up serious problems. Why? The used framework/library might fit perfectly at the moment, but this can (and sometimes does) change over the years. Software systems are usually made to stay for, at least, 8 years until it is time to redesign it. Sure, if the software doesn't need many changes, it can be used for longer time.

What if, either the component becomes updated and its interface(s) changes? What if you upgrade your system to a new platform (e.g. a new .NET framework) and the component doesn't work on this platform? What if new technologies come up and are not covered by the component you are using?
You can say "Hey, I'm sure that wont happen, that's all doomsaying", but how about this:

Microsoft completely redesigned the Workflow Foundation from .NET 3 to .NET 4
ASP.NET web-services are almost obsolete and will not be extended due to WCF
LINQ-to-SQL will not be extended any more
Dino Esposito and Andrea Saltarello wrote in their book Architecting Applications for the Enterprise how they had to redesign almost their whole system as they had to switch from NHibernate to Entity Framework 1

As you see, it is not uncommon that changes happen over the lifetime of a software system. It's up to us to be armed when those things happen

Which Options do we have?

Often, it's not too hard to stay relatively independent of external components.

In case of components like log4net or MEL Unity we just have to create a wrapper class that becomes created by a factory class or a DI (sure, we will not create MEL Unity from another DI container..). The following snippets are just pseudo code to indicate what I'm talking about.

Wrap log4net:


public interface ILogger {
   void Info(string message);
}

class Log4NetLogger : ILogger {
   private static readonly ILog _log = LogManager.GetLogger("MyApp");

   void ILogger.Info(string message) {
      _log.Info(message);
   }
}

Wrap unity:


public interface IDependencyContainer {
   T Resolve<T>();
}

class UnityDependencyContainer : IDependencyContainer {
   IUnityContainer _unity = new UnityContainer();

   T IDependencyContainer.Resolve<T>() {
      return _unity.Resolve<T>();
   }
}

public class DependendcyContainerFactory {
   public static IDependencyContainer Create() {
      // if ever using more than one container, use configuration 
      // or anything else here to switch between them
      return new UnityDependencyContainer();
   }
}

In case of DAL frameworks like EF we need to think one step further. EF comes up with this cool LINQ-to-EF feature that makes database queries quite fancy. Though, do we really need this in applications/libraries that consume our DAL? Does it make sense that a GUI app knows how to query all current orders of a customer like "context.Orders.Where(o => o.Customer.Id == myCustomer.Id)"? What if the relation becomes ever changed on database level and it is not possible to change the EDL to provide this relation any more? I'd say LINQ-to-EF is a nice feature within the DAL, but should not be used from consumers.


public interface IDataContext {
   IEnumerable<Order> GetOrdersForCustomer(Customer customer);
}

public class MyEfDataContext : IDataContext {
   public IEnumerable<Order> GetOrdersForCustomer(Customer customer) {
      using (var ctx = new MyEfContext()) {
         return ctx.Where(o => o.Customer.Id == customer.Id).ToList();
      }
   }
}

Now, everybody can safely use the method to get the Orders and if the relation ever changes, it needs to be changed at only one position and non of the consumers will notice it.

Working Safely

And now, after writing a few wrapper classes and thinking about how to use features (like LINQ-to-EF), we are armed to use all those nice components in our application without the danger to be controlled by them.


public class MyApp {
   public MyApp() {
      _container = DependendcyContainerFactory.Create();
      _logger = _container.Resolve<ILogger>();
   }

   private IDependencyContainer _container;
   private ILogger _logger;

   public void DoWork() {
      _logger.Info("foo");
      IDataContext myData = _container.Resolve<IDataContext>();
      Customer customer = GetCustomerToWorkWith();
      var orders = myData.GetOrdersForCustomer(customer);
      _logger.Info("bar");
   }

   private Customer GetCustomerToWorkWith() {
      // usually the implementation to get the customer
      return null;
   }
}

If there will ever be a new, better framework. If there will ever be a feature that is not covered by our components. If there will ever be any other reason to switch to another technology. We are able to switch without redesigning the whole application.

Don't get this all wrong, it is not always easy to stay independent and switching to another 3rd-party component will always cause effort. But usually this effort is much less if our applications are designed to be ready for changes.

(It felt good to write but, after one year since my last blog, it also felt like not being perfect. Seems like I need some practice after this time, so please be patient with me for this post..)

Business Process Modeling (BPM) with .NET Workflow Foundation (WF) 4.0

2010-07-31T05:58:00.000-07:00

I'm currently reading the book Pro WF: Windows Workflow in .NET 4. As a short intermediate review: It's a great resource! I'll write a complete review when I'm done with the rest of the book. I'll also come up with a post that describes a complete overview of Workflow Foundation (WF) 4.0.

This post provides a simple example how WF can be used by our clients to apply real Business Process Modeling (BPM).

Guess we are developing a sales order system. Depending on an orders net-amount the order gets a discount. The calculation of this discount is usually best done within source code (at least in my opinion). So here is a class that represents our default implementation.

using System.Activities;

namespace WfActivityLibrary1 {
   public sealed class DefaultDiscount : CodeActivity {
      [RequiredArgument]
      public InArgument<Order> Order { get; set; }

      protected override void Execute(CodeActivityContext context) {
         Order order = Order.Get(context);

         if (order.NetAmount >= 1000)
            order.Discount = order.NetAmount * 0.2m;
         else if (order.NetAmount >= 500)
            order.Discount = order.NetAmount * 0.1m;
         else if (order.NetAmount >= 100)
            order.Discount = order.NetAmount * 0.05m;
         else
            order.Discount = 0m;
      }
   }
}

The used base class CodeActivity represents one of the Workflow Foundation base classes that can be used to implement custom workflow activities. Usually I'd move the business logic into a class within the Business Logic Layer (BLL) and consume that class within the WF activity. But this would increase the complexity of this sample with additional layers and without any real worth about working with WF, what's the topic of this post.

The problem with discounting is, operational business is very dynamic and the client usually needs to be able to provide selling campaigns to her/his customer. A campaign can be "20% for everything" or "50% of for premium customers".

Until now, this was the point where we started to create dozens of mapping tables and much more classes, containing hundreds of if-statements, which covered the different types of clients discounts. The client got some new masks within the application to manage all those new master data or (worse) a way to import some outside maintained Excel sheets.

However, what if the client wants to provide a new discount related on information that are not yet covered by our "discount calculation"-subsystem within our software? We started to add more master data tables, more classes, more if-statements and more master data masks within the application.
Humans are creative. Especially sales people, when they try to find a new ways to sales their products. (What's a good thing!) So requirements like calculation of discounts for selling-out campaigns are often spirals that never end.

Business Process Modeling

Often clients do not for complicated master data table relations or huge complexity within their system (which is often expensive to realize). They don't understand why it is so complicated to combine discount rates dynamically in different ways. And from a workflow based point, they are right though.

Instead of all those master data tables, and mask, the client often would prefer some way to really dynamically define their discounts with some simple components. That's the point where an application, that supports those features, enters the space of Business Process Modeling (BPM). BPM enabled software represents a huge benefit to the client, since (s)he does not longer need to specify a new change request for the software vendor whenever business changes. Instead of immutable behavior, the software is able to be adopted into the changing business processes.

BPM with WF

Based on our initial sample, let's take our already existing "Default Discount" workflow activity and one additional activity called "Fix Rate Discount". The "Fix Rate Discount" gets two arguments:

Order: The order to be discounted
Percentage: The percentage rate to apply to the order

I kept the source code of the "Fix Rate Discount" activity away, since it is really simple and does not relate to business modeling at all.
Armed with those two (notice, only two) workflow activities it's time to review our two selling-out campaigns.

20% for everything
50% of for premium customers

WF provides the possibility to define new workflows not only in Visual Studio but in any kind of .NET client application, simply by hosting a special WF designer control. So we can include this control into our application (or an external BPM module) and give the client the possibility to model their business by utilizing any defined activities. Even more, each new workflow represents an activity too and can be reused within any other workflow.

WF provides two different kinds of diagrams. Sequence diagrams represent a sequence of activities, executed top down. Flowchart diagrams are like any kind of well known flow chart out of tools like common BPM tools, Microsoft Visio, and most other modeling tools. Both diagram types can be easily combined with each other. Both diagram types support activities like "if"-decisions or several different loops.

I've chosen a sequence diagram for our "20% for everything" selling-off.

Here you see an excerpt of the WF workflow designer and related toolboxes. Certainly the main component is the diagram designer. Activities can be dragged into the designer and configured. The left toolbox (in my configuration) shows all workflow activities that are currently available. The properties window on the right side can be used to configure distinct activities within a diagram and exchange data. The tool-window at the bottom is used to define input and output arguments of the current workflow. For instance, the current workflow expects an input argument of type "Order" called "OrderToDiscount". Those arguments are provided at runtime when the system calls the workflow. Within the diagram the "DefaultDiscount" activity represents the systems implemented default discount calculation. The "Add 20% Discount" activity represents a "FixRateDiscount" activity where I changed the name to be more meaningful within the diagram.
For the "50% of for premium customers" workflow, I've chosen a Flowchart diagram. Not because it wouldn't be possible to do this with a Sequence diagram, but to show the other diagram type.

The "Decision" activity represents an "if"-decision that determines if the customers category is "Premium". For sure, in a real system this information evaluation should be encapsulated in a custom activity. The "DefaultDiscount" represents our system default discount calculation. The "50% Discount" activity is another instance of our "FixRateDiscount" activity.

Instrumentation Instead of Development

Nevertheless, be careful with too many logic within those workflows. Due to some very powerful activities like "Assign" to assign any value to any variable or property of an object or "Invoke" to invoke any method on an object instance, you can use WF to develop almost everything. In my opinion, this is the wrong way to use workflow engines. Those tasks are usually better placed in source code.

I see a huge strength in workflows to enable clients to change the processing behavior of a system. I don't see a worth in letting the client develop her/his system. Usually they neither do have the skills to do this, nor they are interested in tiny low level tasks within their models.

If You Cannot Use Identities; Cache Your Ids

2010-07-19T05:05:00.000-07:00

Sometimes it is not possible to use IDENTITY columns in database tables. Unfortunately, one of the most common approaches to handle new IDs in non IDENTITY columns is working with MAX(). I just saw this yet another time in a blog post I don't want to refer here. In my opinion, the generation of unique ID values is quiet basic but should be done correct, so this blog post is all about this topic.

If you are able to use IDENTITIES in all tables, you are find and you can finish reading here. :-)

Reasons Not To Work With Identity Columns

One reason might be, that the used framework does not support them, like Entity Framework in combination with SQL Server Compact. SQL Server Compact does not support batch execution, so every query allows only one SQL statement and EF does not support a second query for each INSERT to determine the last generated IDENTITY value.

Another reason not to work with IDENTITY columns are scenarios where computed IDs are required, like in distributed systems. I used them to guarantee a unique PRIMARY KEY over all databases (all over the world) where the first part of the primary key was an incrementing BIGINT value and the second part was a SMALLINT, describing the database where the table row was created. Some of you might argue, that I would have also been able to use a Guid, however I still don't like them in databases since they are way too large and too slow.

The MAX() Approach

A very common approach to generate new ID values is to select the currently maximal value of the table where new rows have to be inserted.

Here a plain SQL sample.


-- ======================================================================
CREATE TABLE Orders (
   Id INT NOT NULL PRIMARY KEY CLUSTERED
   ,OrderDate DATETIME2
);

-- ======================================================================
-- some existing rows
INSERT INTO Orders VALUES (1, SYSDATETIME());

-- ======================================================================
-- generate new ID values, using MAX
DECLARE @newId INT;

-- select current maximal ID + 1
SELECT @newId = ISNULL(MAX(Id), 0) + 1 FROM Orders;

INSERT INTO Orders VALUES(@newId, SYSDATETIME());

Unfortunately, this approach has some issues. First, let's have a look at the performance. Each call of MAX() causes an index scan of the primary key, as shown in the below execution plan. SQL Server 2008 does a great job, avoiding to scan the whole index, however this scan for each new row should be avoided.

The second, more important issues is concurrency. If we are working with SQL Server Compact, this issue does not exist, since the DBMS supports only one current connection at the time ;-). In every other environment, where we have more than one concurrent user connection, we might (and occasionally will) get an error if there are two concurrent inserts and both connections are at the same time between row 16 and row 18 of the above sample script. Both will get the same MAX ID value from the table, but only one of them can use the new calculated ID for insert. The second one will retrieve a primary key violation.

Using an ID Table

To avoid both previously denoted issues, we can use an ID-table, containing the name of the table to create a new ID for and the next available ID.


-- ======================================================================
-- id table
CREATE TABLE Ids (
   Name VARCHAR(30) PRIMARY KEY CLUSTERED
   ,NextId BIGINT NOT NULL
);
-- ======================================================================
-- init ids for Orders table
INSERT INTO Ids VALUES('Orders', 1);
GO
-- ======================================================================
DECLARE @id BIGINT;
-- get the next available "Orders" id from Ids table
UPDATE Ids SET
   @id = NextId
   ,NextId += 1
WHERE Name = 'Orders';

INSERT INTO Orders VALUES(@id, SYSDATETIME());

Usually, we would move the ID allocation into a procedure, which I left out for sake of brevity. This already looks like a much better approach, doesn't it?

However, till now, this solution brings up another issues. Now the whole system has to access (with a write lock) the ID-table for each single row inserted into any other table. This usually causes a system wide performance issue when system is on load. The solution for this problem is to cache IDs as explained below.

ID Caching

To avoid the access problem with the ID table, clients can use a caching mechanism. This means each client avoids to allocate new IDs one by one, but always allocates a larger count of IDs in one step and works with them for the next new rows to be inserted.

The count of IDs to be allocated in one step depends on the client and the count of row it usually creates. A GUI like a web- or windows-application is often fine with an ID cache size of 10 where a data loader process might need a cache size of 1000.

For sure, this causes that some ids are never used. E.g. if a web application allocates 10 new IDs but the user enters only one new row, 9 IDs are lost. However, this doesn't really matter since primary key IDs are only needed for uniqueness and should never be used as shown sequence in the client.

The easiest way to allocate new IDs is a simple procedure which gets the name of the table to allocate new IDs for and the count of IDs to be allocated and returns the next available ID to be used.


ALTER PROCEDURE AllocateIds
   @nextId BIGINT OUTPUT
   ,@name VARCHAR(30)
   ,@count INT
AS
SET NOCOUNT ON;

UPDATE Ids SET
   @nextId = NextId
   ,NextId += @count
WHERE Name = @name
GO

Client Side Caching

Caching IDs in a client application is usually quiet easy, as long as the client is stateful like usual web- or windows-applications and most web services.

This IdGenerator is a very simple sample of how to cache IDs in the client, utilizing our previously shown stored procedure.


static class IdGenerator {
   class IdEntry {
      public long NextId;
      public long MaxId;
   }
   // table depenant cache
   static IDictionary<string, IdEntry> _cache = new Dictionary<string, IdEntry>();
   // connection and allocation information
   static string _connectionString;
   static int _allocationSize;
   // initializes the connection and allocation information
   public static void Init(string connectionString, int allocationSize) {
      _connectionString = connectionString;
      _allocationSize = allocationSize;
   }
   // public interface to allocate new ids
   public static long NextId(string tableName) {
      IdEntry entry = GetEntry(tableName);
      if (entry.NextId > entry.MaxId)
         AllocateNewIds(entry, tableName);
      return entry.NextId++;
   }
   // get an id entry for a specified table
   private static IdEntry GetEntry(string tableName) {
      IdEntry entry;
      if (!_cache.TryGetValue(tableName, out entry)) {
         entry = new IdEntry { NextId = 1 };
         _cache.Add(tableName, entry);
      }
      return entry;
   }
   // allocate new ids from database
   private static void AllocateNewIds(IdEntry entry, string tableName) {
      Console.WriteLine("Allocating new ids from database");
      using (SqlConnection connection = CreateConnection())
      using (SqlCommand command = new SqlCommand("AllocateIds", connection)) {
         command.CommandType = CommandType.StoredProcedure;

         SqlParameter nextId = command.Parameters.Add("@nextId", SqlDbType.BigInt);
         nextId.Direction = ParameterDirection.Output;
         command.Parameters.Add("@name", SqlDbType.VarChar, 30).Value = tableName;
         command.Parameters.Add("@count", SqlDbType.Int).Value = _allocationSize;
         command.ExecuteNonQuery();
         entry.NextId = (long)nextId.Value;
         entry.MaxId = entry.NextId + _allocationSize - 1;
      }
   }
   // create a new, open database connection
   private static SqlConnection CreateConnection() {
      SqlConnection connection = new SqlConnection(_connectionString);
      connection.Open();
      return connection;
   }
}

... and a sample, how to use the ID generator...


IdGenerator.Init("Server=.;Database=Sandbox;Integrated Security=sspi;", 10);
for (int i = 0; i < 100; i++) {
   long id = IdGenerator.NextId("Orders");
   Console.WriteLine(id);
}

ID Tables And T-SQL

Well, while ID-tables and caching are a good way to go in client applications, they are a bit tricky in T-SQL (and probably in most other SQL dialects). The problem is that T-SQL is stateless and due to this fact it does not support caching, since we don't have that "static" place where we can store our cached IDs.

One workaround is to create a temp table that looks like the stateful ID-table and represents our "own" cache. But, due to the scope handling of SQL Server it is not possible to move the temp table creation into a procedure, so this workaround is quiet awkward to maintain since all scripts/procedures that want to a client like caching have always to create their temp table by their own.

However, usually T-SQL scripts should never try to work like clients - in a row based manner, so they should not really need a ID cache. Whenever new rows have to be inserted into a table, the executing procedure/script should determine the full count of needed rows, allocate the required range of IDs and insert all rows in one operation.

One thing that changes when working with ID-tables is, IDs for single row inserts should be provided from client now. Why? Because of the missing ability to cache IDs in T-SQL. Where we usually had insert procedures lie this...


CREATE PROCEDURE InsertOrder
   @id INT OUTPUT
   ,@orderDate DATETIME2
AS
   INSERT INTO Orders (OrderDate)
      VALUES (@orderDate);
   SELECT @id = SCOPE_IDENTITY();

... we would have to act like this...


CREATE PROCEDURE InsertOrder
   @id INT OUTPUT
   ,@orderDate DATETIME2
AS
   EXECUTE AllocateIds @id OUTPUT, 'Orders', 1;
   INSERT INTO Orders (Id, OrderDate)
      VALUES (@id, @orderDate);

... but this would cause a huge traffic in our ID-table. So it is usually more productive to get the IDs from the client, which can easily handle the caching.


CREATE PROCEDURE InsertOrder
   @id INT
   ,@orderDate DATETIME2
AS
   INSERT INTO Orders (Id, OrderDate)
      VALUES (@id, @orderDate);

Some might say, that they don't want to give clients the force to handle the new primary key values. Though, SQL Server enforces the uniqueness for you ;-) and the price to pay is quiet cheap, compared to the other option.

Stateless Clients

Stateless clients have almost same issues like T-SQL procedures and scripts. Due to their statelessness, they are usually not able to cache their IDs. The only additional option we got with stateless clients is to move the ID caching into an external resource like a WCF service. If an system has several stateful and several stateless components, it is an option to keep the stateful applications fast by providing a separate ID cache for those that are stateless.

For sure, you could consume this service also from T-SQL (quiet simple with SQLCLR as I showed here) but SQLCLR to access web services is still rare used and the overhead is usually way larger than directly accessing the ID-table. So, the service should only be used from client side to keep the SQL out of the ID generation.

Defend Your Code

2010-07-09T08:33:00.000-07:00

Ever explained a current bug or dirty implementation with one of those sentences?

"The (project) manager/marketer said, we have to meet the deadline."
"The (project) manager/product owner told you, a quick and dirty solution is okay for now."
"The XYZ told you, we NEED this new feature, no matter how it works behind."

Never? Though, I did, not currently but I did.

Who was responsible to the current problem? It's me. I treated non code-specific requirements over system quality. I did not write the unit test which had found the problem. I did not invest the time for refactoring to ensure a clean implementation. You might argue that somebody else decided. Nevertheless, it's my, and only my, task to keep the system clean, free of bugs and maintainable.

I see software projects are like a play and we, all the stakeholders, are the actors/actresses. The part of the marketer is to push forward for new features. Part of the project manager, managers and product owners is to obsess the schedule. And it's our part to defend the code!

What about "If I did not implement this feature in time, I'd got fired"? Probably not. Most Managers don't want buggy software or software that is hard to maintain for future requirements. Even if they don't say that very clear. Managers, and all other non developing stakeholders, just cannot rate importance of one or more unit tests and have no clue about refactoring (even if they say they do have).

If I would be an electrican, the building owner and the lead worker would always insist on the deadline. However, if there is no power when the building is done, I'd be the single neck to wreck. No building owner, no lead worker. It would be their part to push forward and it would be my part to make (and keep!) it work.

We Are Authors!

2010-07-07T04:19:00.000-07:00

I just read this statement in Robert C. Martin's Book Clean Code where he primary wrote about "how to write code". However, I see this as a more general thing.

We are all authors and and we are writing almost all the day. Everything we write will be read by somebody, now or in months, so we really should think about the reader when we are writing. Reading can make people annoyed about the author or grateful to him/her and we should always prefer the latter.

Source Code

Did you ever see some, so called "very flexible", solution like this?


// get data returns all new users to be created
object[] rows = GetData();
foreach (object[] row in rows) {


    // ix 0 represents the new user id
    // ix 1 is not needed
    // ix 2 is the name of the new user
    int i = (int)row[0];
    string n = (string)row[2];
    CreateUser(i, n);
}

Guess, the author is just sitting right next to you and you are about to add some new features. He tells you that this approach, using untyped object arrays is very flexible, because you don't have to change the interface if something new comes in.

I call this completely inflexible. It is impossible to do any change anything within the user structure since it is almost impossible to find the positions where the structure is used.

How shall I know that GetData returns users? Does it always return users? Why an array of object as rows, instead of an ICollection<T> to let the user know what's inside the collection? Why another array of object as row, instead of a strong typed NewUser type? Why all the noise comments, instead of self describing variables? What's about "ix 1 is not needed"? Is it always empty until now? Is it reserved for future usage? Am I just not allowed to look into? Why the two empty rows at the beginning of the loop?

How about this?


ICollection<NewUser> newUsers = GetNewUsers();
foreach (NewUser user in newUsers) {
    CreateUser(user.Id, user.UserName);
}

This code does exactly the same as the previous, but we don't need any comments. The reader directly knows what's going on and we are able to refactor NewUser whenever we want because we can find all positions where it is used.

If I might ask myself, who should ever read my code? The most faithful reader of my code am I. If we have to change some implementation, we realized days, months or years before we start with reading the existing code. We are jump back and forth to find out what happens, which objects are used and how they are composed to hit the target.

Writing clean source code means to be kind to your readers; especially you.

T-SQL

For sure, T-SQL is source code too. However, for me it felt important to write this own subject. Why? Because the most ugly code I've seen was written in T-SQL.

Ever seen a procedure/script like this?


insert Into TestData100K SELECT Top(10) id, BitVal, FLOATVAL from TestData10K WHERE 
ID > 99
and intval != 1

Unfortunately (yes, unfortunately) T-SQL is not case sensitive, so the upper/lower case of statements or even words within statements are often only depends on the occasional twitches of the writers little finger. What about line breaks? How about indention?

How about this?


INSERT INTO TestData100K
SELECT TOP(10) 
   Id
   ,BitVal
   ,FloatVal
FROM TestData10K 
WHERE ID > 99
   AND IntVal != 1;

I'd never say that my way to write T-SQL is the best, but you can be sure that all my T-SQL looks like this. It is unimportant if you prefer upper case or lower case keywords like SELECT but take your decision and comply with it. Take your decision how and what to indent and where to add the commas.

The worst T-SQL I've seen was most times written by non database developers who "had to do some ugly T-SQL". T-SQL, like other programming languages, is always as ugly as we write it. (Well, T-SQL provides more features to write bad code than most other languages.) So, it's up to us to write pretty procedures.

Emails

Emails? Why bother? In my opinion, emails are some kind of comedown of humans writing. In good old days, as we wrote letters with paper we all knew some formatting rules, and we followed them. Today, with emails, many people seem to forgot any kinds of formatting.

Did you ever get a mail like this?

Subject: todo
Content:
Please implement NewOrder till friday. WE NEED TO GO ONLINE ON MONDAY!!!
thx

What does this subject say to me; except "somebody has to do something"? Who is responsible to implement the new feature? I have to look to the email header to see, if I am the only person in the "TO" recipients. If there are even more than one "TO" recipients, the recipients have to guess who is responsible. Why capitalizing the production schedule? Capitalized text always feels like screaming. Does this mean I might have to work on weekend to meet the deadline? Why those three exclamation marks? Does this mean I have to stay on Friday, even if it becomes Saturday, until I'm done? After this hard task assignment a sloppy "thx"? Are you kidding me?

How about this?

Subject: Feature NewOrder (until Friday)
Content:
Hi Flo

Please implement the “NewOrder” until next Friday. It's important that we meet the deadline due to a presentation on Monday. Please let me know if you need some additional resources.

Thanks
John

Sure, this is much more text to write and read. However, I'm no machine and I don't need (/want) to get my tasks optimized for fast processing (reading). It's a kind of respect to invest some time for assigning a task in a grateful form.

I don't mean emails to a friend, to ask her for lunch tomorrow. I use to write them sloppy because the content is not really important and I'd probably even speak in slang if I'd stand in front of her.

Conclusion

I'd really appreciate if more people would feel like an author whenever they are writing. To keep the readers in mind whenever we write is some kind of respect which is always worth.

Though, sometimes I'd appreciate if I would follow this engagement more consequent than I actually do. However, I know I'm not perfect, I know (at least some of) my personal issues and I'm still working on.

Table-Valued Parameters

2010-07-06T14:40:00.000-07:00

This post is initiated by Ken Simmons (Blog | Twitter). He asked to write a blog post that describes our favorite feature of SQL Server. Ken's post here is not only interesting due to the MSDN package :-) but also caused me thinking about what I changed in my database solutions since SQL Server 2008.

For sure, this is not my first post about SQL Server 2008 feature User Defined Table-Valued types. I'd not call them as the greatest feature of SQL Server in general, however they are one of the greatest new features in version 2008.

Bulk Load by Ids

Often it is required to load many rows of one table by their ids or any other attribute. A sample is reloading of already known data.

One solution is to send one query for each item to be (re-)loaded. However, this causes many small queries, stressing server and network.

Another approach is to create one dynamic SQL query, containing an IN statement for all ids to be reloaded. Though, this has several issues. Dynamic creation of SQL statements is always a potential open door for SQL injection. Those IN statements make it impossible for SQL Server to reuse existing execution plans. IN statements with many items require many resources on server side and become slow. This works only if there is one unique column to filter.

The - in my opinion - best solution, before SQL Server 2008, is to create a concatenated string of all IDs to be loaded, send the string to the server, split them into a temp table and use this table joined to the real table to return the requested rows. I wrote a huge post about SQL split functions here. Though, as cool as this approach is, it stays a little hack. When working with more than one column, this approach becomes awkward since you have to handle hierarchical data (lines and items) within the string.

User-Defined Table-Valued types are a new and clean way to bulk load rows from database. Let's start with the definition of a new type.


CREATE TYPE IntIdTable AS TABLE (
   Id INT NOT NULL PRIMARY KEY CLUSTERED
)

Now, we are able to write a stored procedure which gets this type as parameter. (I don't show the referenced table "TestData100K" because it doesn't really matter. Out of others, it contains one column, called "Id". Please, no bashing for the "SELECT *", it's a sample.).


CREATE PROCEDURE GetTestDataByIds
   @ids IntIdTable READONLY
AS
   SELECT td.*
   FROM TestData100K td
      JOIN @ids ids ON td.Id = ids.Id;

Okay, we're done on server side, let's switch to the client.
First thing we need to be able to address User-Defined Table-Types is a class, implementing the IEnumerable<SqlDataRecord>. (Another way would be a DataTable, but I don't like this class due to its huge overhead.)


class IntRecordEnumerator : IEnumerable<SqlDataRecord> {
   public IntRecordEnumerator(IEnumerable<int> values) {
      _values = values;
   }

   IEnumerable<int> _values;

   public IEnumerator<SqlDataRecord> GetEnumerator() {
      SqlMetaData metaData = new SqlMetaData("Id", SqlDbType.Int);

      foreach (int id in _values) {
         SqlDataRecord record = new SqlDataRecord(metaData);
         record.SetInt32(0, id);
         yield return record;
      }
   }

   System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() {
      return this.GetEnumerator();
   }
}

Now, we can use this class as parameter of a SqlParameter. (Notice, the parameter requires SqlDbType.Structured and TypeName to address the name of the table type.)


[TestMethod]
public void SearchIds() {
   string sql = "GetTestDataByIds";
   using (SqlConnection connection = CreateOpenConnection())
   using (SqlCommand command = new SqlCommand(sql, connection)) {
      command.CommandType = CommandType.StoredProcedure;
      SqlParameter param = command.Parameters.Add("@Ids", SqlDbType.Structured);
      param.TypeName = "IntIdTable";

      int[] idsToLoad = new int[] { 1, 2, 3, 4, 5 };
      param.Value = new IntRecordEnumerator(idsToLoad);

      using (SqlDataReader reader = command.ExecuteReader()) {
         while (reader.Read()) {
            int id = (int)reader["Id"];
            Console.Write(id);
            Assert.IsTrue(idsToLoad.Contains(id));
         }
      }
   }
}

Streaming Data To The Server

In distributed systems it is important to synchronize data between different databases.

If possible to use database features like BCP or SSIS, this is often the best solution. However, sometimes it is required to do some additional work, containing business logic, those solutions require a duplication of business logic into the database. In addition, if there are data to be updated (not only inserted) the data have to be packed into a staging table before they can be handled by a procedure. Staging tables are a nice temporary storage but can become tricky if there are concurrent synchronization jobs.

The classic and, as far as I know, most common solution today is using single INSERT statements to load those bulk data row by row. As denoted above, this becomes a little stress test for database server and network.

Another, a bit esoteric, approach is to use SQL Server 2005's XML functionality to create one huge XML package, containing all data to be written into database server. Problem is XML is a quiet chatty language and causes a huge memory usage on client and server.

Here, User-Defined Table-Valued types can be used to push all data in one step into a procedure. Business logic can be applied while streaming through all data to be synchronized. The procedure finally updates and inserts the data into their destination table. I first wrote about this approach here.

Again, we start with a table type.


CREATE TYPE ImportSampleTable AS TABLE (
   FirstName VARCHAR(30)
   ,LastName VARCHAR(30)
);

A simple procedure, that gets the type as parameter and inserts the rows into a table.


CREATE PROCEDURE ImportSampleFile
   @importData ImportSampleTable READONLY
AS
   INSERT INTO ImportSample
   SELECT *
   FROM @importData;

Heating to the client...
An implementation of a IEnumerable<T>.


class FileToLoad : IEnumerable<SqlDataRecord> {
   public FileToLoad(string fileName, SqlConnection connection) {
      _fileName = fileName;
      _connection = connection;
   }

   string _fileName;
   SqlConnection _connection;
   SqlMetaData[] _metaData;

   public IEnumerator<SqlDataRecord> GetEnumerator() {
      CreateMetaData();

      using (StreamReader reader = new StreamReader(_fileName)) {
         while (!reader.EndOfStream) {
            SqlDataRecord record = CreateRecord(reader);
            yield return record;
         }
      }
   }

   private void CreateMetaData() {
      _metaData = new SqlMetaData[] {
         new SqlMetaData("FirstName", SqlDbType.VarChar, 30),
         new SqlMetaData("LastName", SqlDbType.VarChar, 30),
      };
   }

   private SqlDataRecord CreateRecord(StreamReader reader) {
      string line = reader.ReadLine();
      SqlDataRecord record = new SqlDataRecord(_metaData);
      string[] lineItems = line.Split('\t');
      record.SetString(0, lineItems[0]);
      record.SetString(1, lineItems[1]);
      return record;
   }

   System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() {
      return this.GetEnumerator();
   }
}

And again, we are ready to use the table valued type as parameter for another test.


[TestMethod]
public void StreamingLoad() {
   using (SqlConnection connection = CreateOpenConnection())
   using (SqlCommand command = new SqlCommand("ImportSampleFile", connection)) {
      command.CommandType = CommandType.StoredProcedure;
      SqlParameter param = command.Parameters.Add("@importData", SqlDbType.Structured);
      param.TypeName = "ImportSampleTable";
      // get a sample file
      string fileName = GetType().Assembly.Location;
      fileName = Path.GetDirectoryName(fileName);
      fileName = Path.Combine(fileName, "FileToLoad.txt");
      
      param.Value = new FileToLoad(fileName, connection);
      command.ExecuteNonQuery();
   }
}

Conclusion

User-Defined Table-Valued types a nice new feature of SQL Server 2008! I did not find many reasons to use them in plain T-SQL, but they provide a native support of set-based solutions between client and server and this is a really great enhancement of SQL Server.

Centralized Error Handling With Lambda

2010-06-11T01:52:00.000-07:00

For many reasons it is often useful - and important - to catch exceptions in UI or other boundary classes like web-services. Out of many other the most important reasons are usually security, to avoid to show the information of an internal system error to the client, and traceability, to log the error for investigations.

The problem is, exception handling is quiet complicated code and is often highly redundant, what makes it awkward to keep it in sync.

The Common Approach For Exception Handling

As a simple sample, guess we are developing a web-service method that returns a Country object from data storage by a specified ISO 3-alpha.


[OperationContract]
public Country GetByIsoAlpha3_Simple(string alpha3)
{
    Logger.Trace("Entered GetByIsoAlpha3");
    try
    {
        ThrowHelper.CheckNullArg(alpha3);
        CountrySource source = new CountrySource();
        return source.GetByIsoAlpha3(alpha3);
    }
    catch (ArgumentException ex)
    {
        throw new FaultException(ex.Message);
    }
    catch (DbException ex)
    {
        Logger.LogEx(ex);
        throw new FaultException("There was error with the data storage.");
    }
    catch (Exception ex)
    {
        Logger.LogEx(ex);
        throw new FaultException("A internal error occurred");
    }
    finally
    {
        Logger.Trace("Leaving GetByIsoAlpha3");
    }
}

As we see, the real functionality is only two lines of code, the rest of the 25 lines are standard exception handling and the validation of the input values.

The problem with this method is, it's very dangerous remember all required tasks for standard exception handling. Does it handle all standard exceptions we usually handle? Might it be useful to log the ArgumentException? Do we really want to tell the caller that we had an error with the data storage? How can we ensure that all web methods do the same standard exception handling.

Extract The Exception Handling

A good way to ensure all standard exception handling is to move it out into a static helper class.


static class WsHelper
{
    public static T ExecuteWebMethod<T>(string methodName, Func<T> method)
    {
        Logger.Trace("Entered " + methodName);
        try
        {
            return method();
        }
        catch (ArgumentException ex)
        {
            throw new FaultException(ex.Message);
        }
        catch (DbException ex)
        {
            Logger.LogEx(ex);
            throw new FaultException("There was error with the data storage.");
        }
        catch (Exception ex)
        {
            Logger.LogEx(ex);
            throw new FaultException("A internal error occurred");
        }
        finally
        {
            Logger.Trace("Leaving " + methodName);
        }
    }
}

Using Lambda Expressions To Utilize The Helper

Now, we can utilize the ExecuteWebMethod method with a simple Lambda expression.


[OperationContract]
public Country GetByIsoAlpha3_Lambda(string alpha3)
{
    return WsHelper.ExecuteWebMethod("GetByIsoAlpha3_Lambda", () =>
    {
        ThrowHelper.CheckNullArg(alpha3);
        CountrySource source = new CountrySource();
        return source.GetByIsoAlpha3(alpha3);
    });
}

As we see, the code of our web method is reduced to six lines. Still three lines for our method specific work, two lines with braces and one line calling the helper method which gets a Func<T> that represents the implementation of the web method.

Possible Extensions

The exceptions handled in this sample are only exemplary. If you have some other common standard exceptions like a ValidationException or a general BusinessException, feel free to extend the ExecuteWebMethod.
For sure, you noticed the methodName parameter, provided for the ExecuteWebMethod operation. To get rid of this parameter we can use the StackTrace class to determine the calling method. In case of web services or other boundaries, this is a good approach. Nevertheless, be careful with StackTrace in highly utilized methods since stack trace evaluation is an expensive task.

Restrictions

The helper method should be only responsible for standard exception handling. Don't try to move all use case specific exception handling into the external helper method.

Conclusion

Lambda is generally a powerful feature to inject standard method with external functionality. This blog post showed only one out of many solutions to decorate a specific implementation with other functionalities.

Streaming Serialization with XmlSerializer

2010-05-30T05:02:00.000-07:00

As known, .NET provides several different ways to serialize and de-serialize objects. One of those serialization techniques is the XmlSerializer class. Generally this class provides a nice, straight forward approach. However, there is one problem with this (and most other) serialization classes, it does not support streaming. If you want to (de-)serialize really large counts of objects, you might be run into physical memory problems.

One solution is to implement the IXmlSerializable interface, which exposes the ReadXml(XmlReader) and WriteXml(XmlWriter) operations. Though, this ends in quiet a bunch of complicated code.

To handle the memory problem when using the XmlSerializer class, here you can find two simple wrapper classes which provide a streaming functionality.

XmlStreamingSerializer

The XmlStreamingSerializer creates an internal instance of a XmlSerializer and a XmlWriter which provides the persistence management. To avoid the "xsi" and "xsd" namespaces again and again for each object to be serialized, it simply provides an empty XmlSerializerNamespaces. I found that trick in one of the comments on Scott Hanselman's blog about XmlFragmentWriter.

public class XmlStreamingSerializer<T> {
   // ------------------------------------------------------------------
   static XmlStreamingSerializer() {
      _ns = new XmlSerializerNamespaces();
      _ns.Add("", "");
   }
   // ------------------------------------------------------------------
   private XmlStreamingSerializer() {
      _serializer = new XmlSerializer(typeof(T));
   }
   // ------------------------------------------------------------------
   public XmlStreamingSerializer(TextWriter w)
      : this(XmlWriter.Create(w)) {
   }
   // ------------------------------------------------------------------
   public XmlStreamingSerializer(XmlWriter writer) : this() {
      _writer = writer;
      _writer.WriteStartDocument();
      _writer.WriteStartElement("ArrayOf" + typeof(T).Name);
   }
   // ==================================================================
   static XmlSerializerNamespaces _ns;
   XmlSerializer _serializer = new XmlSerializer(typeof(T));
   XmlWriter _writer;
   bool _finished;
   // ==================================================================
   public void Finish() {
      _writer.WriteEndDocument();
      _writer.Flush();
      _finished = true;
   }
   // ------------------------------------------------------------------
   public void Close() {
      if (!_finished)
         Finish();
      _writer.Close();
   }
   // ------------------------------------------------------------------
   public void Serialize(T item) {
      _serializer.Serialize(_writer, item, _ns);
   }
}

XmlStreamingDeserializer

As the serializer, the XmlStreamingDeserializer class wraps an instance of a .NET XmlSerializer. It uses a XmlReader to provide the streaming functionality and utilizes the XmlReader.ReadSubtree method to get the current serialized item into the XmlSerializer.

public class XmlStreamingDeserializer<T> {
   // ------------------------------------------------------------------
   static XmlStreamingDeserializer() {
      _ns = new XmlSerializerNamespaces();
      _ns.Add("", "");
   }
   // ------------------------------------------------------------------
   private XmlStreamingDeserializer() {
      _serializer = new XmlSerializer(typeof(T));
   }
   public XmlStreamingDeserializer(TextReader reader)
      : this(XmlReader.Create(reader)) {
   }
   public XmlStreamingDeserializer(XmlReader reader) : this() {
      _reader = reader;
   }
   // ==================================================================
   static XmlSerializerNamespaces _ns;
   XmlSerializer _serializer = new XmlSerializer(typeof(T));
   XmlReader _reader;
   // ==================================================================
   public void Close() {
      _reader.Close();
   }
   // ------------------------------------------------------------------
   public T Deserialize() {
      while (_reader.Read()) {
         if (_reader.NodeType == XmlNodeType.Element
               && _reader.Depth == 1
               && _reader.Name == typeof(T).Name) {
            XmlReader reader = _reader.ReadSubtree();
            return (T)_serializer.Deserialize(reader);
         }
      }
      return default(T);
   }
}

Some Tests

Here you can find some performance test results.

I serialized a very simple object structure shown below.

public class Foo {
   [XmlAttribute]
   public int Id { get; set; }
   [XmlAttribute]
   public string Bar { get; set; }
   public List<foo> SubFoos { get; set; }
}

I serialized 10,000 instances, each with 100 sub items. Maybe you don't have to serialize so many objects, but the depth of serialized objects is often far deeper than two levels.

Action	Duration (ms)	RAM (MB)
Serialization with XmlSerializer	2954	134
Serialization with XmlStreamingSerializer	2391	13
De-serialization with XmlSerializer	3662	150
De-serialization with XmlStreamingDeserializer	2953	13

Those test results are not representative for any purpose. Especially the apparently faster processing of the streaming classes seems to be a bit curious to me. Nevertheless, the more important factor in this test results it the far less memory requirement (about 10% here).

Possible Extensions

Both classes, shown above are quiet simple and there are several possible extensions to get them more powerful.

One class for both. If you prefer one serialization class for serialization and de-serialization, feel free to merge both into one. I preferred the two-class solution to keep each of them more pure.
Configurable document element name. Since now, the serializer creates a hard-coded document element name called "ArrayOfMyType". This behavior matches to the XmlSerializer behavior, when serializing an IEnumerable<out T>. Feel free make this
Xml Namespaces. The above serialization classes doe not support XML namespaces. If you need namespaces just add something like a public XmlSerializerNamespaces property.
Different objects. XML files often contain more than one type of objects. Support of different object types could easily be added by a dictionary of serializers.

The intension of this blog was not to present a fully dressed streaming XML framework. I tried to a prove of concept for how to handle large amounts of objects in combination with the .NET XmlSerializer class.

Attachments

Here you can find the XmlStreamingSerializer and XmlStreamingDeserializer classes as well as a simple console application I used for my tests.

Layers, Bondaries And How To Exchange Data

2010-05-14T06:14:00.000-07:00

One of the most popular (architecual) patterns is the Three-Layer design. Unfortunately, this pattern is often convounded with the three-tier pattern. I take the definition of authors like Martin Fowler, Thomas Erl and Craig Larman, which says:

The Three-Tier pattern describes a software deployment into three physical tiers. A common solution for this pattern is a database server, a web server and a web client (browser)
The Three-Layer pattern describes how to split sofware concerns into three different layers. These layers are a data acess layer, a business logic layer and a presentation layer. The layers might also be deployed into different tiers, though, this is optional.

The below picture shows the three layers and their positions.

The intention of this blog is not to describe how to implement the Three-Layer pattern, there are already milions of web resources which does this. This blog focuses on the data exchange between the data access layer (DAL) and the business logic layer (BLL).

The Architectual Challange

Most sources, describing the three-layer pattern, advice to separate the DAL and the BLL into two different assemblies. The reason is to avoid mixing different concerns, what is a good thing. This brings up an intersting issue. How to create BLL objects (like Customer or Order) from DAL?

Why? Because each layer should only communicate with the layer directly below. A layer should never communicate with a above layer. So, the BLL communicates with the DAL, but the DAL never (active) communicates with the BLL. However, the business objects (aka. entities, domain objects, ...) are stored within the BLL, so how to create objects which are not known within the DAL?

The following topics describe diferent solutions of how to get data from DAL into BLL and back.

DataTables

DataTables are one option to transport data from DAL to the BLL. The DAL gets the data from data store, transforms the data into a DataTable and returns the table to the calling BLL. The BLL gets the DataTable and transforms the data into business objects. For saving changes, the BLL transforms the business objects into a DataTable and calls a distinct save method from DAL.

The advantages of DataTable objects are, they support other rich features like complex sorting, searching and data binding. They are serializable and can be used for data exchange over network.

The disadvantages are, DataTables (especially if not using typed DataSets) the contained data are not guaranteed to be valid by type. Another issue of DataTables is the overhead, since DataTables objects are very rich objects. Last but not least, this approach requires a huge effort of quiet simple mapping code (= unhappy developer) within the BLL from a DataTable into business objects and back into a DataTable.

// ====================================================================
// DAL
public DataTable GetCustomers(object criteria) {
   using (IDataReader reader = CreateReader(criteria)) {
      // create results DataTable
      DataTable result = new DataTable();
      result.Columns.Add("Id", typeof(int));
      result.Columns.Add("Name", typeof(string));
      // fill the table
      while (reader.Read()) {
         DataRow row = result.NewRow();
         row["Id"] = reader["Id"];
         row["Name"] = reader["Name"];
         result.Rows.Add(row);
      }

      return result;
   }
}
// --------------------------------------------------------------------
public void SaveCustomers(DataTable data) { /* do save*/ }
// ====================================================================
// BLL
public void DoFoo() {
   // get the DataTable from DAL
   DataTable data = DAL.GetCustomers(null);
   // convert the DataTable into business objects
   IList<Customer> customers = ConvertDataTable(data);
   // --------------------------------------------
   // do business staff
   // --------------------------------------------
   // convert the business objects into a DataTable to return to DAL
   data = ConvertCustomers(customers);
   DAL.SaveCustomers(data);
}
// --------------------------------------------------------------------
private IList<Customer> ConvertDataTable(DataTable data) {
   // convert DataTable into business objects
   List<Customer> customers = new List<Customer>();
   foreach (var row in data.AsEnumerable()) {
      Customer cust = new Customer();
      cust.Id = (int)row["Id"];
      cust.Name = (string)row["Name"];
      customers.Add(cust);
   }
   return customers;
}
// --------------------------------------------------------------------
private DataTable ConvertCustomers(IEnumerable<Customer> customers) {
   // convert business objects into DataTable
   DataTable data = new DataTable();
   data.Columns.Add("Id", typeof(int));
   data.Columns.Add("Name", typeof(string));
   foreach (var cust in customers) {
      DataRow row = data.NewRow();
      row["Id"] = cust.Id;
      row["Name"] = cust.Name;
      data.Rows.Add(row);
   }
   return data;
}

Reflection

Another way to skin the cat is to use .NET (or Java) Reflection features to automate the mapping. The DAL gets the business object types from meta data (like a config file) and handles all the business object creation and field mapping dynamically.

The advantage of using reflection is, there is very less code to be written and it can be reused for many projects.

A disadvantage of reflection is bad runtime performance. Customizations, like differences between data source and business object structures are very difficult to be implemented and error investigations become awkward.

// ====================================================================
// DAL
// the type of the destination business object (usually, from config)
private Type EntityType { get { return typeof(Customer); } }
// --------------------------------------------------------------------
public IList GetCustomers(object criteria) {
   List<object> result = new List<object>();
   using (IDataReader reader = CreateReader(criteria)) {
      while (reader.Read()) {
         // get empty constructor
         object entity = EntityType.GetConstructor(Type.EmptyTypes).Invoke(new object[0]);
         // fill all DB data into object properties (with same name)
         for (int i = 0; i < reader.FieldCount; i++) {
            string name = reader.GetName(i);
            PropertyInfo prop = EntityType.GetProperty(name);
            prop.SetValue(entity, reader.GetValue(i), null);
         }
         result.Add(entity);
      }
   }
   return result;
}
// --------------------------------------------------------------------
public void SaveCustomers(IList data) { /* do save*/ }
// ====================================================================
// BLL
public void DoFoo() {
   // get result from DAL
   IList untyped = DAL.GetCustomers(null);
   // convert into typed list
   List<Customer> customers = untyped.Cast<Customer>().ToList();
   // --------------------------------------------
   // do business staff
   // --------------------------------------------
   DAL.SaveCustomers(customers);
}

Data Transfer Objects

Data Transfer Objects (DTOs) are a very clean and type safe solution for data exchange between the layers. They are usually defined within the DAL, will be filled there and mapped into the destination business objects in the BLL.

The advantages of DTOs are the strong typing and a a good maintainibility. Any kind of mapping customizations are simple to be done.

The disadvantage is the huge amount of simple mapping code (= unhappy developer) within the BLL to convert received DTOs into business objects and back into DTOs for saving concerns. The best solution to avoid writing all the mapping code is using some kind of souce code generation like a CASE tool.

// ====================================================================
// DAL
// a customer DTO object for data exchange
public class CustomerDTO {
   public int Id { get; set; }
   public string Name { get; set; }
}
// --------------------------------------------------------------------
public IList<CustomerDTO> GetCustomers(object criteria) {
   // create list of DTOs to exchange result data
   IList<CustomerDTO> result = new List<CustomerDTO>();
   using (IDataReader reader = CreateReader(criteria)) {
      while (reader.Read()) {
         // create DTOs
         CustomerDTO dto = new CustomerDTO();
         dto.Id = (int)reader["Id"];
         dto.Name = (string)reader["Name"];
         result.Add(dto);
      }
   }
   return result;
}
// --------------------------------------------------------------------
public void SaveCustomers(IEnumerable<CustomerDTO> dtos) { /* do save*/ }
// ====================================================================
// BLL
public void DoFoo() {
   // get DTOs
   IList<CustomerDTO> dtos = DAL.GetCustomers(null);
   // convert into business objects
   IList<Customer> customers = ConvertDTOs(dtos);
   // --------------------------------------------
   // do business staff
   // --------------------------------------------
   // convert back into DTOs to exchange with DAL
   dtos = ConvertCustomers(customers);
   DAL.SaveCustomers(dtos);
}
// --------------------------------------------------------------------
private IList<Customer> ConvertDTOs(IList<CustomerDTO> dtos) {
   // convert DTOs into business objects
   List<Customer> customers = new List<Customer>();
   foreach (var dto in dtos) {
      Customer cust = new Customer();
      cust.Id = dto.Id;
      cust.Name = dto.Name;
      customers.Add(cust);
   }
   return customers;
}
// --------------------------------------------------------------------
private IList<CustomerDTO> ConvertCustomers(IEnumerable<Customer> customers) {
   // convert business objects into DTOs
   IList<CustomerDTO> dtos = new List<CustomerDTO>();
   foreach (var cust in customers) {
      CustomerDTO dto = new CustomerDTO();
      dto.Id = cust.Id;
      dto.Name = cust.Name;
      dtos.Add(dto);
   }
   return dtos;
}

Interfaces as DTOs

A last solution (which is my personal favorite) is to create an additional assembly (usually called Project.Core.dll) which contains only the interfaces that describe the business objects data properties. This library is references from both layers, DAL and BLL. It enables the DAL to assign all data directly to the destination objects, that are implemented within the BLL. To create new instances of objects you can use either any kind of Dependency Injection (DI) framework like the Unity application block from Microsoft Enterprise Library (MEL) or something like an IFactory<T> interface, defined in the Core library, which is used as an Abstract Factory and implemented within the BLL. To inject the DAL with the abstract factory, you can use DI or static code.

The advantages of this approach are all the advantages of DTOs. In addition there is way less source code to be written, since no additional mapping into business objects is needed; the DTOs are covered by the interfaces, which are realized by the business objects.

The disadvantages of this solution is the additional library, conaining the interfaces and an additional architectual complexity due to the DI framework and/or the factory class(es).

// ====================================================================
// Core library
// interface for Customer to exchange data
public interface ICustomer {
   int Id { get; set; }
   string Name { get; set; }
}
// --------------------------------------------------------------------
// factory interface to create new business objects from DAL
public interface IFactory<T> {
   T Create();
}
// ====================================================================
// DAL
public class CustomerMapper<T> : SamplesMapperBase
    where T : ICustomer {
   // the factory; injected at runtime by BLL or any kind of DI
   public IFactory<T> Factory { get; set; }

   public IList<T> GetCustomers(object criteria) {
      IList<T> result = new List<T>();
      using (IDataReader reader = CreateReader(criteria)) {
         while (reader.Read()) {
            // create business object instance from factory
            T customer = Factory.Create();
            customer.Id = (int)reader["Id"];
            customer.Name = (string)reader["Name"];
            result.Add(customer);
         }
      }
      return result;
   }

   public void SaveCustomers(IList<T> data) { /* do save*/ }
}
// ====================================================================
// BLL
// simple sample implementation for customer factory
class CustomerFactory : IFactory<Customer> {
   public Customer Create() {
      return new Customer();
   }
}
// --------------------------------------------------------------------
// instance of customer mapper. The generic parameter specifies the
// business objects type
public CustomerMapper<Customer> DAL { get; set; }
// --------------------------------------------------------------------
public void DoFoo() {
   // directly get the business objects from DAL
   IList<Customer> customers = DAL.GetCustomers(null);
   // --------------------------------------------
   // do business staff
   // --------------------------------------------
   DAL.SaveCustomers(customers);
}

Assert Helper Class

2010-02-01T12:43:00.000-08:00

Today I'll share one of my simple but handy helper classes. Unit testing frameworks like Visual Studio Unit Tests or NUnit usually provide a validation class usually called "Assert" that provides a large set of static methods for test result validation. Without those frameworks the standard .NET way for runtime validation in debug sessions is System.Diagnostics.Debug.Asser(bool) method. Generally, this method works fine, but I prefer the more handy methods of unit testing frameworks like AreEqual(arg1, arg2) or IsNotNull(arg). Therefore, I created a simple static helper class called Assert which covers most of those test framework methods.

The class does nothing special, it just maps the standard Debug.Assert and Debug.Fail methods to a more sophisticated interface. Like .NET Debug.Assert method, all methods of the class are marked with System.Diagnostics.ConditionalAttribute and condition "DEBUG". So all method calls in project code become removed when compiling in "Release Mode".

Here's a little sample which shows how to use the class compared with Debug.Assert method.

// input parameter validation in private methods
Assert.NullArg(someParam, "someParam");
Debug.Assert(someParam != null, "someParam cannot be null");

// not null validation
List<int> list = new List<int>();
Assert.IsNotNull(list);
Debug.Assert(list != null);

// null validation
object nullValue = null;
Assert.IsNull(nullValue);
Debug.Assert(nullValue == null);

// equality validation of reference types (which usually
// don't overwrite the "==" operator)
string s1 = "foo", s2 = "foo";
Assert.AreEqual(s1, s2);
Debug.Assert(
   (s1 == null && s2 == null)
   || (s1 != null && s1.Equals(s2))
   );

// type validation
object o1 = "foo";
Assert.IsOfType(o1, typeof(string));
Debug.Assert(
   o1 != null 
   && typeof(string).IsAssignableFrom(o1.GetType()));

// not equal validation with error text
int num1 = 1, num2 = 2;
Assert.AreNotEqual(num1, num2, "{0} cannot be {1}", num1, num2);
Debug.Assert(num1 != num2,
             string.Format("{0} cannot be {1}", num1, num2));

And here's the complete code of the class.

using System;
using System.Diagnostics;
using System.Globalization;

namespace ClassLibrary1.Diagnostics {
   /// <summary>
   /// Helper class for debug assertions.
   /// </summary>
   /// <remarks>
   /// This class represents a wrapper of 
   /// <see cref="System.Diagnostics.Debug"/> class which provides a 
   /// richer interface and enables a simplified debug assertion.
   /// <p/>
   /// All methods are marked with 
   /// <see cref="System.Diagnostics.ConditionalAttribute"/> with 
   /// "DEBUG" condition string. This causes the method calls to be 
   /// removed from production source code when compiling in "Release" 
   /// mode.
   /// </remarks>
   public static class Assert {
      /// <summary>
      /// Asserts two specified argiments to be equal to each other.
      /// </summary>
      /// <param name="arg1">The first argument to be compared for 
      /// equality.</param>
      /// <param name="arg2">The first argument to be compared for 
      /// equality.</param>
      [Conditional("DEBUG")]
      public static void AreEqual(object arg1, object arg2) {
         if (arg1 == null && arg2 == null)
            return;
         if (arg1 == null)
            Debug.Assert(false);
         Debug.Assert(arg1.Equals(arg2));
      }

      /// <summary>
      /// Asserts two specified argiments to be equal to each other.
      /// </summary>
      /// <param name="arg1">The first argument to be compared for 
      /// equality.</param>
      /// <param name="arg2">The first argument to be compared for 
      /// equality.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void AreEqual(object arg1, object arg2, string message, 
                                  params object[] args) {
         if (arg1 == null && arg2 == null)
            return;
         if (arg1 == null)
            Debug.Assert(false, GetMessage(message, args));
         Debug.Assert(arg1.Equals(arg2), GetMessage(message, args));
      }

      /// <summary>
      /// Asserts two specified argiments to be equal to each other.
      /// </summary>
      /// <param name="arg1">The first argument to be compared for 
      /// equality.</param>
      /// <param name="arg2">The first argument to be compared for 
      /// equality.</param>
      [Conditional("DEBUG")]
      public static void AreEqual<T>(T arg1, T arg2) {
         if (arg1 == null && arg2 == null)
            return;
         if (arg1 == null)
            Debug.Assert(false);
         Debug.Assert(arg1.Equals(arg2));
      }

      /// <summary>
      /// Asserts two specified argiments to be equal to each other.
      /// </summary>
      /// <param name="arg1">The first argument to be compared for 
      /// equality.</param>
      /// <param name="arg2">The first argument to be compared for 
      /// equality.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void AreEqual<T>(T arg1, T arg2, string message, 
                                     params object[] args) {
         if (arg1 == null && arg2 == null)
            return;
         if (arg1 == null)
            Debug.Assert(false, GetMessage(message, args));
         Debug.Assert(arg1.Equals(arg2), GetMessage(message, args));
      }

      /// <summary>
      /// Asserts two specified argiments to be not equal to each other.
      /// </summary>
      /// <param name="arg1">The first argument to be compared for not 
      /// being equal.</param>
      /// <param name="arg2">The first argument to be compared for not 
      /// being equal.</param>
      [Conditional("DEBUG")]
      public static void AreNotEqual(object arg1, object arg2) {
         if (arg1 == null && arg2 == null)
            Debug.Assert(false);
         if (arg1 == null)
            return;
         Debug.Assert(!arg1.Equals(arg2));
      }

      /// <summary>
      /// Asserts two specified argiments to be not equal to each other.
      /// </summary>
      /// <param name="arg1">The first argument to be compared for not 
      /// being equal.</param>
      /// <param name="arg2">The first argument to be compared for not 
      /// being equal.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void AreNotEqual(object arg1, object arg2, string message, 
                                     params object[] args) {
         if (arg1 == null && arg2 == null)
            Debug.Assert(false, GetMessage(message, args));
         if (arg1 == null)
            return;
         Debug.Assert(!arg1.Equals(arg2), GetMessage(message, args));
      }

      /// <summary>
      /// Asserts two specified argiments to be not equal to each other.
      /// </summary>
      /// <param name="arg1">The first argument to be compared for not 
      /// being equal.</param>
      /// <param name="arg2">The first argument to be compared for not 
      /// being equal.</param>
      [Conditional("DEBUG")]
      public static void AreNotEqual<T>(T arg1, T arg2) {
         if (arg1 == null && arg2 == null)
            Debug.Assert(false);
         if (arg1 == null)
            return;
         Debug.Assert(!arg1.Equals(arg2));
      }

      /// <summary>
      /// Asserts two specified argiments to be not equal to each other.
      /// </summary>
      /// <param name="arg1">The first argument to be compared for not 
      /// being equal.</param>
      /// <param name="arg2">The first argument to be compared for not 
      /// being equal.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void AreNotEqual<T>(T arg1, T arg2, string message, 
                                        params object[] args) {
         if (arg1 == null && arg2 == null)
            Debug.Assert(false, GetMessage(message, args));
         if (arg1 == null)
            return;
         Debug.Assert(!arg1.Equals(arg2), GetMessage(message, args));
      }

      /// <summary>
      /// Generates a failing assertion.
      /// </summary>
      [Conditional("DEBUG")]
      public static void Fail() {
         Debug.Assert(false);
      }

      /// <summary>
      /// Generates a failing assertion.
      /// </summary>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void Fail(string message, params object[] args) {
         Debug.Assert(false, GetMessage(message, args));
      }

      /// <summary>
      /// Asserts a specified value not to be null.
      /// </summary>
      /// <param name="value">The value to be asserted not to be null.
      /// </param>
      [Conditional("DEBUG")]
      public static void IsNull(object value) {
         Debug.Assert(value == null);
      }

      /// <summary>
      /// Asserts a specified value not to be null.
      /// </summary>
      /// <param name="value">The value to be asserted not to be null.
      /// </param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      public static void IsNull(object value, string message, 
                                params object[] args) {
         Debug.Assert(value == null, GetMessage(message, args));
      }

      /// <summary>
      /// Asserts a specified value to be null.
      /// </summary>
      /// <param name="value">The value to be asserted to be null.
      /// </param>
      [Conditional("DEBUG")]
      public static void IsNotNull(object value) {
         Debug.Assert(value != null);
      }

      /// <summary>
      /// Asserts a specified value to be null.
      /// </summary>
      /// <param name="value">The value to be asserted to be null.
      /// </param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for 
      /// specified message.</param>
      [Conditional("DEBUG")]
      public static void IsNotNull(object value, string message, 
                                   params object[] args) {
         Debug.Assert(value != null, GetMessage(message, args));
      }

      /// <summary>
      /// Asserts a specified parameter to be null.
      /// </summary>
      /// <param name="value">The parameter to be asserted to be null.
      /// </param>
      /// <param name="paramName">The name of the parameter not to be 
      /// null.</param>
      /// <remarks>
      /// The difference between <see cref="NullArg"/> and IsNotNull
      /// is that <see cref="NullArg"/> should be used for parameters 
      /// provided to a method. IsNotNull should be used for other use 
      /// cases like method return values or conditional variable 
      /// initializations.
      /// </remarks>
      [Conditional("DEBUG")]
      public static void NullArg(object value, string paramName) {
         if (value == null)
            Debug.Fail(string.Concat(paramName + " cannot be null"));
      }

      /// <summary>
      /// Asserts a specified value to be assignable by a specified 
      /// type.
      /// </summary>
      /// <param name="value">The value to be asserted.</param>
      /// <param name="expectedType">The type to validate the specified 
      /// value to be assignable from.</param>
      [Conditional("DEBUG")]
      public static void IsOfType(object value, Type expectedType) {
         if (value == null)
            Debug.Assert(false);
         Debug.Assert(expectedType.IsAssignableFrom(value.GetType()));
      }

      /// <summary>
      /// Asserts a specified value to be assignable by a specified type.
      /// </summary>
      /// <param name="value">The value to be asserted.</param>
      /// <param name="expectedType">The type to validate the specified 
      /// value to be assignable from.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void IsOfType(object value, Type expectedType, 
                                  string message, params object[] args) {
         if (value == null)
            Debug.Assert(false, GetMessage(message, args));
         Debug.Assert(expectedType.IsAssignableFrom(value.GetType()), 
                      GetMessage(message, args));
      }

      /// <summary>
      /// Asserts a specified value not to be assignable by a specified 
      /// type.
      /// </summary>
      /// <param name="value">The value to be asserted.</param>
      /// <param name="expectedType">The type to validate the specified 
      /// value not to be assignable from.</param>
      [Conditional("DEBUG")]
      public static void IsNotOfType(object value, Type expectedType) {
         if (value == null)
            Debug.Assert(false);
         Debug.Assert(!expectedType.IsAssignableFrom(value.GetType()));
      }

      /// <summary>
      /// Asserts a specified value not to be assignable by a specified 
      /// type.
      /// </summary>
      /// <param name="value">The value to be asserted.</param>
      /// <param name="expectedType">The type to validate the specified 
      /// value not to be assignable from.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void IsNotOfType(object value, Type expectedType, 
                                     string message, params object[] args) {
         if (value == null)
            Debug.Assert(false, GetMessage(message, args));
         Debug.Assert(!expectedType.IsAssignableFrom(value.GetType()), 
                      GetMessage(message, args));
      }

      /// <summary>
      /// Asserts a target type to be assignable by a expected type.
      /// </summary>
      /// <param name="targetType">The target type to be assignable.
      /// </param>
      /// <param name="expectedType">The expected type to be assignable 
      /// by specified target type.</param>
      [Conditional("DEBUG")]
      public static void IsTypeOf(Type targetType, Type expectedType) {
         Debug.Assert(expectedType.IsAssignableFrom(targetType));
      }

      /// <summary>
      /// Asserts a target type to be assignable by a expected type.
      /// </summary>
      /// <param name="targetType">The target type to be assignable.
      /// </param>
      /// <param name="expectedType">The expected type to be assignable 
      /// by specified target type.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void IsTypeOf(Type targetType, Type expectedType, 
                                  string message, params object[] args) {
         Debug.Assert(expectedType.IsAssignableFrom(targetType), 
                      GetMessage(message, args));
      }

      /// <summary>
      /// Asserts a specified condition to be true.
      /// </summary>
      /// <param name="condition">The condition to be asserted for being 
      /// true.</param>
      [Conditional("DEBUG")]
      public static void IsTrue(bool condition) {
         Debug.Assert(condition);
      }

      /// <summary>
      /// Asserts a specified condition to be true.
      /// </summary>
      /// <param name="condition">The condition to be asserted for being 
      /// true.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void IsTrue(bool condition, string message, 
                                params object[] args) {
         Debug.Assert(condition, GetMessage(message, args));
      }

      /// <summary>
      /// Asserts a specified condition not to be true.
      /// </summary>
      /// <param name="condition">The condition to be asserted for not 
      /// being true.</param>
      [Conditional("DEBUG")]
      public static void IsFalse(bool condition) {
         Debug.Assert(!condition);
      }

      /// <summary>
      /// Asserts a specified condition not to be true.
      /// </summary>
      /// <param name="condition">The condition to be asserted for not 
      /// being true.</param>
      /// <param name="message">The message to be shown when assertion 
      /// fails.</param>
      /// <param name="args">Optional formatting arguments for specified 
      /// message.</param>
      [Conditional("DEBUG")]
      public static void IsFalse(bool condition, string message, 
                                 params object[] args) {
         Debug.Assert(!condition, GetMessage(message, args));
      }

      private static string GetMessage(string message, 
                                       params object[] args) {
         if (args != null && args.Length != 0)
            return string.Format(CultureInfo.InvariantCulture, message, args);
         return message;
      }
   }
}

.NET 4.0 Lazy Class

2010-01-23T06:00:00.000-08:00

.NET 4.0 and Visual Studio 2010 is raring to go. Current release date is somewhere in May 2010.

One of the new features of new framework is the Lazy<T> class which supports lazy initialization of objects.

As I wrote in some of my previous blogs I'm not best friend of (wrong used) Lazy Loading. However, there are some use cases where this is a very powerful feature. Therefore I use the phrase "Lazy Initialization" instead of "Lazy Loading" in below samples for Lazy<T> class. "Lazy Loading" usually implies automatic loading of reference data in a Data Access Layer. When I speak about "Lazy Initialization" this implies a lazy initialization of some (rare) object properties which require many or unmanaged system resources.

The following sample class shows a common case where lazy initialization becomes a very useful and often required implementation.

sealed class Product1 : IProduct {
   // -------------------------------------------------------------------
   public Product1(string name, Stream picture) {
      Name = name;
      Picture = picture;
   }
   // -------------------------------------------------------------------
   public string Name { get; set; }
   public Stream Picture { get; set; }
}

The "Picture" property of "Product" class is usually very rare needed in a whole system and it works with (probably) unmanaged resources which might cause a very high memory allocation. Always to initialize the picture of a product which might only be required in a web front-end or a sales application would cause a huge I/O traffic which is not required in system directions like billing or call-center order management.

Lazy<T> Class

The Lazy<T> class represents a wrapper class to handle those kind of property initialization.

As a quote from MSDN:

Although you can write your own code to perform lazy initialization, we recommend that you use Lazy instead. Lazy and its related types also support thread-safety and provide a consistent exception propagation policy.

If a system already works with its custom implemented lazy initialization, I'd advice to stay with this. If lazy initialization is a new requirement, we shouldn't run into evil Not Invented Here software project anti-pattern ;-) and use the existing implementation.

Here's a list of methods and constructors provided by Lazy<T> class (descriptions from MSDN):

ctor(valueFactory Func<T>): Initializes a new instance of the Lazy(T) class. When lazy initialization occurs, the specified initialization function is used.
ctor(valueFactory Func<T>, isThreadSafe bool): Initializes a new instance of the Lazy(T) class. When lazy initialization occurs, the specified initialization function and initialization mode are used.
Value: Gets a value that indicates whether lazy initialization has occurred for this Lazy(T) instance.
IsValueCreated: Gets the lazily initialized value of the current Lazy(T) instance.

Lazy<T> class provides some more methods and constructors, but the above seem to be the most important in my opinion.

As a side note for isThreadSafe parameter, a second quote from MSDN:

Some Lazy constructor overloads have a Boolean parameter named isThreadSafe that is used to specify whether the Value() property will be accessed from multiple threads. If you intend to access the property from just one thread, pass in false to obtain a modest performance benefit. If you intend to access the property from multiple threads, pass in true to instruct the Lazy instance to correctly handle race conditions in which one thread throws an exception at initialization time. If you use a constructor that does not have the isThreadSafe parameter, the value defaults to true.

If an application never works with multiple threads, isThreadSafe parameter with value false causes a faster execution due to no thread locking overhead.

How to Use

First, the "Product" class needs to be modified to support lazy initialization. I moved the stream creation into an external factory class

// ======================================================================
sealed class Product2 : IProduct {
   // -------------------------------------------------------------------
   public Product2(string name, string fileName) {
      Name = name;
      _picture = new Lazy<Stream>(() => PictureFactory.GetPicture(fileName), true);
   }
   // -------------------------------------------------------------------
   public string Name { get; set; }
   public Stream Picture {
      get { return _picture.Value; }
      set { _picture = new Lazy<Stream>(() => value, true); }
   }
   Lazy<Stream> _picture;
}
// ======================================================================
static class PictureFactory {
   // -------------------------------------------------------------------
   // get picture from disc
   public static Stream GetPicture(string fileName) {
      Console.WriteLine("## Lazy Initializing Image ##");
      if (string.IsNullOrEmpty(fileName))
         fileName = @"C:\Temp\not_available.jpg";
      return new FileStream(fileName, FileMode.Open, FileAccess.Read);
   }
}

Instead of a direct member field mapping of "Picture" property, "Product" class now works with a Lazy<T> wrapper which only initializes resources when it is requested. This implementation uses the isThreadSafe parameter with value true since we will reuse it below in a multi-threaded sample.

The "fileName" parameter in constructor and factory class to initialize the picture stream is just an example. If no file name was provided, the factory returns the stream of a "not_available.jpg" as default picture (see Special Case pattern).

Notice the Console.WriteLine within the factory class. This will show when data become initialized.

The following sample creates some products and simulates a rendering of product pictures into console.

// ======================================================================
class Program {
   // -------------------------------------------------------------------
   static void Main(string[] args) {
      Console.WriteLine("Creating products");
      List<IProduct> products = new List<IProduct>{
         new Product2("Lord of the Rings", null),
         new Product2("Avatar", @"C:\Temp\avatar.jpg"),
      };

      Console.WriteLine("Loop through products and print picture");
      products.ForEach(p => ConsolePictureRenderer.PrintPicture(p));
      products.ForEach(p => ConsolePictureRenderer.PrintPicture(p));
      Console.ReadKey();
   }
}
// ======================================================================
static class ConsolePictureRenderer {
   // -------------------------------------------------------------------
   public static void PrintPicture(IProduct product) {
      Console.WriteLine();
      // simulate picture rendering by a simple console output
      Console.WriteLine("Picture of {0}", product.Name);
   }
}

As shown in console output, picture data become initialized when it becomes accessed. In addition, picture factory is called only once for each instance of a product.

Multi Threaded

As written above, Lazy<T> class represents a thread-safe implementation of a lazy initialization class. That means, picture factory class does not need to implement a custom thread locking. It's automatically handled in Lazy<T> class.

As a last snippet, we reuse our "Product", "ProductFactory" and "ConsolePictureRenderer" classes from previous sample and change "Main" method to access the product pictures in a multi-threaded environment.

// ======================================================================
class Program {
   // -------------------------------------------------------------------
   static void Main(string[] args) {
      Console.WriteLine("Creating products");
      List<IProduct> products = new List<IProduct>{
         new Product2("Lord of the Rings", null),
         new Product2("Avatar", @"C:\Temp\test.jpg"),
      };

      Console.WriteLine("Loop through products and print picture");
      for (int i = 0; i < 4; i++) {
         ThreadPool.QueueUserWorkItem(foo =>
            products.ForEach(p =>
               ConsolePictureRenderer.PrintPicture(p)));
      }
      Console.ReadKey();
   }
}

Now, products and their pictures become accessed by four parallel threads. A second look into console output shows, all pictures are still loaded only once, since "Product" class initializes Lazy<T> class with isThreadSafe parameter with values true.

Conclusion

.NET 4.0 Lazy<T> class is a nice helper when working with large and/or unmanaged resources. Especially its thread-safe implementation increases the usability in a todays multi-processor environment.

Concatenation of Text and Binary Data in T-SQL

2010-01-18T14:20:00.001-08:00

Some month ago, I blogged about "String Split Functions" in SQL Server, today I'll write about the inverse part of this topic; The concatenation of text and binary data of several rows, using T-SQL (and the available extensions). As we'll see, just like string splitting, there are several ways to skin the cat.

Topics covered in this blog entry:

Classical, recursive @variable assignment within an SELECT statement
FOR XML
A WHILE loop and utilizing the BLOB data types WRITE() method
SQLCLR

Topics not covered in this blog entry:

Recursive @variable assignment within a CURSOR/WHILE-loop. This produces the same memory management as the SELECT based version but in a non-set-based manner.
SQLCLR as a aggregate function. Reason will be described later.

Test Data
Since we'll speak about performance in below topics, here are two simple tables containing two different kinds of data to be concatenated.

The below table contains 200 rows with 180,000 characters and bytes per row. This means 36,000,000 characters/bytes will be concatenated.

CREATE TABLE LargeData (
   Id INT NOT NULL IDENTITY(1,1)
      PRIMARY KEY CLUSTERED
   ,Txt VARCHAR(MAX)
   ,Bin VARBINARY(MAX)
);

-- fill the table with 200 rows 180,000 characters per row
WITH Numbers AS (
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) Num
   FROM master.sys.all_columns
)
INSERT INTO LargeData
   SELECT TOP(200)
      -- text data
      REPLICATE(CONVERT(VARCHAR(MAX), NEWID()), 5000)
      -- binary data
      ,CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARCHAR(MAX), NEWID()), 5000))
   FROM Numbers;

As a second kind of source data we'll work with a table containing 50,000 rows but only 16 characters and bytes per row. This means 800,000 characters/bytes to be concatenated.

CREATE TABLE ManyData (
   Id INT NOT NULL IDENTITY(1,1)
      PRIMARY KEY CLUSTERED
   ,Txt VARCHAR(16)
   ,Bin BINARY(16)
);

-- fill the table with 200 rows 180,000 characters per row
WITH Numbers AS (
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) Num
   FROM master.sys.all_columns c1, master.sys.all_columns c2
)
INSERT INTO ManyData
   SELECT TOP(50000)
      -- text data
      LEFT(CONVERT(VARCHAR(36), NEWID()), 16)
      -- binary data
      ,CONVERT(VARBINARY(MAX), NEWID())
   FROM Numbers;

For sake of brevity, I'll provide only one sample statement in the following topics. You can find the complete SQL script as attachment at the end of this blog entry.

Classical @variable Assignment
The classical approach to concatenate data from rows into one column (or variable in our case) is an SELECT statement with a recursive variable assignment.

DECLARE 
   @txt VARCHAR(MAX) = ''
   ,@bin VARBINARY(MAX) = 0x;

SELECT 
      @txt += TextColumn
      ,@bin += BinaryColumn
   FROM AnyTable;

(The “+=”-Operator is a new feature of SQL Server 2008. If you work on SQL Server 2005, just replace it with a variable reassignment like “@txt = @txt + TextColumn”. The “+=”-Operator does exactly the same; it’s just a simplified way to write T-SQL.)

This is generally a good solution due to three facts. It’s easy to use. It’s a set-based solution. It’s fast for less data to be concatenated. On the other hand this kind of concatenation runs into problems if you have to handle many or large data, because all data are copied several times.

A look into Profiler shows a huge resource usage, especially for concatenating of many rows to be handled. A concatenation of only 800,000 bytes or characters caused over 180,000,000 read and 1,400,000 write operations.

FOR XML
This is a kind of tricking another build-in feature of SQL Server 2005 (and further versions). FOR XML is generally made to create XML output from a SELECT statement. However, it can also be used to concatenate data without any XML-style.

I learned this trick on SQLServerCentral.com. One article that shows the usage is Jeff Moden's article about Performance Tuning: Concatenation Functions and Some Tuning Myths. (Special thanks to Bluedog67 for this link :-) .)

Another good resource in web for T-SQL is Mladen Prajdić's page where this trick is shown in "SQL Server 2005: Concat values XML Style".

DECLARE @txt VARCHAR(MAX);
SELECT @txt = (SELECT Txt FROM LargeData FOR XML PATH(''));

Literally the text to be provided within the PATH(‘’) is made to provide the name of a parent XML element used for the output data. If you provide an empty string you get a concatenated text of all data returned by the query.

This is a very powerful solution to concatenate text data. Unfortunately it has to be used carefully if source data are unknown. Since this method works with XML, any kind of functional XML characters like “>” or “&” become masked by this method. Binary data cannot be concatenated by XML PATH().

A look into profiler shows a great resource usage and duration.

WRITE Method
SQL Server 2005 introduced the new BLOB data types VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX). As well as previous BLOB data types (TEXT, NTEXT and IMAGE), also the old BLOB functions like TEXTPTR or UPDATETEXT became marked as deprecated. As a replacement for those functions, SQL Server 2005 introduced a new method called WRITE() which is a data type method - not a usual T-SQL function. This means, it is not called to get a BLOB as a parameter but it is called on a BLOB data type, like methods in a object-oriented programming language.

DECLARE 
   @all VARCHAR(MAX) = ''
   ,@buffer VARCHAR(MAX)
   ,@position INT = 0
   ,@len INT
   ,@i INT = 0
   ,@max INT = (SELECT MAX(Id) FROM LargeData);

WHILE (@i < @max)
BEGIN
   -- get the next row value
   SELECT TOP(1)
         @i = Id
         ,@buffer = Txt
         ,@len = LEN(Txt)
      FROM LargeData
      WHERE Id > @i
      ORDER BY Id;

   -- add the text to the existing text
   SET @all.WRITE(@buffer, @position, @len);
   SET @position += @len;
END;

Unfortunately, there seem to be no way (I know?) to use the WRITE method in a set-based way. It is possible to use it within an UPDATE statement but it is called only once and not for each row of the source data.

A look into Profiler shows, the WRITE method much faster than the classical approach of variable reassignment. It's two times faster with ten times less resource usage for larger data and it's 60% faster with about four times less resource usage with many data to be concatenated.

However, is there a way to get this WRITE method faster than this? As I already wrote above, it can also be used in an UPDATE statement, so this should also be tried with a temp table.

-- Large Binary
DECLARE 
   @all VARBINARY(MAX) = 0x
   ,@i INT = 0
   ,@max INT = (SELECT MAX(Id) FROM LargeData);

DECLARE @buffer TABLE (Bin VARBINARY(MAX));

INSERT INTO @buffer
   SELECT 0x;

WHILE (@i < @max)
BEGIN
   -- get the next row value
   WITH cte AS (
      SELECT TOP(1)
         Id
         ,Bin
      FROM LargeData
      WHERE Id > @i
      ORDER BY Id
   )
   UPDATE b SET
         -- get the next id
         @i = cte.Id
         -- write into temp table
         ,Bin.WRITE(cte.Bin, DATALENGTH(b.Bin), DATALENGTH(cte.Bin))
      FROM @buffer b CROSS JOIN cte
END;
SELECT @all = Bin FROM @buffer;

Sure, the SQL statement is a bit more complicated since we have to use a common table expression to get the (ORDERED) next row of source data, but what's about performance?

A look into Profiler shows, execution for many, small data didn't really change. On the other hand concatenation of large data became about 80% faster with only 30% resource usage. So this - more complicated - way to use the WRITE method might be a good investment if large data manipulation is needed.

SQLCLR
Well, if you don't consider to ever enable CLR in SQL Server this blog ends here :-).

Generally, SQLCLR provides two different ways to perform this concatenation.

The first way would be a user-defined aggregate function which gets the text or binary data to be concatenated. This solution would provide a very handy way to be used like this.

SELECT CONCATENATE_TEXT(Txt) FROM ManyData;

Unfortunately, this solution would require to set the resulting assembly to unsafe mode and server to be configured at TRUSTWORTHY level. Why? All SQLCLR functions (procedures, functions, ...) are static methods on a wrapper .NET class and aggregate functions are called again and again for each row to be proceeded. Within this function CLR only get the current value to be handled, what means CLR would have to remember the previous data anywhere in a static (shared) variable. Due to concurrency calls this would have to handled a custom thread-locking to store and get information. I find SQLCLR provides some powerful extensions but an unsafe assembly tastes bad. Due to this fact, I'll not provide this solution for now. (If anybody is interested in this, please leave a short note and I'll provide this in another blog entry.)

The second way to handle the concatenation is a simple user-defined scalar function which gets the SQL query to be executed. This requires a dynamic SQL approach to use the function, though, it is build-in thread safe because it doesn't require any class member variables.

Here's a C# method to concatenate text data.

[Microsoft.SqlServer.Server.SqlFunction(
   DataAccess=DataAccessKind.Read,
   IsDeterministic=false,
   IsPrecise=true,
   SystemDataAccess=SystemDataAccessKind.None
   )]
public static SqlChars ConcatText (
   // the sql statement to be executed
   [SqlFacet(IsFixedLength=false ,IsNullable=false ,MaxSize=-1)]
   SqlString sqlIn
   ) {

   // create a System.Text.StringBuilder to push all text data into.
   StringBuilder buffer = new StringBuilder();

   // connect to database and execute the provided SQL statement
   using (SqlConnection cn = new SqlConnection("Context Connection=true")) {
      cn.Open();
      using (SqlCommand cmd = new SqlCommand(sqlIn.Value, cn))
      using (SqlDataReader reader = cmd.ExecuteReader()) {
         while (reader.Read()) {
            // get the next string value from reader and append it to buffer
            string str = (string)reader[0];
            buffer.Append(str);
         }
      }
   }

   // return the concatenated string
   return new SqlChars(buffer.ToString());
}

Here's a C# method to concatenate binary data.

[Microsoft.SqlServer.Server.SqlFunction(
      DataAccess=DataAccessKind.Read,
      IsDeterministic=true,
      IsPrecise=true,
      SystemDataAccess=SystemDataAccessKind.None
      )]
   public static SqlBytes ConcatBinary(
      // the sql statement to be executed
      [SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = -1)]
      SqlString sqlIn
      ) {

      // create a System.IO.MemoryStream to push all text data into
      MemoryStream stream = new MemoryStream();

      // connect to database and execute the provided SQL statement
      using (SqlConnection cn = new SqlConnection("Context Connection=true")) {
         cn.Open();
         using (SqlCommand cmd = new SqlCommand(sqlIn.Value, cn))
         using (SqlDataReader reader = cmd.ExecuteReader()) {
            while (reader.Read()) {
               // get the next binary value from reader and append it to buffer
               byte[] data = (byte[])reader[0];
               stream.Write(data, 0, data.Length);
            }
         }
      }
      // important! reset the stream position to the beginning
      stream.Seek(0, SeekOrigin.Begin);

      return new SqlBytes(stream);
   }

The call from T-SQL is equal to any other scalar function call.

DECLARE @bin VARBINARY(MAX);
SELECT @bin = dbo.ConcatBinary('SELECT Bin FROM LargeData');

Due to the fact that SQLCLR (.NET in general) does not know ANSI text like VARCHAR all non-Unicode data have to be translated into Unicode and back into ANSI. Therefore I extended this tests with two additional executions into NVARCHAR variables instead of VARCHAR.

A look into Profiler shows, only XML transformation is slightly faster than SQLCLR, without any restrictions for functional characters and it fully supports binary concatenation. In case of large text data concatenation into a NVARCHAR variable SQLCLR is even slightly faster than XML.

Conclusion
Classical concatenation by recursive variable assignment is a nice way to handle less, small data. In case of larger or many data it appears to be not the best solution.

New WRITE method is a nice extension for SQL Server, especially when working with larger data.

FOR XML PATH('') is a very powerful trick for text data concatenation. Use it (really!), as long as you keep in mind, it is a kind of a hack and should be used carefully.

We all (including me!) know, there are very less useful ways to use SQLCLR. But concatenation of data as well as string splitting are some of those and might be considered if this is a common task in a SQL Server 2005/2008 environment.

Attachments

Post Scriptum
Thanks again to Bluedog67, who showed me another blog about string concatenation by Anith Sen at:
Concatenating Row Values in Transact-SQL
It also covers the CLR aggregate function.

... didn't know this blog entry before...

Bug in SQL Profiler when working Table-Valued Parameters and DATETIME

2010-01-14T10:07:00.000-08:00

Just provided a new bug note at Microsoft® SQL Server Connect:
Profiler shows DATETIME for TVP as DATETIME2

If you work with table-valued types and table-valued parameters provided by a front-end application you get an invalid SQL Profiler output for DATETIME columns. Profiler shows DATETIME column inserts as DATETIME2 values (like '2010-01-14 18:38:09.2100000'). The problem with this output are the nano-second digits which cannot be converted to DATETIME.

For anybody who is interested, here a short description how to reproduce.

Create a new table-valued type in SQL Server.

CREATE TYPE FooType AS TABLE (
   Dt DATETIME
);

And a little C# application that uses this table-valued type by executing an SqlCommand.

class TestEnumerator : IEnumerable {
   public IEnumerator GetEnumerator() {
      // create meta data for the table-valued parameter
      SqlMetaData[] meta = new SqlMetaData[] {
         new SqlMetaData("Dt", SqlDbType.DateTime)
      };

      // create a single record to be returned as enumerator
      SqlDataRecord record = new SqlDataRecord(meta);
      record.SetDateTime(0, DateTime.Now);
      // return the record as enumerator value
      yield return record;
   }

   IEnumerator IEnumerable.GetEnumerator() {
      return this.GetEnumerator();
   }
}
class Program {
   static void Main(string[] args) {
      string cnStr = Properties.Settings.Default.SandboxConnectionString;

      // connect to the database and create a new sql command that simply 
      // returns the count of rows within a table-valued parameter
      using (SqlConnection cn = new SqlConnection(cnStr))
      using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM @t", cn)) {
         cn.Open();

         // create and configure the table-valued parameter
         SqlParameter p = new SqlParameter("@t", SqlDbType.Structured);
         // specify the table-valued type
         p.TypeName = "FooType";
         p.Value = new TestEnumerator();
         cmd.Parameters.Add(p);

         // execute the command
         object result = cmd.ExecuteScalar();
      }
   }
}

Now start SQL Profiler and enable the "RPC:Completed" event. Start the C# application and you will get the following trace output:

declare @p3 dbo.FooType
insert into @p3 values('2010-01-14 18:38:09.2100000')

exec sp_executesql N'SELECT COUNT(*) FROM @t',N'@t FooType READONLY',@t=@p3

The error occurs if you try to run this SQL in SSMS for debugging.

Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

How to Enable First Chance Exceptions in Visual Studio

2010-01-11T06:55:00.000-08:00

This blog entry explains how to enable first chance exceptions in Visual Studio. I explained this several people, so this might be a basic configuration but there are many people who apparently do not yet know.

First chance exceptions means that Visual Studio immediately stops when any kind of exception (e.g. NullReferenceException or InvalidOperationException) occurs. Often first chance exceptions are disabled in Visual Studio by default installation. This causes exceptions that can be caught by a catch-block, wherever in call stack, will not stop the debugger at the point of exception creation. The debugger only stops if an exception cannot be handled by any catch-block. So a developer often does not see where the exception was created and thrown.

Depending on your installation of Visual Studio you first have to add an additional command to the menu of your IDE. Therefore select "Tools" menu and click "Customize...".

The "Customize" dialog opens where you can select additional items to be added to your menu or toolbars. Activate the "Commands" tab and select "Debug" in the left list with title "Categories". Select "Exceptions..." item from the right list with title "Commands" and drag it into your "Debug" menu on top of Visual Studio.

Now you can close the Customize dialog by hitting the "Close" button.

Go to menu and select the newly added menu item "Exceptions..." in "Debug" top menu. The new "Exceptions" dialog appears where you can configure the exception behavior of Visual Studio debug sessions. Within the shown list select "Common Language Runtime Exceptions" and activate the "Thrown" check-box on the right side.

As you noticed, you also can expand each exception category to select any special kind of exception you want to enable or disable for first chance exception handling. This is useful for some special debugging sessions, but most times its best to force Visual Studio to stop at any kind of thrown exception.

To close the dialog use the "Ok" button.

Now you can start debugging your project and Visual Studio stops as soon as any kind of exception occurs.

Happy coding :-)

O/R-Mapper - Enemy Mine

2010-01-09T07:58:00.000-08:00

Those days, I here many discussions pro and contra O/R-Mappers (ORMs). Jack Corbett, a professional SQL guy I really regard speaks at SQLSaturday#32 about "Why Should I Use Stored Procedures?" in relation to ORM tools. I just had an own, nice discussion with several guys at:
Sind O/R-Mapper potentiell ein schleichender Tod? (a German C# developer forum)
Try of a English translation:
Are O/R-Mapper a potential sneaking dead?

The quint essence of this discussion was. Most, experienced developers generally like the idea of those ORMs but currently they don't trust them in larger projects.

In this blog entry I'll try to address my main problems with O/R-Mappers.

The Holy Cow for Consumers

Often I saw developers who forgot the "R" (relational) within the OR-Mapper. It's a mapping tool to bring relational data into an domain object model. However, there is still a relational database system behind the curtain. It's very very very important to never forget this.

What does this mean?

The first thing is the query support. ORMs usually provide an object oriented way to handle database queries. This is a nice feature (if correct implemented by the ORM), but it's impossible for any tool to cover the whole power of RDBMS specific SQL. Those queries are usually fine for simple queries like "give me a customer by her code" or "give me the not finished orders of a specific customer". That means queries on direct fields on the base table of a domain object and queries over one or maybe two related tables. As soon as you hit this boundary, you should consider to implement a custom SQL query (or procedure). Especially things like "LEFT JOINs", "OR" or the disingenuous "IN" should always be done by custom SQL, since it directly shows potential problems - as execution plans in SQL Server.

The second, most times wrong used feature of O/R-Mappers is lazy loading. There are some, very rare(!) business cases where lazy loading is a good thing and should be used. Whenever I use lazy loading I have to keep in mind that any access to a not yet loaded relation causes an own SQL query fired to the database server. Using lazy loading in form of always just load the root object and let the mapper load every information when I need it quickly ends up in hundreds or thousands or more single queries stressing the server. Let me quote Martin Fowler who calls this a "ripple loading".

It's important to always keep in mind, an O/R-Mapper is a tool and nothing more, really. It's a tool to automate some boring work like simple 1:1 mapping of relational data into a domain object and automate some basic queries. Depending on the technology which is covered by the tool - relational databases - every ORM is always a weak tool. That doesn't mean that developers of ORMs doing a bad job! The weakness depends on the fact that an ORM always can only cover a very small and simple part of the SQL possibilities of a todays RDBMS. They especially cannot handle a professional query tuning like analyzing execution plans which may point a completely different query for same result on different databases - sometimes even for the same RDBMS.

The Holy Cow Mislead by the Publishers

Sadly, some of the problems addressed above are often caused on the way how those tools are promoted (or implemented). Every common O/R-Mapper is promoted as "the all-in-one". However, I think an ORM will never be able to handle every special case - from database and domain model side.

As I pointed the "R" as the most common potential problem while consuming an ORM; the "M" (mapper) is my main issue of todays ORMs. Sure, the mapping is the main part of an O/R-Mapper. It maps relational data into a domain object model and vice versa. Unfortunately, all mappers I know encapsulate this part of the tool as a black-box. My problem with this encapsulation is an build-in tight coupling between database and domain model which is restricted to the mapping strength of the used tool. I think the only restriction of modelling should be the experiences of the developers, DBAs and architects.

What does this mean?

In the domain model, this tight coupling sometimes disables a more normalized form of objects than it is available by the database. I don't speak about a denormalized database but often an domain object model uses more than one object for one table. While a "Orders" table might contain customer information like "CustomerOrderRef" or "RequestedDeliveryDate", the "Order" domain object can contain another "CustomerInfo" reference which holds all the customer specific information. As long as those related objects are mutable (what means they provider setter methods or properties), abstracting ORMs like NHibernate or Entity Framework are able to handle the mapping. However, if there is a mapping to a immutable value object they hit the wall. A good sample (again by Martin Fowler) for a mapping of columns to a immutable object is a Money-object (don't mis with the SQL Server data type MONEY). Money usually has two different information an Amount and a Currency. Usually a table usually contains an "Amount" column and a "CurrencyId" column, but those non normalized information seem to be not the best design in a domain model, especially if I want to be able to calculate with those money information. I'd be glad if I could inject the existing mapping with some custom work without having to provide the database columns in my domain objects.

On the other hand the database can be more normalized than a domain model, what is more common known situation. Again, ORMs like NHibernate and Entity Framework support simple denormalization of 1:1 table relations into a domain model - with some restrictions. A good sample where ORMs are impossible to handle the mapping are EAV/CR tables. Generally EAV/CR is not a common database design today but there are good reasons to use them for some special cases. If I think about a software of a sports club which holds describing information for each member. Storing all playing skills of all members for any kind of sport. This seems to be almost impossible in a usual relational database design. At this point I'd like to be able to map this very special form of data into my domain object model without leaving the whole universe of the rest of my model by defining my "Member" as POCO (or POJO in case of Java) which is not part of my mapper.

Last but not least, many ORMs access way to may information what causes a huge, not needed I/O overhead. An order object and table might contain several information which are needed for very different parts of a system like customer, shipping and billing information. ORMs either always load and write all the information or work with a property/column based lazy loading - what usually becomes the hardest kind of ripple loading. There are some main information like the table identity and an "OrderNumber" which are usually needed for almost any part of the system but I don't need all the other information whenever I load an order from the database.

My Wishes for the Future

From the consumer side, I hope developers come back to the mindset that an O/R-Mapper is a (weak) tool and nothing more. Whenever they create a new ORM query object, reconsider if the query that will be created fits the possibilities of the mapper.

From the publisher side, I don't look for a mapper that handles mapping of value objects or EAV/CR tables. These have just have been samples for boundaries of mappers - maybe there is even a mapper which supports one of those features. I'm looking for an O/R-Mapper which knows its limits and allows a tailor made mapping without the must of leaving the whole universe of the mapper for change tracking, transaction handling and other useful features.

A current Spare Time Project

I'm thinking several month about O/R-Mappers, their strengths and their weaknesses. I tried several mappers and after some short euphorias I hit back the ground due to existing restrictions which could not be handled without some ugly workarounds. About 2 month ago, I started to think (and implement) a own version of an ORM. After more than 150 classes and the first tests it seems to fit my requirements but who knows... Maybe it dies while implementation phase. Maybe it works and I'll use it in one of my future projects. Maybe I'll publish it. If I publish it, maybe it becomes yet another dead born O/R-Mapper in the internet, maybe there will be some guys and/or gals who try and hate it, maybe there will be some guys and/or gals who try and enjoy it.

Generic C# RingBuffer

2010-01-08T14:45:00.000-08:00

Today I'll share an almost coding only blog entry.

A RingBuffer (aka. Circular Buffer) is a kind of a collection with a fixed maximal count of items to be stored within. After it reaches its capacity, the next insertion of a new items causes the first item to be overwritten.

These kind of objects is especially useful when working with large amounts of data where it is required to look back at some previous items (e.g. for validation). A RingBuffer provides this functionality by avoiding to hold all the data ever stored within.

This can be imagined like a window when driving by train. We see the next tree and the previous some, but we don't see all the trees since the journey started.

Before I wrote this blog entry, I searched the .NET framework and some common web sources. I have to say, I was wondering that I did not find another (working) .NET implementation of a RingBuffer.

Here is my version of a generic RingBuffer. Source file and unit tests are attached at the bottom of this post.

/*
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Diagnostics;
*/


/// <summary>
/// Represents a fixted length ring buffer to store a specified maximal count of items within.
/// </summary>
/// <typeparam name="T">The generic type of the items stored within the ring buffer.</typeparam>
[DebuggerDisplay("Count = {Count}")]
public class RingBuffer<T> : IList<T>, ICollection<T>, 
                             IEnumerable<T>, IEnumerable {
   /// <summary>
   /// Creates a new instance of a <see cref="RingBuffer&lt;T&gt;"/> with a 
   /// specified cache size.
   /// </summary>
   /// <param name="capacity">The maximal count of items to be stored within 
   /// the ring buffer.</param>
   public RingBuffer(int capacity) {
      // validate capacity
      if (capacity <= 0)
         throw new ArgumentException("Must be greater than zero", "capacity");
      // set capacity and init the cache
      Capacity = capacity;
      _buffer = new T[capacity];
   }

   /// <summary>
   /// the internal buffer
   /// </summary>
   T[] _buffer;
   /// <summary>
   /// The all-over position within the ring buffer. The position 
   /// increases continously by adding new items to the buffer. This 
   /// value is needed to calculate the current relative position within the 
   /// buffer.
   /// </summary>
   int _position;
   /// <summary>
   /// The current version of the buffer, this is required for a correct 
   /// exception handling while enumerating over the items of the buffer.
   /// </summary>
   long _version;

   /// <summary>
   /// Gets or sets an item for a specified position within the ring buffer.
   /// </summary>
   /// <param name="index">The position to get or set an item.</param>
   /// <returns>The fond item at the specified position within the ring buffer.
   /// </returns>
   /// <exception cref="IndexOutOfRangeException"></exception>
   public T this[int index] {
      get {
         // validate the index
         if (index < 0 || index >= Count)
            throw new IndexOutOfRangeException();
         // calculate the relative position within the rolling base array
         int index2 = (_position - Count + index) % Capacity;
         return _buffer[index2]; 
      }
      set { Insert(index, value); }
   }

   /// <summary>
   /// Gets the maximal count of items within the ring buffer.
   /// </summary>
   public int Capacity { get; private set; }
   /// <summary>
   /// Get the current count of items within the ring buffer.
   /// </summary>
   public int Count { get; private set; }
   
   /// <summary>
   /// Adds a new item to the buffer.
   /// </summary>
   /// <param name="item">The item to be added to the buffer.</param>
   public void Add(T item) {
      // add a new item to the current relative position within the
      // buffer and increase the position
      _buffer[_position++ % Capacity] = item;
      // increase the count if capacity is not yet reached
      if (Count < Capacity) Count++;
      // buffer changed; next version
      _version++;
   }

   /// <summary>
   /// Clears the whole buffer and releases all referenced objects 
   /// currently stored within the buffer.
   /// </summary>
   public void Clear() {
      for (int i = 0; i < Count; i++)
         _buffer[i] = default(T);
      _position = 0;
      Count = 0;
      _version++;
   }

   /// <summary>
   /// Determines if a specified item is currently present within
   /// the buffer.
   /// </summary>
   /// <param name="item">The item to search for within the current
   /// buffer.</param>
   /// <returns>True if the specified item is currently present within 
   /// the buffer; otherwise false.</returns>
   public bool Contains(T item) {
      int index = IndexOf(item);
      return index != -1;
   }

   /// <summary>
   /// Copies the current items within the buffer to a specified array.
   /// </summary>
   /// <param name="array">The target array to copy the items of 
   /// the buffer to.</param>
   /// <param name="arrayIndex">The start position witihn the target
   /// array to start copying.</param>
   public void CopyTo(T[] array, int arrayIndex) {
      for (int i = 0; i < Count; i++) {
         array[i + arrayIndex] = _buffer[(_position - Count + i) % Capacity];
      }
   }

   /// <summary>
   /// Gets an enumerator over the current items within the buffer.
   /// </summary>
   /// <returns>An enumerator over the current items within the buffer.
   /// </returns>
   public IEnumerator<T> GetEnumerator() {
      long version = _version;
      for (int i = 0; i < Count; i++) {
         if (version != _version)
            throw new InvalidOperationException("Collection changed");
         yield return this[i];
      }
   }

   /// <summary>
   /// Gets the position of a specied item within the ring buffer.
   /// </summary>
   /// <param name="item">The item to get the current position for.</param>
   /// <returns>The zero based index of the found item within the 
   /// buffer. If the item was not present within the buffer, this
   /// method returns -1.</returns>
   public int IndexOf(T item) {
      // loop over the current count of items
      for (int i = 0; i < Count; i++) {
         // get the item at the relative position within the internal array
         T item2 = _buffer[(_position - Count + i) % Capacity];
         // if both items are null, return true
         if (null == item && null == item2)
            return i;
         // if equal return the position
         if (item != null && item.Equals(item2))
            return i;
      }
      // nothing found
      return -1;
   }

   /// <summary>
   /// Inserts an item at a specified position into the buffer.
   /// </summary>
   /// <param name="index">The position within the buffer to add 
   /// the new item.</param>
   /// <param name="item">The new item to be added to the buffer.</param>
   /// <exception cref="IndexOutOfRangeException"></exception>
   /// <remarks>
   /// If the specified index is equal to the current count of items
   /// within the buffer, the specified item will be added.
   /// 
   /// <b>Warning</b>
   /// Frequent usage of this method might become a bad idea if you are 
   /// working with a large buffer capacity. The insertion of an item
   /// at a specified position within the buffer causes causes all present 
   /// items below the specified position to be moved one position.
   /// </remarks>
   public void Insert(int index, T item) {
      // validate index
      if (index < 0 || index > Count)
         throw new IndexOutOfRangeException();
      // add if index equals to count
      if (index == Count) {
         Add(item);
         return;
      }

      // get the maximal count of items to be moved
      int count = Math.Min(Count, Capacity - 1) - index;
      // get the relative position of the new item within the buffer
      int index2 = (_position - Count + index) % Capacity;

      // move all items below the specified position
      for (int i = index2 + count; i > index2; i--) {
         int to = i % Capacity;
         int from = (i - 1) % Capacity;
         _buffer[to] = _buffer[from];
      }

      // set the new item
      _buffer[index2] = item;

      // adjust storage information
      if (Count < Capacity) {
         Count++;
         _position++;
      }
      // buffer changed; next version
      _version++;
   }

   /// <summary>
   /// Removes a specified item from the current buffer.
   /// </summary>
   /// <param name="item">The item to be removed.</param>
   /// <returns>True if the specified item was successfully removed
   /// from the buffer; otherwise false.</returns>
   /// <remarks>
   /// <b>Warning</b>
   /// Frequent usage of this method might become a bad idea if you are 
   /// working with a large buffer capacity. The removing of an item 
   /// requires a scan of the buffer to get the position of the specified
   /// item. If the item was found, the deletion requires a move of all 
   /// items stored abouve the found position.
   /// </remarks>
   public bool Remove(T item) {
      // find the position of the specified item
      int index = IndexOf(item);
      // item was not found; return false
      if (index == -1)
         return false;
      // remove the item at the specified position
      RemoveAt(index);
      return true;
   }

   /// <summary>
   /// Removes an item at a specified position within the buffer.
   /// </summary>
   /// <param name="index">The position of the item to be removed.</param>
   /// <exception cref="IndexOutOfRangeException"></exception>
   /// <remarks>
   /// <b>Warning</b>
   /// Frequent usage of this method might become a bad idea if you are 
   /// working with a large buffer capacity. The deletion requires a move 
   /// of all items stored abouve the found position.
   /// </remarks>
   public void RemoveAt(int index) {
      // validate the index
      if (index < 0 || index >= Count)
         throw new IndexOutOfRangeException();
      // move all items above the specified position one step
      // closer to zeri
      for (int i = index; i < Count - 1; i++) {
         // get the next relative target position of the item
         int to = (_position - Count + i) % Capacity;
         // get the next relative source position of the item
         int from = (_position - Count + i + 1) % Capacity;
         // move the item
         _buffer[to] = _buffer[from];
      }
      // get the relative position of the last item, which becomes empty
      // after deletion and set the item as empty
      int last = (_position - 1) % Capacity;
      _buffer[last] = default(T);
      // adjust storage information
      _position--;
      Count--;
      // buffer changed; next version
      _version++;
   }

   /// <summary>
   /// Gets if the buffer is read-only. This method always returns false.
   /// </summary>
   bool ICollection<T>.IsReadOnly { get { return false; } }

   /// <summary>
   /// See generic implementation of <see cref="GetEnumerator"/>.
   /// </summary>
   /// <returns>See generic implementation of <see cref="GetEnumerator"/>.
   /// </returns>
   IEnumerator IEnumerable.GetEnumerator() {
      return this.GetEnumerator();
   }
}

You can download the source file and the unit tests here:
RingBuffer.cs
RingBufferTests.cs

A Strange Kind of a Relational EDI

2009-12-21T14:47:00.000-08:00

Today I'll speak about a very different kind of data exchange. The following approach might sound strange for the first moment (it did to me!). However, after some days thinking about, I think this might be a new way which probably provides some powerful new solutions.

First as first. Let's start up with a general discussion about different types and directions of EDIs (Electronic Data Interchange).

Many of us work with EDIs to exchange data between different systems and/or locations. I think there are two main types of data interchange directions.

The first direction of EDIs are data-loader routines or processes which load data from 3rd level applications. Especially todays EAI (Enterprise Application Integration) applications are often related to many different other applications. This might be an order system which takes customer information from a CRM tool and some employee information from a HR-software. The important attribute here for this kind of data exchange is the communication with other applications which are not part of your own project.

The second kind of EDIs are synchronization routines or processes which are used to keep systems in sync. These processes are often used to exchange data between different servers (e.g. locations) of a distributed system. Another sample for sync routines is a offline support of a product catalog on a sales notebook. Last but not least synchronization processes are used for staging areas between operational and analysis systems like SSAS. The important attribute of this kind of data exchange in this post is the communication between systems of the same project or company.

As you see, I want to point the difference of communication between your own project modules and external systems.

Next we'll go on with a short listing of the most common available EDI formats and their strengths and weaknesses.

An EDI Format Inventory

Todays most common EDI formats seem to be CSV, XML. Sure there are several other - more specific - EDI formats like EDIFACT, BORD128, binary serialization and so forth. Though, for sake of brevity those formats are not scope of this post.

CSV

CSV files (separated by comma, tab, semicolon or whatever) are especially used to exchange huge amounts of data. The main strength of this format is its compactness. CSV files contain almost no overhead. The files are human readable with tools like Notepad (or any other text editor) or Excel. Especially data storage systems like SQL Server, many other RDBMSs or SSIS provide build in functionality to write and read CSV files.

On the other hand, CSV files have some weaknesses. First, the files are not relational, which means you cannot specify links between different kinds of data. Sure you can use distinct files for each object (or data) type and put everything together into an ZIP archive. Nevertheless, the files are not integrity checked. Another issue of CSV files is a missing search functionality. Microsofts OLEDB Jet-Provider enables simple SQL queries on CSV files but this is not a standard on other operational systems. Finally CSV files are not type safe. Your third column might be specified as an amount of goods, but you can never be sure that data in all rows are really stored in a decimal format and even if they are, you need to know the formatting information of the data. E.g. an amount of "1,234.56" in German format is "1.234,56". With date/time formats it becomes more funny ;-)

XML

Over the last some years XML became more and more the most common format for almost any kind of data exchange. Why? Here just a subset of reasons...

It supports references (links) between different objects. This could be direct parent-child relations or foreign-key like references to other XML-objects within or even without the current XML file.
With XPath it supports a full featured XML query language.
Almost all todays development kits provide build in XML support.
XML files can be validated via XSDs for data types, required and optional object properties and required relations.
It is human readable with any kind of text editor, web browsers and many IDEs.

A common approach to use XML for data exchange is object serialization and there are two generally different ways to handle XML serialization. In smaller projects it often makes sense to directly serialize the existing Domain Objects. In more complex domain models serialization is often done by creation of DTOs, since the domain model might be too complex and contain too much validation which might be not needed (or desired) while (de-)serialization. DTOs are a simplified and coarse grained simplified versions of fine grained domain objects.

Sure, XML can also be used without any build in serialization methods. Since this slightly eliminates the "easy to use" factor, this is usually only needed for very large amounts of data or very flexible requirements.

Independent of all the strength of XML, it also has some weaknesses which might be important for some requirements.

XML is a very verbose data format. Though, this can be handled by using any kind of compression (like ZIP, GZip and so on).

Another weakness of XML is XPath. Don't get me wrong, XPath is a powerful query language and I (almost) always get what I want. Nevertheless, most times original data storages are relational databases. When using XML you always have to either implement some kind of XPath based Mapper (when working with plain XML) or any object based way to find all de-serialized objects and their relations (when working with serialization).

A Relational EDI

Wouldn't it be cool to work with SQL when working with EDI, eh? In my opinion, yes! So, why don't do this?

Did you ever think about using embedded databases (like SQL Server Compact, SQLite or others)? Sounds strange to you? It did sound strange to me, too. However, who cares? After several days thinking about this approach, this appears to be a probably very new kind of data exchange.

When you work with an O/R-Mapper you might even get this feature as a gift. Even if you don't work with an O/R-Mapper, most usual SQL statements are supported by those new embedded database systems. When working with SQL Server Compact you might think about using Microsofts Sync Framework to load and update your data from your "database interchange".

Embedded databases support constraints primary keys, foreign keys, indexes (and some even more). Armed with these constraints, embedded databases provide a ensured relational integrity. You can also be sure that all your data are stored in correct format.

One potential issue of embedded databases is the file size. A new, empty SQL Server Compact database has a initial size of 20KB (for being empty :-D ). 20KB appear to be not too much for me, but what about file growing when data become loaded? After this initial size it grows less than XML files. I tried to create an XML and a SQL Server Compact database each containing all Customers (91 rows) and Orders (921 rows) from good old Northwind database. The XML file ended up with 437KB, the SQL Server Compact database ended up with 184KB (containing both primary keys and a foreign key from Orders to Customers). To be fair, I also tried to compress both resulting files with WinRAR. The XML file was compressed to 30KB, the database file was compressed to 31KB. Sure, "large" and "small" always depend on your environment, but in my opinion this compression appears to be okay.

Is missing human readability an issue of embedded databases? I'd say, yes ant no. You cannot open the files with a text editor. But you can use tools like SSMS (in case of SQL Server Compact) or any other database query tool. If you work with an O/R-Mapper or any other Data Access Layer which supports different database systems you can probably even use your tailor made business application to browse the data within the "interchange".

Nevertheless, the most important issue of using embedded databases as EDI is the fact that it is completely no standard today. For this reason this should only be used for in-project synchronization between different storages at the moment.

Conclusion

If network traffic is one of the issues you fight with when exchanging EDIs between systems, CSV is definitely the way to go.

If you need a standardized communication to any outside (of your project) module, XML is usually the way to go.

If you have to synchronize larger amounts of data between different parts of your own system, you might think about using embedded databases doing this job. At least, give it a try or some hours to think about ;-).

What's the Problem with List Properties in BLL?

2009-11-27T03:43:00.000-08:00

Today I'll speak about a practice for business logic layers (BLL).

The .NET List<T> is a nice and really handy class for in many destinations. However, this class is not the best solution for libraries like business logic layers (BLL) to provide related domain objects.

Above a very simple part of a fictive business logic layer (BLL).

class Order {
   public int Id { get; set; }
   // ...
}

class Customer {
   public int Id { get; set; }
   public List<Order> Orders { get; private set; }
   // ...
   internal void SetOrders(List<Order> orders) {
      this.Orders = orders;
   }
   // ...
}

Using the List<Order> in this solution can cause some problems.

Lacy Loading

You should always try to load all information needed for a specific business case. Though, there are reasons why this makes no sense. For instance if the don't know which related data are needed when you start your business transaction. Another reason might be a too huge count of related objects. Here is lazy loading a nice pattern.

In our fictive layer, there are cases when we need the orders of our customer, but there might be several other cases when we don't need the related orders. You can use a Builder to compose different customers for different business cases. However, sometimes the required relations to handle a business case are not clear when the business transaction starts.

Always to load too much information might become a performance problem. Lazy loading is a nice practice (when it is used correct) to ensure related information if they are needed. One way to handle lazy loading is AOP but sometimes you can end up in a ripple loading. For instance, if you have to handle thousand customers and their orders it makes more sense to load all (or a page of 100) orders in one database query. In this case you might prefer a special implementation which handles this loading.

Validation

You can derive from List<T> but it does not provide any way to validate the Order which become accessed/removed/added. Probably you want to validate newly added orders to ensure they are valid and/or not yet attached to any customer. What about deletion? It should not be possible to delete an order which is already billed. For these business cases, you need an observable list or a read-only list.

Presentation

This very depends on how flexible your BLL wants to be but think about your presentation layer. If you want to be able to bind your orders list to a data bindable control like the .NET DataGridView. The List<T> does not provide all required methods to enable add/remove handling for the grid.

Notice, I do not speak about any coupling between business and presentation layer but it might be neat if your BLL provides some binable objects which can be reused in win-, wpf- and/or web-applications.

Custom Behavior

As a last reason for now (there might be more) why List<T> should not be used here is customization of behavior. Probably your list needs to be sorted by any order information. Maybe you need an observer list which organizes this sorting, even if you change one or more of the contained orders.

Better Solution

A better approach to provide related 0...* objects is usually the IList<T> interface. This interface provides (almost) all methods which are available by using List<T> but it provides a very low coupled representation of the Order property. Feel free to use List<T> for common initialization within your BLL. If you ever need a special handling you are able to provide another list though.

class Customer {
   public int Id { get; set; }
   public IList<Order> Orders { get; private set; }
   // ...
   internal void SetOrders(IList<Order> orders) {
      this.Orders = orders;
   }
}

Another solution is to provide a completely custom list interface or base class (I always prefer interfaces). This also works fine and keeps you able to provide different lists with different behavior. Though, this has a larger startup cost for your project. IMO IList<T> fits fine for most consumers of a BLL.

Table-Valued Parameters - A Performance Comparison

2009-11-16T14:47:00.000-08:00

As I wrote in some of my previous posts, table-valued parameters - which have been introduced with SQL Server 2008 - appear to be a great feature to enable client side bulk operations when working with SQL Server. They enable client data provider like ADO.NET to send a complete set of data to the server instead of calling serveral single row operations. Today we'll do some comparsions.

I'll start with a ~~short~~ (had to correct this...) description of the test environment. To use the best possible performance for both ways I'll use stored procedures.

The first test results focus the performance of single statements vs. bulk operations in a single client environemnt - means without any other concurrent activities. The second, more important tests cover a performance comparsion in a multi-client scenario. The last part will show a comparison for some larger bulk operations.

Test Environment

If you already read my previous posts you can skip this section. I try to keep this post uncoupled from my previous post since this covers another general topic (TVPs). So I have to repreat the test environment and the general way to use table-valued parameters from ADO.NET.

For my tests I'll used the following simple table to INSERT/UPDATE rows.

CREATE TABLE TestMany
(
   Id int IDENTITY(1,1) NOT NULL
      PRIMARY KEY CLUSTERED 
   ,SomeInt int NULL
   ,SomeDate datetime NULL
   ,SomeText varchar(100) NULL,
);

Whenever we intend to use table-valued parameters in SQL Server 2008, we need a table-valued type defined in SQL Server which can be used as a collection of Data Transfer Objects (DTOs) to transfer .NET native data to SQL Server.

CREATE TYPE TestManyMergeType AS TABLE(
   Id int NOT NULL
   ,Action char(1) NOT NULL
      CHECK (Action IN ('I', 'D', 'U'))
   ,SomeInt int NULL
   ,SomeDate datetime NULL
   ,SomeText varchar(100) NULL
   
   ,PRIMARY KEY CLUSTERED 
      (Id, Action)
);

This type covers our destination table with some slight exceptions. Since we will use table-valued parameters to push all changed client data in one package to SQL Server, we need a column which tells SS2k8 how to handle a distinct row. Therefore, we'll use the Action column which specifies the current row to be Inserted, Updated or Deleted. We don't use an IDENTITY specification for our "Id" column since we need to be able to specify an destination "Id" to be handled. To insert new rows we can use negative, unique "Id" values which are required to map newly inserted rows on client-side to their new server ids. To get the best selectivity we use a primary key over the "Id" and "Action" columns.

To provide a stored procedure which gets a table-valued parameter, the parameter has to be specified as READONLY in SS2k8.

CREATE PROCEDURE SaveTestMany
   @data TestManyMergeType READONLY
AS
   SET NOCOUNT ON;
   
   -- update
   UPDATE trg SET 
         SomeInt = src.SomeInt
         ,SomeDate = src.SomeDate
         ,SomeText = src.SomeText
      FROM TestMany trg 
         JOIN @data src 
         ON trg.Id = src.Id AND src.Action = 'U';

   -- insert
   INSERT INTO TestMany
      SELECT SomeInt,SomeDate,SomeText 
      FROM @data 
      WHERE Action = 'I' 
      ORDER BY Id

   -- return last IDENTITY and count of added rows
   SELECT SCOPE_IDENTITY(), @@ROWCOUNT;
GO

The important most important part of this procedure is the ORDER BY clause in the INSERT statement. This ensures that we can use the information about the last generated identity value (by SCOPE_IDENTITY()) and the count of inserted rows (by @@ROWCOUNT) to map the provided surrogate ids to the generated server ids. Maybe you think this might be dangerous without a MAXDOP option or anything else and maybe you are right, I just can tell you that I tried different order conditions and all rows have always been inserted in an exact order. So feel free to trust this or not. If you don't trust it (what is a good thing) do your own tests (what is a good thing, too) or just feel free to leave this post (what would be a really bad thing).

To provide a table-valued parameter to a stored procedure from ADO.NET you have two possibilities. You can either provide your data by passing a System.Data.DataTable (which appears be too blown just to provide simple data records in my opinion) or a custom implementation of an IEnumerable<SqlDataRecord>. I'll use the enumerator in my tests since it is way faster and does not require to copy all domain objects into a data table.

class TestManyEnumerator : IEnumerable<SqlDataRecord>
{
   public TestManyEnumerator(IEnumerable<TestMany> data) {
      _data = data;
   }

   private IEnumerable<TestMany> _data;

   public IEnumerator<SqlDataRecord> GetEnumerator() {
      SqlMetaData[] metaData = {
         new SqlMetaData("Id", SqlDbType.Int),
         new SqlMetaData("MergeAction", SqlDbType.Char, 1),
         new SqlMetaData("SomeInt", SqlDbType.Int),
         new SqlMetaData("SomeDate", SqlDbType.DateTime),
         new SqlMetaData("SomeText", SqlDbType.VarChar, 100),
      };

      foreach (var item in _data) {
         SqlDataRecord record = new SqlDataRecord(metaData);

         record.SetValue(0, item.Id);
         record.SetValue(1, item.ChangeState);
         record.SetValue(2, item.SomeInt);
         record.SetValue(3, item.SomeDate);
         record.SetValue(4, item.SomeText);

         yield return record;
      }
   }

   System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() {
      return this.GetEnumerator();
   }
}

This enumerator handles domain objects from type TestMany which map exactly to our above specified table-valued T-SQL type. ChangeState specifies the state ('I', 'D', 'U') of a particular object.

To call our procedure with a set of data to be updated we have to specify a SqlParameter with SqlDbType.Structured, the name of the destination table-valued type as TypeName and provide the previous enumerator as Value.

using (SqlConnection cn = GetConnection(true))
using (SqlTransaction tran = cn.BeginTransaction()) {
   // get test data
   IList<TestMany> data = GetTestData();

   // create a new command as stored procedure
   SqlCommand cmd = new SqlCommand("MergeTestMany", cn);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Transaction = tran;
   // create a new parameter ("Structured" defines a TVP)
   SqlParameter p = cmd.Parameters.Add("@data", SqlDbType.Structured);
   // specify the name of the TVP
   p.TypeName = "TestManyMergeType";
   p.Value = new TestManyEnumerator(data);

   // catch the returned IDENTITY information
   using (SqlDataReader reader = cmd.ExecuteReader()) {
      reader.Read();
      int lastId = reader.IsDBNull(0) ? -1 : (int)reader.GetDecimal(0);
      int count = reader.GetInt32(1);
      // synchronize all newly added keys
      SyncAddedIds(data, count, lastId);
   }

   tran.Commit();
}

For a more detailed explanatin and the implementation of GetTestData():IList<TestMany> and GetConnection(open):SqlConnection please have a look here. For a generic approach, have a look at A Generic BDP Approach for SQL Server 2008.

The procedures to insert single rows into our TestMany table are just simple as they can be.

CREATE PROCEDURE [dbo].[InsertOneTestMany]
   @id INT OUTPUT
   ,@someInt INT
   ,@someDate DATETIME
   ,@someText VARCHAR(100)
AS
   SET NOCOUNT ON;
   
   INSERT INTO TestMany (SomeInt, SomeDate, SomeText)
      SELECT @someInt, @someDate, @someText;
   
   SELECT @id = SCOPE_IDENTITY();
GO

CREATE PROCEDURE [dbo].[UpdateOneTestMany]
   @id INT
   ,@someInt INT
   ,@someDate DATETIME
   ,@someText VARCHAR(100)
AS
   SET NOCOUNT ON;
   
   UPDATE TestMany SET 
         SomeInt = @someInt
         ,SomeDate = @someDate
         ,SomeText = @someText
      WHERE Id = @id;
GO

I think there is nothing which needs to be explained.

We're done with our test enviroment.

Not part of this Post

Calling a single statement procedure from .NET straight forward and part of many tutorials. Therefore I kept this source code away from here. I also don't show how to use multi-threading to call the above procedures in a concurrency scenario.

I kept DELETE operations out of the party since they are often done by special purge processes instead of usual business applications.

Since I don't have a home network, I did my tests with a SQL Server running on a VM on my local host. Therefore I cannot show you any comparison differences depending on network latency.

Single Client Tests

Let's compare the performance of single INSERT/UPDATE statements vs. bulk operations when using a single client connection. To get (almost) linear test results I executed each test configuration 1,000 times. One important thing is that I always called the specified count of INSERT and UPDATE operations in one round trip. So, "Count Insert/Update" with value "1" calls one INSERT and one UPDATE operation, count "50" affects hundred rows. I did my tests with a destination table with just 100,000 rows and witha a table with 10,000,000 rows. Both table have been recreated after/before each test configuration and flushed by calling "DBCC DROPCLEANBUFFERS() WITH NO_INFOMSGS;". To ensure as many index read operations as possible I arranged all operations over the complete complete id range of the table.

Duration on a destination table with 100,000 rows after 1,000 executions.

Insert/Update	Duration
Count	Single Statements	Bulk Operation
1	1,299 ms	3,285 ms
3	2,319 ms	2,995 ms
5	3,201 ms	3,007 ms
10	5,553 ms	3,121 ms
20	11,923 ms	3,449 ms
50	27,644 ms	4,769 ms

Duration on a destination table with 10,000,000 rows after 1,000 executions.

Insert/Update	Duration
Count	Single Statements	Bulk Operation
1	2,631 ms	4,076 ms
3	3,614 ms	4,200 ms
5	4,608 ms	4,470 ms
10	7,541 ms	4,381 ms
20	12,308 ms	4,846 ms
50	26,173 ms	6,336 ms

As you can see, there is a point at about five INSERT/UPDATE operations (means 10 rows!) in one single transaction. With less than ten operations for the same table single statements are faster than bulk operations with table-valued parameters. If we hit this count of rows to be changed, table-valued parameters become way faster than classical single row operations.

A work load between one and fifty INSERT and UPDATE tasks shows that single row operations take about 10 times longer (what's a good thing), the duration of bulk operations increase only about 1.5 times (what appears to be a better thing ;-) ).

Multi-Client Tests

As initially written. SS2k8 is a server and no geeky feature has any worth if it works only in a single-client scenario. Therefore, now we'll have a look at some multi-client tests.

Since concurrent executions take more time I ran the following tests only 100 times. I also changed the count of rows within the destination table to 500,000 what was a convenient value to arrange the threads over the whole table - to get the highest count of reads).

The following results show the overall duration of 100 executions.

Insert/Update	Duration
Count	Single Statements	Bulk Operation
1 Thread
1	159 ms	298 ms
3	237 ms	358 ms
5	344 ms	371 ms
10	562 ms	391 ms
20	1,040 ms	427 ms
50	2457 ms	558 ms
2 Threads
1	387 ms	348 ms
3	679 ms	368 ms
5	854 ms	438 ms
10	1,597 ms	421 ms
20	3,513 ms	575 ms
50	6,839 ms	830 ms
5 Threads
1	1,466 ms	936 ms
3	2,528 ms	943 ms
5	3,542 ms	1,007 ms
10	5,837 ms	1,097 ms
20	10,995 ms	1,293 ms
50	26,092 ms	2,251 ms
10 Threads
1	4,820 ms	1,604 ms
3	13,290 ms	1,936 ms
5	12,624 ms	1,838 ms
10	28,829 ms	2,012 ms
20	45,414 ms	2,789 ms
50	97,498 ms	4,610 ms

Again, remember "1" INSERT/UPDATE means two operations within one transaction. Since single row operations usually scale almost linear they are still the faster solution for single operations. However, as we see bulk operations using with table-valued parameters perform really fine in a multi-threaded environment.

Some Larger Bulks

Okay, we speak about bulk operations so finally, let's do some real bulk operations.

The following results show the complete duration of 10 executions.

Insert/Update	Duration
Count	Single Statements	Bulk Operation
1,000	4,845 ms	370 ms
5,000	23,190 ms	2,033 ms
10,000	46,539 ms	4,854 ms
20,000	95,086 ms	7,209 ms

In my opinion 400,000 changed rows (remember we did 20,000 INSERT and UPDATE operations and we executed 10 times!) in 7 seconds appear to be a really great performance. ;-)

Conclusion

Apparently table-valued parameters might be a great feature for systems which work with bulk data manipulation. This might be some web or rich client applications but especially backend processes like data loader or synchronization processes.

What does this mean? Probably nothing for a general statement. If you think bulk operations with table-valued parameters might be a nice feature for your project. Always do your own tests! We did no tests with a real network and we did not test DELETE operations.

As a general advice, always try to stay innovative but validate everything. My intention was not to show you that you have to change all your running code, but to think about some new features which might be helpful.

A Generic BDP Approach for SQL Server 2008

2009-11-14T04:41:00.000-08:00

As I promised in my previous post (SQL Server and Bulk Data Persistence (BDP), today I'll show a possible generic approach of how to integrate Bulk Data Persistence without writing a new enumerator for each domain object. In addition we will encapsulate the ID synchronization into a small framework.

If you did not yet read the previous posts, please read it before you goon here. I see you as a person who knows the idea of BDP and how to handle it with SQL Server 2008.

Our Task List

Let's reflect the steps of the previous post to figure out all tasks which have to be done:

We have to provide SqlMetaData objects to map our domain object properties
We have to provide an enumerator through the objects and create a SqlDataRecord
We have to call some T-SQL to do the persistence
We have to map all the new server ids to our domain objects

Sounds really complicated? It's not as hard as it sounds. It's more complicated than a command or simple strategy pattern but not much more than a usual Unit of Work pattern.

ObjectState Enumeration

Before we start with our tasks, we have to change a dirty non-OO cheat we previously used. We change the "pseudo enum" to represent the state of a domain object to a real enum ObjectState.

public enum ObjectState
{
   Added,
   Deleted,
   Changed,
   Unchanged,
}

Okay, back to our task list...

Column Mapping

The next thing we need is a general class to map the properties of a domain object into a SqlMetaData object. Above we can see a possible SqlMergeColumn<T> class which represents all information which are generally needed to create the meta data objects.


class SqlMergeColumn<T>
{
   internal SqlMergeColumn(string columnName, SqlDbType sqlType,
                           Func<T, object> getData) {
      ColumnName = columnName;
      SqlType = sqlType;
      _getData = getData;
   }

   // callback to get the value from a current item
   Func<T, object> _getData;
   
   public string ColumnName { get; private set; }
   public SqlDbType SqlType { get; private set; }
   public int MaxLength { get; internal set; }
   public byte Precision { get; internal set; }
   public byte Scale { get; internal set; }

   public virtual object GetData(T item) {
      // get the field value or DBNull
      return _getData(item) ?? DBNull.Value;
   }

   public SqlMetaData GetMetaData() {
      // get meta data for this property mapping
      if (MaxLength != 0)
         return new SqlMetaData(ColumnName, SqlType, MaxLength);
      if (Precision != 0)
         return new SqlMetaData(ColumnName, SqlType, Precision, Scale);
      return new SqlMetaData(ColumnName, SqlType);
   }
}

As you see, the larger part of the class is a simple representation of properties which are needed to create a SqlMetaData object. The MaxLength property is needed for data types with variable length like text or binary data-types. The Precision and Scale properties are required for some numeric types like decimal.

The GetData(T):object method gets an object of generic type of the class. (I always try to avoid reflection if possible, since it is quiet slow and always hard do maintain.) The GetData(T):object returns a value of the specified item by utilizing a delegate Func<T,TReturn> which was provided by the constructor of the class.

The last method GetMetaData():SqlMetaData is a typical strategy method which returns the meta data for a SqlDataRecord enumerator depending on the current configuration of an instance of the class.

We have to handle two special column types. First, we need the identity column to be able to join data for UPDATE and DELETE and synchronize the server identity values with our newly added objects. Second, we need column which can tell us the type of the BDP action (add, change, delete) to be executed. To stay OO geeky we use two additional classes which inherit from SqlMergeColumn<T>.


// represents an identity merge column
sealed class SqlMergeIdColumn<T> : SqlMergeColumn<T>
{
   public SqlMergeIdColumn(string columnName, Func<T, int> getIdData, 
                           Action<T, int> setIdData)
      : base(columnName, SqlDbType.Int, (t) => getIdData(t)) {
      _setIdData = setIdData;
   }

   // private fields
   Action<T, int> _setIdData;

   public int GetIdData(T item) {
      return (int)base.GetData(item);
   }

   public void SetIdData(T item, int id) {
      _setIdData(item, id);
   }
}

// represents a merge action column
sealed class SqlMergeActionColumn<T> : SqlMergeColumn<T>
{
   public SqlMergeActionColumn(string columnName, 
                               Func<T, ObjectState> getMergeAction)
      : base(columnName, SqlDbType.Char, (t) => getMergeAction(t)) {
      MaxLength = 1;
   }

   public override object GetData(T item) {
      switch (GetMergeAction(item)) {
         case ObjectState.Added: return 'I';
         case ObjectState.Changed: return 'U';
         case ObjectState.Deleted: return 'D';
         default: return 'X';
      }
   }

   public ObjectState GetMergeAction(T item) {
      return (ObjectState)base.GetData(item);
   }
}

The SqlMergeIdColumn<T> class provides two additional methods GetIdValue(T):int to return an System.Int32 id value and SetIdValue(T,int) to set a new id value after our database operation.

The SqlMergeActionColumn<T> overwrites the GetData(T):object method to transform the ObjectState enum into a System.Char for our data mapping.

GetMergeAction(T):ObjectState returns the enum value of a specified domain object.

Now, since we have column classes to specify a mapping to a table-valued parameter, we need a collection to store those mappings. I use a System.Collections.ObjectModel.Collection<T> inherited collection in this sample.


// represents a collection of SqlMergeColumn<T> objects
sealed class SqlMergeColumnCollection<T>
   : System.Collections.ObjectModel.Collection<SqlMergeColumn<T>>
{
   // add a data column
   public void AddDataColumn(string columnName, SqlDbType sqlType,
                             Func<T, object> getData) {
      this.AddDataColumn(columnName, sqlType, 0, getData);
   }

   // add a data column
   public void AddDataColumn(string columnName, SqlDbType sqlType,
                             int maxLength, Func<T, object> getData) {
      SqlMergeColumn<T> mapping = new SqlMergeColumn<T>(
         columnName, sqlType, getData);
      mapping.MaxLength = maxLength;
      base.Add(mapping);
   }

   // add a data column
   public void AddDataColumn(string columnName, SqlDbType sqlType,
                             byte precision, byte scale, 
                             Func<T, object> getData) {
      SqlMergeColumn<T> mapping = 
         new SqlMergeColumn<T>(columnName, sqlType, getData);
      mapping.Precision = precision;
      mapping.Scale = scale;
      base.Add(mapping);
   }

   // set the id column
   public void SetIdColumn(string columnName, 
                           Func<T, int> getIdData,
                           Action<T, int> setIdData) {
      base.Add(new SqlMergeIdColumn<T>(columnName, getIdData, setIdData));
   }

   // set the merge action column
   public void SetMergeColumn(string columnName, 
                              Func<T, ObjectState> getMergeAction) {
      base.Add(new SqlMergeActionColumn<T>(columnName, getMergeAction));
   }

   // get the id column
   internal SqlMergeIdColumn<T> GetIdColumn() {
      return this.OfType<SqlMergeIdColumn<T>>().First();
   }

   // get the merge column
   internal SqlMergeActionColumn<T> GetMergeColumn() {
      return this.OfType<SqlMergeActionColumn<T>>().First();
   }

   // get only the data columns
   internal IEnumerable<SqlMergeColumn<T>> GetDataColumns() {
      return this.Where(c => c.GetType() == typeof(SqlMergeColumn<T>));
   }
}

We've got three different implementations of AddDataColumn to add usual data column mappings. In addition we've got two methods (SetIdColumns and SetMergeColumn) to specify the special id and merge-action columns. At last we have three methods (GetIdColumn, GetMergeColumn and GetDataColumns) to access the different column types. I marked those methods as internal since they are only needed within the BDP framework and usually not important for a consumer of the framework.

Note, this framework is just a sample implementation. To keep it simple, I didn't add the possibility to specify the order of all columns. This means, currently all columns have to be specified in same order as defined in the table-valued type. Feel free to add an order parameter for all methods if you need this.

Data Enumerator

As we know, we have to provide a IEnumerable<SqlDataRecord> as value of a .NET SqlParameter which represents a table-valued parameter. The following listing shows a generic implementation of this class.

sealed class Sql2k8MergeEnumerable<T> : IEnumerable<SqlDataRecord>
{
   // generic class to handle field access by lambda expression
   // create a new enumerator
   internal Sql2k8MergeEnumerable(IEnumerable<T> data, 
                                  SqlMergeColumnCollection<T> mappings) {
      _data = data;
      _mappings = mappings;
   }

   // private fields
   IEnumerable<T> _data;
   SqlMergeColumnCollection<T> _mappings;

   // the enumerator which will be called from ADO.NET for a SqlParameter value
   public IEnumerator<SqlDataRecord> GetEnumerator() {
      // get all SqlMetaData for specified column mappings
      SqlMetaData[] metaData =
        _mappings.Select(mapping => mapping.GetMetaData()).ToArray();

      foreach (var item in _data) {
         // create a new record
         SqlDataRecord record = new SqlDataRecord(metaData);
         // fill the record with its values
         for (int i = 0; i < _mappings.Count; i++)
            record.SetValue(i, _mappings[i].GetData(item));
         // return the current record
         yield return record;
      }
   }

   IEnumerator IEnumerable.GetEnumerator() {
      return this.GetEnumerator();
   }
}

The class gets the data to iterate through and an instance of our above implemented merge column collection.

The GetEnumerator():IEnumerator<SqlDataRecord> method utilizes the column mappings collection to create the meta data objects we need for a data record. After this it iterates through all data and returns the data records. To fill the records it uses the GetData method we already defined on our column classes.

Merge Command

A quick look back to our task list shows to missing tasks. We need something to generate the persisting T-SQL and we have to sync the client surrogate ids to the server side generated identity values. (I added both functionalities into one class, feel free to change this for a lower coupling.)


class Sql2k8MergeCommand<T>
{
   public Sql2k8MergeCommand(string tableName, string tableTypeName, 
                             IEnumerable<T> data) {
      _tableName = tableName;
      _tableTypeName = tableTypeName;
      _data = data;
      Columns = new SqlMergeColumnCollection<T>();
   }

   // private fields
   string _tableName;
   string _tableTypeName;
   IEnumerable<T> _data;

   public SqlMergeColumnCollection<T> Columns { get; private set; }
   public SqlConnection Connection { get; set; }
   public SqlTransaction Transaction { get; set; }

   // execute the merge command
   public void Execute() {
      // create an SqlCommand
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = Connection;
      cmd.Transaction = Transaction;
      // get the dynamic SQL
      cmd.CommandText = GetCommandText();
      // create a parameter which gets all data as table-valued parameter
      SqlParameter data = cmd.Parameters.Add("@data", SqlDbType.Structured);
      data.TypeName = _tableTypeName;
      data.Value = new Sql2k8MergeEnumerable<T>(_data, Columns);

      // execute the command and read the IDENTITY and INSERT row count
      using (SqlDataReader reader = cmd.ExecuteReader()) {
         SyncSurrogateIds(reader);
      }
   }

   // get the sql command text to be executed
   private string GetCommandText() {
      StringBuilder update = new StringBuilder(0x100);
      StringBuilder insert = new StringBuilder(0x100);

      // build columns for INSERT and UPDATE
      foreach (var column in Columns.GetDataColumns()) {
         insert.AppendFormat("{0},", column.ColumnName);
         update.AppendFormat("trg.{0}=src.{0},", column.ColumnName);
      }
      // remove the trailing ","
      insert.Remove(insert.Length - 1, 1);
      update.Remove(update.Length - 1, 1);

      string id = Columns.GetIdColumn().ColumnName;
      string merge = Columns.GetMergeColumn().ColumnName;
      StringWriter sql = new StringWriter(new StringBuilder(0x400));

      // delete
      sql.WriteLine("DELETE trg FROM {0} trg WHERE EXISTS (", _tableName);
      sql.WriteLine("   SELECT * FROM @data src");
      sql.WriteLine("   WHERE src.{0} = trg.{0} AND src.{1} = 'D');", 
         id, merge);
      // update
      sql.WriteLine("UPDATE trg SET {0} FROM {1} trg", update, _tableName);
      sql.WriteLine("   JOIN @data src ON trg.{0} = src.{0} AND src.{1} = 'U';",
         id, merge);
      // insert
      sql.WriteLine("INSERT INTO {0} ({1}) SELECT {1} FROM @data", 
         _tableName, insert);
      sql.WriteLine("   WHERE {0} = 'I' ORDER BY {1};", merge, id);
      // return identity information
      sql.WriteLine("SELECT SCOPE_IDENTITY(), @@ROWCOUNT;");

      return sql.ToString();
   }

   private void SyncSurrogateIds(SqlDataReader reader) {
      int lastId;
      int count;

      // get identity information from reader
      reader.Read();
      lastId = reader.IsDBNull(0) ? -1 : (int)reader.GetDecimal(0);
      count = reader.GetInt32(1);
      // no new rows added
      if (count == 0)
         return;
      
      SqlMergeActionColumn<T> mergeCol = Columns.GetMergeColumn();
      SqlMergeIdColumn<T> idCol = Columns.GetIdColumn();
      // get client surrogate ids
      SortedList<int, T> idLookup = new SortedList<int, T>(count);
      foreach (var item in _data)
         if (mergeCol.GetMergeAction(item) == ObjectState.Added)
            idLookup.Add(idCol.GetIdData(item), item);

      // map client ids to server ids
      IList<int> clientIds = new List<int>(idLookup.Keys);
      foreach (var clientId in clientIds)
         idCol.SetIdData(idLookup[clientId], lastId - --count);
   }
}

The merge command class orchestrates all other classes and represents the consumer interface for this simple Bulk Data Persistence framework.

The constructor gets the name of the database table, the name of the table-valued type and the data to be persisted.

The properties Connection and Transaction are used to configure the destination database.

The Columns:SqlMergeColumnCollection<T> property enables the consumer to configure the mapping between a domain object and a database table.

The Execute() method performs the database operation. It creates an ADO.NET SqlCommand, specifies the dynamic T-SQL by calling the GetCommandText():string method, executes the command and utilizes the SyncSurrogateIds(SqlDataReader) method to synchronize the ids of added rows.

As I wrote in my previous post, probably the SQL Server 2008 MERGE statement performs better than classical separate INSERT/UPDATE/DELETE operations. I have to investigate this and will share my results in a separate post.

How to use the Framework

Okay. Now we've got a shiny new framework. How to use?

I recycle the test environment we've already seen in the previous post to persist TestMany objects.

Below a T-SQL DDL which specifies the destination table and the table-valued type DTO.


-- the database table
CREATE TABLE TestMany (
   Id int NOT NULL IDENTITY(1,1)
      PRIMARY KEY CLUSTERED
   ,SomeInt int NULL
   ,SomeDate datetime NULL
   ,SomeText varchar(100) NULL
);

-- the table-valued type DTO
CREATE TYPE TestManyMergeType AS TABLE(
   Id int NOT NULL
   ,Action char(1) NOT NULL
      CHECK (Action IN ('D', 'U', 'I'))
   ,SomeInt int NULL
   ,SomeDate datetime NULL
   ,SomeText varchar(100) NULL
   
   ,PRIMARY KEY CLUSTERED 
      (Id, Action)
);

And our domain object which maps to the database table.


public class TestMany
{
   public TestMany(int id, int someInt, DateTime someDate,
                   string someText, ObjectState changeState) {
      Id = id;
      SomeInt = someInt;
      SomeDate = someDate;
      SomeText = someText;
      ChangeState = changeState;
   }

   public int Id { get; set; }
   public int SomeInt { get; set; }
   public DateTime SomeDate { get; set; }
   public string SomeText { get; set; }
   public ObjectState ChangeState { get; set; }
}

Anyway, what do we need to consume our framework?

We need one class which inherits our SqlMergeCommand<T> class to specify the column mapping.


class TestManyMergeCommand : Sql2k8MergeCommand<TestMany>
{
   public TestManyMergeCommand(IEnumerable<TestMany> data)
      : base("TestMany", "TestManyMergeType", 
             data.Where(t => t.ChangeState != ObjectState.Unchanged)) {
      // map the identity column
      Columns.SetIdColumn("Id", (t) => t.Id, (t, id) => t.Id = id);
      // map the merge action column
      Columns.SetMergeColumn("Action", (t) => t.ChangeState);
      // map the data columns
      Columns.AddDataColumn("SomeInt", SqlDbType.Int, 
                            (t) => t.SomeInt);
      Columns.AddDataColumn("SomeDate", SqlDbType.DateTime, 
                            (t) => t.SomeDate);
      Columns.AddDataColumn("SomeText", SqlDbType.VarChar, 100, 
                            (t) => t.SomeText);
   }
}

The class calls the base constructor to specify the destination table and the name of the table-valued type. The body of the constructor does the mapping. Done.

(At this point I have to say "Thanks god for Lambda Expressions" to provide the callback methods. :-P )

Last but not least a sample how to use this class to persist data.


public void GenericMergeTest() {
   using (SqlConnection cn = GetConnection(true))
   using (SqlTransaction tran = cn.BeginTransaction()) {
      // get test data
      IList<TestMany> data = GetTestData(0);

      TestManyMergeCommand mergeCommand = new TestManyMergeCommand(data);
      mergeCommand.Connection = cn;
      mergeCommand.Transaction = tran;
      mergeCommand.Execute();

      tran.Commit();
   }
}

As you see, we just have to initialize our custom command with data to be persisted, specify the database information and execute the command.

Conclusion

As you see, the complete framework takes about 350 lines of code what sounds quiet fair to me.

The merge command class can be plugged in to any existing O/R-Mapper framework which provides a "Unit of Work" functionality and a method/event to catch when data become persisted. If you like to use Bulk Data Persistence you don't have to rewrite your existing persistence layer.

Note, I did not add any error handling to this sample. If you like to use this in your production environment you have to add this. (E.g. to avoid duplicate column names for the mapping.)

If you already use an O/R-Mapper, you should change the way how to map your properties to columns. In this case you don't need a custom command for each of your domain objects, but you need a way to map the meta data of your O/R-Mapper to a merge command.

SQL Server and Bulk Data Persistence

2009-11-10T12:28:00.000-08:00

In my last post I presented Bulk Data Persistence (BDP) as a general concept to send bulks of data data from a client to a database server.

Today I'll show you ways how to use BDP with SQL Server.

Sample Environment

To avoid duplicate source code I'll start with a short description of the environment we will work with.

The database actually contains one table "TestMany" which is described by the following listing and contains about 1,000,000 rows. The following DDL statement describes the structure of the "TestMany" table.

CREATE TABLE dbo.TestMany (
   Id int IDENTITY(1,1) NOT NULL
      PRIMARY KEY CLUSTERED
   ,SomeInt int NULL
   ,SomeDate datetime NULL
   ,SomeText varchar(100) NULL,
);

We use a domain objects from type TestMany which represents a 1:1 mapping to our database table with an additional property to represent the state of the object.

public class TestMany
{
   public TestMany(int id, int someInt, DateTime someDate, 
                   string someText, char changeState) {
      Id = id;
      SomeInt = someInt;
      SomeDate = someDate;
      SomeText = someText;
      ChangeState = changeState;
   }

   public int Id { get; set; }
   public int SomeInt { get; set; }
   public DateTime SomeDate { get; set; }
   public string SomeText { get; set; }
   public char ChangeState { get; set; }
}

Most likely you noticed the type char of the ChangeState property. Usually the change state of an objects should be represented as enum, I choose this just to keep the following samples simple. To maintain the value of ChangeState I'll use the following pseudo enum.

public static class ObjectState
{
   public const char Added = 'I';
   public const char Deleted = 'D';
   public const char Changed = 'U';
   public const char Unchanged = 'X';
}

Since we'll send a complete package of data to the database we have to tell it how to handle specific rows. Therefore I'll use the simple characters "I" (for INSERT), "U" (for UPDATE) and "D" (for DELETE). The Unchanged is not really needed in our samples, but added for completeness.

To avoid writing a connection string always and always again I use a simple helper method which gets a connection string from app.config and opens the connection automatically.

private SqlConnection GetConnection(bool open) {
   SqlConnection cn;
   cn = new SqlConnection(Properties.Settings.Default.SandboxOnSql2k801);
   if (open)
      cn.Open();
   return cn;
}

To create TestMany objects to be persisted we'll use the following GetTestData() method.

private IList<testmany> GetTestData() {
   List<testmany> data = new List<testmany>();

   // to update
   for (int i = 1; i <= 1000; i++)
      data.Add(new TestMany(
         i, 
         i, 
         DateTime.Now.AddSeconds(i),
         string.Format("Updated {0}", i),
         ObjectState.Changed));

   // to delete
   for (int i = 20000; i <= 21000; i++)
      data.Add(new TestMany(
         i, 
         0, 
         DateTime.Now,
         null,
         ObjectState.Deleted));

   // to insert
   for (int i = -1; i >= -1000; i--)
      data.Add(new TestMany(
         i, 
         i, 
         DateTime.Now.AddSeconds(i),
         string.Format("Added {0}", i),
         ObjectState.Added));

   return data;
}

Important: To get BDP work we need a unique surrogate id for new objects which will be send to the database and mapped to the new generated Ids in the client. I work with negative integer numbers in the previous method.

Since we will persist a complete bulk of objects in one database round trip we need a slightly different way to get the server side generated IDENTITY values for new inserted objects. When persisting single objects we can use the SCOPE_IDENTITY() (or @@IDENTITY) function to get the id of the last inserted row. With BDP we send all new rows in one operation, so we have to synchronize all new ids after this operation. The following method gets all data which have been handled in a BDP operation, the count of inserted rows (returned by the database) and the id of the last inserted row (returned by the database). Later in this post, we'll see how we get those information and how to keep them valid. For the moment I just want to show you how to synchronize the client ids with the specified last id and the count of added objects. Probably it makes sense to jump over this method for the moment and come back when we did the first BDP operations.

private void SyncAddedIds(IEnumerable<TestMany> data, int count, int lastId) {

   // get surrogate client ids for new added items
   SortedList<int, TestMany> idLookup = new SortedList<int, TestMany>(count);
   foreach (var item in data)
      if (item.ChangeState == ObjectState.Added)
         idLookup.Add(item.Id, item);

   // map client ids to server ids
   IList<int> clientIds = new List<int>(idLookup.Keys);
   foreach (var clientId in clientIds)
      idLookup[clientId].Id = lastId - --count;
}

SQL Server 2008 Configuration

To use BDP in SQL Server 2008, we need to define table-valued types for the data to be persisted in bulk. We will use these types as simple Data Transfer Objects (DTOs) to transfer structural data from the client to the database.

Notice: We are not needed to use stored procedures for BDP. We will see a sample using a SP but we will also see another sample which work without any procedure.

The table-valued type DTO for our TestMany class and table looks just like our class, containing a column Action which represents the kind of operation to be executed with each row.

CREATE TYPE TestManyMergeType AS TABLE (
   Id int NOT NULL
   ,Action char(1) NOT NULL
      CHECK (Action IN ('D', 'U', 'I'))
   ,SomeInt int NULL
   ,SomeDate datetime NULL
   ,SomeText varchar(100) NULL
   ,PRIMARY KEY CLUSTERED (Id, Action)
);

The PRIMARY KEY on the Id and Action columns is important to ensure integrity and optimizes the lookup performance when we merge data. I also defined a CHECK constraint for the "Action" column for my personal paranoia :-) - to avoid wrong data.

As a little hint: Keep all lookup columns (Id and Action in this case) at the beginning of the table. Try to avoid them at the end, especially after any columns with variable length (as SomeText). This could be another paranoia statement in case of our DTO, but is important with real tables.

With this type our database is ready to handle BDP operations. Let's start with some samples.

SQL Server 2008 and Stored Procedures

We start with the stored procedure sample because it separates the two parts of the party and makes it easier to discuss each of them separately.

Let's start with the stored procedure definition.

ALTER PROCEDURE MergeTestMany
   @data TestManyMergeType READONLY
AS
   -- delete
   DELETE trg 
      FROM TestMany trg 
      WHERE EXISTS (
         SELECT * 
         FROM @data src 
         WHERE src.Id = trg.Id AND src.Action = 'D'
         );

   -- update
   UPDATE trg SET 
         SomeInt = src.SomeInt
         ,SomeDate = src.SomeDate
         ,SomeText = src.SomeText
      FROM TestMany trg 
         JOIN @data src 
         ON trg.Id = src.Id AND src.Action = 'U';

   -- insert
   INSERT INTO TestMany
      SELECT SomeInt,SomeDate,SomeText 
      FROM @data 
      WHERE Action = 'I' 
      ORDER BY Id -- ## IMPORTANT to match ids in client

   -- return last IDENTITY and count of added rows
   SELECT SCOPE_IDENTITY(), @@ROWCOUNT;

The procedure gets one table-valued parameter with the melodic name @data (feel free to change this to a more specific name). Table-valued parameters are a new and very powerful feature of SQL Server 2008 to send a complete set of data to the server. The procedure body represents four separate parts.

As first we use a DELETE statement to delete all provided data which are marked with a "D"-action and matched by a specified Id. (At this point I have to say thank's to Paul White - a real SQL guru - from SQLServerCentral.com for his explanation of the left semi join when using EXISTS here.)

The second part of the procedure uses a joined UPDATE to update all existing data with their new values which are marked a "U"-action.

The third part simply inserts all provided DTOs which are marked with "I". Spot the ORDER BY for the source data. This ordering is very important to ensure the rows to be inserted in the order of the specified surrogate ids from client to be able to calculate the generated server IDENTITY values in the client application.

Last but not least we return the last generated IDENTITY value and the count of rows which have been newly inserted into our table. Feel free to scroll up at to the SyncAddedIds method which should be more comprehensible now since this is the most tricky and most important part to get a well working bulk insert operation.

Let's move to the client side implementation.

To provide a table-valued parameter from ADO.NET to SQL Server 2008, there are two different ways. You can provide a DataTable or an implementation of an IEnumerable<Microsoft.SqlServer.Server.SqlDataRecord> which returns all data to be transfered to the parameter. Personally I prefer the enumerator since a DataTable always represents a not needed overhead and requires relatively much memory.

The following listing shows a quiet straight forward implementation of this enumerable for our TestMany domain object.

class TestManyEnumerator : IEnumerable<SqlDataRecord>
{
   public TestManyEnumerator(IEnumerable<TestMany> data) {
      _data = data;
   }

   private IEnumerable<TestMany> _data;

   public IEnumerator<SqlDataRecord> GetEnumerator() {
      SqlMetaData[] metaData = {
         new SqlMetaData("Id", SqlDbType.Int),
         new SqlMetaData("MergeAction", SqlDbType.Char, 1),
         new SqlMetaData("SomeInt", SqlDbType.Int),
         new SqlMetaData("SomeDate", SqlDbType.DateTime),
         new SqlMetaData("SomeText", SqlDbType.VarChar, 100),
      };

      foreach (var item in _data) {
         SqlDataRecord record = new SqlDataRecord(metaData);

         record.SetValue(0, item.Id);
         record.SetValue(1, item.ChangeState);
         record.SetValue(2, item.SomeInt);
         record.SetValue(3, item.SomeDate);
         record.SetValue(4, item.SomeText);

         yield return record;
      }
   }

   System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() {
      return this.GetEnumerator();
   }
}

As you see, the GetEnumerator() first creates an array of SqlMetaData which is required for the constructor of a SqlDataRecord objects. After this we have to set the values of the current TestMany object and return it.

The next sample shows how to call our stored procedure from ADO.NET.

using (SqlConnection cn = GetConnection(true))
using (SqlTransaction tran = cn.BeginTransaction()) {
   // get test data
   IList<TestMany> data = GetTestData();

   // create a new command as stored procedure
   SqlCommand cmd = new SqlCommand("MergeTestMany", cn);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Transaction = tran;
   // create a new parameter ("Structured" defines a TVP)
   SqlParameter p = cmd.Parameters.Add("@data", SqlDbType.Structured);
   // specify the name of the TVP
   p.TypeName = "TestManyMergeType";
   p.Value = new TestManyEnumerator(data);

   // catch the returned IDENTITY information
   using (SqlDataReader reader = cmd.ExecuteReader()) {
      reader.Read();
      int lastId = reader.IsDBNull(0) ? -1 : (int)reader.GetDecimal(0);
      int count = reader.GetInt32(1);
      // synchronize all newly added keys
      SyncAddedIds(data, count, lastId);
   }

   tran.Commit();
}

As you see, to call a procedure which receives a table-valued parameter we have to specify a SqlParameter as SqlDbType.Structured, the name of the our T-SQL table-valued type as TypeName and provide the above shown enumerator as value of the parameter. This enables ADO.NET to use a single RPC call to utilize the procedure with all changes which have been made on our objects.

A SqlDataReader gets the IDENTITY information, returned by the procedure, and calls the SyncAddedIds method to set the newly generated ids to all our added domain objects.

Sadly, the current version of JDBC apparently not yet supports table-valued parameters. However, since there are many voices in the web (and on the Microsoft JDBC Driver Team Blog) I really hope this great feature will be added to the next version.

SQL Server 2008 without Procedures

Sure, if we don't work with stored procedures (probably we use an O/R-Mapper) in our environment, we are still able to realize BDP.

Whenever we changed domain objects in memory for a specific business case there will come the point where we have to persist the data to the database. At this position we usually generate some dynamic SQL statements to send the changes to the database. This is the position where we just have to replace the existing single-row CRUD statements with bulk operation statements as we've already seen in the procedural approach. ADO.NET is able to bind table-valued parameters to any custom SQL command.

Important: As in the procedural approach, we need a table-valued type which can be used as DTO when communicating with the database and an implementation of a IEnumerable<SqlDataRecord> (or a DataTable which can be provided as value for the SqlParameter.

When we compare the following snippet with the stored procedure solution we'll see, the only difference is the custom SQL instead of a stored procedure call.

using (SqlConnection cn = GetConnection(true))
using (SqlTransaction tran = cn.BeginTransaction()) {
   // get test data
   IList<TestMany> data = GetTestData();

   // create the complete DELETE/UPDATE/INSERT sql command
   String sql = @"
      DELETE trg FROM TestMany trg WHERE EXISTS(
         SELECT * FROM @data src WHERE src.Id = trg.Id AND src.Action = 'D'
         );

      UPDATE trg SET SomeInt=src.SomeInt, 
                     SomeDate=src.SomeDate, 
                     SomeText=src.SomeText
         FROM TestMany trg 
         JOIN @data src ON trg.Id = src.Id AND src.Action='U';

      INSERT INTO TestMany
         SELECT SomeInt,SomeDate,SomeText 
         FROM @data 
         WHERE Action='I' 
         ORDER BY Id -- <- IMPORTANT to match ids in client

      SELECT SCOPE_IDENTITY(), @@ROWCOUNT;
      ";

   SqlCommand cmd = new SqlCommand(sql, cn);
   cmd.Transaction = tran;
   // create a new table-valued parameter for our TestManyMergeType type
   SqlParameter p = cmd.Parameters.Add("@data", SqlDbType.Structured);
   p.TypeName = "TestManyMergeType";
   // provide the data
   p.Value = new TestManyEnumerator(data);

   // execute the sql command and map the returned server ids
   using (SqlDataReader reader = cmd.ExecuteReader()) {
      reader.Read();
      int lastId = reader.IsDBNull(0) ? -1 : (int)reader.GetDecimal(0);
      int count = reader.GetInt32(1);
      SyncAddedIds(data, count, lastId);
   }

   tran.Commit();
}

Indeed, usually we will never put such dynamic SQL statements to our client application. Those statements will be generated by a generic implementation within an O/R-Mapper or any other data access layer.

BDP and SQL Server 2005

Sadly, table-valued types have just been introduced with SQL Server 2008, what means they are not available in SQL Server 2005 and previous version. The DTOs could be realized with user defined CLR types but SQL Server does not support arrays like other database systems. Therefore BDP is not possible to use with SQL Server 2005.

One workaround might be to create a XML document which contains the data to be persisted but this is a hard way to go and requires deep performance tests. I successfully used this for larger operations (more than 10,000 rows in one batch) but I have no experiences with general solutions for client layers.

Conclusion

We have seen how we can realize Bulk Data Persistence when using SQL Server 2008. We need a table-valued type defined in SQL Server which can be used as Data Transer Object and an enumerator for SqlDataRecord objects.

Hence, if you do not want to use user-defined table-valued types in your database BDP is not the right way to go in your system.

Outstanding

Probably the idea of BDP sounds nice for you. Nevertheless, one important part is missing here. A comparison with a single statement based solution. I'll provide this in a following post and reference it here.
Edit: Table-Valued Parameters - A Performance Comparison

Since it would be way too much source to code for lazy people like me, I'll also provide a possible implementation of a generic approach which can be used for any kind of domain object.
Edit: A Generic BDP Approach for SQL Server 2008

For the database gurus out there I'll provide an special post where I'll compare the classical INSERT/DELETE/UPDATE functions to the shiny new MERGE function which was introduced with SQL Server 2008. Since now my tests are not stressful enough to provide a real statement about.

Bulk Data Persistence Pattern

2009-11-07T12:14:00.000-08:00

"Bulk Data Persistence" (BDP) covers a problem I've seen many times in while doing my business or speaking with people all over the world. BDP is an extension of the "Unit of Work" (UoW) pattern, defined by by Martin Fowler in his great book Patterns of Enterprise Application Architecture (PoEAA). BDP describes a different way how clients communicate with their persistence layer to save changes.

A Real-Life Scenario

Guess it's Sunday morning. You stand up, go to bath for oral hygiene and taking a shower. After that you dress up.

Now you leave your house/flat, go to the baker and buy one biscuit (a bab if you live in England or a Semmel if you live in Bavaria - like I do :-D ); back at home you put your biscuit (bab/Semmel) onto the kitchen table. Now you leave your house again, go to the baker to buy the biscuit (bab/Semmel) for your husband or wife; back to home and put it onto the kitchen table. Since you have two children and mom is currently visiting you end up with five trips to the baker and back.

Sure, you'd never do this. You go to the backer and take all biscuits (babs/Semmeln) for the whole family in one round trip. However this is a good example to see how software interacts with databases.

A Technical Scenario

Guess a system is made to enter orders.

The user creates a new order and adds ten order positions. The implementation of this might be realized with a UoW design. When hitting the save button the business logic layer (BLL) validates the order and all oder positions against existing business rules. When everything is fine the client opens a connection to the persistence tier (usually a database). The data access layer (DAL) maps the order object to a "Orders" table and sends row to the database. When the order was inserted the DAL maps the first order position object to the OrderPositions table and sends a row to the database. When this operation returns it maps the next oder position and sends the next row to the database. And so on...

Hence, quiet same scenario as the real-live baker sample.

Tip to the Balance

Todays systems become larger and larger and the count of data to be persisted heavily grows, too. Scalability is a very important design for applications like web applications or service endpoints. A scalable designed application enables the production provider to use load balancing technologies to host the applications on several boxes.

Unfortunately, the operational persistence layer is almost never scalable. It is possible to use replication or data-warehouse architectures to move reporting processes away form the operational system. Nevertheless, business C(R)UD operations always have to work on the one and only operational database and this database is a common bottleneck of several system.

A short look into the Unit of Work (UoW)

Since Bulk Data Persistence (BDP) pattern is a extension of the Unit of Work (UoW) pattern I'll start with a short description of UoW pattern.

The UoW describes a client side transaction object which registers all object changes during one complete business operation. In our sample of the order system, an order only makes sense with all of it's order positions. The UoW transaction object holds the order and all order positions until everything becomes stored in the database.

The following diagram shows the most important part of the UoW pattern needed to understand BDP.

I abstracted the data persistence part since this is the important we will look at in the following text.

A classic implementation of the data persistence would be single database operations for each object which was changed within the UoW. The following diagram describes this approach.

In our sample order system we would generate 11 single database operations. One to insert a row into the Orders table and ten to insert the rows into the OrderPositions table.

The Bulk Data Persistence Pattern

Bulk Data Persistence means to aggregate all data operations of one object type and send them as one single database operation to the database system.

The idea of BDP is to enable the database system to do what it is really optimized for, work with bulks of data. This usually causes way faster statement execution and even better responsibility for concurrent applications since one larger lock works faster than many small locks caused by many single persistence operations. Last but not least the network round trips of the clients are reduced to a minimum what also causes a faster execution.

In our order system sample there would be just two database operations. One to insert the new Order row and one to insert all the OrderPosition rows.

In this sample 11 single statements seem to be no problem but consider the number of concurrent users.

Another scenario can be a process which synchronizes data between a distributed database system or a process which imports data from another system. Both these scenarios might work with thousands of rows to be saved.

What BDP does not cover

Please Don't misunderstand. The idea of BDP is not to move business logic into the database! Don't try to move all changed object types of one UoW in one package to the database. When your database handles complete messages of business objects, it becomes a data service and that's usually not the intention in todays software development. Instead of this send one data package per type. BDP covers the same functionality and contract as any single data persisting data access layer and persistence tier.

Outstanding

I'll provide some different samples in the next days to show how it works and what's the profit. Links to these samples will be added here.

SQL Server and Bulk Data Persistence
PostgreSQL and Bulk Data Persistence - pending
Oracle and Bulk Data Persistence - pending
A Generic BDP Approach for SQL Server 2008
SQL Server Table-Valued Parameters - A Performance Comparison

Factory Pattern

2009-10-10T11:27:00.000-07:00

The factory pattern is probably of the most common creational pattern.

What is a "creational" pattern?

A creational pattern is pattern which describes ways to create objects. You can call it an abstraction of the new operator. The gain of factory classes is to encapsulate the creation of objects to a central position - the factory - and ensure that an object becomes correct initialized. A well known factory is the static XmlWriter.Create() method. It gets either a file name or an output stream and an optional XmlWriterSettings parameter which specifies things like formatting behavior of the returned XmlWriter.

Sample Scenario

Before we start to show how the factory works I'll define a sample scenario. Our system is a ordering system which handles "Order" and "Customer" objects and we got the following classes.

public class Order : ICreationInformation
{
   public DateTime CreationDate { get; set; }
   public String CreationUser { get; set; }
   public String OrderNo { get; set; }
   public Decimal Amount { get; set; }
   public Customer Customer { get; set; }
}

public class Customer : ICreationInformation
{
   public DateTime CreationDate { get; set; }
   public String CreationUser { get; set; }
   public String Name { get; set; }

   public override string ToString()
   {
      return this.Name;
   }
}

As you can see the "Order" as well as the "Customer" contain the system fields "CreationDate" and "CreationUser". In addition the order only becomes valid if it has a related "Customer".

Problem of using the "new" operator

Our ordering system might have more than one position where orders become created. If you work with new operators to create a new "Order" wherever it is possible to create a new order you always have to specify the "CreationDate" and the "CreationUser". As first you might forget to set this information at one point. Remember Murphys law "Anything that can go wrong will go wrong.". As second you duplicate your source code. If you start with a "CreationDate" from local system it might be a future requirement to change this to use a server date since many computers don't have a correct configured local time. In this case you have to fly all over your code to find all position where you worked with local system time to set a "CreationDate".

Factory

A factory class is a class which encapsulates the creation and correct initialization of objects.

The following listing shows a sample factory to create "Customer" and "Order" objects.

public class Factory
{
   public Customer CreateCustomer()
   {
      Customer customer = new Customer();

      customer.CreationDate = DateTime.Now;
      customer.CreationUser =
         System.Security.Principal.WindowsIdentity.GetCurrent().Name;

      return customer;
   }

   public Order CreateOrder()
   {
      Order order = new Order();

      order.CreationDate = DateTime.Now;
      order.CreationUser =
         System.Security.Principal.WindowsIdentity.GetCurrent().Name;

      return order;
   }
}

Once created we can use our factory wherever we need a new "Order" or "Customer" object.

Factory factory = new Factory();

Customer customer = factory.CreateCustomer();
customer.Name = "ALFKI";

Order order = factory.CreateOrder();
order.Customer = customer;

To ensure that your objects are not created by using their constructor from outside the factory it makes sense to define an internal constructor - if your objects are stored in a separate assembly. Now it is impossible to create one of these business objects without a correct initialization.

In our simple example one factory fits to create both types of objects. In a real world scenario it might make sense to use specific factory classes for each type of a business object.

Abstract Factory

An abstract factory is a factory which can create different types of objects with a single implementation. In .NET a abstract factory can be realized with a generic method (or class).

Maybe you noticed the ICreationInformation interface which was added to our "Order" and "Customer" classes. Here is the definition of our this interface which provides the properties "CreationDate" and "CreationUser".

interface ICreationInformation
{
   DateTime CreationDate { get; set; }
   String CreationUser { get; set; }
}

In addition we don't specify any constructor, so both objects have an implicit public constructor. This enables us to create a generic function which can handles the creation of both objects with one implementation.

public class AbstractFactory
{
   public T Create<T>()
      where T : ICreationInformation, new()
   {
      T instance = new T();

      instance.CreationDate = DateTime.Now;
      instance.CreationUser =
         System.Security.Principal.WindowsIdentity.GetCurrent().Name;

      return instance;
   }
}

As you see, the method "Create" works with a generic type "T" with two constraints. The specified type has to be an implementation of ICreationInformation and it needs an empty public constructor. If your business objects don't support an empty constructor you can use the not generic static method System.Activator.Create() which creates objects and enables you to specify constructor arguments.

The usage of this factory is quiet equal to the usual factory, you just have only one method and have to define the generic type.

AbstractFactory factory = new AbstractFactory();

Customer customer = factory.Create<Customer>();
customer.Name = "ALFKI";

Order order = factory.Create<Order>();
order.Customer = customer;

Now that's cool. First, because we have just one implementation for probably hundreds of different objects which implement our ICreationInformation interface and provide an empty public constructor. Second, if you a little bit like me, you are still feeling like a kid sometimes and have to say these generic approaches are always cool! :-)

I'm afraid to say, these abstract factories are not as cool as they look at the first moment. The generic approach is a huge restriction for a factory. Suggest you would be more restrictive as we have been since now. Eventually you wouldn't like to enable the user of your factory to create any "Order" without specifying a "Customer". In a usual factory you just change the "CreateOrder" function to get an instance of a "Customer" object. An abstract factory cannot handle special business cases like this.

Combine Both

As we saw an abstract factory seems to be nice to do some general initializations for objects, but it's a week solution for specific requirements. Anyway, it helps to avoid duplicated code. So why not combining both? You should use a non abstract factory to create new business objects within an application which consumes your business layer but you can use the abstract factory within other factories.

Here is a new version of our factory classes.

class AbstractFactory
{
   internal T Create<T>()
      where T : ICreationInformation, new()
   {
      T instance = new T();

      instance.CreationDate = DateTime.Now;
      instance.CreationUser =
         System.Security.Principal.WindowsIdentity.GetCurrent().Name;

      return instance;
   }
}

public class Factory
{
   AbstractFactory _abstract = new AbstractFactory();

   public Customer CreateCustomer()
   {
      Customer customer = _abstract.Create<Customer>();
      return customer;
   }

   public Order CreateOrder(Customer customer)
   {
      if (customer == null)
         throw new ArgumentNullException("customer");

      Order order = _abstract.Create<Order>();
      order.Customer = customer;

      return order;
   }
}

As you see, the abstract factory became a private class. So it is not possible to use it from outside of our assembly. The usual factory has an instance of an abstract factory to use it's gain to avoid duplicate code for the ICreationInformation interface members. The "CreateOrder" method became more restrictive to ensure that no order can be created without a specified instance of a "Customer".

Now the consumer can use a secure and handy factory and everything is ensured to be done correct.

Factory factory = new Factory();

Customer customer = factory.CreateCustomer();
customer.Name = "ALFKI";

Order order = factory.CreateOrder(customer);

Conclusion

As you saw, the factory pattern is a helpful way to ensure correct initialized objects. The abstract factory is more a tool to be used for less complex requirements but it can be very helpful to do basic initializations.

Easy Working with Threads

2009-10-05T13:37:00.000-07:00

.NET 4.0 introduces the new Parallelism namespace which will simplify working with threads. So, as long as we are just .NET 3.5 coders we have to wait and keep our hands of these complicated multi-threading, don't we? We don't! I'll show you some approaches how to work clean and simple in a multi-threaded environment.

If you just need to start threads which have to do some work without returning any information back to the caller you are usually out of any problems. Start your threads and let them go.

Unfortunately, most times this is not the way how to work with threads usually you have an application thread which dispatches a set of tasks but needs the result of those tasks to work with. We'll simulate this with a "Task" class which will be provided to the worker methods within the following samples.

Our Task Class

In the following samples we will work with a "Task" class which will be provided to our threads to simulate a asynchronous work. Here is the definition of this class.

// represents a task which shall be handled by 
// asynchronous working thread
class Task
{
   public int Id { get; set; }
   public int ReturnValue { get; set; }
}

Classic Thread Approach

Let's start with the old-school solution, which uses lock to ensure thread safe working.

// monitor the count of working threads
static int _workingTasks;

static void Main(string[] args)
{
   // count of tasks to be simulated
   int taskCount = 10;
   // hold the dispated tasks to work with the results
   List tasks = new List();

   // first set the complete count of working tasks
   _workingTasks = taskCount;

   for (int i = 0; i < taskCount; i++)
   {
      // create a new thread
      Thread thread = new Thread(new ParameterizedThreadStart(DoWork));

      // create and remember the task
      Task task = new Task { Id = i };
      tasks.Add(task);
      // start the thread
      thread.Start(task);
   }

   while (_workingTasks != 0)
   {
      // wait until all tasks have been done
      Thread.Sleep(1);
   }

   // show the return values after all threads finished
   tasks.ForEach(t => 
      Console.WriteLine("Thread {0} returned: {1}", t.Id, t.ReturnValue));

   Console.ReadKey();
}

// work method
static void DoWork(object o)
{
   Task task = (Task)o;
   int id = task.Id;

   for (int i = 0; i < 10; i++)
   {
      Console.WriteLine("Thread {0} is working", id);
      // simulate some long work
      Thread.Sleep(200);

      task.ReturnValue++;
   }

   // we have to lock the monitoring variable to 
   // ensure nobody else can work with until we decremented it
   lock (typeof(Program))
   {
      _workingTasks--;
   }
}

As you see, there are quiet a lot of things to keep in mind.We have to use the C# lock statement to ensure a thread safe work with our monitoring variable. Usually you don't have to lock a complete System.Type; you can use lock(this) or any other reference type. I just used lock(typeof(Program)) because I worked with static methods.We use Thread.Sleep(1) to to poll the state of the dispatched tasks.Now we'll start to simplify this work.

Using volatile

The first thing we can do to slightly simplify our method is to define our monitoring variable as


static volatile int _workingTasks;

The volatile keyword can be used to tell .NET that a variable might be accessed by many threads for write access. If you define a member variable as volatile, you don't have to use lock to ensure thread safeness.Since we used lock just once in our previous sample this just changes the call of

lock (typeof(Program))
{
   _workingTasks--;
}

to this


_workingTasks--;

Seems to be senseless to work with volatile? Keep in mind, this is a very very simple sample. Not to need lock becomes really handy if you have to do different things with shared member fields.

Avoid the explicit Polling

The next thing to simplify your multi-threading is to remove the explicit polling. What does this mean? Since now we worked with a member field "_workingTasks" which monitored the state of our working threads. Suggest a larger class with several multi-threading implementations. In this case you would need several member fields to monitor the different threading activities. Another way to wait for the execution of a thread is to Join() it.

static void Main(string[] args)
{
   // count of tasks to be simulated
   int taskCount = 10;
   // hold the dispated tasks to work with the results
   List tasks = new List(taskCount);
   // remember all threads
   List threads = new List(taskCount);

   for (int i = 0; i < taskCount; i++)
   {
      // create and remember a new thread
      Thread thread = new Thread(new ParameterizedThreadStart(DoWork));
      threads.Add(thread);

      // create and remember the task
      Task task = new Task { Id = i };
      tasks.Add(task);
      // start the thread
      thread.Start(task);
   }

   // --> HERE <--
   // wait until all threads are finished
   threads.ForEach(thread => thread.Join());

   // show the return values after all threads finished
   tasks.ForEach(task =>
      Console.WriteLine(
         "Thread {0} returned: {1}", 
         task.Id, 
         task.ReturnValue));

   Console.ReadKey();
}

// work method
static void DoWork(object o)
{
   Task task = (Task)o;
   int id = task.Id;

   for (int i = 0; i < 10; i++)
   {
      Console.WriteLine("Thread {0} is working", id);
      // simulate some long work
      Thread.Sleep(200);

      task.ReturnValue++;
   }
}

As you see, we don't need our monitoring variable any more. The usage of Join() makes it possible to implement a much more encapsulated multi-threading.

Working with the ThreadPool

Keep in mind, threads are a system resource which are important to create. If you have to do many smaller tasks it can be more expensive to create all the threads instead work single-threaded. If you have complex tasks which probably have to handle their thread state in conjunction with your application thread or other threads, you should use the System.Threading.Thread class because it gives you the largest flexibility. If you just have to dispatch tasks and wait for them (as in our sample) it can be the wrong way always to create a new thread. A good approach to work with threads in this scenario is to reuse them for the next tasks.Q: Okay, cool!! Let's start to code a custom thread manager!A: Er... nope. It's already available.For this kind of work we have to do with our "Task" objects you can use the System.Threading.ThreadPool class and it's especially made for this kind of work. It's a pool of threads which can be used to schedule working tasks within.To schedule means you can store a larger count of small jobs within. Whenever a pooled thread is available the next scheduled (queued) task will be started. By default .NET determines the count of initially available threads by your environment information. It also starts new threads if they seem to be useful. However, you can customize all this information by static methods (e.g. ThreadPool.GetMaxThreads, ThreadPool.SetMinThreads).So let's use the ThreadPool to do our tasks. For this propose I extended our "Task" class with an additional property.

class Task
{
   public int Id { get; set; }
   public int ReturnValue { get; set; }
   public AutoResetEvent AutoResetEvent { get; set; }
}

The AutoResetEvent is a special type of a WaitHandle which provides some special methods for multi-threading tasks.

static void Main(string[] args)
{
   // count of tasks to be simulated
   int taskCount = 10;
   // hold the dispated tasks to work with the results
   List tasks = new List(taskCount);

   for (int i = 0; i < taskCount; i++)
   {
      // create and remember the task
      Task task = 
         new Task 
         { 
            Id = i, 
            AutoResetEvent = new AutoResetEvent(false) 
         };
      tasks.Add(task);

      // queue the task in ThreadPool
      ThreadPool.QueueUserWorkItem(new WaitCallback(DoWork), task);
   }

   // wait until all queued tasks are finished
   tasks.ForEach(task => task.AutoResetEvent.WaitOne());

   // show the return values after all threads finished
   tasks.ForEach(task =>
      Console.WriteLine(
         "Thread {0} returned: {1}", 
         task.Id, 
         task.ReturnValue));

   Console.ReadKey();
}

// work method
static void DoWork(object o)
{
   Task task = (Task)o;
   int id = task.Id;

   for (int i = 0; i < 10; i++)
   {
      Console.WriteLine("Thread {0} is working", id);
      // simulate some long work
      Thread.Sleep(200);
      
      task.ReturnValue++;
   }

   // notify the application thread that the task finished
   task.AutoResetEvent.Set();
}

As you see, we don't need the monitoring member fields any more. We even don't need a Thread object any more. The AutoResetEvent class provides a WaitOne() method which can be used by our application thread to wait until all tasks finished. To release the wait handle of a AutoResetEvent you can use the Set() method.Conclusion

I hope I could show you some tricks how to simplify the work with multiple threads in your application.Almost every new computer has two or more CPUs, start to use them ;-).

How to handle SQL Server IDENTITIES in .NET

2009-10-04T13:41:00.000-07:00

Many people still don't use SQL Server IDENTITY columns to generate their primary key values. Most times they just don't know how to handle them in client applications. In this case implementations often end up with client side ID generations or GUID primary keys. This post tries to show why server side IDs are a good approach and how to work with.

IDENTITY is not a Sequence
First I have to say, IDENTITY columns are not made to represent a sequence which can/shall be shown to the user. What does this mean? Every ID is used exactly once, when you delete an existing ID it will not be reused for the next inserted row.

Here's a little sample to show this behavior.

DECLARE @t TABLE
(
   Id INT IDENTITY(1,1) 
      PRIMARY KEY CLUSTERED
   ,SomeInt INT
);

-- insert some rows
INSERT INTO @t (SomeInt) 
   VALUES (100)
         ,(100)
         ,(100);

SELECT * FROM @t;

Since now the data appear to have a correct sequence.

Id	SomeInt
1	100
2	100
3	100

Let's delete one row and add another one.

-- delete one row in the middle of the table
-- and insert another one
DELETE FROM @t WHERE Id = 2;

INSERT INTO @t (SomeInt) 
   VALUES (200);

SELECT * FROM @t;

As you see the deleted Id with value "2" will not be refilled with the new inserted row. The Id "2" was used, deleted and will never be reused by SQL Server (without using tricks like IDENTITY_INSERT or re-seed the IDENTITY).

Id	SomeInt
1	100
3	100
4	200

Why doesn't SQL Server decrement the ID "3" when "2" became deleted?
This would be a huge overhead for the database server. Adopt a table with 1,000,000 rows and delete ID "1". This would cause 999,999 rows to be decremented.

Why doesn't SQL Server reuse the ID "3" for the next inserted row?
In this case SQL Server would have to remember each deleted ID. This would affect the INSERT performance and the storage usage.

If you need a dense rank or a sequence without any gaps you can use functions like ROW_NUMBER() or DENSE_RANK() in your select statement. Anyway, usually this kind of sequences should be handled in client if possible. It's quiet simple to use a int variable to create a sequence to show in GUI.

Client Side ID Generation

Before we start with server side IDs, let's have a short look to the client side approaches.

One solution I've seen some times is a SELECT MAX(Id) FROM ... before execution of the INSERT statement. This has two huge problems! First, this is a huge performance problem since every INSERT causes an index scan. The second problem is, this solution is not safe. Keep in mind, a database server is a multi-user environment. Between your MAX select and the INSERT statement another user can INSERT the same ID and you get a primary key violation!

A second solution to handle client side IDs is to use a ID table which contains the next available. The client applications use this table to get the next IDs for their INSERT statements. This way to handle client side IDs is a good approach but needs a correct implementation. What does this mean? Suppose a table like this:

table_name	next_id
Table1	344828
Table2	6454
Table3	23432

If every client application selects one ID whenever it wants to insert a new row into a table, the whole system meets in one single table with just some rows, which are probably stored in same data and index page. The result would be a problem with locks on this table.

A correct solution to work with a ID table would be to use a client side ID caching. Every client selects a range of IDs to work with for the next INSERT statements. The cache size depends on the type of the client and the count of INSERT operations generated by this client. A windows application which does not generate too many new rows might be fine with a cache size of 10, a server side import process probably needs a cache size of 500 IDs or more.

Doesn't this mean that cached IDs will not be used if the client application exists when IDs are cached? Yes, but this doesn't matter. Keep in mind, primary key IDs are not made to show to a user. They are the database identity of a row. Since INT has a maximum value of 2,147,483,647 this should be enough for most systems - even if you loose some IDs. If you think you might exceed this count of IDs you can use BIGINT which has a maximum value of 9,223,372,036,854,775,807.

Database Structure
Before we start with server side IDENTITIES, you should know the database structure for the following tests.

And here the create statements for both tables.

CREATE TABLE Parent
(
 Id int IDENTITY(1,1) NOT NULL
    PRIMARY KEY CLUSTERED
 ,SomeInt int NULL
);

CREATE TABLE Child
(
   Id int IDENTITY(1,1) NOT NULL
      PRIMARY KEY CLUSTERED
   ,ParentId int NOT NULL
      REFERENCES Parent (Id)
   ,SomeInt int NULL
);

As you see, both tables have an IDENTITY column defined as primary key and "Child" table has a foreign key which points to "Parent" table.

Low Level ADO.NET
First we'll have a look to the low level way to work with server side IDs. Therefore we use a SqlCommand to execute an INSERT statement and retrieve the new id from server.

string cnStr = "Server=.;Database=Sandbox;Integrated Security=SSPI;";

// the INSERT sql statement. Notice the "SELECT @Id"!
string insertSql = @"INSERT INTO Parent (SomeInt) 
                        VALUES (@SomeInt); 
                     SELECT @Id = SCOPE_IDENTITY();";

using (SqlConnection cn = new SqlConnection(cnStr))
using (SqlCommand insertCmd = new SqlCommand(insertSql, cn))
{
   cn.Open();

   // Add the @SomeInt param with a value
   SqlParameter someIntParam = insertCmd.Parameters.Add(
      "@SomeInt", SqlDbType.Int);
   someIntParam.Value = 123;
   
   // Add the @Id param and define it as output parameter
   SqlParameter idParam = insertCmd.Parameters.Add(
      "@Id", SqlDbType.Int);
   idParam.Direction = ParameterDirection.Output;

   // execute the command
   insertCmd.ExecuteNonQuery();

   // show the "Id" after the data are written to server
   Console.WriteLine("after database insert: {0}", idParam.Value);
}

Since our "idParam" is specified as output parameter ADO retrieves the new SCOPE_IDENTITY() from server and we can work with it in our client.

Mostly we don't need to work with this low level implementations. Anyway, sometimes it's good to know how the upper level works.

Working with untyped DataTables

Still quiet low level but common. I've seen many solutions working with untyped DataTables to handle data from a database server. Usually I would say use at least typed DataSets or an O/R-mapper. Anyway. ;-)

How to handle server side IDs with a DataTable? The DataTable will be updated with a SqlDataAdapter, so there is no way to customize the parameters and handle the returned values, is there? To combine the columns of a DataTable with return values of a SqlParameter you can use the SqlParameter.SourceColumn property, which specifies the mapping between the parameter and the columns of the table.

string cnStr = "Server=.;Database=Sandbox;Integrated Security=SSPI;";

// the INSERT sql statement. Notice the "SELECT @Id"!
string insertSql = @"INSERT INTO Parent (SomeInt) 
                        VALUES (@SomeInt); 
                     SELECT @Id = SCOPE_IDENTITY();";

using (SqlConnection cn = new SqlConnection(cnStr))
using (SqlCommand insertCmd = new SqlCommand(insertSql, cn))
using (SqlDataAdapter adap = new SqlDataAdapter())
{
   // create a DataTable which represents our Parent table
   DataTable table = new DataTable();
   // specify Id columnd as AutoIncrement
   table.Columns.Add("Id", typeof(int)).AutoIncrement = true;
   table.Columns.Add("SomeInt", typeof(int));

   // Add a row to our table and show the current Id value (which is "0")
   DataRow row = table.Rows.Add(null, 1);
   Console.WriteLine("before database insert: {0}", row["Id"]);

   // add the "SomeInt" param
   insertCmd.Parameters.Add("@SomeInt", SqlDbType.Int, 4, "SomeInt");
   // add the "Id" param and define it as output param
   SqlParameter idParam = insertCmd.Parameters.Add(
      "@Id", SqlDbType.Int, 4, "Id");
   idParam.Direction = ParameterDirection.Output;

   // inject the adapter with the custom command and update data
   adap.InsertCommand = insertCmd;
   adap.Update(table);

   // show the "Id" after the data are written to server
   // this returns the new IDENTITY now.
   Console.WriteLine("after database insert: {0}", row["Id"]);
}

The fourth parameter of SqlCommand.Parameters.Add specifies the "SourceColumn". The data adapter uses this column to create the mapping between the parameters and the columns of the table to be updated. The console output shows the server side generated value of our "Id" column.

Working with typed DataSets

Welcome on "it works build in" level. Maybe some of you made bad experiences with IDENTITY columns and typed DataSets, let me tell you that's just caused of a tiny configuration issue within the DataSet designer.

If you already used the previously shown CREATE statements to create the sample tables "Parent" and "Child" on a sample database just create a typed DataSet named "DataSet1" in a Visual Studio C# project. You will find some detailed tutorials in MSDN, which show hot to handle this. Here I just want to show how to configure a correct handling of IDENTITY columns.

Have a look to the DataSet1.Designer.cs file and navigate to the ParentTableAdapter. You will find the following line in "InitAdapter()" method.

this._adapter.InsertCommand.CommandText = 
   "INSERT INTO [dbo].[Parent] ([SomeInt]) VALUES (@SomeInt);\r\n" +
   "SELECT Id, SomeInt FROM Parent WHERE (Id = SCOPE_IDENTITY())";

As you see, it selects all inserted values back to client as soon as the new row is inserted. Unfortunately, in default configuration the selected "Id" value will not be written to our related ChildDataTable. Go back to the DataSet Designer and do the following tasks:

Right click the relation between "Parent" and "Child" table and select "Edit Relation...". The relation configuration dialog appears.
In section "Choose what to create" select option "Both Relation and Foreign Key Constraint".
Change "Update Rule" to "Cascade".
Ensure "Accept/Reject Rule" is set to "None". If you change this option to "Cascade", the changes of our "Child" row will be automatically when the "Parent" table becomes saved.

The following picture shows the configuration.

Finally a little sample which can be copied and pasted to illustrate the correct identity handling with a typed DataSet.

// create a DataSet
DataSet1 ds = new DataSet1();

// get the parent table and add a new row
DataSet1.ParentDataTable parentTable = ds.Parent;
DataSet1.ParentRow parentRow = parentTable.AddParentRow(1);

// get the child table and add a new row
DataSet1.ChildDataTable childTable = ds.Child;
DataSet1.ChildRow childRow = childTable.AddChildRow(parentRow, 1);

// create the table adapters
DataSet1TableAdapters.ParentTableAdapter parentAdapter =
   new ConsoleApplication2.DataSet1TableAdapters.ParentTableAdapter();
DataSet1TableAdapters.ChildTableAdapter childAdapter =
   new ConsoleApplication2.DataSet1TableAdapters.ChildTableAdapter();

// update the parent table
parentAdapter.Update(parentTable);
// #######################
// At this point, the new IDENTITY value from parent row
// was written to our child row!
// #######################
Console.WriteLine(childRow.ParentId);

childAdapter.Update(childTable);

Special thanks at this point goes to ErfinderDesRades, a power-user state member of myCSharp.de - a great C# forum. He showed me the correct configuration of the DataSet.

LINQ 2 SQL and Entities Framework

LINQ 2 SQL as well as Entities Framework support IDENTITY columns out of the box, so there is nothing more to say about these O/R mappers.