ASP.NET MVC和LINQ一般问题

I've asked a few questions on this topic before. Before we're able to implement either MVC or LINQ at work we need to resolve a few issues.

我以前就这个话题问了几个问题。在我们能够在工作中实现MVC或LINQ之前，我们需要解决一些问题。

Multiple Record Sets in ASP.NET MVC

ASP.NET MVC中的多个记录集

The only examples of MVC in use only have a single result set returned. When using stored procedures multiple record sets can be retrieved, and the whole reason we tend to use stored procedures is for two reasons (which I'm sure many of you are also aware of). Firstly in case we need to pass parameters, and secondly if we want multiple tables of data returned. How is this possible in ASP.NET's MVC architecture?

使用的MVC的唯一示例仅返回单个结果集。当使用存储过程时，可以检索多个记录集，并且我们倾向于使用存储过程的全部原因有两个原因（我相信很多人都知道）。首先，如果我们需要传递参数，其次，如果我们想要返回多个数据表。在ASP.NET的MVC架构中，这怎么可能？

In this tutorial we see how the data is retrieved. But it uses ViewData.Model which indicates a single result set, it doesn't explain what happens if multiple result sets are returned, or how to get them.

在本教程中，我们将了解如何检索数据。但是它使用了指示单个结果集的ViewData.Model，它没有解释如果返回多个结果集会发生什么，或者如何获取它们。

Strongly Typed Stored Procedure Output

强类型存储过程输出

Additionally, the examples on the ASP.NET website for using LINQ for strongly typed resolution of output are achieved by using the *.dbml format which is a mirror image of the table schema allowing for the lookup against fields using LINQ. Great. But what happens if your output is custom from a stored procedure which does not map directly to either a view or a table? How do we resolve column names from these stored procedures?

此外，ASP.NET网站上使用LINQ进行强类型输出解析的示例是通过使用* .dbml格式实现的，该格式是表模式的镜像，允许使用LINQ查找字段。大。但是，如果您的输出是从存储过程自定义的，而不直接映射到视图或表，会发生什么？我们如何从这些存储过程中解析列名？

In the previous section I described this tutorial, but this also only shows how to create LINQ to SQL for tables only, not the custom output from a sproc.

在上一节中，我描述了本教程，但这也只显示了如何仅为表创建LINQ to SQL，而不是从sproc创建自定义输出。

LINQ Column Lookups

LINQ列查找

At work we run a macro which exports a bunch of classes to our App_Code folder so stored procedure parameters are pre-defined. This is done so we don't have to call DeriveParameters which consists of an extra call to the database. We don't want this to happen because there's a lot of traffic as it is. If we're using LINQ, how are column data types resolved? Is there a call to the database everytime we define a parameter to find out the data type and name of the parameter? Have things changed since? Does it still call DeriveParameters every time? Are these cached somewhere?

在工作中，我们运行一个宏，它将一堆类导出到我们的App_Code文件夹，因此存储过程参数是预定义的。这样做是为了让我们不必调用DeriveParameters，它包含对数据库的额外调用。我们不希望这种情况发生，因为它有很多流量。如果我们使用LINQ，如何解析列数据类型？每当我们定义参数以找出参数的数据类型和名称时，是否有对数据库的调用？事情发生了变化吗？它每次都会调用DeriveParameters吗？这些缓存在某个地方吗？

DBML Formats

DBML格式

Should *.dbml files incorporate all tables from a database? We have about 15 databases with many, many tables in each one.

* .dbml文件是否应包含数据库中的所有表？我们有大约15个数据库，每个数据库中有许多表。

A View For Every Output

每个输出的视图

Yet another point to add to this post. Instead of manually creating the dbml classes is it better to represent the data as a view, even if it's custom output? Or is it better to create a custom class in the dbml file?

还有一点要补充这篇文章。而不是手动创建dbml类最好将数据表示为视图，即使它是自定义输出？或者在dbml文件中创建自定义类更好？

This Must Be The Last Problem Or I Will Eat My Own Arm

这必须是最后一个问题，否则我会吃掉自己的手臂

"Unable to cast object of type 'SingleResult`1[IntranetMVC.UserDetail]' to type 'IntranetMVC.UserDetail'."

“无法将'SingleResult`1 [IntranetMVC.UserDetail]'类型的对象强制转换为'IntranetMVC.UserDetail'。”

Here's the function:

这是功能：

  Function Index() As ActionResult
    ViewData("Message") = "Welcome to ASP.NET MVC!"

    Dim userDetail As UserDetail
    Dim office As IList(Of Office)
    Dim activeUser As IList(Of ActiveUser)
    Dim dept As IList(Of Department)

    Using db As PersonnelDataContext = New PersonnelDataContext
      Dim results As IMultipleResults = db.UserDetail(1168)

      userDetail = results.GetResult(Of UserDetail)()
      office = results.GetResult(Of Office)()
      activeUser = results.GetResult(Of ActiveUser)()
      dept = results.GetResult(Of Department)()
    End Using

    Return View(New IndexViewData(userDetail, office, activeUser, dept))
  End Function

It's occurring on all of the userDetail, office, activeUser and dept assignments, but I have no idea why. Now, I haven't mapped them properly yet, but take for example the Department one. I've dragged and dropped the table schema onto the dbml file, so it definitely exists and is in the right format.

它发生在所有userDetail，office，activeUser和dept分配上，但我不知道为什么。现在，我还没有正确地映射它们，但是以部门为例。我已经将表模式拖放到dbml文件中，因此它肯定存在并且格式正确。

UPDATE

UPDATE

Here's my actual code. It's not final, I've been playing around with it. It seems the return types aren't right, but I'm not sure why. It seems to think only a single result is ever returned when the stored procedure actually returns four sets of data. One of those sets only ever has a single result, the others always have multiple rows returned:

这是我的实际代码。这不是最终的，我一直在玩它。似乎返回类型不对，但我不确定为什么。它似乎认为当存储过程实际返回四组数据时，只返回一个结果。其中一个集合只有一个结果，其他集合总是返回多个行：

Unable to cast object of type 'SingleResult1[IntranetMVC.Office]' to type 'System.Collections.Generic.IList1

无法将类型为“SingleResult1 [IntranetMVC.Office]”的对象强制转换为“System.Collections.Generic.IList1”

Imports System.Data.Linq
Imports System.Reflection
Imports System.Data.Linq.Mapping

Partial Class PersonnelDataContext

  <FunctionAttribute(Name:="dbo.UserDetailProc"), _
  ResultType(GetType(UserDetail)), _
  ResultType(GetType(IList(Of Office))), _
  ResultType(GetType(IList(Of ActiveUser))), _
  ResultType(GetType(IList(Of Department)))> _
  Public Function UserDetail( _
                  <Parameter(Name:="User_Key", DbType:="Int")> ByVal User_Key As Integer, _
                  <Parameter(Name:="EditYN", DbType:="Char")> Optional ByVal EditYN As Char = "N") As IMultipleResults

    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod(), MethodInfo), User_Key, EditYN)
    Return CType(result.ReturnValue, IMultipleResults)
  End Function
End Class

FIX

固定

Okay, I didn't realise because to be honest with you I wasn't checking the return types correctly. I assumed that results.GetResult(Of MyType) (from IMultipleResults) would return a collection. On the contrary, it only returns single results and moves the pointer to the next item in the collection. Unfortunately GetResult is the only exposed method for bringing back results, so you have to iterate over the collection and add them to a generic list.

好吧，我没有意识到，因为老实说，我没有正确检查返回类型。我假设results.GetResult（Of MyType）（来自IMultipleResults）将返回一个集合。相反，它只返回单个结果并将指针移动到集合中的下一个项目。遗憾的是，GetResult是唯一可以返回结果的公开方法，因此您必须遍历集合并将它们添加到通用列表中。

Thanks very much!

非常感谢！

2 个解决方案

#1

Multiple Record Sets in ASP.NET MVC

Yep - most definately.

是的 - 最肯定的。

First u need to manually create a method that calls the stored proc, returning an IMultipleResults result.

首先，你需要手动创建一个调用存储过程的方法，返回一个IMultipleResults结果。

This blog posts has all the info u need. It's simple to do and very easy and works.

此博客文章包含您需要的所有信息。它很简单，非常简单和有效。

What you need to do is two steps.

你需要做的是两个步骤。

1. Create a method that calls the stored procedure and returns multiple records (refer to blog post above).
2. 创建一个调用存储过程并返回多个记录的方法（请参阅上面的博客文章）。
3. Create a simple class object which is used in the view, and the controller sets the properties.
4. 创建一个在视图中使用的简单类对象，控制器设置属性。

eg.

例如。

IndexViewData.cs
public class IndexViewData
{
    IList<Customers> Customers { get; set; }
    IList<Products> Products { get; set; }
}

.

。

HomeController.cs
public ActionResult Index()
{
    IList<Customers> customers;
    IList<Products> products;

    // This grabs the multiple records from a single stored procedure. 
    // This code taken from the blog post link, above.
    using (NorthwindDataContext db = new NorthwindDatacontext)
    {
        IMultipleResults results = db.GetMultipleRecordSets(arg1, ....);
        customers = results.GetResult<Customer>();
        products = results.GetProducts<Product>();
    }

    // Now return the view, with the viewdata that is required.
    return View(new IndexViewData
                    {
                        Customers = customers,
                        Products = products
                    });
}

.

。

Index.aspx
<%@ Page 
    Language="C#" 
    MasterPageFile="~/Views/Shared/Site.Master" 
    Inherits="System.Web.Mvc.ViewPage<IndexViewData>" %>

<% Html.RenderPartial("CustomersUserControl", 
                       ViewData.Model.Customers); %>

 <br/>

<h2>Products</h2>
<% foreach(var product in ViewData.Model.Products) { %>
Name: <%= product.Name %><br/>
<% } %>

...

Please note, i have not done any error checking, etc. it's a really quick pseduo code guide to getting u started.

请注意，我没有做任何错误检查，等等。这是一个非常快速的pseduo代码指南，让你开始。

Note #2: Please take note that the Index view is strongly typed (it inherits the ViewPage.

注意＃2：请注意索引视图是强类型的（它继承了ViewPage。

Strongly Typed Stored Procedure Output

I've answered this, above. Please note, u can strongly type your ISingleResult stored procedures.

我在上面回答了这个问题。请注意，您可以强烈键入您的ISingleResult存储过程。

LINQ Column Lookups

Ok, i think i understand what you mean, here. When you create your method, which calls the stored procedure (either an ISingleResult or an IMultipleResult) you are defining the paramters that are required, there and then ... think of it as being hard-coded.

好的，我想我明白你的意思，在这里。当您创建调用存储过程（ISingleResult或IMultipleResult）的方法时，您正在定义所需的参数，然后......将其视为硬编码。

When you drag-n-drop tables onto the linq to sql context gui canvas, Visual Studio does a lookup check there and then. It then creates the classes in one of the various files for the context. eg. NorthwindDataContext.designer, etc. So, it's a one-hit-job. Once the class is created, the designer then displays this on the canvas. There is NO SYNC back to the database. None. Nada. Zilch. If you change anything in your database schema (eg. add a new field, change a stored procedure argument, etc) the datacontext will NOT know about it. You need to delete the table and drag-n-drop it back.

当您将表拖放到linq到sql context gui canvas时，Visual Studio会在那里执行查找检查。然后，它在上下文的各种文件之一中创建类。例如。 NorthwindDataContext.designer等。所以，这是一个一击的工作。创建类后，设计器会在画布上显示该类。没有SYNC回到数据库。没有。纳达。小人物。如果更改数据库模式中的任何内容（例如，添加新字段，更改存储过程参数等），datacontext将不会知道它。您需要删除该表并将其拖放回来。

Bonus Trick!

If you have SQL Profiler running while you drag-n-drop a table or stored procedure onto the canvas, you can see Visual Studio 'querying' the database for the information. :)

如果在将表或存储过程拖放到画布上时运行SQL事件探查器，则可以看到Visual Studio“查询”数据库以获取信息。 :)

So yeah. It's fire-n-forget. A one-hit-job. Required manual sync.

嗯是的。这是永远不会忘记的。一个打击的工作。需要手动同步。

HTH.

HTH。

Update

I noticed u added two more q's, so i'll add my answers, under here.

我注意到你又增加了两个q，所以我会在这里添加我的答案。

DBML Formats

This is a personal decision. 15 DB's! shees! that's a fair number. Anyways, it comes down to how maintainable your Context canvas becomes. Secondly, every context creates it's OWN database connection. So if your method decides to call 4 contexts, then you've got 4 connections (and round trips) to the db, dude :)

这是个人决定。 15 DB！ shees！这是一个公平的数字。无论如何，它归结为您的Context画布的可维护性。其次，每个上下文都创建它的OWN数据库连接。因此，如果您的方法决定调用4个上下文，那么您有4个连接（和往返）到数据库，dude :)

A View For Every Output

Personally, I have all my tables on the Context Canvas. I never use those table classes in my code. They are private and only used in my Repository namespace/project/dll. I THEN use POCO classes to move all my stuff around. This keeps my code, cleaner and not dependant on the repository.

就个人而言，我在Context Canvas上拥有所有表格。我从不在代码中使用这些表类。它们是私有的，仅用于我的Repository namespace / project / dll。我然后使用POCO课程来移动我所有的东西。这使我的代码更清晰，更不依赖于存储库。

Update #2

This Must Be The Last Problem Or I Will Eat My Own Arm

If you've dragged the stored proc onto the linq context canvas, please delete it. There needs to be no references to the method UserDetails(int userId).

如果您已将存储的proc拖到linq上下文画布上，请将其删除。不需要引用UserDetails方法（int userId）。

Now, add the following code (you'll need to convert it to VB.NET) to the data context's partial class (i'm assuming u know what that is/means, btw) :-

现在，添加以下代码（您需要将其转换为VB.NET）到数据上下文的部分类（我假设您知道这是什么/意味着什么，顺便说一句）： -

[Function("UserDetails")] // <-- This is the name of your stored procedure.
[ResultType(TypeOf(UserDetail))]
[ResultType(TypeOf(Office))]
[ResultType(TypeOf(ActiveUser))]
[ResultType(TypeOf(Department))]
public IMultipleResults UserDetails(
    [Parameter(Name = "UserId", DbType = "Int")] int userId)
//                      /\____     /\_____         ____/\                    
// This is where u _define_ the stored proc arguments.
{
    IExecuteResult result = this.ExecuteMethodCall(this, 
           ((MethodInfo)MethodInfo.GetCurrentMethod())), userId);
// This is where all the stored proc arguments are set ____/\
// This can be multiple args. eg. userId, name, ...
    return (IMultipleResults)result.ReturnValue;
}

then use it like u did in your previous VB.NET code.

然后就像你在以前的VB.NET代码中那样使用它。

The problem (i'm guessing) was that you haven't made the method to handle IMultipleResults. You're still using the old stored proc code-signature, which was made (by default) to only be a single recordset result (ie. ISingleResult).

问题（我猜）是你没有使用方法来处理IMultipleResults。您仍在使用旧的存储过程代码签名（默认情况下），它只是一个记录集结果（即ISingleResult）。

This is the default if u drag-n-drop a stored from from the Server Explorer onto the linq Context Canvas.

如果您将存储的内容从服务器资源管理器拖放到linq Context Canvas上，则这是默认设置。

#2

Multiple Record Sets in ASP.NET MVC:

ASP.NET MVC中的多个记录集：

If you have stored procedure that return A and B. Then you create a specific ViewModel:

如果您有存储过程返回A和B.然后您创建一个特定的ViewModel：

public class AB
{
  public A DataA { get; set; };
  public B DataB { get; set; };
}

You can also use the ViewData dictionary instead of the Model property (or in combination with this property which also works)

您还可以使用ViewData字典而不是Model属性（或与此属性结合使用）

Strongly Typed Stored Procedure Output

强类型存储过程输出

You create a specific class for the results returning from the stored procedure with custom fields.

您可以使用自定义字段为存储过程返回的结果创建特定类。

LINQ Column Lookups

LINQ列查找

Not 100% sure about this, but LINQ lookups the column field names and parameter names from a stored procedure at design time.

不是100％肯定这一点，但LINQ在设计时从存储过程中查找列字段名称和参数名称。