C# MySql how to bind stored procedure with dataGridView

Question

I have here stored procedure and I don't know how to bind to the datagridview, need help with this?

here mysql code stored procedure:

SELECT local, SUM(free), SUM(busy), SUM(pause)
FROM (
SELECT local.locations AS local,
CASE car_stat_loc.id_status WHEN 1 THEN 1 ELSE 0 END AS free,
CASE car_stat_loc.id_status WHEN 2 THEN 1 ELSE 0 END AS busy,
CASE car_stat_loc.id_status WHEN 3 THEN 1 ELSE 0 END AS pause
FROM local, car_stat_loc
WHERE local.id_loc = car_stat_loc.id_status
) AS a
GROUP BY local
ORDER BY local

here C# code calling stored procedure:

private void dataGridView3_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    connect.Open();
    MySqlCommand camd = connect.CreateCommand();
    camd.CommandText = "my_pr2";
    camd.CommandType = CommandType.StoredProcedure;

    MySqlDataReader rez;
    rez = camd.ExecuteReader(CommandBehavior.CloseConnection);
    dataGridView3.DataSource = rez;
    camd.ExecuteNonQuery();
    connect.Close();
}

If I have mistakes, please help to fixed, I am new from this!

Answer 1

first create a static class for my sql helper

using System;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;

namespace MainVoteWeb.Helpers
{
    public static class MyHelper
    {
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["VoteContext"].ConnectionString;

        public static DataTable MyGetData(MySqlCommand command)
        {
            MySqlConnection connection = new MySqlConnection();
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataTable data = new DataTable();

            try
            {
                connection.ConnectionString = ConnectionString;
                command.CommandTimeout = 500;
                command.Connection = connection;
                adapter.SelectCommand = command;

                adapter.Fill(data);
                return data;
            }
            catch (Exception e)
            {           
                return data;
            }
            finally
            {
                connection.Close();
            }
        }

        public static int MyExecuteNonQuery(MySqlCommand command)
        {
            MySqlConnection connection = new MySqlConnection();
            int rowsAffected = 0;

            try
            {
                command.CommandTimeout = 500;
                connection.ConnectionString = ConnectionString;
                connection.Open();
                command.Connection = connection;

                rowsAffected = command.ExecuteNonQuery();
                return rowsAffected.GetString();
            }
            catch (Exception e)
            {
                return 0;
            }
            finally
            {
                connection.Close();
            }
            return rowsAffected.ToString();
        }


    }
}

in the private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["VoteContext"].ConnectionString; change the "VoteContext" into the name of your connection string i.e

 <connectionStrings>
<add name="NewContext" connectionString="Server=127.0.0.1;Database=mysqlvotedb;user id=root; password=" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

should be named as private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["NewContext"].ConnectionString;

then on your button

private void dataGridView3_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    List<local> localList = new List<local>();
    MySqlCommand camd = new MySqlCommand();
    camd.CommandText = "my_pr2";
    camd.CommandType = CommandType.StoredProcedure;

    //NOTE: MyExecuteNonQuery is for INSERT UPDATE AND DELETE., MyGetData is for Select/Retrieve

    //if the stored procedure has parameter
    /*
        command.Parameters.AddWithValue("intLocalId", p.localId).Direction = ParameterDirection.Input;
        command.Parameters.AddWithValue("strLocalName", p.localName).Direction = ParameterDirection.Input;            
    */
    try
    {
        DataTable dt =  MyHelper.MyGetData(camd);
        foreach (DataRow row in dt.Rows)
        {
            local item = new local();

            item.localId = Convert.ToInt32(row["localId"]);
            item.localName = Convert.ToString(row["localName"]);
            item.localDetails = Convert.ToString(row["localDetails"]);

            localList .Add(item);
        }

    }

    dataGridView3.DataSource = localList ;
    dataGridView3.AutogenerateColumns = true;
    //no need to open and close the connection. the MyHelper did that already
    //camd.ExecuteNonQuery();
    //connect.Close();


}

ADVANTAGE: you can use the MyHelper class to any mysql operations, regardless of the class. just provide the name of the stored procedure and execute it

Answer 2

Try this

cmd = new MySqlCommand("ur_sp_name", cn);
            cn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            Fill(ds);
            dg.DataSource = ds;
            dg.DataBind();
            cn.Close();