从PROC asp.net多个结果集：是否有必要映射的结果来上课？如果是这样，怎么样？ [英] asp.net multiple result set from proc: is it necessary to map results to class? If so, how?

问题描述

请考虑以下情形。

您有一个存储过程返回multilple结果集 查询运行报告。

You have a stored proc returning multilple result sets for queries run for reporting purposes.

在PROC还可以使用表值参数（SQL 2008）。 http://www.sommarskog.se/arrays-in-sql-2008 html的＃LINQ_EF

The proc also use table valued parameters (SQL 2008). http://www.sommarskog.se/arrays-in-sql-2008.html#LINQ_EF

这些结果集是只读的，这意味着你不必 担心更新，删除或插入。

These result sets are read-only, meaning you don't have to worry about updates, deletes, or inserts.

这些结果集没有映射到数据库中的任何表; 这些报告的结果，而不是镜子的数据库表。

These result sets do not map to any table in the Database; these are reporting results, not mirrors of database tables.

现在，我的背景是ASP经典，虽然我一直在努力读了 在所有的.NET数据访问的策略，看来： （1）MS是推动实体框架 （2）MS有大量的数据访问/架构策略 （3）整个话题是一个争论的持续主题 （4）该项目的具体情况帮助决定适当的数据访问策略，     和ORM似乎并不对这种数据访问方案优化 （5）有出位的现成的无解映射多个结果集存储的特效

Now, my background is asp classic, and while I have been trying to read up on all the .NET data access strategies, it seems: (1) MS is pushing Entity Framework (2) MS has a plethora of data access/architecture strategies (3) the entire topic is a continual subject of debate (4) the circumstances of the project help dictate the proper data access strategy, and ORM does not seem optimized for this data access scenario (5) there is no out-of-the-box solution for mapping multiple result set stored procs

另外，我在寻找完全控制HTML输出，因此，如果使用的背后code， 仅中继器和面值将被用于数据绑定。

Also, I'm looking for full control over the html output, so if using code behind, only Repeaters and Literals would be used for databinding.

我找到Web窗体编程模型无法提供完整的分离 的code和内容，并可以就如同意大利面条作为ASP经典。 （该MVC 做法似乎更像ASP经典，但这个项目已经WebForms的。）

I find the Web Forms programming model fails to deliver complete separation of code and content, and can be just as "spaghetti" as asp classic. (The MVC approach seems much more like asp classic, but this project is already WebForms.)

我认为这将是很容易通过让数据访问和内联脚本＆LT落实;％％>在同一页上。 这里是那种code，会导致的一个例子：

I think this would be fairly easy to implement by having data access and inline scripting <% %> on the same page. Here's an example of the kind of code that would result:

<%
' data access (skipping a bunch of code)....
Dim myDataSet As New DataSet()
myCommand.Fill(myDataSet)
'...etc.    

Teachers = myDataSet.Tables(0).Rows
Students = myDataSet.Tables(1).Rows
'... etc.%>

然后

<%  If Teachers.Count > 0 Then%>
<table><%  For Each _Teachers In Teachers%>
    <tr>
        <td><%= _Teachers(0)%></td>
        <td><%= _Teachers(1)%></td>
    </tr><% Next %>    
</table>
<%  Else%>Hey, there's no records.<% End If %> 

（当我尝试分开下落后于code中的数据访问保护小组的Page_Load ... 在后面，我在.aspx页面中使用了code中的变量不断给错误： [变量]未声明，它可能无法访问由于其保护级别。）

(When I try to separate the data access in code behind under the "Protected Sub Page_Load...", the variables in the code behind that I use in the .aspx page keep giving the error: "[variable] is not declared. It may be inaccessible due to its protection level.")

如果从在proc的结果集不是强类型， 难道是世界的末日，可怕的编程实践，一个维修噩梦， 最糟糕的选择，等等？

If the result sets from the proc are not strongly typed, is it the end of the world, awful programming practice, a maintainability nightmare, the worst possible choice, etc.?

如果从存储过程的结果集应该是强类型， 那么什么是要对这个最有效的方法是什么？ （我不是 关于这个主题寻找简单的教程。）

If the result sets from the stored proc ought to be strongly typed, then what is the most effective way of going about this? (I'm not finding straightforward tutorials on this topic.)

在此先感谢您的帮助。

Thanks in advance for any help.

推荐答案

这听起来像你问是否应该继续使用ADO.NET容器或自定义域类为您的数据。

It sounds like you're asking whether you should continue to use ADO.NET containers or custom domain classes for your data.

如果你觉得你需要一个类的强类型，并输入一些验证，或者应用到你的数据显示之前其他逻辑，那么考虑编写一个方法将数据表转换成YourClass的集合。否则，如果这是显示，然后没有什么错与DataTable中坚持。如果你提供的其他组件的接口，或者需要一个阶级会给你的特点，去为一类的强类型。

If you feel you need the strong typing of a class, and some validation of input, or other logic applied to your data before display, then consider writing a method to convert a DataTable into a collection of YourClass. Otherwise, if this is for display, then there's nothing wrong with sticking with the DataTable. If you're providing an interface for other components, or need the features that a class would give you, go for the strong typing of a class.

public IEnumerable<Teacher> ConvertToTeachers(DataTable dt)
{
    foreach (var row in dt.Rows.AsEnumerable())
    {
        //create a teacher from this row. modify row indexers as required.
        yield return new Teacher{ TeacherName = row["Name"].Value,
                                  Location = row["Location"].Value };
    }   
}

在presentation层，可以考虑充分利用服务器控件ASP.NET web表单为您提供。他们提供的数据绑定功能，删除所有的循环。

On the presentation tier, consider leveraging the server controls that ASP.NET webforms provides you. They provide data-binding capabilities that remove all that looping.

• 确保你的GridView，只要你喜欢的设计。正确的列中包含所需数据：常规绑定字段和超链接

• Ensure your gridview is designed as you like. Proper columns containing the data you want: regular bound fields and hyperlinks.

您的数据绑定到网格。

 gridViewTeachers.DataSource = myDataSet.Tables(0).Rows
 gridViewTeachers.DataBind

这篇关于从PROC asp.net多个结果集：是否有必要映射的结果来上课？如果是这样，怎么样？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！