DB Null conversion

Question

I have written a relatively big insert statement, and some of the fields are null.

I cannot convert from db null to other types and I don't really want to check Convert.IsDBNull for every single item

What should I do?

 System.Data.OleDb.OleDbCommand iCommand = new System.Data.OleDb.OleDbCommand("Insert into ProductCode values('" +
                    greader["CODE"].ToString() +
                    "','" + Convert.ToChar(greader["DISC_CODE01"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE02"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE03"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE04"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE05"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE06"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE07"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE08"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE09"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE10"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE11"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE12"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE13"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE14"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE15"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE16"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE17"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE18"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE19"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE20"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE21"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE22"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE23"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE24"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE25"]) +
                    "','" + Convert.ToChar(greader["DISC_CODE26"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT01"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT02"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT03"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT04"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT05"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT06"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT07"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT08"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT09"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT10"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT11"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT12"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT13"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT14"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT15"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT16"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT17"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT18"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT19"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT20"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT21"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT22"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT23"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT24"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT25"]) +
                    "','" + Convert.ToDouble(greader["DISC_PCT26"]) +
                    "')", iConnect);

without doing in every item, do you expect to have it implemented in Convert.ToDouble and Convert.ToChar ? — PraveenVenu, Mar 20 '12 at 13:24
Why don't you do something like `Convert.ToDouble(greader["DISC_PCT05"]) ?? 0` — Ivan Crojach Karačić, Mar 20 '12 at 13:25
@justin kirk: If you want your application to be solid you need to check for null conditions and if needed use stored procedure so that application does not have any sql injection problems — dhinesh, Mar 20 '12 at 13:28
oh wow, people on this form are so childish lol,... as if that's a bad question, i simply assumed there would be an easier way then checking for every single item ... — , Mar 20 '12 at 13:30
Database normalization, parameters usage required. After that your work should be easier and you won't need to check so much items. — Renatas M., Mar 20 '12 at 13:38
Stack Overflow is a form AND a forum, anyway, thank you Ivan Crojach, that seems simple enough to implement — , Mar 20 '12 at 13:39

score 1 · Accepted Answer · edited Mar 20 '12 at 13:59

Without analysing why you're using the above method, I'd simply write my own method to do this. Simply write your own Convert.ToChar and Convert.ToDouble, something like this:

public class MyConvert
{
    public static char ToChar(object value, char defaultValue)
    {
        return Convert.IsDBNull(value) ? defaultValue : Convert.ToChar(value);
    }
}

So rather than having a Convert.ToChar(greader["DISC_CODE01"]) you would use MyConvert.ToChar(greader["DISC_CODE01"], ''). Do the same for double as well.

alex.b · Answer 2 · 2012-03-20T13:40:38.113

Dictionary<string, object> greaderDic = new Dictionary<string,object>();
foreach(var item in greader.Items/*or whatever you have to enumerate your greader*/)
{
greaderDic.Add(item.Name, GetValue(item.Name, item.Value));// hope you have something like Name or Value properties
}

object GetValue(string name, objetc value)
{

if (name.StartsWith("DISC_CODE"))
return value == null? "NULL" : Convert.ToChar(value)

if (name.StartsWith("DISC_PCT"))
return value == null? "NULL" : Convert.ToDouble(value);

throw new Exception("Mapping not found for " + item.Name);

}

Additionally replace in old code all greader tokens by greaderDic

DB Null conversion

2 Answers2