How to pass DBNull in Nvarchar type(procedure paramter) in MySql

Question

I'm using MySQL c# 4

My Procedure parameter is NVARCHAR(100).

So what I'm doing is

MySqlParameter DBParam1 = new MySqlParameter("var_Name", (object)(string.IsNullOrEmpty(Name)? DBNull.Value : Name));

MySqlCommand _DBCommand = new MySqlCommand();
_DBCommand.Connection = _DBConnection;
_DBCommand.CommandText = "udsp_ProcedureName";
_DBCommand.CommandType = System.Data.CommandType.StoredProcedure;
_DBCommand.Parameters.Clear();
_DBCommand.Parameters.Add(DBParam1);

so this is throwing "ErrorMessage":"Unhandled type encountered"

How can i solve this?

what i looked into

How do I Parameterize a null string with DBNull.Value clearly and quickly

Is there a more elegant form for assigning NULL to InsertCommand's NVarChar?

Exception when AddWithValue parameter is NULL

`new MySqlParameter("@var_Name"` , notice `@`. – houssam May 21 '15 at 14:23 — houssam, May 21 '15 at 14:23

Zohar Peled · Answer 1 · 2015-05-21T15:12:19.050

2

You are using the constructor to set the value, while it expects a value of MySqlDBType.

Try this:

MySqlParameter DBParam1 = new MySqlParameter("@var_Name", MySqlType.VarChar)
DBParam1.Value = (object)(string.IsNullOrEmpty(Name) ? DBNull.Value : Name;

edited May 21 '15 at 15:12

answered May 21 '15 at 14:21

Zohar Peled

79,642
10
69
121

score 1 · Accepted Answer · answered Sep 29 '16 at 13:11

Passing DBNull.Value to NVARCHAR type will throw an exception

"ErrorMessage":"Unhandled type encountered"

So I passed this as Empty string.

MySqlParameter DBParam1 = new MySqlParameter("var_Name", (object)(string.IsNullOrEmpty(Name)? string.Empty : Name));

score 0 · Answer 3 · answered Sep 05 '20 at 15:47

if you know that the nullable parameter will always be of string type then you can use the below code. Using this you don't have to replace null with with blank(string.Empty)-

MySqlParameter pm = new MySqlParameter();
                        pm.ParameterName= "ParamName";
                        pm.Direction = System.Data.ParameterDirection.Input;
                        pm.IsNullable = true;
                        if(param.Value!=null)
                            pm.Value = param.Value;
                        else
                        {
                            pm.MySqlDbType = MySqlDbType.VarChar;
                        }

                        _DBCommand.Parameters.Add(pm);

Please note that for the nullable varchar parameters you don't have to set any value at-all instead it will set "IsNullable" to "true" and need to set the "MySqlDbType" to "VarChar". For all not null values it will set the value and no need to set type explicitly. This code is working for me , hope it will help.

How to pass DBNull in Nvarchar type(procedure paramter) in MySql

3 Answers3