.NET and MySql parameters, AddWithValue NULL variables, how to avoid checking for zeros

Question

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

In VB.NET, if I don't check 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 would like to pass Nothing, and the driver knows that he must put NULL in Db. But then, in the stored procedure, I have to check the field if it is empty:

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

Awful, huh? I have to double check the void / void. Is there a better, more direct way?

, (.. ) , (, , -1 id, ...).

Answer 1

DbNull.Value , , , NULL.

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()

, . ( "" ) SP.

, , , /:

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

:

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

Answer 2

, NULL:

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

,

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

null-coalescing MSDN

char '', :

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

Answer 3

Use DbNull.Valuean empty string instead.