0

I am materializing entities through a stored procedure in Entity Framework Code-First. The Stored Procedure takes an int parameter and outputs both a selection of records and several output parameters. For whatever reason, I am getting Null back on my output parameters while executing the Proc from SQL Management studio results in expected behavior; all output parameters have values. My entities are being materialized, so at least that is working...

EF Code

        SqlParameter DocsWithHits = new SqlParameter()
        {
            ParameterName = "DocumentsWithHits",
            Direction = System.Data.ParameterDirection.Output,
            Value = null,
            SqlDbType = System.Data.SqlDbType.Int
        };

        SqlParameter TotalGB = new SqlParameter()
        {
            ParameterName = "TotalGb",
            Direction = System.Data.ParameterDirection.Output,
            Value = null,
            SqlDbType = System.Data.SqlDbType.Float
        };

        ObservableCollection<SearchResult> ResultCollection;
        ResultCollection = new ObservableCollection<SearchResult>(db.Database.SqlQuery<SearchResult>(
            "exec ListSearchResultsWithTotals @SearchAnalysisID, @DocumentsWithHits, @RelatedDocuments, @TotalDocuments, @TotalGb, @DocumentsWithHitsNotExported, @RelatedDocumentsNotExported, @TotalDocumentsNotExported, @TotalGbNotExported",
            new SqlParameter
            {
                ParameterName = "SearchAnalysisID",
                Value = this.SearchAnalysisId
            },
            DocsWithHits,
            TotalGB));

SQL Profiler

The SQL that is generated from the SqlQuery method is below. I captured it in Profiler. When executed I get records and null output parameters.

declare @p4 int
set @p4=NULL
declare @p5 float
exec sp_executesql N'exec ListSearchResultsWithTotals @SearchAnalysisID, @DocumentsWithHits, @TotalGb',N'@SearchAnalysisID int,@DocumentsWithHits int output,@TotalGb float output',@SearchAnalysisID=170,@DocumentsWithHits=@p4 output,@TotalGb=@p5 output
select @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11

Am I using SqlParameter wrong or what?

CodeWarrior
  • 7,388
  • 7
  • 51
  • 78

1 Answers1

1

You must use OUT keyword in SQL passed to SqlQuery (example here). Also make sure that you are reading those parameters after you iterated or closed the main result set of the query (it should be done by ObservableCollection constructor).

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670