如何从DBContext中的存储过程返回值到Web API Controller [英] How to return values to Web API Controller from a stored procedure in DBContext

问题描述

我正在尝试将验证值从Web API 2和Repository Pattern插入到SQL中。数据库具有用于检查所传递值（外键依赖关系等）的存储过程。存储的proc将为无效的每个值返回-1，否则isValid = true。我需要将无效的参数传递给我的存储库/控制器，所以我可以向最终用户返回一个http响应消息，指出什么是无效的。这是我迄今为止在我的DBContext中。如何做到这一点？

I am trying validate values to be inserted into SQL from Web API 2 and Repository Pattern. The database has a stored procedure for checking the values passed (foreign key dependencies etc). The stored proc returns a -1 for each value that is invalid or else isValid=true. I need to pass the param that is invalid to my repository/controller so i can return an http response message to the end user indicating what was invalid. Here's what I have so far in my DBContext. How can I do this?

public void Api_Event_Prefs_InsertUpdateCheck(int individualID, int eventID) {

        var p_PrefID = new SqlParameter() { ParameterName = "@PrefID", Value = System.Data.SqlTypes.SqlInt32.Null, Direction = ParameterDirection.InputOutput };
        var p_individualID = new SqlParameter() { ParameterName = "@IndividualID", Value = individualID, Direction = ParameterDirection.InputOutput };
        var p_eventID = new SqlParameter() { ParameterName = "@eventID", Value = eventID, Direction = ParameterDirection.InputOutput };
        var p_IsValid = new SqlParameter() { ParameterName = "@IsValid", Value = System.Data.SqlTypes.SqlBoolean.False, Direction = ParameterDirection.Output };

        this.Database.ExecuteSqlCommand("Exec dbo.Api_Event_Prefs_InsertUpdateCheck @PrefID, @IndividualID OUT, @EventID OUT, @IsValid OUT", p_PrefID, p_individualID, p_eventID, p_IsValid);

    }

推荐答案

使用 Database.SqlQuery ，它让您执行存储过程或任何其他查询，并获取执行结果。

You need to use Database.SqlQuery, which lets you execute an stored procedure, or any other query, and get the results of the execution.

ExecuteSqlCommand 只是用于运行不需要返回数据的查询（例如更新或DDL句子）。

ExecuteSqlCommand is only useful for running queries from which don't need to return the data (for example updates or DDL sentences).

这篇关于如何从DBContext中的存储过程返回值到Web API Controller的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！