How to Excute Procedure in MySQL Entity Framework

Question

I have c# code to execute query on database. Here is my code.

internal string ExcuteQuery(string query, Type type, out int totalCount, out TimeSpan second)
        {
            using (var dbContextTransaction = Database.BeginTransaction())
            {
                try
                {
                    var index2 = query.LastIndexOf("limit ", StringComparison.OrdinalIgnoreCase);
                    var isLastLimitNotInBetween = query.Length - 20;

                    if (query.IndexOf(") as data ", StringComparison.OrdinalIgnoreCase) == -1)
                    {
                        if (index2 != -1 && index2 > isLastLimitNotInBetween)
                        {
                            query = query.Insert(index2, "\n) as data ");
                        }
                        else
                        {
                            query = query.Insert(query.Length, "\n) as data ");
                        }
                    }

                    if (query.Contains("***final_output_query_start***"))
                    {
                        var index =
                            query.IndexOf("***final_output_query_start***", StringComparison.OrdinalIgnoreCase) +
                            "***final_output_query_start*** ".Length;
                        query = query.Insert(index, Environment.NewLine + "   select SQL_CALC_FOUND_ROWS * from( ");
                    }
                    else
                    {
                        query = Environment.NewLine + " select SQL_CALC_FOUND_ROWS * from( " + query;
                    }


                    Watch.Start();

                    var data = Database.SqlQuery(type, query);

                    // need to do here becuase of the execution flow manage
                    var jsonString = JsonConvert.SerializeObject(data, Formatting.None,
                        new IsoDateTimeConverter {DateTimeFormat = Constants.DateTimeFormat});

                    totalCount = Database.SqlQuery<int>("SELECT  FOUND_ROWS();").First();

                    dbContextTransaction.Commit();

                    Watch.Stop();
                    second = Watch.Elapsed;
                    Watch.Reset();

                    return jsonString;
                }
                catch (Exception exception)
                {
                    ElmahLogger.Log(exception, "LM api");
                    dbContextTransaction.Rollback();
                    throw;
                }
            }
        }

But Now I want to execute Mysql Procedure with the same code. How can I do it? I tried passing "call myPrcName()" in query variable. but giving error of

"Invalid query"

how do I achieve this?`

What is the final value of query variable being passed to `Database.Query` method? Did you debug the code and check what changes are made to query variable ? Try running that query value directly against database from workbench. — Chetan, Jun 25 '17 at 16:12
Actually I want to add limit 2 (Have to select only 2 records) from result of procedure. How can I do it? — GOPAL SHARMA, Jun 25 '17 at 16:51
Did you try executing stored procedure with limit 2 in workbench? Does it work there? — Chetan, Jun 26 '17 at 02:43
No, It is not working there. Please see this - http://prntscr.com/fo68f0 — GOPAL SHARMA, Jun 26 '17 at 04:09
If it gives error in work bench then how can you expect it to work via C#? It is clear that you can not use limit option with stored procedure call. Either you put limit in the select query in the stored procedure or process only first two rows from all the rows returned by the procedure in C#. — Chetan, Jun 26 '17 at 05:03

score 0 · Answer 1 · answered Jun 25 '17 at 17:12

This should call a stored procedure named MyProcName. SqlCommand works for both SQL Queries and Stored Procedures and is very flexible and easy to use. See here How to pass int parameters in Sql commandText. Perhaps you can change your code to use SqlCommand instead of SqlQuery. I am not fully sure, there may also be a way to call SP through SqlQuery.

using (Common.DbCommand cmd = conn.CreateCommand()) {
    cmd.CommandText = "MyProcName";
    dynamic dr = cmd.ExecuteReader();
    int index = 0;
    List<string> columns = new List<string>();

    for (index = 0; index <= dr.FieldCount - 1; index++) {
/////
    }

}

How to Excute Procedure in MySQL Entity Framework

1 Answers1