Passing and return values from stored procedure to view model

Question

In advance, I appreciate there are lots of stored procedure related questions and answers out there on Stackoverflow, I know because over the last few days I have read a tonne of them but I still cannot get my head around the process of creating a stored procedure that will return values to my view model.

This question is in two parts:

Why does my code not successfully run and instead returns -1 ?
If with your help I get past that first issue how do I then get the returned data from the stored procedure into a list of my view model

I very much appreciate any help the community can give me...

DB name is Discovery
Table name is Person
Stored procedure name is uspGetOrgChart

My table

CREATE TABLE Discovery.dbo.Person 
(
     ADID nvarchar(50) NOT NULL,
     First_Name nvarchar(50) NOT NULL,
     Last_Name nvarchar(50) NOT NULL,
     Report_To_ADID nvarchar(50) NULL,
     Position_ID int NULL,
     Role_ID int NULL,
     IGEMS nvarchar(50) NULL,
     DOB date NULL,
     Start_Date date NULL,
     Cost_Code nvarchar(50) NULL,

     PRIMARY KEY CLUSTERED (ADID),
     CONSTRAINT FK_Person_Position1 
         FOREIGN KEY (Position_ID) REFERENCES dbo.Position (Position_ID),
     CONSTRAINT FK_Person_Role 
         FOREIGN KEY (Role_ID) REFERENCES dbo.Role (Role_ID)
)
ON [PRIMARY]
GO

My stored procedure is currently as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspGetOrgChart]
    @ContactID VARCHAR(100) = 'NOE1WWD'
AS
BEGIN
    SET NOCOUNT ON;

    --grab id of @contactid
    DECLARE @Test varchar(36)
    SELECT @Test = (SELECT ADID FROM Person c1 WHERE c1.ADID = @ContactID)

    ;WITH StaffTree AS 
    ( 
        SELECT  
            c.ADID, 
            c.First_Name, 
            c.Last_Name,
            c.Report_To_ADID, 
            c.Report_To_ADID as Manager_ID,
            cc.First_Name AS Manager_First_Name, 
            cc.Last_Name as Manager_Last_Name, 
            cc.First_name + ' ' + cc.Last_name AS [ReportsTo], 
            c.First_Name + ' ' + c.Last_Name as EmployeeName,  
            1 AS LevelOf 
        FROM 
            Person c 
        LEFT OUTER JOIN 
            Person cc ON c.Report_To_ADID = cc.ADID 
        WHERE 
            c.ADID = @Test 
            OR (@Test IS NULL AND c.Report_To_ADID IS NULL) 

        UNION ALL 

        SELECT  
            s.ADID, 
            s.First_Name, 
            s.Last_Name, 
            s.Report_To_ADID, 
            t.ADID, 
            t.First_Name, 
            t.Last_Name, 
            t.First_Name + ' ' + t.Last_Name, 
            s.First_Name + ' ' + s.Last_Name,
            t.LevelOf + 1 
        FROM 
            StaffTree t 
        INNER JOIN 
            Person s ON t.ADID = s.Report_To_ADID 
        WHERE 
            s.Report_To_ADID = @Test 
            OR @Test IS NULL 
            OR t.LevelOf > 1 
    )
    SELECT * FROM StaffTree 
END

I have tested and the Procedure above works as I expected and returns the following columns:

 ADID    
 First_Name
 Last_Name
 Report_To_ADID
 Manager_ID
 Manager_First_Name
 Manager_Last_Name
 ReportsTo
 EmployeeName
 LevelOf

My view model is called vmNewOrgChart:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Discovery.ViewModels
{
    public class vmNewOrgChart
    {
        public string ADID { get; set; }
        public string First_Name { get; set; }
        public string Last_Name { get; set; }
        public string Report_To_Adid { get; set; }
        public string Manager_ID { get; set; }
        public string Manager_First_Name { get; set; }
        public string Manager_Last_Name { get; set; }
        public string ReportsTo { get; set; }
        public string EmployeeName { get; set; }
        public int LevelOf { get; set; }
    }
}

This is where I start to get completely stuck

public IActionResult Orgchart(string id)
{
    var personIdParam = new SqlParameter("@ContactID", SqlDbType.VarChar);
    personIdParam.Value = id;

    var result = _context.Database.ExecuteSqlCommand("exec uspGetOrgChart", personIdParam);
}

The code above runs but the value of result is -1 which means it returned nothing or some error?

Many thanks for any assistance

Derek

A stored procedure returns `-1` if no rows affected (because you're using `SET NOCOUNT ON`, related issue [here](https://stackoverflow.com/questions/23421356/why-stored-procedure-return-1)). If you want to project the result into a model class, use `_context.Database.SqlQuery("exec uspGetOrgChart", personIdParam);` instead. — Tetsuya Yamamoto, Aug 03 '18 at 09:58
I am using SQLServer, the management sofwatre I am using is MS SQL Studio — Derek, Aug 03 '18 at 10:27
why don't you use a function with table result and make a select on it? Something like this: _context.Database.SqlQuery( "SELECT Name, Login FROM Users()").ToList(); More info about these funtions here: https://stackoverflow.com/questions/440308/tsql-returning-a-table-from-a-function-or-store-procedure — Gianluca Conte, Aug 03 '18 at 10:35

Tetsuya Yamamoto · Answer 1 · 2018-08-03T11:44:43.037

Database.ExecuteSqlCommand method usually used for executing action DML queries (INSERT, UPDATE, or DELETE), which returns number of affected rows from those operations. If you want to return SELECT query results into designated model class, you should use Database.SqlQuery<TElement> with target model class set as type parameter like this:

_context.Database.SqlQuery<vmNewOrgChart>("exec uspGetOrgChart", personIdParam);

Note: By default if no records/rows affected with ExecuteSqlCommand, it returns -1 value.

Additional reference: Executing Raw SQL Queries Using Entity Framework

Update:

For Entity Framework Core the Database.SqlQuery<TElement> is not available, you can use DbSet.FromSql or use Database.GetDbConnection().CreateCommand() as in example below:

using (var context = new DataContext())
{
    using (var cmd = context.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = "exec uspGetOrgChart";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        var personIdParam = cmd.CreateParameter();
        personIdParam.ParameterName = "ContactID";
        personIdParam.Value = id;

        cmd.Parameters.Add(personIdParam);

        context.Database.OpenConnection();
        using (var result = command.ExecuteReader())
        {
            if (result.HasRows)
            {
                // do something with results
            }
        }
    }
}

Related issues:

How to run stored procedures in Entity Framework Core?

Database.SqlQuery Missing

Thank you for the reponse and help so far, it seems I may be doing semething out of context because I do not have the SqlQuery method available to me, am I missing a reference perhaps? — Derek, Aug 03 '18 at 11:13
If you are using EntityFramework and your context is extending DbContext class you should see it as a method of Database property. May be that your intellisense is gone. Try to open and close vs. — Gianluca Conte, Aug 03 '18 at 11:43

score 0 · Answer 2 · answered Aug 03 '18 at 10:52

A procedure can't return a table. Use instead function with table return value:

CREATE FUNCTION  fnGetOrgChart()
RETURNS  @rtnTable TABLE 
(
---you fields
)
AS
BEGIN
DECLARE @TempTable table (id uniqueidentifier, name nvarchar(255)....)
insert into @myTable 
select from your stuff
--This select returns data
insert into @rtnTable
SELECT ID, name FROM @mytable 
return
END

See microsoft doc about function with table result

Then call

var result = context.Database.SqlQuery<vmNewOrgChart>("select * from fnGetOrgChart()");

as suggested by Tetsuya Yamamoto

Otherwise you must call the stored to fill a temp table then select data from the temp table and flush it

NB. You can easly pass parameters to these function. Omitted for brevity

score 0 · Answer 3 · answered Aug 03 '18 at 13:33

Thank you all so far

Thank you this is looking much closer to working. when i use the code

using (_context)
{
using (var cmd = _context.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "exec uspGetOrgChart";
cmd.CommandType = System.Data.CommandType.StoredProcedure;

var personIdParam = cmd.CreateParameter();
personIdParam.ParameterName = "ContactID";
personIdParam.Value = id;

cmd.Parameters.Add(personIdParam);

_context.Database.OpenConnection();
using (var result = cmd.ExecuteReader())
{
if (result.HasRows)
{
// do something with results

}
}
}

}

I get an error that the stored procedure cannot be found from this line, the procedure is definately there and working

(var result = cmd.ExecuteReader())

I thought this was because the code was closing and openning a second connection so I commented out the line

_context.Database.OpenConnection();

This resulted in an error that the connection was not open.

my current context is called

_Context

and is being used throughout the rest of the application, do I really have to create an entirely new context and connection in order to run the procedure.

Thanks Derek

score 0 · Answer 4 · answered Aug 13 '18 at 12:53

Thank you all for your assistance I managed to get the Sproc to run successfully using this code

using (_context)
{
                using (var cmd = _context.Database.GetDbConnection().CreateCommand())
                {
                    cmd.CommandText = "uspGetOrgChart";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    var personIdParam = cmd.CreateParameter();
                    personIdParam.ParameterName = "ContactID";
                    personIdParam.Value = id;

                    cmd.Parameters.Add(personIdParam);

                    _context.Database.OpenConnection();
                    using (var result = cmd.ExecuteReader())
                    {
                        if (result.HasRows)
                        {
                            // do something with results

                        }
                    }
                }

            }

The "exec" is not required in the commandtext

score 0 · Answer 5 · answered Sep 08 '19 at 07:20

you can use this solution :

 using (var cmd = _db.Database.GetDbConnection().CreateCommand())
                {
                    cmd.CommandText = "dbo.GetCategories"; //sp name
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    _db.Database.OpenConnection();
                    using (var result = cmd.ExecuteReader())
                    {
                        if (result.HasRows)
                        {
                             var List= MapToList<Category>(result);
                        }
                    }
                }

public IList<T> MapToList<T>(DbDataReader dr)
        {
            var objList = new List<T>();
            var props = typeof(T).GetRuntimeProperties();

            var colMapping = dr.GetColumnSchema()
                .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
                .ToDictionary(key => key.ColumnName.ToLower());

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    T obj = Activator.CreateInstance<T>();
                    foreach (var prop in props)
                    {
                        if (colMapping.Any(a => a.Key.ToLower() == prop.Name.ToLower()))
                        {
                            var val = dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
                            prop.SetValue(obj, val == DBNull.Value ? null : val);
                        }
                    }
                    objList.Add(obj);
                }
            }
            return objList;
        }

i try to call store procedure in entity framework core . in using section i call stored procedure . This sp return the list of category model and MapToList method map the result to the Category class — Rasool Ghorbani, Sep 08 '19 at 07:53

Passing and return values from stored procedure to view model

5 Answers5