Call MySql stored procedure which take 2 parameters from asp.net core 2.2 web API controller

asp.net-core-webapi c# entity-framework-core mysql stored-procedures

Question

What is a proper way of calling a stored procedure with 2 parameters and getting result back which contain 10 columns and 403 records.

Below is the code I have written.

try
{

    string startDate = procedureResource.StartDate.ToString("yyyy-MM-dd") + " 00:00:00";
    string endDate = procedureResource.EndDate.ToString("yyyy-MM-dd") + " 23:59:59";

    var FromDate = new MySqlParameter("@FromDate", startDate);
    var ToDate = new MySqlParameter("@ToDate", endDate);
    var financial = context.Query<FinancialResource>().FromSql("EXECUTE GetChargesFromToDate @FromDate,@ToDate", FromDate, ToDate).ToList();
    return financial;


}
catch(Exception ex) { Console.Write(ex);throw ex; }

and here is the exception

{"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2019-09-28 00:00:00','2019-10-04 23:59:59'' at line 1"}

1
1
10/22/2019 11:59:34 AM

Popular Answer

Try declaring the startDate and endDate variables as DateTime instead of string. You might be sending a date\time format not acceptable by your SQL provider.

You could try something like this (i assume that procedureResource.StartDate/EndDate are of DateTime types):

DatetTime startDate = procedureResource.StartDate.Date;
DateTime endDate = procedureResource.EndDate.Date.Add (new TimeSpan (23, 59, 59));
1
10/22/2019 1:46:14 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow