Post related to SQL

Subtle deadly bug

I almost never give my opinion about code I see, but I felt this one was interesting and educative. It all started while working on a legacy project (an ASP.NET web application), I found out that most connections were not closed. So I fixed them all and making sure every connection was closed once the I finished with it, should an error occurred or not.

try
{
    using (IDataReader reader = SqlHelper.ExecuteReader(Sql.MainConnection, "sp_name", paramter1, parameter2))
    {
        while (dr.Read())
        {
            //do something
        }
    }
}
finally
{
    Sql.MainConnection.Close();
}

Pretty basic stuff. Today I deployed the application to a server for some testing. After two or  three users getting in, Boumm! SQL Server is dead. I check the activity monitor and found out that a hundred connections were still open. The reason is : I have wrongly assumed that a static class in the project was providing me with a connection that was shared between all data access class.

I was sure I was closing all the connection, but I ended up closing nothing.  Let’s take a look at the MainConnection property of the Sql class :

public static SqlConnection GetMainConnection
{
    get { return new SqlConnection("connectionstring"); }
}

Each time I was accessing the database, and it was accessed a lot (20 times per request), I ended up with a lot of open connections. Enough to make SQL Server unavailable for some time. You could argue that it was my mistake and I shouldn’t be assuming things like this. But in my opinion, it’s a case of bad naming. The choice of name for the property that return me a connection was inconstant. If I access a property, I assumed that if I call it five times in a row, I would have five times the same result. The best choice would have been to create a method that return a new connection and name it accordingly.

public void SqlConnection GetNewSqlConnection()
{
    return new SqlConnection("connectionString");
}

It would have reduced the ambiguity and would have been a lot more easy to read. Good naming is very important and can greatly enhance productivity on a project. Good code should be easy to read and you should not ask yourself if a calling a method would cause side-effects or not doing what it said it should.

Failed to generate a user instance of SQL Server

I got this strange error when adding a database to the App_Data folder of a web application project :

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

Deleting the C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data folder has resolved the problem. I had to try twice after that for adding it correctly.

The importance of database versioning

2-3 months ago, I have deployed the application I was working on the client. Since then a lot of changes have been made and I did not work on this project. The database changed significantly but the sql script have not been updated with the latest changes. Now, I have to get back on the project to make an update to the client. I can generate a new set of scripts to create a new database, but how am I suppose to update the production database ...

Before I left, I gave instruction on how to version every changes to the database. I wanted to have scripts with every changes that have been made and then run them on the client on future update.

Let's see the pros and cons of not doing so :

PROS :

• Save time when developing

CONS :

• No way to easily update an existing database
• Loose time trying to figure out what is changed
• No confidence that you will update the existing database correctly
• No way to get back in time if needed

Please, version your sql script! It will be much easier for those doing the futur work.

Getting started with NHibernate

Without digging in too much detail about NHibernate, I will presume you are familiar with what this O/R Mapper can do.

The application configuration file :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
  </configSections>
  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
      <property name="hibernate.dialect">NHibernate.Dialect.MsSql2005Dialect</property>
      <property name="hibernate.connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="hibernate.connection.connection_string">Server=.SQLEXPRESS;Initial Catalog=RecipeHelperTest;User Id=recipeuser;Password=recipeuser;</property>
      <mapping assembly="RecipeHelper.Domain" />
    </session-factory>
  </hibernate-configuration>
</configuration>

This is standard stuff that you can find in the documentation. One interesting point however is the mapping. You can specify where your domain object to be persisted are. This will allow you to not worry about specifying them in code.

Creating a mapping file :

To be able to persist your entities, you'll need to create a mapping file. In my opinion, this is the hardest thing to learn about NHibernate. But don't worry, it is well documented.

The strategy I am using is one file per entities, in the same assembly. I also use the same name with the extension ".hbm.xml". The trickiest part is that you need to set the Build Action to "Embedded Resource". Once all this is completed you can now start writing your mapping information :

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="RecipeHelper.Domain" namespace="RecipeHelper.Domain" default-lazy="false">
  <class name="Recipe" table="Recipes" >
    <id name="ID" column="ID" type="int" access="field.lowercase-underscore">
      <generator class="native" />
    </id>
    <property name="Name" column="Name" type="string" not-null="false"/>
    <property name="Reference" column="Reference" type="string" not-null="false" />
    <property name="Rating" column="Rating" type="int" not-null="false" />
  </class>
</hibernate-mapping>

Preparation for using NHibernate :

The biggest performance hit is the creation of the ISessionFactory. For this reason, it should only be instantiated once. I created a singleton that will allow me to have access to a single session factory.

using NHibernate;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;

namespace RecipeHelper.Domain
{
    public class NHibernateConfig
    {
        private static ISessionFactory _sessionFactory;

        static NHibernateConfig()
        {
            Configuration config = new Configuration();
            
            _sessionFactory = config.Configure().BuildSessionFactory();
        }

        public static ISessionFactory GetSessionFactory()
        {
            return _sessionFactory;
        }

    }
}

Conclusion :

This isn't a complete tutorial to get started with NHibernate but should cover the essential steps to prepare your project to use it.

Select a number of rows based on a parameter using T-SQL

Today I was trying to select a predetermined number of rows in a table using a parameter... Syntax Error.

SELECT TOP @n ID FROM TableName

After a small research I found two solutions to this problem. First, the SET ROWCOUNT :

SET ROWCOUNT @n

SELECT ID FROM TableName

SET ROWCOUNT 0

The second solution is with dynamic SQL :

declare @a int, @str varchar(100)

set @a = 10

set @str = 'select top ' + cast(@a as varchar(100)) + ' * from Orders'exec (@str)

A little more complex, but worth to take a look at it. Oh and also, on the new SQL Server that will be coming soon, this problem is solved and you can pass parameter to the TOP operator.