Stored Procedure with Multiple Data Sets to Web Page
Introduction
There used to be instances where I had written into my code a whole host of different SQL Commands and then treated each one individually.
This isn't the most efficient way of accomplishing this, as in SQL you can return multiple datasets in one command, I should have looked into this a little earlier as it would have cut down the amount of code I have written in a few places in previous web pages.
So this is it at it's most simple, a stored procedure below to return 3 data sets, and then a page load event to utilise it.
Put this simple code below into one of your databases, you will need to create a simple stored proc.
SQL
ALTER PROC GetData
AS BEGIN
SELECT 1 Data UNION
SELECT 2
SELECT 3 Data2 UNION
SELECT 4
SELECT 'x' Data2 --WHERE 1<>1
END
Create gridviews
HTML
<asp:GridViewID="GridView1"runat="server">
asp:GridView>
<asp:GridViewID="GridView2"runat="server">
asp:GridView>
<asp:GridViewID="GridView3"runat="server">
asp:GridView>
Page Load
VB
Imports DataConnections
Imports System.Data.SqlClient
Imports System.Data
Partial Class Default13
Dim con As New SqlConnection(GC)
Private Sub Page_Load() Handles Me.Load
con.open()
Using com As New SqlCommand("EXEC GetData", con)
Dim da As New SqlDataAdapter(com)
Dim ds As New DataSet
da.Fill(ds)
GridView1.DataSource = ds.Tables(0)
GridView2.DataSource = ds.Tables(1)
GridView3.DataSource = ds.Tables(2)
GridView1.DataBind()
GridView2.DataBind()
GridView3.DataBind()
End Using
End Sub
End Class
Wrapping up
Website design by Claytabase
This is a section of code that has been modified from Ousia Content Management System code, one of the quickest and most optimised systems on the market, part of our website design services.
These are available with sites starting at around £500.
