I am trying to get the content a table with a dynamic SQL stored procedure called from the database context object (using Entity Framework 6.1.1), in order to populate a
GridView control. I fail to retrieve the data.
Here's the stored procedure. It is for a student demonstration about SQL injection in stored procedures, so I KNOW this is inject-able and it's fine.
ALTER PROCEDURE dbo.SearchProducts @SearchTerm VARCHAR(max) AS BEGIN DECLARE @query VARCHAR(max) SET @query = 'SELECT * FROM dbo.Products WHERE Name LIKE ''%' + @SearchTerm + '%''' EXEC(@query) END
The C# code behind I then use to execute the stored procedure is :
var db = new MyEntities(); var TEST_SEARCH_TERM = "product"; var result = db.SearchProducts(TEST_SEARCH_TERM); MyGridView.DataSource = result; MyGridView.DataBind();
When executed, in the Database Explorer in Visual Studio, the stored procedure works fine. But when executed in the running ASP.NET app, I get an exception in the
DataBind() method because
-1 instead of an
DataSet containing the objects resulting from the stored procedure's SELECT.
How can I retrieve the data and populate my
Use the following steps to solve this issue:
Add -> Function Import.
Search_Products, choose your procedure from the drop down list, and choose the return value of the procedure to be
Productsfrom the drop down list.
Then in the code behind:
var db = new MyEntities(); var TEST_SEARCH_TERM = "product"; var result = db.Search_Products(TEST_SEARCH_TERM);//Search_Products is the name that you specified in Function Import dialog MyGridView.DataSource = result; MyGridView.DataBind();
The reason that you get
-1 for result is that Entity Framework cannot support Stored Procedure Return values out of the box. I think support of stored procedure return values depends on version of Entity framework. Also Entity Framework doesn't have rich stored procedure support because its an ORM, not a SQL replacement.
I have come across this before with stored procedures using dynamic SQL. I have had success using complex types if I add the line 'SET FMTONLY OFF;' (see https://msdn.microsoft.com/en-us/library/ms173839.aspx) to the top of my stored procedure before it is added to the EF model. Once you have your model setup with your complex type, be sure to remove this line.
ALTER PROCEDURE dbo.SearchProducts @SearchTerm VARCHAR(max) AS BEGIN SET FMTONLY OFF; DECLARE @query VARCHAR(max) SET @query = 'SELECT * FROM dbo.Products WHERE Name LIKE ''%' + @SearchTerm + '%''' EXEC(@query) END