asp.net multiple result set from proc: is it necessary to map results to class? If so, how?

Question

Please consider the following scenario.

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

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.

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

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.

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

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

then

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

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

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.

Answer 1

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

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

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.

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

• bind your data to the grid.

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