Получение данных из хранимой процедуры с помощью Entity Framework

Я пытаюсь получить содержимое таблицы с динамической хранимой процедурой SQL, вызванной из объекта контекста базы данных (используя Entity Framework 6.1.1), чтобы заполнить элемент управления GridView. Я не могу получить данные.

Здесь хранится процедура. Это для студенческой демонстрации о SQL-инъекции в хранимых процедурах, поэтому я KNOW это инъекция и она прекрасна.

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

Код С#, который я использую для выполнения хранимой процедуры:

var db = new MyEntities();
var TEST_SEARCH_TERM = "product";
var result = db.SearchProducts(TEST_SEARCH_TERM);

MyGridView.DataSource = result;
MyGridView.DataBind();

При выполнении в Проводнике базы данных в Visual Studio хранимая процедура работает нормально. Но когда выполняется в запущенном приложении ASP.NET, я получаю исключение в методе DataBind(), потому что result возвращает -1 вместо IEnumerable DataSet, содержащий объекты, полученные из хранимой процедуры SELECT.

Как я могу получить данные и заполнить свой GridView?

Ответ 1

Для решения этой проблемы выполните следующие действия:

Вам нужно импортировать хранимую процедуру как функцию. Щелкните правой кнопкой мыши область рабочей области вашей модели Entity и выберите Add -> Function Import.
В диалоговом окне "Добавить функцию импорта" введите имя, на которое ваша хранимая процедура будет упомянута в вашей модели, например Search_Products, выберите вашу процедуру из выпадающего списка и выберите возвращаемое значение процедуры, которая будет Entities и выберите Products из выпадающего списка.

Затем в коде позади:

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();

Причина, по которой вы получаете результат -1, заключается в том, что Entity Framework не может поддерживать значения хранимой процедуры. Я думаю, что поддержка возвращаемых значений хранимой процедуры зависит от версии инфраструктуры Entity. Также Entity Framework не имеет богатой поддержки хранимых процедур, поскольку ее ORM, а не замена SQL.

Ответ 2

Я столкнулся с этим раньше с помощью хранимых процедур с использованием динамического SQL. У меня был успех с использованием сложных типов, если я добавлю строку "SET FMTONLY OFF"; (см. https://msdn.microsoft.com/en-us/library/ms173839.aspx) до верхней части хранимой процедуры до ее добавления в модель EF. После того, как у вас установлена ваша модель с вашим сложным типом, обязательно удалите эту строку.

Пример:

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

Ответ 3

Убедитесь, что ваш EDMX имеет тип возврата: Перейдите в Function Imports → SearchProducts и дважды щелкните по нему.

Чтобы использовать сложный тип возврата, Entity Framework потребует, чтобы вы явно определяли имена столбцов в своей хранимой процедуре, а не использовали *.

После изменения хранимой процедуры для определения имен столбцов вы можете обновить свою модель в проекте. (Обратите внимание, что полное падение SP, а затем добавление его обратно в edmx может быть лучшим маршрутом.)

ИЗМЕНИТЬ

Возможно, вы можете изменить свой SP следующим образом:

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

Ответ 4

Вы, похоже, разобрали свою проблему, официальная документация от Microsoft доступна по ссылкам ниже:

Как импортировать хранимую процедуру в модель данных сущности: https://msdn.microsoft.com/en-us/library/vstudio/bb896231(v=vs.100).aspx

Сложные типы в дизайнере EF: https://msdn.microsoft.com/en-gb/data/jj680147.aspx

Убедитесь, что вы работаете с последней версией .net и обновляете свою модель при внесении изменений в свою базу данных.