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 中的多个记录集
唯一使用的 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?
在上一节中，我描述了本教程，但是这也仅显示了如何仅为表创建 LINQ to SQL，而不是 sproc 的自定义输出.
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 文件夹，因此存储过程参数是预定义的.这样做是为了我们不必调用由对数据库的额外调用组成的 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 类相比，将数据表示为视图是否更好，即使它是自定义输出?还是在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、office、activeUser 和 dept 分配中，但我不知道为什么.现在，我还没有正确地映射它们，但以第一个部门为例.我已将表架构拖放到 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.
更新
这是我的实际代码.这不是最终的，我一直在玩它.似乎返回类型不正确，但我不确定为什么.当存储过程实际返回四组数据时，它似乎认为只返回一个结果.其中一组只有一个结果，其他的总是返回多行:
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:
无法将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
修复
好吧，我没有意识到，因为老实说我没有正确检查返回类型.我假设 results.GetResult(Of 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.
非常感谢！
推荐答案
ASP.NET MVC 中的多记录集
是的 - 绝对是.
Multiple Record Sets in ASP.NET MVC
Yep - most definately.
首先你需要手动创建一个调用存储过程的方法，返回一个 IMultipleResults 结果.
First u need to manually create a method that calls the stored proc, returning an IMultipleResults result.
这篇博文有你需要的所有信息.做起来很简单，而且非常容易和有效.
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.
- 创建一个调用存储过程并返回多条记录的方法(请参阅上面的博客文章).
- 创建一个在视图中使用的简单类对象，控制器设置属性.
例如
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.
注意 #2:请注意 Index 视图是强类型的(它继承了 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)的方法时，您正在定义所需的参数，然后……想想它是硬编码的.
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.
当您将表拖放到 linq 到 sql 上下文 gui 画布上时，Visual Studio 会在那里然后进行查找检查.然后，它在上下文的各种文件之一中创建类.例如.NorthwindDataContext.designer 等.所以，这是一个一击必杀.一旦创建了类，设计器就会在画布上显示它.没有没有同步回数据库.没有任何.纳达.齐尔奇.如果您更改了数据库架构中的任何内容(例如，添加新字段、更改存储过程参数等)，数据上下文将不 知道它.您需要删除表格并将其拖回.
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 Profiler，您可以看到 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.
HTH.
我注意到你又添加了两个问题，所以我会在这里添加我的答案.
I noticed u added two more q's, so i'll add my answers, under here.
这是个人决定.15 个数据库！鞋！这是一个公平的数字.无论如何，这归结为您的 Context 画布变得如何可维护.其次，每个上下文创建它自己的数据库连接.因此，如果您的方法决定调用 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 :)
就我个人而言，我的所有表格都在 Context Canvas 上.我从不在我的代码中使用这些表类.它们是私有的，仅在我的存储库命名空间/项目/dll 中使用.然后我使用 POCO 类来移动我所有的东西.这使我的代码更简洁，并且不依赖于存储库.
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.
如果您已将存储的 proc 拖到 linq 上下文画布上，请删除它.不需要引用方法 UserDetails(int userId).
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).
现在，将以下代码(您需要将其转换为 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;
}
然后像在之前的 VB.NET 代码中一样使用它.
then use it like u did in your previous VB.NET code.
问题(我猜)是你没有制定处理IMultipleResults的方法.您仍在使用旧的存储过程代码签名，该代码签名(默认情况下)仅为单个记录集结果(即 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).
如果您将存储的从服务器资源管理器拖放到 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屋！
