CODEWITHSUNDEEP
c#mysqlsql
ykh
ykh

Reputation: 1825

C#-MySQL Pass Null Paratmer To Stored Procedure

Since MySQL doesn't allow default values for parameters, i have been trying to send Null values from the code but unfortunately nothing is working.

I have read many questions here and i know that there is a workaround by sending empty string and then doing an if-statement in the stored procedure.

My question is, isn't there anyway i could send a value from C# that MySql could interpret as a null value. Reason i am asking this cause i am using IS NULL checks which is already implemented in all of sp's becuase i have migrated from MSSQL to MySQL.

I have tried regular null and DBNull.Value but i keep getting an Unhandled type encountered exception.

CREATE DEFINER=`root`@`localhost` PROCEDURE `User_Check_Avialability`(  
   IN Username NVARCHAR(50),
   IN Email NVARCHAR(50)
    )

BEGIN   
    SELECT  COUNT(*) AS NumberOfUsers
    FROM    User
    WHERE   (( User.Username = Username OR Username IS NULL ) AND ( User.Email = Email OR Email IS NULL ));

END

Here is my code:

    try
{
    using (MySqlConnection mySqlConnection = new MySqlConnection(Settings.Default.ConnectionString))
    {
        using (MySqlCommand mySqlCommand = new MySqlCommand())
        {
            mySqlCommand.Connection = mySqlConnection;
            mySqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            mySqlCommand.CommandText = "User_Check_Avialability";

            if (!string.IsNullOrEmpty(objUsers.Username))
                mySqlCommand.Parameters.AddWithValue("Username", objUsers.Username);
            else
                mySqlCommand.Parameters.AddWithValue("Username", DBNull.Value); //Also tried null                      

            if (!string.IsNullOrEmpty(objUsers.Email))
                mySqlCommand.Parameters.AddWithValue("Email", objUsers.Email);
            else
                mySqlCommand.Parameters.AddWithValue("Email", DBNull.Value); //Also tried null

//Also tried
mySqlCommand.Parameters["Username"].IsNullable = true;
mySqlCommand.Parameters["Email"].IsNullable = true;

            bool isAvailable = false;

            mySqlConnection.Open();
            using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
            {
                if (!mySqlDataReader.HasRows)
                    isAvailable = true;
                else
                {
                    while (mySqlDataReader.Read())
                    {
                        if (int.Parse(mySqlDataReader["NumberOfUsers"].ToString()) == 0)
                            isAvailable = true;
                    }
                }
            }

            return isAvailable;
        }
    }
}
catch (Exception ex)
{
    throw new Exception(ex.ToString());
}

Upvotes: 2

Views: 2485

Answers (1)

ykh
ykh

Reputation: 1825

Thanks to the valuable comments, the solution is that you should specify the type of the parameter and make it Nullable.

Here is the sample code:

                    mySqlCommand.Parameters.Add("Username", MySqlDbType.VarChar);
                    mySqlCommand.Parameters["Username"].Direction = System.Data.ParameterDirection.Input;
                    mySqlCommand.Parameters["Username"].IsNullable = true;

                    if (!string.IsNullOrEmpty(objUsers.Name))
                        mySqlCommand.Parameters["Username"].Value = objUsers.Name;

As you can see here, even if you don't pass a null value there will be no problem because the parameter is already added and if there is no value then it will be null.

Upvotes: 3

Related Questions