Geek asks and answers

Entity Framework with saved Proc, which optionally returns a recordset

I have a stored proc that usually returns a recordset, but under certain circumstances it does not return a recordset at all. This leads to some chagrin of EF:

"The data reader is not compatible with the specified" myDBContext.usp_MyProc_Result ". An element of type" TheFirstColumn "does not have a corresponding column in the data reader with the same name."

Is there a way I can say that it just returns null or something like that if proc doesn't return anything

+5
source share
2 answers

- EF + MySQL , , , , . , EF + SQLServer, .

, , :

-, . , , , ! mysql

CREATE PROCEDURE `YourProcedureName` (IN YourParameter YourType)
BEGIN
    DECLARE STUFF;

    -- Thanks to tips from http://khanrahim.wordpress.com/2010/05/16/transaction-with-stored-procedure-in-mysql-server/
    -- and syntax from http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING 
    BEGIN 
        ROLLBACK; 
        SELECT AnInstanceOfTheResultSet FROM YourTable WHERE 1 = 0; -- Force an empty result-set 
    END;

    START TRANSACTION;
        -- Do your complex operations
    COMMIT;
END

-

-, EF , , : EntityCommandExecutionException. #

YourType result = null;
try 
{ 
    result = yourContext.YourProcedureName(YourParameter).FirstOrDefault(); 
}
catch (EntityCommandExecutionException ecee) 
{ 
    // Log it? Handle it somehow... 
}
if (result == null)
{
    // Handle your error state
}

// Continue processing normally...

!

+1

More articles:


All Articles