Tuesday, April 23, 2013

Returning multiple Recordsets into a single DataSet

Hack the Gibson

In my particular industry (financial), they perform trades in what are known as blocks.  From there they will break that block up into allocations.  Lets take a 401K point of view -
Every two weeks you put $100 (we’re going to use nice round numbers for simplicity) towards your 401K (Through a firm like Prudential).  This is called a block.  However, after your investment firm gets the $100 they break it up and invest it into different companies (usually determined by you).  Obviously, this is called an allocation.  So you have $100 block money that is applied like so $40 to Disney stock, $20 to Coke and $20 to Apple.
Well in order for companies like mine to follow the cash flow (usually for government reporting purposes) we have to be able to view both the block and allocations.  In ASP.Net reporting, this usually means putting two Grids on a page so that someone can bean count the numbers rather quickly.
We could write two separate Stored Procedures which would make us have to hit the database twice (four trips).  This seems like a very ridiculous and wasteful way to retrieve the data.  It is also less secure considering every trip to the database and transporting data opens methods for people to get at your data.  So what if we only wanted to make a single trip to the database.  How do we then separate the data into two separate Grids?  Well, I tell ya what… Continue reading and you will find out.

This is the best practices method of returning multiple recordsets.  Since we are already using best practices, we’ll using a tier architecture to retrieve our data.

Ok, so first thing’s first.  We need to create our data.  Otherwise we have no idea what to expect in the Grid.  So fire up SSMS (Sql Server Management Studio) and (of course) we will be using the trusty, dusty Northwind database.
For simplicity we are going to create two recordsets.  One that gives us product related information and one that returns Order information on products.  So they are kind of related.
So let’s create a new Stored Procedure, I called mine dbo.getMultipleResultSets
Quite honestly, it doesn’t matter what you call the SP, as long as you remember what you named it.

So the idea is to return a couple rows from a table.  It makes sense to do the Products table first and then the Order Details.  So our Select statement will look like:

SELECT
p.ProductID
, p.ProductName
, p.UnitPrice
, p.UnitsInStock
From
dbo.Products p

I just grabbed a couple columns that seem to make sense to be put together.  Now we will want to get the Order Details.  Let’s make this interesting and do some math and joins.  Whoop Whoop!  This one is going to be slightly larger than the Select statement for Products -




Select 
c.CompanyName
, p.ProductName
, od.Quantity
, od.UnitPrice
, (Select od.Quantity * od.UnitPrice)
As 'Total Price'
From
dbo.[Order Details] od
Inner Join dbo.Orders o ON
od.OrderID = o.OrderID
Inner Join dbo.Customers c ON
o.CustomerID = c.CustomerID
Inner Join dbo.Products p ON
od.ProductID = p.ProductID


 



So here we are, we are fetching the Company name, Product name, Quantity (that was ordered), unit price, and the total value of the order (minus taxes etc… work with me here…)

You can see that we have three inner joins (could also be written as simply Join).


So all together, our SP should look like -




Use [Northwind]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE dbo.getMultipleResultSets
AS
BEGIN
SET NOCOUNT ON
;

SELECT
p.ProductID
, p.ProductName
, p.UnitPrice
, p.UnitsInStock
From
dbo.Products p

Select
c.CompanyName
, p.ProductName
, od.Quantity
, od.UnitPrice
, (Select od.Quantity * od.UnitPrice)
As 'Total Price'
From
dbo.[Order Details] od
Inner Join dbo.Orders o ON
od.OrderID = o.OrderID
Inner Join dbo.Customers c ON
o.CustomerID = c.CustomerID
Inner Join dbo.Products p ON
od.ProductID = p.ProductID
END
GO
-- dbo.getMultipleResultSets

If you are asking yourself why I comment out the name of the SP at the end of the SP, that is because it makes it really simple to run the SP while in Alter.  You simply highlight the name and then click the Execute (or Ctrl-E) and execute the SP -


imageimage

Once you execute the SP, you should have two resultsets, like so -

image



If you still don’t have two result sets, you need to look at your SQL.



From here, we are done with our SQL.  We need to move into the .NET framework to setup the Grids.  You will retrieve the recordset in a normal fashion using a DataSet.  But what is a DataSet?  A DataSet is a collection of DataTables.  So if you are retrieving a single recordset, the preferred method is a DataTable.  However, in this case we are retrieving several (two to be specific) recordsets.  For this, we will use a DataSet.

When looking at the methods/properties of a DataSet, you will notice the Tables properties.  Due to the fact that this is a collection, if you populate more than a single record set you need to specify which Table you want as the source (in this case for the Grid).  You accomplish this by using the property like any other collection DataSet.Tables(0).  Obviously the first table in the collection (array) is in placement 0.  Now how do you know which recordset from SQL is in which table.  Well, it is ordinal.  So the first select statement gets populated into the first table.  Second recordset into the second table… etc…



NOTE: I am not going to go through the web.config settings to talk to SQL.  This is usually environmentally specific and implementations are so varied it would be a practice in futility.





Open up VS2010.  Create your project.  Now we need to add some specific folders that the framework knows about.  Right click your project solution and click Add ASP.NET folder.  Choose App_Code.  This is where we will define our Business and Data Tiers. 
Thus, we need to add two folders to the App_Code folder.  Right Click the App_Code folder | New Folder.  Call the folder BLL and repeat and name the second folder DAL.  BLL – Business Logic Layer. DAL – Data Access Layer.



If you named the folders correctly, we just completed the hardest part of this scenario 8^).

Now Right Click the DAL folder | Add New Item… In the list choose Class.  Name the Class MultipleRecordset.vb



I ABSOLUTELY HATE lazy coders who refuse to use Regions!  You need to be crucified for making your brother coders work so hard because you are too lazy to include two lines of script and obvious have no idea how a hierarchy works.  I automatically deduce the coder is absolutely worthless if there are no Regions in the code.  Thus, you’ll see Regions in my code.



Ok, first things first – Obviously we need to setup our globals.  Thus, Imports are first -



Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration.WebConfigurationManager


Nothing too exciting here.  We need the System.Data to gain access to the DataSet member.  System.Data.SqlClient for the SQLConnection. Finally, System.Web.Configuration.WebConfigurationManager to get to the web.config settings for our ConnectionString. Easy peasy…



Now we need to create a class level variable that will hold the connectionString.  I do this because of the hierarchy of classes and the way you organize them.  Typically, classes are formed into like groups in the DAL and thus, use the same connectionString, although they do not have to.  So lets make our class level global variable -




#Region "Globals"
Dim conn As SqlConnection = New _
SqlConnection(ConnectionStrings("Northwind").ToString())
#End Region


Obviously, If I had any other global variables, they would go into this region.



Now comes the thought, we need our method to return the dataset.  Just in the context of the last sentence, we have decided that our method will be of type dataset.  Ok, So how will it look -



#Region "Methods"
Public Function getMultipleRecordsets() As DataSet
If Not (conn.State = ConnectionState.Closed) Then
conn.Close()
End If
… ADO.NET code to fetch records inserted here …


        End Function
#End Region


Again, we need to create a region to hold our methods.  Then we create our method of type DataSet (it will be returning a DataSet).  Next I check to make sure the connection is not already open, I do this because it has bit me in the ass before so I just include it in all my database calls.  Due to the fact that a DataSet is a bi-directional communication between the framework and the database, when the DataSet is destroyed, the connection to the database is also severed – which effectively closes the connection.

Now all we need to do is properly fill the DataSet per ADO.Net like usual -




#Region "Methods"
Public Function getMultipleRecordsets() As DataSet
If Not (conn.State = ConnectionState.Closed) Then
conn.Close()
End If
Dim
cmd As SqlCommand = New _
SqlCommand("dbo.getMultipleResultSets", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd
conn.Open()
Dim ds As DataSet = New DataSet
da.Fill(ds)
conn.Close()
Return ds
End Function
#End Region


Fairly typical method using ADO.Net to fill a DataSet.  Here is the entire class listed -

(Please note the use of Namespacing here.  This allows us to have multiple classes named the same in different hierarchies).




Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration.WebConfigurationManager

Namespace MultRecordsExample.DAL
Public Class MultipleRecordset
#Region "Globals"
Dim conn As SqlConnection = New _
SqlConnection(ConnectionStrings("Northwind").ToString())
#End Region
#Region
"Methods"
Public Function getMultipleRecordsets() As DataSet
If Not (conn.State = ConnectionState.Closed) Then
conn.Close()
End If
Dim
cmd As SqlCommand = New _
SqlCommand("dbo.getMultipleResultSets", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd
conn.Open()
Dim ds As DataSet = New DataSet
da.Fill(ds)
conn.Close()
Return ds
End Function
#End Region
End Class
End Namespace


That concludes our entire Data Access Layer.  Now we need to look at the Business Logic Layer.  This one is even simpler as we are not performing any calculations or such.

Create another class in the BLL folder and call it the same (MultipleRecordset).



First we need to import our System.Data because we are going to have a function of type DataSet passing back a DataSet collection -



Imports System.Data


Now I am going to post the entire BLL and then describe what is going on -



Namespace MultRecordsExample.BLL
Public Class MultipleRecordset
#Region "Methods"
Public Function getMultipleRecordsets() As DataSet
Dim mr_DAL As MultRecordsExample.DAL.MultipleRecordset = New _
MultRecordsExample.DAL.MultipleRecordset
Return mr_DAL.getMultipleRecordsets()
End Function
#End Region
End Class
End Namespace


Of course, we need to create our namespace at the BLL hierarchy.  Then our class declaration.  From there we need our Region (just incase we have any helpers to manipulate data – thankfully we don’t).  From there we go into our function of type DataSet.

We declare a variable that represents the class we created in the DAL.  Then we return the value we get from the DAL call.  Simple!



Finally, we are in the home stretch and are at our presentation layer.  We need to put in two Grids like so on our Default.aspx page -



<asp:GridView ID="gvProducts" runat="server" DataKeyNames="ProductID" 
AutoGenerateColumns="false" >
<
Columns>
<
asp:BoundField HeaderText="Product ID" DataField="ProductID" />
<
asp:BoundField HeaderText="Product Name" DataField="ProductName" />
<
asp:BoundField HeaderText="Unit Price" DataField="UnitPrice" />
<
asp:BoundField HeaderText="# In Stock" DataField="UnitsInStock" />
</
Columns>
</
asp:GridView>




This represents our first grid.  For the second one -



<asp:GridView ID="gvOrders" runat="server" DataKeyNames="ProductName" 
AutoGenerateColumns="false" >
<
Columns>
<
asp:BoundField HeaderText="Company Name" DataField="CompanyName" />
<
asp:BoundField HeaderText="Product Name" DataField="ProductName" />
<
asp:BoundField HeaderText="Quantity" DataField="Quantity" />
<
asp:BoundField HeaderText="Unit Price" DataField="UnitPrice" />
<
asp:BoundField HeaderText="Total Price" DataField="Total Price" />
</
Columns>
</
asp:GridView>


These are our two grids on the default page.  So all together, our entire default page would look like -



<asp:Content ID="HeaderContent" runat="server" 
ContentPlaceHolderID="HeadContent">
</
asp:Content>
<
asp:Content ID="BodyContent" runat="server"
ContentPlaceHolderID="MainContent">
<
asp:GridView ID="gvProducts" runat="server" DataKeyNames="ProductID"
AutoGenerateColumns="false" >
<
Columns>
<
asp:BoundField HeaderText="Product ID" DataField="ProductID" />
<
asp:BoundField HeaderText="Product Name" DataField="ProductName" />
<
asp:BoundField HeaderText="Unit Price" DataField="UnitPrice" />
<
asp:BoundField HeaderText="# In Stock" DataField="UnitsInStock" />
</
Columns>
</
asp:GridView>
<
br />
<
asp:GridView ID="gvOrders" runat="server" DataKeyNames="ProductName"
AutoGenerateColumns="false" >
<
Columns>
<
asp:BoundField HeaderText="Company Name" DataField="CompanyName" />
<
asp:BoundField HeaderText="Product Name" DataField="ProductName" />
<
asp:BoundField HeaderText="Quantity" DataField="Quantity" />
<
asp:BoundField HeaderText="Unit Price" DataField="UnitPrice" />
<
asp:BoundField HeaderText="Total Price" DataField="Total Price" />
</
Columns>
</
asp:GridView>
</
asp:Content>




Of course this is only half the battle on the default page.  We also need to look at the code behind.  Obviously, we will use the Page_Load event as the trigger.  First we need to declare a variable that will represent (instantiate) our class.  Then we will define our datasource for the first grid.  DataBind it.  And do the exact same for the second grid -




Protected Sub Page_Load(sender As Object, e As System.EventArgs) _
Handles Me.Load
Dim mr_BLL As MultRecordsExample.BLL.MultipleRecordset = New _
MultRecordsExample.BLL.MultipleRecordset
If Not (Page.IsPostBack) Then
gvProducts.DataSource = mr_BLL.getMultipleRecordsets.Tables(0)
gvProducts.DataBind()

gvOrders.DataSource = mr_BLL.getMultipleRecordsets.Tables(1)
gvOrders.DataBind()
End If
End Sub




Here you will notice that in our datasources we are specifying a table of the DataSet.  This makes it easy for us to separate the data and populate the two totally independent grids with a single data call to the database.  This makes it more secure, use less resources, and actually improves performance.  What you end up with is something like so -

image





Using this method you can potentially cut down all data calls to a single connection for any given page.



Håþþ¥ .ñꆆïñg…

2 comments:

  1. Hey!
    I'm truly enjoying the design and layout of your site. What a commendable work you have done, with simplest of language. I can’t resist myself to leave a comment and trust me it’s hard to impress me.

    Vachel
    PHP Development Chicago
    .NET Development Chicago
    Chicago Development Team
    cmscentral.net

    ReplyDelete
  2. Thanks! I really do appreciate it!

    ReplyDelete