从存储过程传递和返回值到视图模型

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 

Answer 1

过程不能返回表。使用带有表返回值的函数：CREATE FUNCTION&nbsp; fnGetOrgChart()RETURNS&nbsp; @rtnTable TABLE&nbsp;(---you fields)ASBEGINDECLARE @TempTable table (id uniqueidentifier, name nvarchar(255)....)insert into @myTable&nbsp;select from your stuff--This select returns datainsert into @rtnTableSELECT ID, name FROM @mytable&nbsp;returnEND请参阅有关带有表结果的函数的 microsoft 文档然后打电话var result = context.Database.SqlQuery<vmNewOrgChart>("select * from fnGetOrgChart()");正如 Tetsuya Yamamoto 所建议的否则，您必须调用存储来填充临时表，然后从临时表中选择数据并刷新它注意。您可以轻松地将参数传递给这些函数。为简洁起见省略

Answer 2

您可以使用此解决方案：&nbsp;using (var cmd = _db.Database.GetDbConnection().CreateCommand())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cmd.CommandText = "dbo.GetCategories"; //sp name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cmd.CommandType = System.Data.CommandType.StoredProcedure;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _db.Database.OpenConnection();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; using (var result = cmd.ExecuteReader())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (result.HasRows)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;var List= MapToList<Category>(result);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }public IList<T> MapToList<T>(DbDataReader dr)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var objList = new List<T>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var props = typeof(T).GetRuntimeProperties();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var colMapping = dr.GetColumnSchema()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToDictionary(key => key.ColumnName.ToLower());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (dr.HasRows)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while (dr.Read())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; T obj = Activator.CreateInstance<T>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (var prop in props)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (colMapping.Any(a => a.Key.ToLower() == prop.Name.ToLower()))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var val = dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prop.SetValue(obj, val == DBNull.Value ? null : val);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; objList.Add(obj);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return objList;&nbsp; &nbsp; &nbsp; &nbsp; }

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&nbsp;并且正在整个应用程序的其余部分使用，我是否真的必须创建一个全新的上下文和连接才能运行该过程。