CODEWITHSUNDEEP
c#mysqlstored-proceduresentity-framework-coreasp.net-core-webapi
Awais Khan
Awais Khan

Reputation: 25

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

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"}

Upvotes: 0

Views: 2564

Answers (3)

user19133548
user19133548

Reputation: 1

change EXECUTE to CALL in query

Upvotes: 0

Awais Khan
Awais Khan

Reputation: 25

`try
        {
            var fromDate = resource.StartDate.ToString("yyyy-MM-dd") + " 00:00:00";
            var toDate = resource.EndDate.ToString("yyyy-MM-dd") + " 23:59:59";
            string connectionstring = "Server=dbwithriderinstance.crefat3b9j9c.ap-southeast-1.rds.amazonaws.com;Database=dborderstage;User=stagging_su_production_s;Password=85s2!892Stfe7";
            using (MySqlConnection con = new MySqlConnection(connectionstring))
            {
                using (MySqlCommand cmd = new MySqlCommand("GetChargesFromToDateV2", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", fromDate);
                    cmd.Parameters.AddWithValue("@ToDate", toDate);
                    using (MySqlDataAdapter dbr = new MySqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        dbr.Fill(dt);
                        return dt;
                    }
                }
            }
        }
        catch (Exception ex) { Console.Write(ex); throw ex; }
    }`

Upvotes: -1

Shahafo
Shahafo

Reputation: 255

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

Upvotes: 2

Related Questions