MVC5中的存储过程不返回值

2022-01-06 00:00:00 mysql stored-procedures asp.net-mvc-5

我正在通过 db 方法在 MVC5 中使用实体框架.我使用 Mysql 作为数据库.我创建了一个过程，当在 Mysql 中调用过程时，它按我的预期工作.但是当我在 MVC 中使用过程时，它每次返回 0.

I am working in MVC5 with Entity Framework through db approach. I use Mysql as database. I create a procedure and when Call Procedure in Mysql it work as my expectation. But when i use procedure in MVC it return every time 0.

存储过程

CREATE PROCEDURE `checkSeasonAvailability`( 
    IN paramSeasonId INT,   
    IN paramHotelId INT,
    IN paramStartDate varchar(20),
    IN paramEndDate varchar(20)
)
BEGIN
    DECLARE _startDate,_endDate DATETIME;
    DECLARE _hotelseasonID BIGINT(20);
    DECLARE _count,_rowCount,_dateDiff INT;
    DECLARE _index INT DEFAULT 1;
    DECLARE i INT DEFAULT 0;
    DECLARE cursorSeasonList CURSOR FOR SELECT `hotel_season_id`,`start_date`,`end_date`
            FROM    hotel_season_link
            WHERE   hotel_id    =   paramHotelId        &&
                    season_id   =   paramSeasonId       &&
                    active      =   TRUE;
    OPEN cursorSeasonList;
    SET _rowCount=FOUND_ROWS();
    SET _count=_rowCount;
    IF(_rowCount>0) THEN                    
        Proc:WHILE _index <= _rowCount DO
            FETCH cursorSeasonList INTO _hotelseasonID,_startDate,_endDate;
            SET _dateDiff=datediff(paramEndDate,paramStartDate);
            SET i=0;
            WHILE i <= _dateDiff DO

                SELECT COUNT(*) FROM hotel_season_link
                WHERE
                        adddate(paramStartDate,i) BETWEEN _startDate AND _endDate INTO _count;

                IF(_count>0) THEN
                    SELECT 1;
                    LEAVE Proc;     
                END IF;
                SET i=i+1;
            END WHILE;
        SET _index=_index+1;
        END WHILE;
    END IF;    
END

CALL checkSeasonAvailability(1,3,'2014-12-26 00:00:00','2014-12-31 00:00:00');//call in mysql

控制器

String format = "yyyy-MM-dd hh:mm:ss";
var status = db.checkSeasonAvailability(seasonId, hotelId, startDate.ToString(format), endDate.ToString(format));

当我调试我的代码时，我发现所有必需的参数都成功发送，但我没有得到返回值.如果我的代码有任何问题，请向我提出建议.

When i debug my code i find all required parameter send successfully but i didn't get return value. If any thing wrong in my code so please suggest me.

推荐答案

我自己解决了这个问题.我对存储过程和操作方法进行了更改.

i solve this problem by myself. I make changed in store procedure and action method.

我在我的程序中再添加一个参数作为 OUTPUT 并将返回值存储在这个变量中.

I add one more parameter in my procedure as OUTPUT and store return value in this variable.

OUT Flag int

SET Flag=0;
SELECT Flag;

然后改变动作方法.

String format = "yyyy-MM-dd hh:mm:ss";
System.Data.Entity.Core.Objects.ObjectParameter flag = new System.Data.Entity.Core.Objects.ObjectParameter("flag", typeof(int));
var status = db.checkSeasonAvailability(hotelSeasonId,seasonId, hotelId, startDate.ToString(format), endDate.ToString(format), flag);

相关文章