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

其他回答

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

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

_db是dbContext

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

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

您可以在DbContext类中调用存储过程,如下所示。

this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);

但是如果您的存储过程返回多个结果集作为示例代码,那么您可以在MSDN上看到这篇有用的文章

具有多个结果集的存储过程

用你的例子,这里有两种方法来实现这一点:

方法#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对象。

我发现以代码优先的方式调用存储过程并不方便。

我更喜欢用Dapper代替。

下面的代码是用实体框架编写的:

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);
    

我认为第二段代码更容易理解。