INSERT query - Null vs Empty String .. does it matter?

Question

I have the following code that inserts some data into an Access table. It starts with a comma-separated string, which I split into a string array, and then add the elements of the array as parameters:

string line = "one\ttwo\t\tfour"; string[] values = line.Split('\t'); using (OleDbCommand com = new OleDbCommand("INSERT INTO [MyTable] (" + "[Field1], [Field2], [Field3], [Field4] " ") VALUES (" + "?, ?, ?, ?" + ")", con)) { com.Parameters.Add("@p0", OleDbType.Char, 255).Value = (object)values[0] ?? DBNull.Value; com.Parameters.Add("@p1", OleDbType.Char, 255).Value = (object)values[1] ?? DBNull.Value; com.Parameters.Add("@p2", OleDbType.Char, 255).Value = (object)values[2] ?? DBNull.Value; com.Parameters.Add("@p3", OleDbType.Char, 255).Value = (object)values[3] ?? DBNull.Value; com.ExecuteNonQuery(); } 

In the above example, @p2 will not matter. It is entered into the Access table as a null value instead of a null value. At least I think I'm explaining it right. If I request Field3 is Null , I get no results. If I request Field3 = "" , I get the results. Does it mean empty or empty? Is there a "preferred" option?

Thanks!

Answer 1

Original statement:

 com.Parameters.Add("@p2", OleDbType.Char, 255).Value = (object)values[2] ?? DBNull.Value; 

An empty string is sent from the description '' from values[2] , not DBNull.Value . I would explicitly convert the empty string (or zero) instead of using the operator with zero coalescing (??):

 com.Parameters.Add("@p2", OleDbType.Char, 255).Value = String.IsNullOrEmpty(values[2]) ? DBNull.Value : values[2]; 

Added : since the type specification (OleDbType.Char) prevents DBNull from being sent to the database, I will return to AddWithValue :

 com.Parameters.AddWithValue("@p2", String.IsNullOrEmpty(values[2]) ? DBNull.Value : values[2]); 

This does not indicate a type, so you should accept DBNull or a string. I prefer this approach anyway: if we specify a type, we need to make sure that it is fully consistent with the data type of the field -> let C # handle this.

Answer 2

Well, null definitely doesn't match the empty string. Yes, it is important. But only in your domain. In other words, if the field is really not NULL, and Access interprets DBNull.Value as an empty string, then you just need to consider this when looking for rows without a value. If the column was null in Access, you will not see this behavior.

However, if the field is null, I would recommend that you just send null instead of DBNull.Value . So in other words:

 values[0]; 

instead:

 (object)values[0] ?? DBNull.Value;