Where has my stored procedure parameter gone?

I had to change the database and some code to allow for proper saving/reading of Unicode from the application. Changing the parameter type in the stored procedure from varchar to nvarchar and doing the same change to the appropriate database table columns was the easy part. Doing some adjustments to the code, well, was also the easy part but with a little trick hidden inside.

So, here's how the parameters are added to the collection:

_collParam.Add(DataManager.BuildSqlParameter("@MessageText", SqlDbType.VarChar,
 ParameterDirection.Input, Message.ToString()));

Just change VarChar to NVarChar, should be easy as pie.

And I end up with the SqlException "Procedure or function 'udp_MyProcedure_ups' expects parameter '@MessageText', which was not supplied". So, where has my parameter gone if I can clearly see that I'm adding it? The answer lies within the DataManager.BuildSqlParameter() function. Here's part of what it does:

if (value != "")
 {
  if (paramType == SqlDbType.Int)
   param.Value = Convert.ToInt32(value);
  else if (paramType == SqlDbType.Bit)
   param.Value = Convert.ToBoolean(value);
  else if (paramType == SqlDbType.DateTime)
  {
   param.Value = Convert.ToDateTime(value);
  }
  else if (paramType == SqlDbType.VarChar)
   param.Value = Convert.ToString(value);
  else if (paramType == SqlDbType.Float)
   param.Value = (float)Convert.ToDecimal(value);
 }

So what happens when the parameter does not belong to any of the types? It gets no value assigned, causing the exception. There is no "default" value. Easy to fix, but worth noting.

by Evgeny. Also posted on my website