Using stored procedure with ODBC and MySql in C# and ASP.Net

Question

The hosting where I am registered, does not support temporarily MySql.Data.MySqlClient using C# ASP NET and they cannot say when this will be possible.

The FAQ suggests to use the MySQL ODBC 5.2 UNICODE Driver to connect to my mysql database and exec the stored procedure.

I have never used this type of ODBC connection in the past and in fact I get the empty return.

The output value from sProc is null.

The stored procedure on the MySQL database, using phpmyAdmin, working correctly.

The output value from sProc is 3.

I found many different suggestions on the web but without solving my problem.

How to do resolve this?

My code below.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            try
            {
                using (OdbcConnection connection =
                    new OdbcConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
                {
                    using (OdbcCommand cmd =
                        new OdbcCommand("{Call s_sproc(?,?)}", connection))
                    {
                        cmd.Connection.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@pin_dd", DateTime.Now.ToString("dd"));
                        cmd.Parameters.AddWithValue("@out_userid", OdbcType.Char).Direction = ParameterDirection.Output;
                        cmd.ExecuteNonQuery();
                        cmd.Connection.Close();

                        if (cmd.Parameters["@out_userid"].Value != DBNull.Value)
                        {
                            int number = Convert.ToInt32(cmd.Parameters["@out_userid"].Value);

                            if (number > 0)
                            {
                                Page.ClientScript.RegisterStartupScript(this.GetType(), "Alert",
                                    "alert('" + number.ToString() + "');", true);
                            }
                            else
                            {
                                Page.ClientScript.RegisterStartupScript(this.GetType(), "Alert",
                                    "alert('null');", true);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("operation failed!", ex);
            }
            finally
            {

            }
        }
    }
}


DELIMITER $$
CREATE DEFINER=`Sql_user`@`%` PROCEDURE `s_sproc`(
IN `pin_dd` VARCHAR(255), 
OUT `out_userid` VARCHAR(255))
BEGIN
SET @out_userid := NULL;
SET @s = CONCAT('SELECT DAY(t_Date) from t_table WHERE DAY(t_Date) = ',pin_dd,' INTO @out_userid;');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE `stmt`;
SET out_userid := @out_userid;
SELECT @out_userid;
END$$
DELIMITER ;

@out_userid
3

Update #01 -- Solution @AnandSowmithiran

        try
        {
            using (OdbcConnection connection =
                new OdbcConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
            {
                using (OdbcCommand cmd =
                    new OdbcCommand("{Call s_sproc(?,?)}", connection))
                {
                    cmd.Connection.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();


                    cmd.Parameters.Add("@pin_dd", OdbcType.VarChar, 100);
                    cmd.Parameters["@pin_dd"].Value = DateTime.Now.ToString("dd");

                    cmd.Parameters.Add("@out_userid", OdbcType.VarChar, 100);
                    cmd.Parameters["@out_userid"].Direction = ParameterDirection.Output;

                    string getValue = cmd.ExecuteScalar().ToString();

                    if (!String.IsNullOrEmpty(getValue))
                    {
                            Page.ClientScript.RegisterStartupScript(this.GetType(), "Alert",
                                "alert('" + getValue.ToString() + "');", true);
                    }
                    else
                    {
                            Page.ClientScript.RegisterStartupScript(this.GetType(), "Alert",
                                "alert('null');", true);
                    }

                    cmd.Connection.Close();
                }
            }
        }
        catch (Exception ex)
        {
            throw new ApplicationException("operation failed!", ex);
        }
        finally
        {

        }

Instead of `AddWithValue()`, use the Add method and then set the value and the "direction" property, refer SO [answer](https://stackoverflow.com/a/22111654/14973743) — Anand Sowmithiran, Apr 03 '23 at 08:26
Also, you should be using ExecuteScalar() instead of ExecuteNonQuery. — Anand Sowmithiran, Apr 03 '23 at 08:28
@AnandSowmithiran Thanks for help. Please see **update #01** in the question. Now I have error `String[2]: the Size property has an invalid size of 0.` — George A. Custer, Apr 03 '23 at 08:52
You need to mention Size property for string parameter types, refer [this](https://learn.microsoft.com/en-us/dotnet/api/system.data.odbc.odbcparameter.size?view=dotnet-plat-ext-7.0) — Anand Sowmithiran, Apr 03 '23 at 08:57
@AnandSowmithiran Thank you so much for help. Now the output value is correct! — George A. Custer, Apr 03 '23 at 09:17

score 1 · Accepted Answer · answered Apr 03 '23 at 09:53

Here are the changes to be done to get this working,

Add the Parameters to the stored procedure by calling the Parameters collection Add method, instead of the AddWithValue().
Mention the size of the string typed parameters
Instead of using ExecuteNonQuery, you should be using ExecuteScalar

Using stored procedure with ODBC and MySql in C# and ASP.Net

1 Answers1