Berichten met label database

In this post I want to shine a light on a different aspect of developing on one OS to deliver on a different OS: different implementations of the .NET framework can have different behaviour. What runs on one, might crash on the other. That happened to me when accessing my SQL Server database using ADO.NET.

Storing data using plain ADO.NET

Ok, you might argue that there is no need to talk about plain ADO.NET, since nobody in his right mind is actually doing that anymore. Of course we -developers- have evolved and use document-databases like CouchDB and Raven DB or ORMs like NHibernate and Entity Framewok.

But every now and then you need code like the code below. In my case because we were building a demo and needed to be done quickly. Which we were not, because of the problems I ran into when trying to store null values in the database.

Book book = new Book {
	Id = Guid.NewGuid(),
	ISBN = "123-456-222",
	Title = "Through the looking glass"
};
 
using (SqlConnection connection = new SqlConnection ("some connections string")) {
	connection.Open ();
	using (SqlCommand cmd = connection.CreateCommand ()) {
		cmd.CommandText = @"INSERT INTO Book ([Id], [ISBN], [Title], [Publisher], [SuggestedPrice])
		       VALUES (@Id, @ISBN, @Title, @Publisher, @SuggestedPrice)";
 
		cmd.Parameters.AddWithValue ("@Id", book.Id);
		cmd.Parameters.AddWithValue ("@ISBN", book.ISBN);
		cmd.Parameters.AddWithValue ("@Title", book.Title);
		cmd.Parameters.AddWithValue ("@Publisher", book.Publisher);
		cmd.Parameters.AddWithValue ("@SuggestedPrice", book.RetailPrice);
 
		var rowsAffected = cmd.ExecuteNonQuery();
		if (rowsAffected == 0)
		{
			Console.WriteLine("No rows inserted!");
		}
	}
}

The sql to create the table is like this:

CREATE TABLE Book (
	Id			uniqueidentifier	NOT NULL,
	ISBN			varchar(255)		NOT NULL,
	Title			varchar	(128)	NOT NULL,
	Publisher		varchar	(128)	NULL,
	SuggestedPrice	money 			NULL
) ON [PRIMARY]

Finally the Book class is this:

class Book
{
	public Guid Id {
		get;
		set;
	}
 
	public string ISBN {
		get;
		set;
	}
 
	public string Title {
		get;
		set;
	}
 
	public string Publisher {
		get;
		set;
	}
 
	public decimal? RetailPrice {
		get;
		set;
	}
}
}

The reason to use SqlXXXX objects in stead of programming against interfaces like IDbConnection and IDbCommand is that I know I have a SQL Server database and will not change that in the course of this project and the fact that I have this nice simple syntax for setting parameters on the Command object:

cmd.Parameters.AddWithValue ("@Title", book.Title);

The AddWithValue-method is not on the IDbCommand interface, but is available as a public method on the SqlCommand class.

The book object comes from somewhere outside of this piece of code normally, and I cannot make any assumptions on the properties that are filled or not. For example the RetailPrice property is a nullable decimal, so it might therefore be null. No problem, since the database column SuggestedPrice is also nullable.

No problem? No problem in Mono. Run this code in MonoDevelop and store a book with a null value for the retaill-price and the publisher. Sweet.

A problem on Windows

Copy this code (or the compiled assemblies) to your Windows VM and run it there. You get an exception:

Why is that? Why would .NET complain about the Publisher parameter, suggesting it is not there? It contains a null value, right, but the parameter itself is there anyway.

So the message itself is confusing. It took me quite some time (and the use of the great SqlProfiler from AnjLab) to find out that the problem was in the null values. That must have happened to other people before. So Google to the rescue? Even that took some time before I found a thread on a PC Review forum from 2005 that suggested to go through all the parameters in your command to see if they are null or not and then set them to DBNull.

The platform independent version

Are you kidding me? Every time and everywhere I have a Command object and add Parameters to it, I must go through all the parameters like this?

foreach (SqlParameter parameter in cmd.Parameters)
{
    if (parameter.Value == null)
    {
        parameter.Value = DBNull.Value;
    }
}

And that has been like that since .NET 2.0? No improvements in the last 5 years? Probably, the reason you haven’t bumped into this problem lately, is that you have been using NHibernate (I did) and that solved it for you. I haven’t tried it with Entity Framework yet, but I guess that would solve it too.

I really think Mono does a better job here. When I set a parameter on a DbCommand object to null, I expect that object to translate that null value to something that the database understands. As is does for any other value.

Download the code

P.S. At one time in the process of finding a solution , I got so frustrated with Microsoft .NET and Visual Studio 2008 (since that somehow stopped building properly) that I decided to run Mono on Windows too. It took me 5 minutes to download and install Mono on my VM, then 5 more minutes to discover the existence of XPS (the Mono web-server) and I had my webservice running! Unfortunately, that was not an option for our production server, but to me it proved that the guys at Mono do a really good job.