我对实体框架6非常陌生，我想在我的项目中实现存储过程。我有一个存储过程如下:
ALTER PROCEDURE [dbo].[insert_department]
@Name [varchar](100)
AS
BEGIN
INSERT [dbo].[Departments]([Name])
VALUES (@Name)
DECLARE @DeptId int
SELECT @DeptId = [DeptId]
FROM [dbo].[Departments]
WHERE @@ROWCOUNT > 0 AND [DeptId] = SCOPE_IDENTITY()
SELECT t0.[DeptId]
FROM [dbo].[Departments] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[DeptId] = @DeptId
END
系类:
public class Department
{
public int DepartmentId { get; set; }
public string Name { get; set; }
}
modelBuilder
.Entity<Department>()
.MapToStoredProcedures(s =>
s.Update(u => u.HasName("modify_department")
.Parameter(b => b.Department, "department_id")
.Parameter(b => b.Name, "department_name"))
.Delete(d => d.HasName("delete_department")
.Parameter(b => b.DepartmentId, "department_id"))
.Insert(i => i.HasName("insert_department")
.Parameter(b => b.Name, "department_name")));
protected void btnSave_Click(object sender, EventArgs e)
{
string department = txtDepartment.text.trim();
// here I want to call the stored procedure to insert values
}
我的问题是:如何调用存储过程并将参数传递给它?
.NET Core 5.0没有FromSql，而是有frommsqlraw
下面这些都对我有用。这里的帐户类是c#中的实体，具有与数据库中完全相同的表和列名。
App配置类如下所示
class AppConfiguration
{
public AppConfiguration()
{
var configBuilder = new ConfigurationBuilder();
var path = Path.Combine(Directory.GetCurrentDirectory(), "appsettings.json");
configBuilder.AddJsonFile(path, false);
var root = configBuilder.Build();
var appSetting = root.GetSection("ConnectionStrings:DefaultConnection");
sqlConnectionString = appSetting.Value;
}
public string sqlConnectionString { get; set; }
}
DbContext类:
public class DatabaseContext : DbContext
{
public class OptionsBuild
{
public OptionsBuild()
{
setting = new AppConfiguration();
opsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
opsBuilder.UseSqlServer(setting.sqlConnectionString);
dbOptions = opsBuilder.Options;
}
public DbContextOptionsBuilder<DatabaseContext> opsBuilder { get; set; }
public DbContextOptions<DatabaseContext> dbOptions { get; set; }
private AppConfiguration setting { get; set; }
}
public static OptionsBuild ops = new OptionsBuild();
public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
{
//disable initializer
// Database.SetInitializer<DatabaseContext>(null);
}
public DbSet<Account> Account { get; set; }
}
这段代码应该在你的数据访问层:
List<Account> accounts = new List<Account>();
var context = new DatabaseContext(DatabaseContext.ops.dbOptions);
accounts = await context.Account.ToListAsync(); //direct select from a table
var param = new SqlParameter("@FirstName", "Bill");
accounts = await context.Account.FromSqlRaw<Account>("exec Proc_Account_Select",
param).ToListAsync(); //procedure call with parameter
accounts = context.Account.FromSqlRaw("SELECT * FROM dbo.Account").ToList(); //raw query
它为我工作在代码第一。它返回一个匹配视图模型属性的列表(StudentChapterCompletionViewModel)
var studentIdParameter = new SqlParameter
{
ParameterName = "studentId",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.BigInt,
Value = studentId
};
var results = Context.Database.SqlQuery<StudentChapterCompletionViewModel>(
"exec dbo.sp_StudentComplettion @studentId",
studentIdParameter
).ToList();
已更新
Context是继承DbContext类的实例，如下所示。
public class ApplicationDbContext : DbContext
{
public DbSet<City> City { get; set; }
}
var Context = new ApplicationDbContext();
.NET Core 5.0没有FromSql，而是有frommsqlraw
下面这些都对我有用。这里的帐户类是c#中的实体，具有与数据库中完全相同的表和列名。
App配置类如下所示
class AppConfiguration
{
public AppConfiguration()
{
var configBuilder = new ConfigurationBuilder();
var path = Path.Combine(Directory.GetCurrentDirectory(), "appsettings.json");
configBuilder.AddJsonFile(path, false);
var root = configBuilder.Build();
var appSetting = root.GetSection("ConnectionStrings:DefaultConnection");
sqlConnectionString = appSetting.Value;
}
public string sqlConnectionString { get; set; }
}
DbContext类:
public class DatabaseContext : DbContext
{
public class OptionsBuild
{
public OptionsBuild()
{
setting = new AppConfiguration();
opsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
opsBuilder.UseSqlServer(setting.sqlConnectionString);
dbOptions = opsBuilder.Options;
}
public DbContextOptionsBuilder<DatabaseContext> opsBuilder { get; set; }
public DbContextOptions<DatabaseContext> dbOptions { get; set; }
private AppConfiguration setting { get; set; }
}
public static OptionsBuild ops = new OptionsBuild();
public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
{
//disable initializer
// Database.SetInitializer<DatabaseContext>(null);
}
public DbSet<Account> Account { get; set; }
}
这段代码应该在你的数据访问层:
List<Account> accounts = new List<Account>();
var context = new DatabaseContext(DatabaseContext.ops.dbOptions);
accounts = await context.Account.ToListAsync(); //direct select from a table
var param = new SqlParameter("@FirstName", "Bill");
accounts = await context.Account.FromSqlRaw<Account>("exec Proc_Account_Select",
param).ToListAsync(); //procedure call with parameter
accounts = context.Account.FromSqlRaw("SELECT * FROM dbo.Account").ToList(); //raw query
您所要做的就是创建一个对象，该对象具有与存储过程返回的结果相同的属性名。对于以下存储过程:
CREATE PROCEDURE [dbo].[GetResultsForCampaign]
@ClientId int
AS
BEGIN
SET NOCOUNT ON;
SELECT AgeGroup, Gender, Payout
FROM IntegrationResult
WHERE ClientId = @ClientId
END
创建一个类，如下所示:
public class ResultForCampaign
{
public string AgeGroup { get; set; }
public string Gender { get; set; }
public decimal Payout { get; set; }
}
然后调用该过程，执行以下操作:
using(var context = new DatabaseContext())
{
var clientIdParameter = new SqlParameter("@ClientId", 4);
var result = context.Database
.SqlQuery<ResultForCampaign>("GetResultsForCampaign @ClientId", clientIdParameter)
.ToList();
}
结果将包含ResultForCampaign对象的列表。您可以根据需要使用任意多的参数调用SqlQuery。
