c# - ¿Cómo puedo llamar a un procedimiento almacenado de SQL usando EntityFramework 7 y Asp.Net 5?

sql-server asp.net-core-mvc

Question

Durante los últimos días, busqué algunos tutoriales sobre cómo llamar a un Stored Procedure desde un método de controlador de Web API usando EntityFramework 7 .

Todos los tutoriales que leí lo muestran al revés, es decir Code First enfoque Code First . Pero ya tengo una base de datos y necesito usarla para construir una Web API . Ya se han escrito varias lógicas comerciales como Procedimientos almacenados y Vistas y tengo que consumirlas desde mi API web.

Pregunta 1: ¿Es esto posible continuar con el enfoque de Database First con EF7 y consumir objetos de base de datos como el anterior?

Instalé EntityFramework 6.1.3 en mi paquete con el siguiente comando NuGet :

install-package EntityFramework que agrega la versión 6.1.3 a mi proyecto pero inmediatamente comienza a mostrarme un mensaje de error (vea la captura de pantalla a continuación). No tengo idea de cómo resolver esto.

Tengo otro proyecto de prueba donde en project.json puedo ver dos entradas como las siguientes:

"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final", "EntityFramework.MicrosoftSqlServer.Design": "7.0.0-rc1-final",

Sin embargo, cuando estoy buscando en el administrador de paquetes Nu-Get , ¡no veo esta versión! Solo viene 6.1.3.

Mi objetivo principal es consumir vistas y procedimientos almacenados ya escritos de una base de datos existente.

1) No quiero usar ADO.Net , más bien me gustaría usar ORM usando EntityFramework

2) Si EntityFramework 6.1.3 tiene la capacidad de llamar a Stored Procs y Views Stored Procs desde una base de datos ya existente, ¿cómo puedo resolver el error (captura de pantalla)?

¿Cuál es la mejor práctica para lograr esto?

Answer 1

Al igual que la respuesta anterior, simplemente puede usar FromSQL () en lugar de SqlQuery <> ().

context.Set().FromSql("[dbo].[GetFoo] @Bar = {0}", 45);

Answer 2

Espero haber entendido correctamente su problema. Tiene un PROCEDIMIENTO ALMACENADO existente, por ejemplo dbo.spGetSomeData , en la base de datos, que devuelve la lista de algunos elementos con algunos campos y necesita proporcionar los datos del método API web.

La implementación podría ser sobre lo siguiente. Puede definir un DbContext vacío como:

public class MyDbContext : DbContext
{
}

y para definir appsettings.json con la cadena de conexión a la base de datos

{
  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=(localdb)//mssqllocaldb;Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=true"
    }
  }
}

Debe usar Microsoft.Extensions.DependencyInjection para agregar MyDbContext al

public class Startup
{
    // property for holding configuration
    public IConfigurationRoot Configuration { get; set; }

    public Startup(IHostingEnvironment env)
    {
        // Set up configuration sources.
        var builder = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);
            .AddEnvironmentVariables();
        // save the configuration in Configuration property
        Configuration = builder.Build();
    }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        services.AddMvc()
            .AddJsonOptions(options => {
                options.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
            });

        services.AddEntityFramework()
            .AddSqlServer()
            .AddDbContext<MyDbContext>(options => {
                options.UseSqlServer(Configuration["ConnectionString"]);
            });
    }
    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
        ...
    }
}

Ahora puede implementar su acción WebApi de la siguiente manera:

[Route("api/[controller]")]
public class MyController : Controller
{
    public MyDbContext _context { get; set; }

    public MyController([FromServices] MyDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async IEnumerable<object> Get()
    {
        var returnObject = new List<dynamic>();

        using (var cmd = _context.Database.GetDbConnection().CreateCommand()) {
            cmd.CommandText = "exec dbo.spGetSomeData";
            cmd.CommandType = CommandType.StoredProcedure;
            // set some parameters of the stored procedure
            cmd.Parameters.Add(new SqlParameter("@someParam",
                SqlDbType.TinyInt) { Value = 1 });

            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();

            var retObject = new List<dynamic>();
            using (var dataReader = await cmd.ExecuteReaderAsync())
            {
                while (await dataReader.ReadAsync())
                {
                    var dataRow = new ExpandoObject() as IDictionary<string, object>;
                    for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++) {
                        // one can modify the next line to
                        //   if (dataReader.IsDBNull(iFiled))
                        //       dataRow.Add(dataReader.GetName(iFiled), dataReader[iFiled]);
                        // if one want don''t fill the property for NULL
                        // returned from the database
                        dataRow.Add(
                            dataReader.GetName(iFiled),
                            dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled] // use null instead of {}
                        );
                    }

                    retObject.Add((ExpandoObject)dataRow);
                }
            }
            return retObject;
        }
    }
}

El código anterior solo se ejecuta usando exec dbo.spGetSomeData y usa dataRader para leer todos los resultados y guardarlos en dynamic objeto dynamic . Si realiza $.ajax llamada $.ajax desde api/My , obtendrá los datos devueltos por dbo.spGetSomeData , que puede usar directamente en el código JavaScript. El código anterior es muy transparente. Los nombres de los campos del conjunto de datos devueltos por dbo.spGetSomeData serán los nombres de las propiedades en el código JavaScript. No necesita administrar ninguna clase de entidad en su código C # de ninguna manera. Su código C # no tiene nombres de campos devueltos por el procedimiento almacenado. Por lo tanto, si desea ampliar / cambiar el código de dbo.spGetSomeData (cambiar el nombre de algunos campos, agregar nuevos campos), deberá ajustar solo su código JavaScript, pero no el código C #.

Answer 3

Para el primer enfoque de la base de datos, debe usar el comando Scaffold-DbContext

Instalar paquetes Nuget Microsoft.EntityFrameworkCore.Tools y Microsoft.EntityFrameworkCore.SqlServer.Design

Scaffold-DbContext "Server=(localdb)/mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

pero eso no obtendrá sus procedimientos almacenados. Todavía está en proceso, rastreando el problema #245

Pero, para ejecutar los procedimientos almacenados, use el método FromSql que ejecuta consultas RAW SQL

p.ej

var products= context.Products
    .FromSql("EXECUTE dbo.GetProducts")
    .ToList();

Para usar con parámetros

var productCategory= "Electronics";

var product = context.Products
    .FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)
    .ToList();

o

var productCategory= new SqlParameter("productCategory", "Electronics");

var product = context.Product
    .FromSql("EXECUTE dbo.GetProductByName  @productCategory", productCategory)
    .ToList();

Existen ciertas limitaciones para ejecutar consultas RAW SQL o procedimientos almacenados. No puede usarlo para INSERT / UPDATE / DELETE. si desea ejecutar consultas INSERT, UPDATE, DELETE, use ExecuteSqlCommand

var categoryName = "Electronics";
dataContext.Database
           .ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName);

Answer 4

Uso del conector MySQL y Entity Framework core 2.0

Mi problema era que estaba recibiendo una excepción como fx. Ex.Message = "La columna requerida ''cuerpo'' no estaba presente en los resultados de una operación ''FromSql''". Por lo tanto, para recuperar filas a través de un procedimiento almacenado de esta manera, debe devolver todas las columnas para ese tipo de entidad con el que está asociado el DBSet, incluso si no necesita todos los datos para esta llamada específica.

var result = _context.DBSetName.FromSql($"call storedProcedureName()").ToList(); 

O con parámetros

var result = _context.DBSetName.FromSql($"call storedProcedureName({optionalParam1})").ToList(); 

Answer 5

DbContext tiene una propiedad de Database , que mantiene una conexión con la base de datos con la que puede hacer lo que quiera con:

context.Database.SqlQuery<Foo>("exec [dbo].[GetFoo] @Bar = {0}", bar);

Sin embargo, en lugar de hacer esto en sus acciones de Web Api, sugeriría agregar un método a su contexto o cualquier servicio / repositorio que interactúe con su contexto. Entonces simplemente llame a este método en su acción. Idealmente, desea mantener todas sus cosas SQL en un solo lugar.