CODEWITHSUNDEEP
entity-frameworkstored-procedures
Robert Achmann
Robert Achmann

Reputation: 2045

EF 6 .Database.SqlQuery for Stored proc just returns string of stored proc

The results returned is just the string passed to SQLQuery...

             var beginDt = new DateTime(2014, 03, 17);
                string beginDtStr = beginDt.ToString("yyyy-MM-dd");
                var callParams = new object[]
                {
                    dbName
                    , true
                    , 4
                    , 5 //   @EntityAffiliateTypeID 
                    , "Test Name "
                    , "Empty Note" /
                    , "5192223333"
                    , "5193334444"
                    , "[email protected]"
                    , "59 London Rd"
                    , null
                    , 8
                    , "Sarnia"
                    , 53
                    , "N7T2B1"
                    , 6
                    , 2
                    , null
                    , null
                    , null
                    , beginDtStr
                    , null
                    , "Another Testname"
                    , "5192223333"
                    , "5193334444"
                    , "[email protected]"
                    , null
                    , null
                };

              var result2 = db.Database.SqlQuery<int>(
                    "EXEC SPEntityAffiliateInsert {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}",
                    callParams
                    );

When you examine result2, it's just exactly:

"EXEC SPEntityAffiliateInsert {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}"

it's like you make the select statement for a string:

SELECT 'EXEC SPEntityAffiliateInsert {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}';

So, how do I get it to actually invoke the sproc?

(I already tried: var result2 = db.Database.SqlQuery( "SPEntityAffiliateInsert {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, {26}, {27}",

and var result2 = db.Database.SqlQuery( "EXEC SPEntityAffiliateInsert {0} {1} {2} {3} {4} {5} {6} {7} {8} {9} {10} {11} {12} {13} {14} {15} {16} {17} {18} {19} {20} {21} {22} {23} {24} {25} {26} {27}", ) Any help appreciated

Upvotes: 0

Views: 1608

Answers (1)

Robert Achmann
Robert Achmann

Reputation: 2045

One of the issues here was that the regular method of calling an SP won't work; this SP uses the Return statement for a single integer value. All normal EF SP calling conventions expect an IEnumerable compatible result, So I had to act differently - I was not allowed to update the SP.

The string wasn't being interpreted as a command as the SP was not qualified by a schema. I also updated it to run exactly as it would be run in a Query window:

var commandSQL = "DECLARE @return_value int; " +
      "EXEC @return_value = [dbSchemaName].[SPEntityAffiliateInsert] " +
       "@ParamXName = {0} ; SELECT 'Return Value' = @return_value;";

Tthen call it:

   IEnumerable<int> results = db.Database.SqlQuery<int>(commandSQL, strParams.ToArray()).ToList();
                    foreach (int result in results)
                    {
                        model.expectedresultsfromDBfield = result;
                        //only looking for one result...(could have called FirstOrDefault, I suppose...
                        break;
                    }

All works fine

Upvotes: 1

Related Questions