如何使用EF使用输入和输出参数执行存储过程？ [英] How to execute stored procedure with input and output parameters using EF?

问题描述

我使用的是EF代码优先方法，并且通过以下方式使用迁移创建了存储过程：

I am using EF code first approach and I have created stored procedure using migration as following:

public override void Up()
    {
        Sql(@"CREATE TYPE IdsList AS TABLE   
                    ( 
                    Id Int
                    )
                    GO

                    Create Procedure getChildIds(
                    @IdsList dbo.IdsList ReadOnly
                    )
                    As
                    Begin
                    WITH RecursiveCTE AS
                    (
                        SELECT Id
                        FROM dbo.PhysicalObjects
                        WHERE ParentId in (Select * from @IdsList)
                        --Where Id=108
                        UNION ALL

                        SELECT t.Id
                        FROM dbo.PhysicalObjects t
                        INNER JOIN RecursiveCTE cte ON t.ParentId = cte.Id
                    )
                    SELECT * FROM RecursiveCTE
                    End");
    }

    public override void Down()
    {
        Sql(@"Drop Type IdsList
                Go
                Drop Procedure getChildIds");
    }

现在，如果我去sql server management studio并执行以下脚本： / p>

Now If I go to sql server management studio and execute the following scripts:

Declare @Ids dbo.IdsList

Insert into @Ids
SELECT 1

Exec getChildIds @Ids

它将成功执行，但是现在我尝试执行该存储过程如下：

It will execute successfuly, but Now I am trying to execute that stored procedure as following:

 var idsList = new SqlParameter {ParameterName = "idsList",  Value = new int[] { 1,2,3,4,5} };

 var idParams = new SqlParameter("idParams", SqlDbType.Structured)
            {
                Direction = System.Data.ParameterDirection.Output
            };

var results = dbContext.Database.SqlQuery<int>("getChildIds @idsList, @idParams out", idsList,idParams) ;

var idsResult = (List<int>)idParams.Value;

它不会返回任何内容。

那么，如何使用Table类型的输入和输出参数执行存储过程？

So how I could execute stored procedure with input and output parameters of type Table?

推荐答案

我已经解决了这种问题。

I have solved it this way.

首先，我更新了存储过程以返回ID，如下所示：

First of all I have updated my stored procedure to return Ids as following:

 public override void Up()
    {
        Sql(@"CREATE TYPE IdsList AS TABLE   
                    ( 
                    Id Int
                    )
                    GO

                    Create Procedure getChildIds(
                    @IdsList dbo.IdsList ReadOnly
                    )
                    As
                    Begin
                    WITH RecursiveCTE AS
                    (
                        SELECT Id
                        FROM dbo.PhysicalObjects
                        WHERE ParentId in (Select * from @IdsList)
                        UNION ALL

                        SELECT t.Id
                        FROM dbo.PhysicalObjects t
                        INNER JOIN RecursiveCTE cte ON t.ParentId = cte.Id
                    )
                    SELECT Id From RecursiveCTE
                    End");
    }

    public override void Down()
    {
        Sql(@" Drop Procedure getChildIds
               Go
               Drop Type IdsList
               ");
    }

下面是我如何解决使用实体框架执行存储过程的问题：

And here how I have solved executing stored procedure using entity framework:

 var dataTable = new DataTable();
 dataTable.TableName = "idsList";
 dataTable.Columns.Add("Id", typeof(int));
 dataTable.Rows.Add(1);
 dataTable.Rows.Add(2);

  SqlParameter idsList = new SqlParameter("idsList", SqlDbType.Structured);
  idsList.TypeName = dataTable.TableName;
  idsList.Value = dataTable;

  var results = dbContext.Database.SqlQuery<int>("exec getChildIds @idsList", idsList).ToList();

我希望我的代码能帮助其他遇到相同问题的人

I hope my code will help others having the same issue

这篇关于如何使用EF使用输入和输出参数执行存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！