Consider the following code (didn't compile it so forgive me for eventual bugs):
private static List<Book> ReadData(string connectionString)
{
List<Book> books = new List<Books>();
string queryString =
"SELECT Author, Title, Year FROM Books";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Book book = new Book();
book.Author = reader("Book");
book.Title= reader("Title");
book.Year= reader("Year");
books.Add(book);
}
reader.Close();
return books;
}
}
}
This code should work pretty well, the problem comes when you start using nullables. If year were int? instead of int this code this would throw an exception as soon as a null value appears in the reader.
Null in Sql Server and in c# are two different things.
From the database points of view a field has three values:
- A value. For example 1993.
- A null value. We left the year empty because we didn't know the year.
- Empty, the book does not exist.
Now you might argue that if the row was empty a try would anyway give you an error and the pragmatical side of me totally agrees, but still, the issue stems from the fact that null in C# is the absence of a reference to an object, while null in a database is an uninitialized or empty value. It exists and we have to deal with it.
I like solving this with extension methods (actually I am generally pretty hooked on extension methods).
The standard approach is to use something like this:
public static int? ToNullableInt(this int value)
{
return value.IsNull ? (int?) null : value.Value;
}
And then call the reader like:
book.Year= reader("Year").ToNullableInt();
It is a reasonable solution. But you would have to write those extension methods for every nullable data type.
A single function version that handles all data types plus basic conversion but also is slightly slower looks something like this:
private static T Get<T>(this SqlDataReader reader, string index, T defaultValue = default(T))
{
var t = reader[index];
if (t == DBNull.Value)
return defaultValue;
Type type = typeof(T);
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
{
var subType = Nullable.GetUnderlyingType(type);
return (T)Convert.ChangeType(t, subType);
}
return (T)System.Convert.ChangeType(t, typeof(T));
}
This would be called like this:
book.Year= reader.Get<int?>("Year");
When microseconds are not the issue, I prefer that solution, but I am always looking for something even better. Ideas? :)
