call mysql stored procedure in c#

Question

This is my stored procedure:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(goals = ''',
      goals,
      ''', round(value, 2), NULL)) AS ',
      goals
    )
  ) INTO @sql
FROM sgwebdb.dim_module;
SET @sql = CONCAT('SELECT alternative, ', @sql, ' FROM sgwebdb.dim_module GROUP BY 
alternative');

prepare stmt from @sql;
execute stmt;

I need to call this procedure in below code instead of below MySQL query (query1)

C# code -->

protected void Page_Load(object sender, EventArgs e)
    {
        BindGrid(); 
    }

    private void BindGrid()
    {
        string query1 = "SELECT alternative as 'Alternative',max( case when goals='G1' then round( value, 2 ) end ) as 'Goal 1',max( case when goals='G2' then round( value, 2 ) end ) as 'Goal 2',max( case when goals='G3' then round( value, 2 ) end ) as 'Goal 3',max( case when goals='G4' then round( value, 2 ) end ) as 'Goal 4' from sgwebdb.dim_module group by alternative";

        this.GridView1.DataSource = DataManager.DatabaseManager.GetOrCreateConnection(DataManager.DatabaseManager.ConnectionType.MySQL).GetData(query1);
        GridView1.DataBind();
        for (int n = 0; n < (GridView1.Rows.Count - 1); n++)
        {
                            Textval.Text = GridView1.Rows[n].Cells[1].Text;
                            double gdval = Convert.ToDouble(Textval.Text);
         }


        }

Inplace of Query1 in c# code how can I call above MySQL procedure ?

@PreetSangha how can I call MySQL procedure inplace of MySQL query — vim, May 07 '14 at 03:12
possible duplicate of [this](http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) — jomsk1e, May 07 '14 at 04:56
@jomsk1e this duplicate is also not explaining that how to call procedure's body.its calling only procedure's name. — vim, May 07 '14 at 06:22
because that's the only way to call a stored proc in c#! And I don't get what you mean with "how to call procedure's body". — jomsk1e, May 07 '14 at 07:24
@jomsk1e I mean to say if we call MySQL procedure by its name then procedure's main body must be defined somewhere.like if we call one procedure "sqlproc" in our code then sqlproc's functionality also defined somewhere and that should execute as well. — vim, May 07 '14 at 08:42
@vim: The logic of the Stored Procedure lies on the MySQL database side and not in the application code - You only refer to it by name when calling a Stored Procedure. Much like using an API, those implementation details are hidden from the caller. You just need the name (and necessary parameters, if any). — Derek W, May 08 '14 at 02:23
@DerekW I saved procedure in db and then in application code I refer it by name and getting error MySql.Data.MySqlClient.MySqlException: Unknown prepared statement handler (stmt) given to EXECUTE — vim, May 10 '14 at 17:38

Derek W · Answer 1 · 2014-05-07T03:34:25.563

2

When you create the MySqlCommand object you need to set the Name of the Stored Procedure in the CommandText property and set the CommandType property to CommandType.StoredProcedure.

Here's a code sample setting up a MySqlCommand object to do just that:

MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = "NameOfYourStoredProcedure";
command.CommandType = CommandType.StoredProcedure;

A little caveat with adding parameters is that the names of the parameters in the stored procedure must match those added to the Parameters collection of the MySqlCommand object.

edited May 07 '14 at 03:34

answered May 07 '14 at 03:27

Derek W

9,708
5
58
67

but here u r not specifying procedure's body.you specified only procedure's name. – vim May 07 '14 at 06:20
Yes, that is how you call a stored procedure from C#. See here for documentation sample: http://msdn.microsoft.com/en-us/library/d7125bke.aspx – Derek W May 07 '14 at 13:22

call mysql stored procedure in c#

1 Answers1

Linked