3/9/2006, 8:06 PM Arrix
I’ve been happy with the constructor public SqlParameter (string parameterName,Object value) since I started programming .NET.
But the other day, when I passed the literal 0 as the 2nd parameter, the Value was not set to 0 at all, but an null reference. After a painful 15 minutes scratching my head, I realized that the compiler had chosen another constructor overload of which the 2nd parameter is SqlDbType. This is reasonable because literal 0 matches enum better than Object. But what should I do if I just want to set the Value to 0?
The answer is pass Convert.ToInt32(0) as the 2nd parameter.
SqlParameter p = new SqlParameter("@p", 0); //after this, p.Value == null but SqlDbType is set
SqlParameter p = new SqlParameter("@p", Convert.ToInt32(0)); //after this, p.Value == 0
The strange thing is that what Convert.ToInt32 returns is absolutely the same thing as the literal 0.
This is just a case in which the C# compiler is too clever.
Another case is about the ThreadStart.
Thread t = new Thread(new ThreadStart(ThreadProc));
Are you familiar with the code above? That’s just what we usually write. Not until March 2006 did I know that the ThreadStart constructor can actually be omitted.
Thread t = new Thread(ThreadProc); //OK
Again, the compiler is clever enough to create a "correct" delegate!
After fetching data from the database via ADO.NET, we usually need to cast the database type to the a specific type. This could be done by the cast operator of the programming language (e.g. in C#: ( type-name ) cast-expression). But it’s better to use System.Convert.Toxxx methods because the Convert class is dedicated to perform such conversions and thus more capable and less error prone. We must be extremely careful when dealing with data which might be DbNulls or null references. They cannot be converted to any other types and the Convert.Toxxx will throw an InvalidCastException. Convert.IsDbNull is for checking against DbNull.
In one of my recent projects, I misused the ?? operator when checking an output SqlParameter.
SqlParameter p = new SqlParameter("@p", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
//int id = Convert.ToInt32(p.Value ?? Consts.INVALID_IDENTITY); //wrong, p.Value can never be null
int id = Convert.IsDBNull(p.Value) ? Consts.INVALID_IDENTITY : Convert.ToInt32(p.Value); //OK
The Value property of an SqlParameter is null by default, by when it has gone through an SqlCommand.Execuitexxx call, it can never be null.
After an ExecuteNonQuery call, p.Value can never be null, but possibly DbNull.Value. Don’t use the ?? operator (introduced in C# 2.0) in the DbNull.Value case. The ?? op is only happy with the CLR null reference, not the database null. Be careful!
But confusingly, SqlCommand.ExecuteScaler may return a null reference if the result set is empty and you should check for both null reference and DbNull.