用c#调用mysql存储过程

本文关键字：存储过程 mysql 调用 | 更新日期: 2023-09-27 18:01:58

这是我的存储过程:

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

        }

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

用c#调用mysql存储过程

创建MySqlCommand对象时，需要在CommandText属性中设置"存储过程名称"，在CommandType属性中设置"CommandType.StoredProcedure"。

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

MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = "NameOfYourStoredProcedure";
command.CommandType = CommandType.StoredProcedure;
在添加参数时需要注意的一点是，存储过程中的参数名必须与添加到MySqlCommand对象的Parameters集合中的参数名相匹配。
相关文章：