Returning Multiple Recordsets Using a Separate DataLayer - Demo


<%@ Page Language="VB" %>
<%@ Import Namespace="System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
    Private Sub Page_Load()
        ' Get database data
        Dim categories As New List(Of DataLayer1.MovieCategory)()
        Dim movies As New List(Of DataLayer1.Movie)()
        DataLayer1.GetMovieData(categories, movies)
 
        ' Bind the data
        grdCategories.DataSource = categories
        grdCategories.DataBind()
        grdMovies.DataSource = movies
        grdMovies.DataBind()
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show DataLayer1</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <h1>Movie Categories</h1>
    <asp:GridView
        id="grdCategories"
        Runat="server" />
        
    <h1>Movies</h1>    
    <asp:GridView
        id="grdMovies"
        Runat="server" />    
    
    </div>
    </form>
</body>
</html>


** DataLayer1.vb ** 

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Collections.Generic

Public Class DataLayer1
    Private Shared ReadOnly _connectionString As String

    Public Class MovieCategory
        Private _id As Integer
        Private _name As String

        Public Property Id() As Integer
            Get
                Return _id
            End Get
            Set(ByVal Value As Integer)
                _id = value
            End Set
        End Property

        Public Property Name() As String
            Get
                Return _name
            End Get
            Set(ByVal Value As String)
                _name = value
            End Set
        End Property
    End Class

    Public Class Movie
        Private _title As String
        Private _categoryId As Integer

        Public Property Title() As String
            Get
                Return _title
            End Get
            Set(ByVal Value As String)
                _title = value
            End Set
        End Property

        Public Property CategoryId() As Integer
            Get
                Return _categoryId
            End Get
            Set(ByVal Value As Integer)
                _categoryId = value
            End Set
        End Property
    End Class

    Public Shared Sub GetMovieData(ByVal movieCategories As List(Of DataLayer1.MovieCategory), ByVal movies As List(Of DataLayer1.Movie))
        Dim commandText As String = "SELECT Id,Name FROM MovieCategories;SELECT Title,CategoryId FROM Movies"
        Dim con As New SqlConnection(_connectionString)
        Dim cmd As New SqlCommand(commandText, con)
        Using con
            ' Execute command
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()

            ' Create movie categories
            While reader.Read()
                Dim NewCategory As New DataLayer1.MovieCategory()
                NewCategory.Id = CType(reader("Id"), Integer)
                NewCategory.Name = CType(reader("Name"), String)
                movieCategories.Add(NewCategory)
            End While

            ' Move to next result set
            reader.NextResult()

            ' Create movies
            While reader.Read()
                Dim NewMovie As DataLayer1.Movie = New DataLayer1.Movie()
                NewMovie.Title = CType(reader("Title"), String)
                NewMovie.CategoryId = CType(reader("CategoryID"), Integer)
                movies.Add(NewMovie)
            End While
        End Using
    End Sub

    Shared Sub New()
        _connectionString = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
    End Sub
End Class