fetch records with multiple input id in mvc 5 using store procedure and linq

Question

User1106546308 posted

i working on an application where need to fetch more than one record, if user is select date range and input more 20 ids.. need to fetch from database,

so i was created store procedure for that and calling that procedure in mvc , store procedure have input parameter, like startdate , enddate and Id so i was created an object in controller and passing those object in store procedure using linq.. i am getting input value in controller object as expected.. but when i am passing ..it's not executing

if i am passing inline value in store procedure its executing 

ex. EXEC [dbo].[SP_GET_ALL_DEATILS] '2018-09-17','2018-09-20','6,3,1,7,4,9,2,5,8'

i am getting correct records based on id and date range..

but when i was passing from my view to application its not executing.

need help..

my controller class

public JsonResult Log(DateTime CallFromDate, DateTime CallToDate, string CBD)
    {

    var result = Test_ALL_SR.SP_GET_ALL_DEATILS(CallFromDate, CallToDate, CBD ).ToList();
    return Json(result, JsonRequestBehavior.AllowGet);

    } 

where Test_ALL_SR is my database instance and SP_GET_ALL_DEATILS is my store procedure

Answer 1

User1120430333 posted

but when i was passing from my view to application its not executing.

I don't understand what you are talking about. Can you further explain just what is it that you are trying to accomplish?

Answer 2

User1106546308 posted

Hi DA924,

As i am created mvc 5 app , 

i want fetch bulk record based on ids and date range,

so, i was created parameter's store procedure with (startdate, end date, ids)

like ex. EXEC [dbo].[SP_GET_ALL_DEATILS] '2018-09-17','2018-09-20','6,3,1,7,4,9,2,5,8'

and its working in sql sever if i am executing , getting expected results.

then i use same store procedure in my application ..

my application controller action method is

public JsonResult Log(DateTime CallFromDate, DateTime CallToDate, string CBD)
    {

    var result = Test_ALL_SR.SP_GET_ALL_DEATILS(CallFromDate, CallToDate, CBD ).ToList();
    return Json(result, JsonRequestBehavior.AllowGet);

    } 

where Test_ALL_SR is my database instance and SP_GET_ALL_DEATILS is my store procedure

so, getting exception, i was try to debug also..but my bad luck..

cannot recognized where the issue..

Answer 3

User1120430333 posted

var result = Test_ALL_SR.SP_GET_ALL_DEATILS(CallFromDate, CallToDate, CBD ).ToList();

How do you know that result has anything in it?

What is hosting the code and how are you even testing it?

Answer 4

User-271186128 posted

Hi Rockxyxy,

so, i was created parameter's store procedure with (startdate, end date, ids)

like ex. EXEC [dbo].[SP_GET_ALL_DEATILS] '2018-09-17','2018-09-20','6,3,1,7,4,9,2,5,8'

and its working in sql sever if i am executing , getting expected results.

then i use same store procedure in my application ..

my application controller action method is

public JsonResult Log(DateTime CallFromDate, DateTime CallToDate, string CBD)
    {

    var result = Test_ALL_SR.SP_GET_ALL_DEATILS(CallFromDate, CallToDate, CBD ).ToList();
    return Json(result, JsonRequestBehavior.AllowGet);

    } 

You could set a break point in the log method, and check the stored procedure parameters, make sure the data type and data value are correct.

Best regards,
Dillion