我需要从我的 .Net Core 项目中的数据库中获取一个存储过程。通常我通过执行以下操作来运行此存储过程:
首选代码
readonly private SqlConnection _dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString);
public int Insert(Employee employee)
{
var result = 0;
using (var cmd = new SqlCommand("Sp_Insert", _dbConnection) { CommandType = CommandType.StoredProcedure })
{
try
{
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.Parameters.AddWithValue("@EmployeeCode", employee.EmployeeCode);
cmd.Parameters.AddWithValue("@Position", employee.Position);
cmd.Parameters.AddWithValue("@Office", employee.Office);
_dbConnection.Open();
result = cmd.ExecuteNonQuery();
}
catch
{
// ignore
}
finally
{
_dbConnection.Close();
}
}
return result;
}
我的连接字符串在 Web.config 中但是对于 .net 核心,我的连接字符串位于 appsettings.json 中:
.Net 实体框架代码
{
"ConnectionStrings": {
"Default": "server=DESKTOP-98TG6JE\SERVER_2014;database=vega;user=sa;password=ComplexPassword!123;"
},
"Logging": {
"LogLevel": {
"Default": "Warning"
}
}
}
然后我创建一个 DbContext,如下所示:
public class VegaDbContext : DbContext
{
public VegaDbContext(DbContextOptions<VegaDbContext> options) : base(options)
{}
public DbSet<Make> Makes { get; set; }
}
然后在我的启动中调用它.cs如下所示:
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<VegaDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("Default")));
services.AddMvc();
}
如果我使用实体框架进行 CRUD,这很好,但是,多次我需要创建复杂的查询,因此我需要 SQL 存储过程。您能否告诉我如何将我的"首选代码"与".net 实体框架代码"集成?谢谢。
附言如果可能的话,你能用我上面的代码作为例子吗?
进行以下更改:
-
在
ConfigureServices
方法中添加以下行:services.AddSingleton<IConfiguration>(Configuration);
-
在带有
InsertEmployee
的class other
中,添加IConfiguration
构造函数参数,configuration
调用它并将其设置为私有字段。
以下是InsertEmployee
应该的样子:
public int InsertEmployee(Employee employee)
{
var sql = new SqlConnection(
this.configuration.GetConnectionString("Default"));
//... rest of your ADO code.
}