我对实体框架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
}
我的问题是:如何调用存储过程并将参数传递给它?
您所要做的就是创建一个对象，该对象具有与存储过程返回的结果相同的属性名。对于以下存储过程:
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。
没什么…当您为代码创建dbcontext时，首先要进行初始化
命名空间下面的fluent API区域列出sp和使用它的另一个地方，你想。
public partial class JobScheduleSmsEntities : DbContext
{
public JobScheduleSmsEntities()
: base("name=JobScheduleSmsEntities")
{
Database.SetInitializer<JobScheduleSmsEntities>(new CreateDatabaseIfNotExists<JobScheduleSmsEntities>());
}
public virtual DbSet<Customer> Customers { get; set; }
public virtual DbSet<ReachargeDetail> ReachargeDetails { get; set; }
public virtual DbSet<RoleMaster> RoleMasters { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//modelBuilder.Types().Configure(t => t.MapToStoredProcedures());
//modelBuilder.Entity<RoleMaster>()
// .HasMany(e => e.Customers)
// .WithRequired(e => e.RoleMaster)
// .HasForeignKey(e => e.RoleID)
// .WillCascadeOnDelete(false);
}
public virtual List<Sp_CustomerDetails02> Sp_CustomerDetails()
{
//return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Sp_CustomerDetails02>("Sp_CustomerDetails");
// this.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails");
using (JobScheduleSmsEntities db = new JobScheduleSmsEntities())
{
return db.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails").ToList();
}
}
}
}
public partial class Sp_CustomerDetails02
{
public long? ID { get; set; }
public string Name { get; set; }
public string CustomerID { get; set; }
public long? CustID { get; set; }
public long? Customer_ID { get; set; }
public decimal? Amount { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
public int? CountDay { get; set; }
public int? EndDateCountDay { get; set; }
public DateTime? RenewDate { get; set; }
public bool? IsSMS { get; set; }
public bool? IsActive { get; set; }
public string Contact { get; set; }
}
用你的例子，这里有两种方法来实现这一点:
方法#1:使用存储过程映射
请注意，这段代码可以使用映射，也可以不使用映射。如果在实体上关闭映射，EF将生成一个插入+选择语句。
protected void btnSave_Click(object sender, EventArgs e)
{
using (var db = DepartmentContext() )
{
var department = new Department();
department.Name = txtDepartment.text.trim();
db.Departments.add(department);
db.SaveChanges();
// EF will populate department.DepartmentId
int departmentID = department.DepartmentId;
}
}
方法#2:直接调用存储过程
protected void btnSave_Click(object sender, EventArgs e)
{
using (var db = DepartmentContext() )
{
var name = new SqlParameter("@name", txtDepartment.text.trim());
//to get this to work, you will need to change your select inside dbo.insert_department to include name in the resultset
var department = db.Database.SqlQuery<Department>("dbo.insert_department @name", name).SingleOrDefault();
//alternately, you can invoke SqlQuery on the DbSet itself:
//var department = db.Departments.SqlQuery("dbo.insert_department @name", name).SingleOrDefault();
int departmentID = department.DepartmentId;
}
}
我建议使用第一种方法，因为您可以直接使用department对象，而不必创建一堆SqlParameter对象。
