ASP.NET MVC：调用存储过程的最佳方法

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和Entity Framework以及Repository Pattern的内容。说实话，我很难理解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文件，从Server Explorer中选择了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?

有人能认出我的问题在这里吗？

5 个解决方案

#1

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.

我使用Enterprise Library，但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.

MSDN has a decent tutorial on the topic.

MSDN有一个关于这个主题的体面教程。

#2

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 从一个单独的存储库类中的函数传递回来进行编辑，如果更新成功进行更新，则返回布尔值。

#3

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参数映射实体字段。因此，假设您有一个Customers表，EF会生成一个Customers Entity，并且您可以在尝试更新/插入/删除时将proc参数映射到Customer实体的属性。

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.

HTH。

#4

-1

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非常有益。

#5

-5

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要学习更多，在这种情况下不需要。

#1