如何在实体框架6中调用存储过程(代码优先)?

我对实体框架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
}

我的问题是:如何调用存储过程并将参数传递给它?

当前回答

public IList<Models.StandardRecipeDetail> GetRequisitionDetailBySearchCriteria(Guid subGroupItemId, Guid groupItemId)
{
    var query = this.UnitOfWork.Context.Database.SqlQuery<Models.StandardRecipeDetail>("SP_GetRequisitionDetailBySearchCriteria @SubGroupItemId,@GroupItemId",
    new System.Data.SqlClient.SqlParameter("@SubGroupItemId", subGroupItemId),
    new System.Data.SqlClient.SqlParameter("@GroupItemId", groupItemId));
    return query.ToList();
}

2017-11-14 10:25:56

其他回答

当EDMX创建时，如果你在表选择选项中选择存储过程，那么只需使用过程名调用存储过程…

var num1 = 1; 
var num2 = 2; 

var result = context.proc_name(num1,num2).tolist();// list or single you get here.. using same thing you can call insert,update or delete procedured.

2018-04-25 10:47:19

你可以将参数传递给sp_GetById并在ToList()或FirstOrDefault()中获取结果;

var param  = new SqlParameter("@id", 106);
var result = dbContext
               .Database
               .SqlQuery<Category>("dbo.sp_GetById @id", param)
               .FirstOrDefault();

2019-09-16 12:59:36

.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

2021-01-10 11:08:20

对我来说，这是通过在传递参数时从存储过程中拉回数据来实现的。

var param = new SqlParameter("@datetime", combinedTime);
var result = 
        _db.Database.SqlQuery<QAList>("dbo.GetQAListByDateTime @datetime", param).ToList();

_db是dbContext

2017-02-16 00:50:46

没什么…当您为代码创建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; }
}

2019-04-16 13:46:28

如何在实体框架6中调用存储过程(代码优先)?

推荐文章

最新文章

标签