从存储过程向视图模型传递值并返回值

Question

我很感激Stackoverflow上有很多与存储过程相关的问题和答案，我知道，因为在过去的几天里，我读了很多，但我仍然无法理解创建将向视图模型返回值的存储过程的过程。

这个问题分为两部分：

1. 为什么我的代码没有成功运行，而是返回-1
2. 如果在您的帮助下，我克服了第一个问题，那么如何将存储过程中返回的数据获取到视图模型的列表中

我非常感谢社区能给我的任何帮助…

• 数据库名称为Discovery
• 表名为Person
• 存储过程名称为uspGetOrgChart

我的表

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 

我的存储过程目前如下：

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

我已经进行了测试，上面的程序如我所期望的那样工作，并返回了以下列：

ADID    
First_Name
Last_Name
Report_To_ADID
Manager_ID
Manager_First_Name
Manager_Last_Name
ReportsTo
EmployeeName
LevelOf

我的视图模型称为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; }
}
}

这就是我开始完全陷入的地方

public IActionResult Orgchart(string id)
{
var personIdParam = new SqlParameter("@ContactID", SqlDbType.VarChar);
personIdParam.Value = id;
var result = _context.Database.ExecuteSqlCommand("exec uspGetOrgChart", personIdParam);
}

上面的代码运行，但结果的值是-1，这意味着它没有返回任何内容或一些错误？

非常感谢的任何帮助

Derek

Answer 1

过程不能返回表。使用具有表返回值的函数：

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

请参阅微软文档中关于具有表结果的功能

然后呼叫

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

如山本哲也所建议

否则，您必须调用存储以填充临时表，然后从临时表中选择数据并刷新

注：。您可以轻松地将参数传递给这些函数。为简洁起见，省略

Answer 2

Database.ExecuteSqlCommand方法，通常用于执行动作DML查询(INSERT、UPDATE或DELETE(，该方法返回这些操作中受影响的行数。如果您想将SELECT查询结果返回到指定的模型类中，您应该使用Database.SqlQuery<TElement>，并将目标模型类设置为类型参数，如下所示：

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

注意：默认情况下，如果没有受ExecuteSqlCommand影响的记录/行，则返回-1值。

附加参考：使用实体框架执行原始SQL查询

更新：

对于实体框架核心，Database.SqlQuery<TElement>不可用，您可以使用DbSet.FromSql或使用Database.GetDbConnection().CreateCommand()，如下例所示：

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
}
}
}
}

相关问题：

如何在实体框架核心中运行存储过程？

Database.SqlQuery缺少

Answer 3

感谢大家到目前为止

谢谢你，这看起来离工作更近了。当我使用代码时

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
}
}
}
}

我得到一个错误，存储过程不能从这一行找到，该过程是明确存在和工作

(var result = cmd.ExecuteReader())

我以为这是因为代码正在关闭并打开第二个连接，所以我注释掉了行

_context.Database.OpenConnection();

这导致连接未打开的错误。

我当前的上下文叫做

_Context 

并且正在整个应用程序的其余部分中使用，我真的必须创建一个全新的上下文和连接才能运行该过程吗。

谢谢Derek

Answer 4

感谢大家的帮助我使用代码成功运行了Sproc

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
}
}
}
}

命令文本中不需要"exec">

Answer 5

您可以使用此解决方案：

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