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.
Multiple Record Sets in 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?
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.
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?
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 Column Lookups
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 Formats
Should *.dbml files incorporate all tables from a database? We have about 15 databases with many, many tables in each one.
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?
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'."
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.
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
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.
Thanks very much!
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.
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.
- Create a method that calls the stored procedure and returns multiple records (refer to blog post above).
- Create a simple class object which is used in the view, and the controller sets the properties.
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.
Note #2: Please take note that the Index view is strongly typed (it inherits the ViewPage.
Strongly Typed Stored Procedure Output
I've answered this, above. Please note, u can strongly type your ISingleResult stored procedures.
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.
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.
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. :)
So yeah. It's fire-n-forget. A one-hit-job. Required manual sync.
HTH.
Update
I noticed u added two more q's, so i'll add my answers, under here.
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 :)
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.
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).
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;
}
then use it like u did in your previous VB.NET code.
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).
This is the default if u drag-n-drop a stored from from the Server Explorer onto the linq Context Canvas.
Multiple Record Sets in ASP.NET MVC:
If you have stored procedure that return A and B. Then you create a specific 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)
Strongly Typed Stored Procedure Output
You create a specific class for the results returning from the stored procedure with custom fields.
LINQ Column Lookups
Not 100% sure about this, but LINQ lookups the column field names and parameter names from a stored procedure at design time.
