asp.net-mvc ASP.NET MVC：调用存储过程的最佳方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4259989/
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
ASP.NET MVC: Best Way To Call Stored Procedure
提问by dcolumbus
I'm trying to decide which is the best way to call a stored procedure.
我正在尝试确定调用存储过程的最佳方式。
I'm new to ASP.NET MVC and I've been reading a lot about Linq to SQL and Entity Framework, as well as the Repository Pattern. To be honest, I'm having a hard time understanding the real differences between L2S and EF... but I want to make sure that what I'm building within my application is right.
我是 ASP.NET MVC 的新手，我已经阅读了很多关于 Linq to SQL 和实体框架以及存储库模式的内容。老实说，我很难理解 L2S 和 EF 之间的真正区别……但我想确保我在应用程序中构建的内容是正确的。
For right now, I need to properly call stored procedures to: a) save some user information and get a response and, b) grab some inforation for a catalog of products.
现在，我需要正确调用存储过程来：a) 保存一些用户信息并获得响应，b) 获取产品目录的一些信息。
So far, I've created a Linq to SQL .dbml file, selected the sotred procedure from the Server Explorer and dragged that instance into the .dbml. I'm currently calling the Stored Procedure like so:
到目前为止，我已经创建了一个 Linq to SQL .dbml 文件，从服务器资源管理器中选择了 sotred 过程并将该实例拖到 .dbml 中。我目前正在像这样调用存储过程：
MyLinqModel _db = new MyLinqModel();
_db.MyStoredProcedure(args);
I know there's got to be more involved... plus I'm doing this within my controller, which I understand to be not a good practice.
我知道必须有更多的参与......而且我正在我的控制器中这样做，我理解这不是一个好的做法。
Can someone recognize what my issues are here?
有人能认出我的问题是什么吗？
回答by 3Dave
LINQ and EF are probably overkill if all you're trying to do is call a stored proc.
如果您尝试做的只是调用存储过程，则 LINQ 和 EF 可能有点矫枉过正。
I use Enterprise Library, but ADO.NET will also work fine.
我使用企业库，但 ADO.NET 也可以正常工作。
See this tutorial.
请参阅本教程。
Briefly (shamelessly copied-and-pasted from the referenced article):
简要（从引用的文章中无耻地复制粘贴）：
SqlConnection conn = null;
SqlDataReader rdr = null;
// typically obtained from user
// input, but we take a short cut
string custId = "FURIB";
Console.WriteLine("\nCustomer Order History:\n");
// create and open a connection object
conn = new SqlConnection("Server=(local);DataBase=Northwind; Integrated Security=SSPI");
conn.Open();
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(
"CustOrderHist", conn);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which
// will be passed to the stored procedure
cmd.Parameters.Add(
new SqlParameter("@CustomerID", custId));
// execute the command
rdr = cmd.ExecuteReader();
// iterate through results, printing each to console
while (rdr.Read())
{
Console.WriteLine(
"Product: {0,-35} Total: {1,2}",
rdr["ProductName"],
rdr["Total"]);
}
}
Update
更新
I missed the part where you said that you were doing this in your controller.
我错过了您说您在控制器中执行此操作的部分。
No, that's not the right way to do this.
不，这不是正确的做法。
Your controller should really only be involved with orchestrating view construction. Create a separate class library, called "Data Access Layer" or something less generic, and create a class that handles calling your stored procs, creating objects from the results, etc. There are many opinions on how this should be handled, but perhaps the most common is:
您的控制器应该只参与编排视图构建。创建一个单独的类库，称为“数据访问层”或不那么通用的东西，并创建一个处理调用存储过程、从结果创建对象等的类。 关于如何处理这个问题有很多意见，但也许是最常见的是：
View
|
Controller
|
Business Logic
|
Data Access Layer
|--- SQL (Stored procs)
-Tables
-Views
-etc.
|--- Alternate data sources
-Web services
-Text/XML files
-blah blah blah.
回答by vapcguy
Try this:
尝试这个：
Read:
读：
var authors = context.Database.SqlQuery<Author>("usp_GetAuthorByName @AuthorName",
new SqlParameter("@AuthorName", "author"));
Update:
更新：
var affectedRows = context.Database.ExecuteSqlCommand
("usp_CreateAuthor @AuthorName = {0}, @Email= {1}",
"author", "email");
From this link: http://www.dotnetthoughts.net/how-to-execute-a-stored-procedure-with-entity-framework-code-first/
从这个链接：http: //www.dotnetthoughts.net/how-to-execute-a-stored-procedure-with-entity-framework-code-first/
And I would go with the framework David Lively mentioned, instead of having the routines in the controller. Simply pass the results back as IEnumerable<blah>from a function in a separate repository class for an edit, pass a boolean back for if the update succeeded for an update.
我会使用 David Lively 提到的框架，而不是在控制器中使用例程。只需将结果IEnumerable<blah>从单独的存储库类中的函数传回进行编辑，如果更新成功，则将布尔值传回更新。
回答by Brian Mains
LINQ to SQL and ADO.NET EF attach read stored procs to the data/object context class that you use to go against its various entities. For create, update, and delete, you can create a proc that maps the properties of an entity that the model generates, and using the entity mapping window (forget the exact name right now), you can map an entities fields with the proc parameters. So, say you have a Customers table, EF generates a Customers Entity, and you can map the proc parameters to the properties of the Customer entity when attempting to update/insert/delete.
LINQ to SQL 和 ADO.NET EF 将读取的存储过程附加到您用来对抗其各种实体的数据/对象上下文类。对于创建、更新和删除，您可以创建一个映射模型生成的实体属性的 proc，并使用实体映射窗口（现在忘记确切名称），您可以使用 proc 参数映射实体字段. 因此，假设您有一个客户表，EF 生成一个客户实体，并且您可以在尝试更新/插入/删除时将 proc 参数映射到客户实体的属性。
Now, you can map a CUD proc to a function, but I don't know all the repercussions; I like the way I just mentioned the best.
现在，您可以将 CUD proc 映射到一个函数，但我不知道所有的影响；我喜欢我刚才提到的最好的方式。
HTH.
哈。
回答by Matt
I common pattern is to pass a repository interface into your controller by dependency injection. The choice of what persistence/orm technology you use is really another issue and unrelated to the fact that you are using MVC. Using the repository pattern and coding to abstractions (interfaces) makes your application easy to test by mocking out your repositories.
我的常见模式是通过依赖注入将存储库接口传递到您的控制器中。选择您使用的持久性/orm 技术实际上是另一个问题，与您使用 MVC 的事实无关。使用存储库模式和编码抽象（接口）使您的应用程序易于通过模拟您的存储库来测试。
I think you should also try to use as few stored procedures as possible. This means you can more easily test your logic in isolation (unit tests) without needing to be connected to a database. I would highly recommend looking at NHibernate. The learning curve is fairly steep but you are in full control of your mappings and configuration. There are obviously occasions where you will need stored procs for performance reasons, but using an ORM predominantly is very beneficial.
我认为您还应该尝试使用尽可能少的存储过程。这意味着您可以更轻松地单独测试您的逻辑（单元测试），而无需连接到数据库。我强烈建议您查看 NHibernate。学习曲线相当陡峭，但您可以完全控制您的映射和配置。显然，出于性能原因，您在某些情况下需要存储过程，但主要使用 ORM 是非常有益的。
回答by PDCBob
I can't imagine that your goal is to be able to call a stored procedure. To me it sounds as if you need to forget stored procedures and use Linq to Sql. I say L2S because EF is far more to learn, and not needed in this case.
我无法想象您的目标是能够调用存储过程。对我来说，这听起来好像您需要忘记存储过程并使用 Linq to Sql。我说 L2S 是因为 EF 要学得多，在这种情况下不需要。
