c# - net - executesqlcommand entity framework stored procedure

Obteniendo datos del procedimiento almacenado con Entity Framework

Question

Estoy tratando de obtener el contenido de una tabla con un procedimiento almacenado SQL dinámico llamado desde el objeto de contexto de la base de datos (usando Entity Framework 6.1.1), para completar un control GridView . No consigo recuperar los datos.

Aquí está el procedimiento almacenado. Es para una demostración de los estudiantes sobre la inyección de SQL en procedimientos almacenados, así que sé que esto es inyectable y está bien.

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

El código C # detrás que luego uso para ejecutar el procedimiento almacenado es:

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

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

Cuando se ejecuta, en el Explorador de la base de datos en Visual Studio, el procedimiento almacenado funciona bien. Pero cuando se ejecuta en la aplicación ASP.NET en ejecución, obtengo una excepción en el método DataBind() porque el result devuelve -1 lugar de un IEnumerable DataSet contiene los objetos resultantes del SELECT del procedimiento almacenado.

¿Cómo puedo recuperar los datos y completar mi GridView ?

Answer 1

Me he encontrado con esto antes con procedimientos almacenados que usan SQL dinámico. He tenido éxito usando tipos complejos si agrego la línea ''SET FMTONLY OFF;'' (consulte https://msdn.microsoft.com/en-us/library/ms173839.aspx ) en la parte superior de mi procedimiento almacenado antes de agregarlo al modelo EF. Una vez que tenga la configuración de su modelo con su tipo complejo, asegúrese de eliminar esta línea.

Ejemplo:

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

Answer 2

Parece que resolvió su problema, hay documentación oficial de Microsoft disponible en los enlaces a continuación:

Cómo importar un procedimiento almacenado en su modelo de datos de entidad: https://msdn.microsoft.com/en-us/library/vstudio/bb896231(v=vs.100).aspx

Tipos complejos en el diseñador EF: https://msdn.microsoft.com/en-gb/data/jj680147.aspx

Asegúrese de estar trabajando con la última versión de .net y mantenga actualizado su modelo cuando realice cambios en su base de datos.

Answer 3

Utilice los siguientes pasos para resolver este problema:

1. Debe importar el procedimiento almacenado como una función. Haga clic derecho en el área del espacio de trabajo de su modelo de entidad y elija Add -> Function Import .
2. En el cuadro de diálogo Agregar importación de función, ingrese el nombre al que desea que se haga referencia a su procedimiento almacenado en su modelo, por ejemplo, Search_Products , elija su procedimiento de la lista desplegable y elija el valor de retorno del procedimiento para ser Entities y elija Products de la lista desplegable

3. Luego en el código detrás:

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

La razón por la que obtiene -1 como resultado es que Entity Framework no puede admitir valores de retorno de procedimiento almacenado listos para usar. Creo que el soporte de los valores de retorno del procedimiento almacenado depende de la versión de Entity Framework. Además, Entity Framework no tiene soporte de procedimiento almacenado rico porque es un ORM, no un reemplazo de SQL.

Answer 4

Verifique que su EDMX tenga un tipo de retorno: Vaya a Importaciones de funciones -> Buscar productos y haga doble clic en él.

Para utilizar un tipo de retorno complejo, Entity Framework requerirá que defina explícitamente los nombres de columna en su procedimiento almacenado en lugar de usar *.

Una vez que se modifica su procedimiento almacenado para definir los nombres de columna, puede actualizar su modelo en el proyecto. (Tenga en cuenta que realizar una caída completa del SP y luego agregarlo nuevamente a su edmx puede ser la mejor ruta).

EDITAR

Tal vez pueda modificar su SP de la siguiente manera:

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