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

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

Try using FromSql("CALL GetChargesFromToDate(@p0, @p1)", startDate, endDate) instead. — Mark G
– Mark G, Commented Oct 25, 2019 at 20:06

Shahafo · Accepted Answer · 2019-10-22 13:46:14Z

2

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

edited Oct 22, 2019 at 13:46

answered Oct 22, 2019 at 12:12

Shahafo

2551 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Awais Khan Over a year ago

ok sir but i want this formate 2019-10-04 00:00:00 how can convert datetime type to this formate

Awais Khan Over a year ago

Sir both procedureResource.StartDate/EndDate are of DateTime type and i have try the same syntax but again a new error

Awais Khan Over a year ago

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

Awais Khan Over a year ago

After adding time stamp it will append millisecond to and also the "timstamp" but i didn't want all these i want this formate '2019-09-28 00:00:00','2019-09-28 23:59:59' how can i do this.

Shahafo Over a year ago

Can you share GetChargesFromToDate stored procedure?

|

user19133548 · Accepted Answer · 2022-05-17 07:11:56Z

0

change EXECUTE to CALL in query

answered May 17, 2022 at 7:11

user19133548

1

1 Comment

lemon Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Awais Khan · Accepted Answer · 2019-10-25 06:51:54Z

-1

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

answered Oct 25, 2019 at 6:51

Awais Khan

251 silver badge4 bronze badges

1 Comment

Awais Khan Over a year ago

its a old way of calling procedure but its work for me

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related