C# 如何在MVC/EF/LINQ中执行存储过程并获取返回结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19142976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute stored procedure and get return result in MVC/EF/LINQ
提问by user845405
Could any one guide me on how to execute a SQL Server stored procedure in ASP.NET MVC / EF
application and get results back?
任何人都可以指导我如何在 ASP.NET MVC / EF
应用程序中执行 SQL Server 存储过程并取回结果？
SQL Server stored procedure
SQL Server 存储过程
CREATE PROCEDURE dbo.StoredProcedure2 AS
declare @parameter2 int
SET @parameter2 = 4
RETURN @parameter2
MVC code
MVC代码
private readonly TestDatastoreContext _context = new TestDatastoreContext();
public ViewResult Index(string id)
{
ViewData["EnvironmentId"] = id;
using (_context)
{
_context.Database.Connection.Open();
var command = _context.Database.Connection.CreateCommand();
command.CommandText = "dbo.StoredProcedure2";
command.CommandType = System.Data.CommandType.StoredProcedure;
var test = (command.ExecuteScalar());
}
var bigView = new BigViewModel
{
VersionsModel = _context.Versions.ToList(),
EnvironmentViewModel = _context.Environments.ToList(),
};
return View(model: bigView);
}
采纳答案by marc_s
Your problem is this: you're returning the value from the stored procedure (using RETURN @paramter2), but your .NET code is trying to read a result set; something that would be "returned" by using a SELECT .....statement inside the stored procedure
您的问题是：您正在从存储过程（使用RETURN @paramter2）返回值，但您的 .NET 代码正在尝试读取结果集；通过SELECT .....在存储过程中使用语句“返回”的东西
So change your stored procedure to this:
因此，将您的存储过程更改为：
CREATE PROCEDURE dbo.StoredProcedure2 AS
declare @parameter2 int
SET @parameter2 = 4
SELECT @parameter2
and then your .NET code should work just fine.
然后您的 .NET 代码应该可以正常工作。
The RETURNstatement should be used for status codesonly and it can return INTvalues only. If you want to use that, you'll have to define a SqlParameterfor your stored procedure with a Direction.ReturnValue
该RETURN语句应仅用于状态代码，并且只能返回INT值。如果你想使用它，你必须SqlParameter为你的存储过程定义一个Direction.ReturnValue
回答by Leniel Maccaferri
Check this official doc on how to map the Stored Procedureto your Context:
查看此官方文档，了解如何将 映射Stored Procedure到您的上下文：
Stored Procedures in the Entity Framework
After the mapping you'll be able to call the Stored Procedurethis way:
映射后，您将能够以Stored Procedure这种方式调用：
var val = _context.StoredProcedure2();
回答by Erik Funkenbusch
One option is to simply do this:
一种选择是简单地这样做：
MyReturnEntity ret = context.Database
.SqlQuery<MyReturnEntity>("exec myStoredProc ?, ?", param1, parm2);
回答by MrMins
You can use this library: https://github.com/mrmmins/C-StoreProcedureModelBinding
你可以使用这个库：https: //github.com/mrmmins/C-StoreProcedureModelBinding
Returns the values as a List, you only need create a simple class with the names and values types, like:
将值作为列表返回，您只需要创建一个具有名称和值类型的简单类，例如：
var productos = DataReaderT.ReadStoredProceadures<MyCustomModel>(myDbEntityInstance, "dbo.MySPName", _generic);
and MyCumtomModel class is something like:
和 MyCumtomModel 类是这样的：
public int id {get; set;}
public int salary {get; set;}
public string name {get; set;}
public string school {get; set;}
and generic like:
和通用像：
List<Generic> _generic = = new List<Generic>
{
new Generic
{
Key = "@phase", Type = SqlDbType.Int, Value = "207"
}
}
};
And now, your productshas the options like: products.First(), products.Count(), foreachetc.
而现在，你products有一个像的选项：products.First()，products.Count()，foreach等。
