如何使用 EntityFramework 7 和 Asp.Net 5 调用 SQL 存储过程 [英] How can I call a SQL Stored Procedure using EntityFramework 7 and Asp.Net 5
问题描述
最近几天我正在寻找一些关于如何使用 EntityFramework 7 从 Web API 控制器方法内部调用 Stored Procedure 的教程代码>.
我所经历的所有教程都以相反的方式展示了它，即 Code First 方法.但是我已经有了一个数据库，我需要用它来构建一个 Web API.各种业务逻辑已经编写为存储过程和视图，我必须从我的 Web API 中使用它们.
问题 1:使用 EF7 的 Database First 方法是否可以继续使用上述的数据库对象?
我通过以下 NuGet 命令将 EntityFramework 6.1.3 安装到我的包中:
安装包实体框架它将版本 6.1.3 添加到我的项目，但立即开始向我显示错误消息(请参见下面的屏幕截图).我不知道如何解决这个问题.
我有另一个测试项目，在 project.json 中我可以看到两个条目，如下所示:
"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final","EntityFramework.MicrosoftSqlServer.Design": "7.0.0-rc1-final",
然而，当我在 Nu-Get 包管理器中搜索时，我没有看到这个版本！只有 6.1.3 即将推出.
我的主要目标是使用现有数据库中已经编写好的存储过程和视图.
1) 我不想使用 ADO.Net，而是想使用 ORM 使用 EntityFramework
2) 如果 EntityFramework 6.1.3 能够从现有的数据库中调用 Stored Procs 和 Views，我该如何解决错误(截图)?
实现这一目标的最佳做法是什么?
我希望我能正确理解您的问题.您在数据库中有现有的 STORED PROCEDURE，例如 dbo.spGetSomeData，它返回具有某些字段的某些项目的列表，您需要从 Web API 方法提供数据.
实施可能与以下内容有关.你可以定义一个 empty DbContext 像:
公共类 MyDbContext : DbContext{}并使用到数据库的连接字符串定义appsettings.json
<代码>{数据": {默认连接":{"ConnectionString": "Server=(localdb)\mssqllocaldb;Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=true"}}}您应该使用 Microsoft.Extensions.DependencyInjection 将 MyDbContext 添加到
公共类启动{//保存配置的属性公共 IConfigurationRoot 配置 { 获取；放;}公共启动(IHostingEnvironment env){//设置配置源.var builder = new ConfigurationBuilder().AddJsonFile("appsettings.json").AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);.AddEnvironmentVariables();//将配置保存在配置属性中配置 = builder.Build();}//这个方法被运行时调用.使用此方法向容器添加服务.public void ConfigureServices(IServiceCollection 服务){//添加框架服务.服务.AddMvc().AddJsonOptions(options => {options.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();});services.AddEntityFramework().AddSqlServer().AddDbContext(options => {options.UseSqlServer(Configuration["ConnectionString"]);});}公共无效配置(IApplicationBuilder 应用程序，IHostingEnvironment 环境，ILoggerFactory loggerFactory){...}} 现在您可以按如下方式实现您的 WebApi 操作:
[Route("api/[controller]")]公共类 MyController :控制器{公共 MyDbContext _context { 获取；放;}公共 MyController([FromServices] MyDbContext 上下文){_context = 上下文；}[HttpGet]公共异步 IEnumerable上面的代码只是使用exec dbo.spGetSomeData 执行，并使用dataRader 读取所有结果并将其保存在dynamic 对象中.如果您从 api/My 进行 $.ajax 调用，您将获得从 dbo.spGetSomeData 返回的数据，您可以直接在JavaScript 代码.上面的代码非常透明.dbo.spGetSomeData 返回的数据集中的字段名称将是 JavaScript 代码中的属性名称.您无需以任何方式管理 C# 代码中的任何实体类.您的 C# 代码没有从存储过程返回的字段名称.因此，如果您要扩展/更改 dbo.spGetSomeData 的代码(重命名某些字段，添加新字段)，您只需调整 JavaScript 代码，而不需要调整 C# 代码.>
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.
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.
Question 1: Is this at all possible to carry on with Database First approach with EF7 and consume database objects like above?
I installed EntityFramework 6.1.3 to my package by the following NuGet command:
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.
I have another test project where in project.json I can see two entries like following:
"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
"EntityFramework.MicrosoftSqlServer.Design": "7.0.0-rc1-final",
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) I do not want to use ADO.Net, rather I would like to use ORM using EntityFramework
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?
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.
The implementation could be about the following. You can define an empty DbContext like:
public class MyDbContext : DbContext
{
}
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"
}
}
}
You should use Microsoft.Extensions.DependencyInjection to add MyDbContext to the
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)
{
...
}
}
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;
}
}
}
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屋！
