VB.Net IIf function when adding SqlParameter

Question

I am rewriting this long INSERT statement and parameters that look like this:

cmd.Parameters.AddWithValue("@Website", General.fnSQLNullValues(tWebsite.Text)) 

Where is General.fnSQLNullValues:

  Public Shared Function fnSQLNullValues(ByVal sValue As Object, Optional ByVal Len As Integer = 999999) As Object If sValue Is Nothing Then Return DBNull.Value fnSQLNullValues = IIf(sValue.ToString.Length = 0, DBNull.Value, Left(sValue.ToString(), Len)) End Function 

I don’t like it at all, and there seems to be a lot of code for this,

  cmd.Parameters.AddWithValue("@Website" , If(tWebsite.Text , DBNull.Value)) 

from my understanding that one line of code there DBNull.Value will replace tWebsite.Text as a value if tWebsite.Text is null or not accepted, and it seems to me that I am doing the same thing as another function in General. Is this right and one way is better than the other?

In addition, I get a warning: “It is not possible to deduce a common type, a supposed object” from the second path, but it seems to me that the first method used a universal object anyway, so I don’t know if I should just ignore this warning

Answer 1

Strings are a bit complicated when working with database parameters in code. If you have an empty string, it will pass an empty string value for insertion. However, if you set your string to Nothing, it will enter NULL into the database. Otherwise, it will set an empty string.

Ideally, you should have some kind of business layer on top of the data layer that will check the tWebsite.Text value and either pass the Nothing value or the Text value to your function, which sets the parameters. This is slightly different from the first code example above. Thus, your data layer should only execute the following command:

 cmd.Parameters.AddWithValue("@Website" , valueWebsite)) 

... and no hassle.

For other data types (such as integers, decimals, etc.) check Nullable Types and work with them. It is very easy to work with them. By default, they are initialized to Nothing, so if you do not assign a value to it (from your input fields), the value remains Nothing and will correctly insert NULL into the database.