我怎么能叫使用的EntityFramework 7和Asp.Net 5存储过程的SQL [英] How can I call a SQL Stored Procedure using EntityFramework 7 and Asp.Net 5

问题描述

有关过去几天我正在寻找有关如何调用一些教程的存储过程从的Web API里面使用控制器的方法的EntityFramework 7 。

For last couple of days I am searching for some tutorials about how to call a Stored Procedure from inside a Web API controller method using EntityFramework 7.

我都挺过来了所有教程都围绕显示它的另一种方式，即 code首先办法。但我已经有一个适当的数据库，我需要用它来构建一个的Web API 。各种商业逻辑已经写成存储过程和视图，我必须消费那些从我的Web API。

All tutorials I came through are showing it the other way round, i.e. Code First approach. But I already have a database in place and I need to use it to build a Web API. Various business logic are already written as Stored Procedures and Views and I have to consume those from my Web API.

问题1：这是在所有可能与 EF7 数据库首页办法进行，并消耗数据库对象像上面？

Question 1: Is this at all possible to carry on with Database First approach with EF7 and consume database objects like above?

我装的EntityFramework 6.1.3 来我的包由以下的NuGet 命令：

I installed EntityFramework 6.1.3 to my package by the following NuGet command:

安装包的EntityFramework
这增加了6.1.3版本，我的项目，而是立即开始显示我的错误信息（请参阅下面的屏幕截图）。我不知道如何解决这一线索。

install-package EntityFramework which adds version 6.1.3 to my project but immediately starts showing me error message (please see the screenshot below). I have no clue about how to resolve this.

我有另一个测试项目，其中在 project.json 我可以看到类似下面的两个条目：

I have another test project where in project.json I can see two entries like following:

EntityFramework.MicrosoftSqlServer：7.0.0-RC1决赛
EntityFramework.MicrosoftSqlServer.Design：7.0.0-RC1决赛，

然而，当我在正在寻找怒江-GET 包管理器，我不;吨看到这个版本！只有6.1.3快到了。

However, when I am searching in Nu-Get package manager, I don;t see this version! Only 6.1.3 is coming up.

我的主要目标是从现有的数据库消耗已经编写存储过程和视图。

My main objective is to consume already written Stored Procedures and Views from an existing database.

1）我不想使用 ADO.Net ，而我想用 ORM <使用code>的EntityFramework

1) I do not want to use ADO.Net, rather I would like to use ORM using EntityFramework

2）的EntityFramework 6.1.3 还打电话叫存储特效的能力和查看从已经存在的数据库，我怎么能解决错误（截图）？

2) If EntityFramework 6.1.3 has the ability to call Stored Procs and Views from already existing database, how I can resolve the error (screenshot)?

什么是实现这一目标的最佳做法？

What is the best practice to achieve this?

推荐答案

我希望我正确理解你的问题。你已经现有的存储过程，例如 dbo.spGetSomeData ，在数据库中，它返回的一些项目与某些字段列表中，你需要从提供的Web API方法数据

I hope that I correctly understand your problem. You have existing STORED PROCEDURE, for example dbo.spGetSomeData, in the database, which returns the list of some items with some fields and you need to provide the data from Web API method.

的实施可能是有关以下。您可以定义一个空 的DbContext 这样的：

The implementation could be about the following. You can define an empty DbContext like:

public class MyDbContext : DbContext
{
}

和定义 appsettings.json 通过连接字符串到数据库

and to define appsettings.json with the connection string to the database

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

您应该使用 Microsoft.Extensions.DependencyInjection 添加 MyDbContext 到

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)
    {
        ...
    }
}

现在，你可以实现你的WebAPI行动如下：

Now you can implement your WebApi action as the following:

[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;
        }
    }
}

以上code只执行使用 EXEC dbo.spGetSomeData 并使用dataRader阅读所有结果，并保存在有动态对象。如果你想使从 API $。阿贾克斯呼叫/我的您将得到数据返回 dbo.spGetSomeData ，您可以在JavaScript中code直接使用。以上code是非常透明的。字段从 dbo.spGetSomeData 返回的数据集的名称将在JavaScript的code中的属性的名称。 您不需要以任何方式你的C＃code管理任何实体类。你的C＃code都没有字段名称从存储过程返回。因此，如果你想扩展/修改 dbo.spGetSomeData 的code（重命名某些领域，增加新的字段），您将需要调整只是你的JavaScript code ，但没有C＃code。

The above code just execute using exec dbo.spGetSomeData and use dataRader to read all results and save there in dynamic object. If you would make $.ajax call from api/My you will get the data returned from dbo.spGetSomeData, which you can directly use in JavaScript code. The above code is very transparent. The names of the fields from the dataset returned by dbo.spGetSomeData will be the names of the properties in the JavaScript code. You don't need to manage any entity classes in your C# code in any way. Your C# code have no names of fields returned from the stored procedure. Thus if you would extend/change the code of dbo.spGetSomeData (rename some fields, add new fields) you will need to adjust only your JavaScript code, but no C# code.

这篇关于我怎么能叫使用的EntityFramework 7和Asp.Net 5存储过程的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！