ASP.NET MVC和LINQ一般问题 [英] ASP.NET MVC And LINQ General Questions

问题描述

我已经问过关于这一主题的几个问题。我们能够在工作中实现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.

在ASP.NET MVC的多个记录集

Multiple Record Sets in ASP.NET MVC

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?

在本教程我们看到检索数据的方式。但它使用 ViewData.Model 这表明一个结果集，它不能解释如果返回的多个结果集，或者如何让他们发生了什么。

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.

强类型的存储过程输出

此外，ASP.NET的网站使用LINQ输出的强类型的分辨率的实施例是通过使用*的.dbml格式，它是表架构允许针对使用LINQ字段的查找的镜像来实现。大。但是，如果你的输出的自定义从一个存储过程不直接映射到一个视图或表时会发生什么？我们如何解决这些存储过程中的列名？

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?

在previous部分我介绍了本教程，但是这也只显示了如何创建LINQ to SQL中的表只，从一个存储过程的输出没有自定义。

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列查找

在工作中我们运行宏其中出口一堆类来我们APP_ code文件夹，以便存储过程的参数是pre定义。这样做，所以我们不必调用它由一个额外调用数据库DeriveParameters。我们不希望这种情况发生，因为有大量的流量，因为它是。如果我们使用LINQ，怎么也列数据类型解决？有对数据库每次我们定义一个呼叫找出参数的数据类型和名称的参数？有事情从此改变？它是否仍然称DeriveParameters每一次？难道这些缓存的地方？

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?

DBML格式

*应该的.dbml文件包括从数据库中的所有表？我们有大约15个数据库，在每一个很多很多表。

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

一查看每个输出

另一个点添加到这个职位。而不是手动创建的dbml类是它更好地重新present数据作为视图，即使它的自定义输出？或者是更好地在DBML文件中创建一个自定义类？

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?

这必须是最后一个问题或我会吃我自己的手臂

的无法投类型的对象SingleResult`1 [IntranetMVC.UserDetail]'键入'IntranetMVC.UserDetail'。的

下面的功能：

  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

这是发生在所有的 userDetail，办公室，activeUser 和部门的任务，但我不知道为什么。现在，我还没有映射他们还正确，而是采取例如部门之一。我拖放表架构到dbml的文件，所以它肯定存在并且是正确的格式。

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.

更新

下面是我的实际code。这不是最终的，我一直在玩弄它。这似乎返回类型不正确的，但我不知道为什么。看来当存储的过程实际上返回四组数据是有史以来只返回一个结果去思考。其中一个组的永远只能有一个结果，其他人永远都返回了多行：

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:

的无法投类型的对象SingleResult 1 [IntranetMVC.Office]'键入'System.Collections.Generic.IList 1 的

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

好吧，我不知道因为跟你说实话，我没有正确检查返回类型。我的假设的那个results.GetResult（中的MyType）（从IMultipleResults）将返回一个集合。相反，它只返回单个结果和指针集合中移动到下一个项目。不幸的是调用getResult是带回结果的唯一公开方法，所以你必须遍历集合并将它们添加到一个通用的列表。

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.

非常感谢！

推荐答案

是的 - 最肯定

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

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

<一个href=\"http://blogs.msdn.com/swiss%5Fdpe%5Fteam/archive/2008/02/04/linq-to-sql-returning-multiple-result-sets.aspx\"相对=nofollow>这个博客帖子有u需要的全部信息。这是简单的事情，很容易和作品。

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. 创建，调用存储过程返回多个记录的方法（参见上面的博客后）。


2. 创建这是在视图中使用一个简单的类对象，控制器设置属性。

如：

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/>
<% } %>

...

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

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

注2：请注意，该索引视图是强类型（它继承的ViewPage

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

我上面回答了这个。请注意，美国可以强烈地输入您的 ISingleResult 的存储过程。

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

好吧，我想我明白你的意思，在这里。
当你创建你的方法，它调用存储过程（或者一个 ISingleResult 或 IMultipleResult ）您所定义的paramters这是必需的，那里，然后......把它看成是硬codeD。

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.

当你拖N - 降表到LINQ to SQL的情况下GUI帆布，Visual Studio中做了检查查找的那里，然后的。然后，它在上下文中的各种文件中的一个产生的类。例如。 NorthwindDataContext.designer等，所以，这是一个的一击在职的。一旦类被创建，设计师则显示这在画布上。有 NO SYNC回数据库的。没有。纳达。小人物。如果您在数据库架构进行任何更改（例如，添加一个新的领域，改变一个存储过程的参数，等等）的datacontext会的不的了解它。您需要删除表并拖动正拖放回来。

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.

如果您有SQL事件探查器运行的同时拖动正删除表或存储过程到画布上，你可以看到Visual Studio的查询的信息数据库。 ：）

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. :)

所以，是的。这是火正忘记。一个一击作业。需要手动同步。

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

心连心。

我注意到ü增加了两个Q的，所以我会加入我的答案，在这里下。

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

这是一个个人的决定。 15 DB的！ shees！这是一个公平的数字。不管怎么说，这都归结到你的画布背景下如何维护而成。
其次，每一个方面创建它自己的数据库连接。所以，如果你的方法决定调用4上下文，那么你已经有了4个连接（和往返），以分贝，花花公子：）

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 :)

就个人而言，我有我的语境画布所有表。我从来没有在我的code使用这些表类。他们是私有的，只能在我的仓库空间/项目/ DLL使用。然后我用 POCO 类走动我所有的东西。这使我的code，更清洁，不依赖于资源库中。

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.

如果你已经拖了存储过程到LINQ背景画布，请删除它。需要有对方法没有引用的UserDetails（INT用户id）。

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).

现在，添加以下code（你需要将其转换为VB.NET）到数据上下文的分部类（我假设你知道那是什么/手段，顺便说一句）： -

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) :-

[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;
}

然后使用它就像妳previous VB.NET code一样。

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

问题（我猜）是，你还没有做出处理 IMultipleResults 的方法。你还在使用旧的存储过程code-签名，这是作出（默认）仅是一个单一的记录结果（即 ISingleResult ）

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).

这是如果U拖动丁基下降，从服务器资源管理器到LINQ上下文画布存储在默认的。

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

这篇关于ASP.NET MVC和LINQ一般问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！