Friday, January 16, 2009

MS SQL-Server - How to select numeric rows from a varchar column

select ISNUMERIC('PostalCode123') -- Returns 0
select ISNUMERIC('123') -- Returns 1


select *
from Customer
where ISNUMERIC(PostalCode) = 1

Monday, January 12, 2009

Why you should not use the ADO.NET Entity Framework

Note: This post is about ADO.NET Entity Framework version 1.0. Newer versions of the framework might have been improved or solved any of the issues mentioned below.
For some days now I've been using the ADO.NET Entity Framework on a pet project of mine. My conclusion so far is that it's useless in most scenarios, and that I'm lucky to not use it on a real business application.

No support for the traditional n-layer architecture with a presentation, business and data-access layer

  • It's not possible to use your own business entities. If you have an existing codebase, then you have to rewrite all the code vs. if you used NHibernate, it's enough to modify the data-access layer to use NHibernate and map the result to your existing business entities using XML.
  • The only supported data source is database. In many applications today, data sources are both databases and services. For the services, you would need to define your own set of business entities and data-access code.
  • It is not possible to define the business-entities in a separate assembly. Thus the presentation layer such as a web-application, will need to reference the data-access layer in order to get access to the business entities.
  • The entity context instance has to be preserved for the entire request in order to allow for select, update and deletion of data. This needs to be taken care of in the business layer. One way of doing this is to add a reference to the System.Web assembly, and then use the request cache available through HttpContext.Current.Items. But you don't want a dependency on System.Web in your business layer. As Sean points out in his comment below, a way around this is to detach the object from the original context, attach the object to the new context, then call the extension method defined in Seans comment which will mark all of the properties on the attached object as modified. When SaveChanges() is called, the database will be properly updated. The same goes for deleting an object: detach, attach, DeleteObject(entity) and then finally SaveChanges().
  • If you want to delete data without using a stored procedure, then you first have to select the data to delete, iterate through each of the returned entities and call DeleteObject(entity), before you call SaveChanges(). Of course, this gives you really bad performance.

No support for calling stored procedures

When you add a stored procedure from the database to your data model, then the stored procedure will only be available through the entity context if it returns any of the model entities. The stored procedure will not be available if it returns nothing or any of the .NET primitive types such as string, int, bool etc.

Problems when updating the model from the database

When you make some database modifications after the model has been created, and you choose to update the model through the designer, then the end result is usually compiler exceptions. In my case, I always end up deleting the model and creating it from scratch, rather than fooling around in the designer generated file to find the bugs.