logo标签列表

如何在Entity Framework 6（Code-First）中调用存储过程？

c#entity-frameworkstored-procedures
304

我对Entity Framework 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

Department 类：

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
}

我的问题是：如何调用存储过程并向其中传递参数？

我也对此很感兴趣。理想情况下，我会完全跳过EF，而只通过存储过程运行一切。虽然我是SQL专家，但我发现实现EF非常令人沮丧。 - David Britz
22个回答
5

在代码优先方案中，它能很好地工作。它返回一个与视图模型（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();

更新上下文

上下文是继承 DbContext 类的实例，如下所示。

public class ApplicationDbContext : DbContext
{
    public DbSet<City> City { get; set; }
}

var Context = new  ApplicationDbContext();
嗨，我找不到这个Context.Database.SqlQuery<Model>，但我可以使用Context.TableName.SqlQuery(ProcName)。这给我带来了问题。 - Marshall
@Marshall，也许你正在使用数据库优先设计，请查看此链接https://dev59.com/yGbWa4cB1Zd3GeqPbN2W。 - reza.cse08
5
我发现在使用Code-First方法调用存储过程并不方便。
我更喜欢使用Dapper。
以下代码是使用Entity Framework编写的：
var clientIdParameter = new SqlParameter("@ClientId", 4);

var result = context.Database
.SqlQuery<ResultForCampaign>("GetResultsForCampaign @ClientId", clientIdParameter)
.ToList();

以下代码是使用 Dapper 编写的：
return Database.Connection.Query<ResultForCampaign>(
            "GetResultsForCampaign ",
            new
            {
                ClientId = 4
            },
            commandType: CommandType.StoredProcedure);

我认为第二段代码更容易理解。
2
您可以向sp_GetById传递参数，并在ToList()或FirstOrDefault();中获取结果。
var param  = new SqlParameter("@id", 106);
var result = dbContext
               .Database
               .SqlQuery<Category>("dbo.sp_GetById @id", param)
               .FirstOrDefault();
1

无意义的乘客有一个使用实体框架允许从存储过程返回多个结果集的项目。以下是他的其中一个示例....

using (testentities te = new testentities())
{
    //-------------------------------------------------------------
    // Simple stored proc
    //-------------------------------------------------------------
    var parms1 = new testone() { inparm = "abcd" };
    var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
    var r1 = results1.ToList<TestOneResultSet>();
}
1

.NET Core 5.0没有FromSql，而是有FromSqlRaw

以下所有内容对我都有效。这里的Account类是C#中的实体，其表和列名称与数据库中完全相同。

应用程序配置类如下所示

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
0

当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.
0
如果您想将表参数传递到存储过程中，必须为您的表参数设置TypeName属性。
SqlParameter codesParam = new SqlParameter(CODES_PARAM, SqlDbType.Structured);
            SqlParameter factoriesParam = new SqlParameter(FACTORIES_PARAM, SqlDbType.Structured);

            codesParam.Value = tbCodes;
            codesParam.TypeName = "[dbo].[MES_CodesType]";
            factoriesParam.Value = tbfactories;
            factoriesParam.TypeName = "[dbo].[MES_FactoriesType]";


            var list = _context.Database.SqlQuery<MESGoodsRemain>($"{SP_NAME} {CODES_PARAM}, {FACTORIES_PARAM}"
                , new SqlParameter[] {
                   codesParam,
                   factoriesParam
                }
                ).ToList();
0

当你为代码优先方法初始化dbcontext时，在流畅的API区域下方创建名称空间列表，以便在需要时在另一个地方使用。

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; }
}
0
public static string ToSqlParamsString(this IDictionary<string, string> dict)
        {
            string result = string.Empty;
            foreach (var kvp in dict)
            {
                result += $"@{kvp.Key}='{kvp.Value}',";
            }
            return result.Trim(',', ' ');
        }

public static List<T> RunSproc<T>(string sprocName, IDictionary<string, string> parameters)
        {
            string command = $"exec {sprocName} {parameters.ToSqlParamsString()}";
            return Context.Database.SqlQuery<T>(command).ToList();
        }
0

这是EF（数据库优先）在DbContext类中生成的内容：

public ObjectResult<int> Insert_Department(string department)
{
    var departmentParameter = new ObjectParameter("department", department);

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>("insert_department", departmentParameter);
}
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接