title: asp.net multiple result set from proc: is it necessary to map results to class? If so, how?
body: <p>Please consider the following scenario. </p>
  
  <p>You have a stored proc returning multilple result sets for 
  queries run for reporting purposes. </p>
  
  <p>The proc also use table valued parameters (SQL 2008). 
  <a href="http://www.sommarskog.se/arrays-in-sql-2008.html#LINQ_EF" rel="nofollow">http://www.sommarskog.se/arrays-in-sql-2008.html#LINQ_EF</a></p>
  
  <p>These result sets are read-only, meaning you don't have to 
  worry about updates, deletes, or inserts. </p>
  
  <p>These result sets do not map to any table in the Database; 
  these are reporting results, not mirrors of database tables. </p>
  
  <p>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</p>
  
  <p>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. </p>
  
  <p>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.)</p>
  
  <p>I think this would be fairly easy to implement by having data access and inline scripting &lt;% %> on the same page. 
  Here's an example of the kind of code that would result: </p>
  
  <pre><code>&lt;%
  ' 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.%&gt;
  </code></pre>
  
  <p>then </p>
  
  <pre><code>&lt;%  If Teachers.Count &gt; 0 Then%&gt;
  &lt;table&gt;&lt;%  For Each _Teachers In Teachers%&gt;
      &lt;tr&gt;
          &lt;td&gt;&lt;%= _Teachers(0)%&gt;&lt;/td&gt;
          &lt;td&gt;&lt;%= _Teachers(1)%&gt;&lt;/td&gt;
      &lt;/tr&gt;&lt;% Next %&gt;    
  &lt;/table&gt;
  &lt;%  Else%&gt;Hey, there's no records.&lt;% End If %&gt; 
  </code></pre>
  
  <p>(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.")</p>
  
  <p>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.? </p>
  
  <p>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.)</p>
  
  <p>Thanks in advance for any help. </p>