tags:

views:

93

answers:

3

Let's say I have a MySql stored procedure that inserts a record with some nullable CHAR fields.

In VB.NET if I don't check for Nothing (or Null in other languages), I get an exception from the db driver, so I write:

command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("_name", if(name Is Nothing, "", name)).Direction = ParameterDirection.Input;

And this is the first thing I don't like; I'd like to pass Nothing and the driver knows it has to put NULL in the Db. But then, in the stored procedure, I have to check back the field if it is empty:

INSERT INTO mytable
(
    name,
-- other 200 char fields
)
VALUES
(
    NULLIF(_name, ''),
-- other 200 char fields
)

Ugly uh? I have to check for nothingness/emptiness twice. Is there a better, more direct, way?

Even worse, for non reference types (i.e: Integers) the check for nothingness makes no sense, since a primitive type can't be nothing (yes, I could set an Integer to a non meaningful value, say -1 for a positive id, but...).

A: 

Use DbNull.Value instead of the empty string.

klausbyskov
that doesn't solve the double check.
vulkanino
+2  A: 

Use next if you want to insert NULL:

command.Parameters.AddWithValue("_name", name ?? DBNull.Value);

means the same as

if(name != null)
    command.Parameters.AddWithValue("_name", name);
else
    command.Parameters.AddWithValue("_name", DBNull.Value);

See more about null-coalescing operator on MSDN

And if you want to insert empty char '' you next:

command.Parameters.AddWithValue("_name", name ?? '');
abatishchev
useful, but not in VB, only C#. and it doesn't solve the conversion in the SP.
vulkanino
I've tried, but at least with CHAR fields, DBNull.Value can't be set: the db driver raises an exception.
vulkanino
@vulkanino: right, C#, your post wasn't tagged with `vb.net` and C# syntax used. Anyway in VB.NET you can use `If(name, DBNull.Value)`
abatishchev
@vulkanino: What version of MySQL .NET connector are you using? The latest is `6.3.4`. Try to upgrade
abatishchev
I'm using 6.3.4 already, the DBNull thing doesn't work, please read my own answer below, it works like I wanted.
vulkanino
@vulkanino: Then approve your own answer as correct
abatishchev
A: 

DbNull.Value doesn't solve the problem, the exception is thrown if you don't specify that the parameter is nullable.

Dim par As New MySqlParameter("p1", Nothing)
par.IsNullable = True
par.MySqlDbType = MySqlDbType.Int32 ' or any other type you need '
par.Direction = ParameterDirection.Input
command.Parameters.Add(par)
command.ExecuteNonQuery()

This way it works. I don't have to check for the empty string (or weird "impossible" value) in the SP.

Of course you can write a shared generic method to easy the parameter setting, working for any database/language type:

Public Shared Function GetNullableSqlParameter(Of T As IComparable)(ByVal parameterName As String, ByVal parameterType As MySqlDbType, ByVal value As T, Optional ByVal nonNullable As T = Nothing) As MySqlParameter

        Dim par As New MySqlParameter
        par.ParameterName = parameterName
        par.MySqlDbType = parameterType
        par.Direction = ParameterDirection.Input

        If value Is Nothing OrElse (nonNullable IsNot Nothing AndAlso nonNullable.Equals(value)) Then
            par.IsNullable = True
            par.Value = DBNull.Value
            par.SourceColumnNullMapping = True
        Else
            par.IsNullable = False
            par.Value = value
        End If

        Return par
    End Function

And call it like:

command.Parameters.Add(General.GetNullableSqlParameter("_zip", MySqlDbType.String, zipcode))
vulkanino