在c#中调用mysql存储过程

问题描述：

这是我的存储过程:

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;

我需要在下面的代码中调用这个过程，而不是在 MySQL 查询 (query1) 下

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

C# 代码 -->

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);
         }


        }

在 C# 代码中代替 Query1 我如何调用上面的 MySQL 过程?

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

答

当你创建 MySqlCommand 对象时，你需要在 CommandText 中设置存储过程的名称属性并将 CommandType 属性设置为 CommandType.StoredProcedure.

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.

这是一个设置 MySqlCommand 对象的代码示例:

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;

添加参数需要注意的一点是，存储过程中的参数名称必须与添加到 MySqlCommand 对象的 Parameters 集合中的参数名称相匹配.

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.

在c#中调用mysql存储过程

相关推荐