Lately we have been investigating ORM’s (Object Relational Mapping) frameworks for use on a large enterprise level website which we been given the rare opportunity to rebuild from the ground up in Microsoft MVC.

I’ve used Subsonic in the past while working with dashCommerce and more recently on my latest project based on the wonderful Subsonic 3.0. We decided we needed something more robust and proven with a large enterprise level system like ours which currently comprises with hundreds of tables and store procedures all managed by a team of professional DBA’s who do a pretty good job at optimising and tuning the Microsoft SQL 2005 database.

After much deliberation we managed to convince the DBAs and Developer Head to give NHibernate a shot. It seemed to be the most mature fully featured ORM for .NET and rated highly whitin the .NET community as is apparent by the huge number of blog posts about it, so we felt more secure choosing it over the premature Entity Framework.

NHibernate and MVC brought us to reconsider our architecture and after finally getting my head around to the S#harp Architecture I managed to convince the team it was the way to go and gave us a good foundation to build our application on.

After a few weeks we got a really good prototype working. We mapped some of our core tables using FluentNhibernate, wrote some simple LinqToNHibernate queries, religiously followed Test Driven Development and Domain Driven Design until we stumbled across our first major issue.

Queries run by NHibernate were not re-using SQL Server execution plans This was causing us some major performance issues since some queries were taking a few seconds just to generate a plan. We quickly managed to figure out the problem caused viewing the queries using SQL Profiler. NHibernate was passing parameter sizes dynamically based on the length of the value rather than from the mapping which was causing SQL Server to create new execution plans for each unique query.

We found a good explanation and “fix” setting prepare_sql to true in the NHibernate config.

This seemed to fix the parameter size issue but now the queries were being sent using exec sp_prepexec which even had the DBA’s scratching their heads over as it’s documented as a system procedure used to facilitate cursors within SQL Server. The DBAs later informed us that running queries like this causes the execution plans to only be re-used per open connection so for every connection it would still have to create new execution plans.

Carsten Hess wrote a good comment on Ayende’s Blog explaning the issue to which no follow up was made but helped us find and fix the issue. I’ve attached the file for those interested in the fix as to why this isn’t the default behaviour of NHibernate has us perplexed. I've just commenting out an if statement in \Driver\SQLClientDriver.cs found in NHibernate source code thus forcing it to call SetParameterSizes() all the time regardless of the prepare_sql setting alowing us to set it to false so queries are sent normally and excecution plans being issued and re-used correctly.

The whole experience left us feeling weary, had we not discovered the issue beforehand it almost certainly would have brought down our application if we went live with NHibernate. In the end we decided to hold off on implementing the ORM to give it more investigation.

Our journey wasn’t a waste of time as it has brought us to learn about many new skills along the way that has already had an affect on our programming. Test Driven Development, Dependency Injection, Inversion of Control, Domain Driven Design all of which has given us an understanding of some techniques we can use to better develop our system.

Attached: SqlClientDriver.cs

EDIT: Andrei Volkov has posted a good followup in which he explains the history of this issue and an alternative fix to create your own driver. NHibernate parameter sizes controversy

How many times have you had to create a comma delimited list from a table column? Do you ever remember how to do it? I don’t. Here is a little snippet I find useful:

-- this will be a comma delimited list of location ids

DECLARE @LocationIds Varchar(Max)

-- set it to an empty string to start with

SET @LocationIds = ''

--add commas 

SELECT @LocationIds = COALESCE(@LocationIds + ',','') + CAST(Location_ID AS VARCHAR) FROM Locations_Table

--get rid of the comma at the front of the string

SELECT SUBSTRING(@LocationIds, 2, Len(@LocationIds))

Result: 1234,3445,6778,6789,…

The other day we needed to write a report on online sales for the last week. SQL doesn’t offer developers many predefined functions to work with date ranges unlike the C# programming language. Here is a little example how to query SQL for some data between the dates for the last week.

By default the first day of the week is set to Sunday (default value 7), so if you need it to be Monday run the following command:

SET DATEFIRST 1

DECLARE @TodayDayOfWeek INT

DECLARE @EndOfPrevWeek DateTime

DECLARE @StartOfPrevWeek DateTime

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)

SET @TodayDayOfWeek = datepart(dw, GetDate())

--get the last day of the previous week (last Sunday)

SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())

--get the first day of the previous week (the Monday before last)

SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())

Now we can use above expressions in our query:

SELECT …. FROM… WHERE Sale_Date BETWEEN 

CONVERT(VARCHAR, @StartOfPrevWeek,7)

AND

CONVERT(VARCHAR, @EndOfPrevWeek+1,7)

Note that we had to convert dates in order to reset minutes/hours/seconds to 00:00:00, but it also means that if we want to include the whole Sunday into our week report we need to set the dates BETWEEN Monday AND Monday 00:00:00.

As a .NET programmer most of my time is spent coding in C# and I try to avoid writing SQL where possible, which is great as we have dba's that can help with the more complicated queries. Why is it so complicated to write a loop without turning the database upside down? I'm not saying it's not possible it's just the language can me annoying at times.

Anyways, Recently I had to write an insert stored procedure and needed to return the ID of the row I was inserting. While writing my usual bad SQL I came across a fascinating function I’ve never used before, SCOPE_IDENTITY().
Here's short example of how you can use the SCOPE_IDENTITY() function while doing an SQL INSERT to return the identity value of the row just inserted.

In this example I’m inserting a new customer record using a stored procedure and have declared a output parameter which I'd like to return to my application.

CREATE PROCEDURE [dbo].[Customer_Insert]

    @Name VARCHAR(255),

    @Email VARCHAR(255),

    @Phone VARCHAR(255),

    @CustomerID INT OUTPUT

AS

BEGIN

    INSERT INTO dbo.Customer ([Name], Email, Phone)

    VALUES (@Name,@Email,@Phone)

    SET @CustomerID = SELECT CustomerID 

              FROM dbo.Customer 

                  WHERE [Name] = @Name 

              AND Email = @Email 

              AND Phone = @Phone

END

What I’ve done wrong here is after inserting run a select to try and SET the @CustomerID. This is a bit risky as it could return more than 1 record, or it return an of completely different record.

SQL's SCOPE_IDENTITY() function can return the last identity value inserted into an identity column in the same scope e.g. a stored procedure, trigger, function, or batch. Alternativly you can also use the @@IDENTITY function to return the last identity value inserted into an identity column regardless of the scope.

Now lets improve my stored procedure using SCOPE_IDENTITY().

CREATE PROCEDURE [dbo].[Customer_Insert]

    @Name VARCHAR(255),

    @Email VARCHAR(255),

    @Phone VARCHAR(255),

    @CustomerID INT OUTPUT

AS

BEGIN

    INSERT INTO dbo.Customer ([Name], Email, Phone)

    VALUES (@Name,@Email,@Phone)

    SET @CustomerID = CAST(SCOPE_IDENTITY() AS INT)

END

I've casted the value returned by SCOPE_IDENTITY() to make sure the confirms to the return parametor @CustomerID INT OUTPUT.