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 injectable 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