C# 调用mysql存储过程

来源：互联网发布：淘宝考试不编辑：程序博客网时间：2024/05/22 14:08

这是一个web api框架下的项目，主要是为了给他人调用。

      public IDictionary<string, IList<v_order_menu>> GetDishes(string date, string windowid, int enjoyid)        {            IDictionary<string, IList<v_order_menu>> v_order_menu = new Dictionary<string, IList<v_order_menu>>();            StringBuilder sql = new StringBuilder();            sql.Append("proc_GetCurrentDishesByWindow");            MySqlParameter[] parameters =            {                new MySqlParameter("@windowNumber",MySqlDbType.VarChar,8),                new MySqlParameter("@enjoyid",MySqlDbType.Int32),                new MySqlParameter("@mydate",MySqlDbType.DateTime)            };            parameters[0].Value = windowid;            parameters[1].Value = enjoyid;            parameters[2].Value = date;            parameters[0].Direction = ParameterDirection.Input;            parameters[1].Direction = ParameterDirection.Input;            parameters[2].Direction = ParameterDirection.Input;            DataSet ds = DbHelperMySQL.GetStoreProcedure(Convert.ToString(sql),parameters);            IList<v_order_menu> order_menu = DbHelperMySQL.DataSetToIList<v_order_menu>(ds, "v_order_menu");            v_order_menu.Add("data", order_menu);            return v_order_menu;        }

这里是调用方法，接受三个参数，返回一个Dictrionnary类型的数据

     public static DataSet GetStoreProcedure(string SQLString, params MySqlParameter[] cmdParms)        {            using (MySqlConnection connection = new MySqlConnection(Conn))            {                using (MySqlCommand cmd = new MySqlCommand())                {                                           PrepareCommand(cmd, connection, null, SQLString, cmdParms);                        cmd.CommandType = CommandType.StoredProcedure;                        using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))                        {                            DataSet ds = new DataSet();                            try                            {                                da.Fill(ds, SQLString);                                cmd.Parameters.Clear();                            }                            catch (MySql.Data.MySqlClient.MySqlException ex)                            {                                throw new Exception(ex.Message);                            }                            return ds;                        }                }            }        }

      private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)        {            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Connection = conn;            cmd.CommandText = cmdText;            if (trans != null)                cmd.Transaction = trans;            cmd.CommandType = CommandType.Text;//cmdType;            if (cmdParms != null)            {                foreach (MySqlParameter parameter in cmdParms)                {                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&                        (parameter.Value == null))                    {                        parameter.Value = DBNull.Value;                    }                    cmd.Parameters.Add(parameter);                }            }        }

这是数据库操作类

这里我犯的错误，在传参时 new MySqlParameter("@windowNumber",MySqlDbType.VarChar,8), 这个@windowNumber 不能有空格

不然就会这样

"ExceptionMessage": "Parameter 'windowNumber' not found in the collection.",

百思不得其解希望大家不要犯我这个错误

阅读全文

0 0