ASP.NET MVC：最好的方法来调用存储过程 [英] ASP.NET MVC: Best Way To Call Stored Procedure

问题描述

我试图决定哪个是调用存储过程的最佳方式。

I'm trying to decide which is the best way to call a stored procedure.

我新的ASP.NET MVC和我一直在阅读了很多关于LINQ to SQL和实体框架，以及Repository模式。说实话，我有一个很难理解L2S和EF之间真正的不同......但我要确保我正在我的应用程序内部建立是正确的。

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.

有关，现在，我需要正确地调用存储过程：一）节省一些用户信息和得到回应和b）抓住一些inforation的目录产品

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.

到目前为止，我创建了一个LINQ to SQL的.dbml文件，从服务器资源管理器中选择的sotred程序并拖累该实例进入的.dbml。我目前正在调用存储过程像这样：

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:

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?

推荐答案

LINQ和EF可能矫枉过正，如果你正在试图做的一切都是调用存储过程。

LINQ and EF are probably overkill if all you're trying to do is call a stored proc.

我使用的企业库，但也ADO.NET将正常工作。

I use Enterprise Library, but ADO.NET will also work fine.

请参阅本教程。

简单（无耻复制并粘贴从引用的文章）：

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"]);
        }
    }

更新

我错过了，你说你在你的控制器这样做的部分。

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对主题的体面的教程。

这篇关于ASP.NET MVC：最好的方法来调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！