By mak


2015-08-21 12:50:06 8 Comments

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 result returns -1 instead of an IEnumerable DataSet containing the objects resulting from the stored procedure's SELECT.

How can I retrieve the data and populate my GridView?

4 comments

@user1641172 2015-09-08 13:43:00

You seem to have sorted your problem out, there is official documentation from Microsoft available at the links below:

How to import a stored procedure into your entity data model: https://msdn.microsoft.com/en-us/library/vstudio/bb896231(v=vs.100).aspx

Complex types in the EF designer: https://msdn.microsoft.com/en-gb/data/jj680147.aspx

Make sure you are working with the latest version of .net and you keep your model up to date when you make changes to your database.

@gotmilk13531 2015-09-03 20:32:05

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.

Example:

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

@mak 2015-09-11 15:13:44

Yeah tried that, did not help me. But I kind of found it interesting because it made me understand the inner workings of EF and how it gets the metadata to build the result set.

@Salah Akbari 2015-09-02 17:04:02

Use the following steps to solve this issue:

  1. You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import.
  2. In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model for example Search_Products, choose your procedure from the drop down list, and choose the return value of the procedure to be Entities and choose Products from the drop down list.
  3. 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.

@mak 2015-09-02 17:45:45

Sweet mother of all code languages. I HAVE NO freaking idea why it works now. I hope this stays stable. Thanks x1000 dude. +100 reputation well deserved.

@Vahlkron 2015-08-21 14:36:32

Verify that your EDMX has a return type: Go to Function Imports --> SearchProducts, and double click it.

In order to utilize a Complex return type, Entity Framework will require that you explicitly define column names in your stored procedure instead of using *.

Once your stored procedure is modified to define the column names, you can update your model in the project. (Note, performing a complete drop of the SP, and then adding it back to your edmx may be the best route.)

EDIT

Maybe you can modify your SP like the following:

ALTER PROCEDURE dbo.SearchProducts
  @SearchTerm VARCHAR(max)
AS
BEGIN
  SELECT * FROM dbo.Products WHERE Name LIKE '%' + @SearchTerm + '%'
END

@mak 2015-08-21 15:44:10

When trying to add a complex type to this stored procedure in the model, model explorer does not let you add it because "the selected stored procedure does not return any column"

@Vahlkron 2015-08-21 15:49:51

Can you modify it like I have above, or do you have to have @query?....with the exception that you should declare your columns specifically.

@mak 2015-08-21 16:10:34

So... Got it working by defining the return type as an Entity of type "Product". Now what I want is to have the return type to be a complex type, not an entity. Imma try what you suggested. Anyway, your solution helped, I'll accept after my last attempts.

@mak 2015-09-02 14:51:10

I'm back on this once again. I have tried your suggestion. Does not work.

@mak 2015-09-02 14:54:12

I have narrowed down the problem to the LIKE part of the statement. If I remove the WHERE condition altogether, I have EF generating a complex type automatically on model update. If I let the WHERE condition but replace the @SearchTerm by a constant string, it's the same, EF generates the complex type and it's all good. When I try to use the parameter, it fails... So I guess Dynamic stored procedures in EF are just not possible ? I tried the SETFMT ON trick... and still no.

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

26 Answered Questions

11 Answered Questions

[SOLVED] How can I get Id of inserted entity in Entity framework?

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

28 Answered Questions

[SOLVED] Fastest Way of Inserting in Entity Framework

26 Answered Questions

[SOLVED] Get int value from enum in C#

  • 2009-06-03 06:46:39
  • jim
  • 1291057 View
  • 1578 Score
  • 26 Answer
  • Tags:   c# enums casting int

17 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

16 Answered Questions

[SOLVED] Select columns from result set of stored procedure

4 Answered Questions

[SOLVED] Stored procedure EXEC vs sp_executesql difference?

7 Answered Questions

[SOLVED] Entity Framework 5 Updating a Record

Sponsored Content