Dataset Relation

Posted on 2007-10-10
Last Modified: 2013-12-16
From my  Business Access Layer I want to expose a  Function which returns a dataset populated from Customer & Orders Table using a Stored procedure to the Client application...

The client application firstly would have a GridView populated with Customers and when a  row is clicked it has to show related ORDERS in a Detail View. So In the Business access layer should I join both of the tables and return a dataset . If so wouldnt the Dataset bound do the GridView have both customer and orders records. Thats not we want right.......... I want to do everything in BAL and just bind customer Records to the GRidview and on click  show all the child record of orders in the Details view.

So @ the BAL can code be shown how it can be done.
Question by:dotnet0824
    LVL 12

    Accepted Solution


    Let the BAL return the dataset itself. Remaining go thru the sample

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="GridViewList.aspx.vb" Inherits="Database_GridViewList" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

    <html xmlns="" >
    <head runat="server">
        <title>Untitled Page</title>
        <form id="form1" runat="server">
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AutoGenerateSelectButton="True"
                    <asp:BoundField DataField="Id" />
                    <asp:BoundField DataField="Name" />
            <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" Height="50px"
                    <asp:BoundField DataField="Id" />
                    <asp:BoundField DataField="Qul" />


    Imports System.Data
    Partial Class Database_GridViewList
        Inherits System.Web.UI.Page
        Dim ds As DataSet
        Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
            Dim dv As DataView = New DataView(ds.Tables("Child"))
            dv.RowFilter = "Id=" + Me.GridView1.SelectedValue.ToString()
            Me.DetailsView1.DataSource = dv
        End Sub

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            ds = Data()
            If Not Me.IsPostBack Then
                Me.GridView1.DataSource = ds.Tables("Parent")
            End If
        End Sub

        Private Function Data() As DataSet

            Dim dt As New DataTable
            dt.Columns.Add("Id", GetType(Integer))
            dt.Columns.Add("Name", GetType(String))

            dt.Rows.Add(New Object() {1, "aaaa"})
            dt.Rows.Add(New Object() {2, "bbbb"})
            dt.Rows.Add(New Object() {3, "cccc"})
            dt.TableName = "Parent"

            Dim dtc As New DataTable
            dtc.Columns.Add("Id", GetType(Integer))
            dtc.Columns.Add("Qul", GetType(String))

            dtc.Rows.Add(New Object() {1, "aaaa"})
            dtc.Rows.Add(New Object() {2, "bbbb"})
            dtc.Rows.Add(New Object() {2, "bbbb"})
            dtc.TableName = "Child"

            Dim ds As New DataSet()


            Return ds

        End Function

    End Class

    Author Comment

    so in the BAL  just a join statement is enough.............Or in the code class in BAL should i create Datarelation between both tables also?  
    LVL 37

    Expert Comment

    >>Or in the code class in BAL should i create Datarelation between both tables also?  

    That would be up to you...personally...I would put in the biz layer

    Author Comment

    finally ... does that mean in my stored proc if i have a join with both the tables... and in Code do we still need dataRelation in BAL level.... Can both exist or should be opt for only  one of those
    LVL 37

    Expert Comment

    again, it's really up to you and/or if you have 2 very different one a sql server table and the other a csv file or something....then the easiest way to relate the data would be in the BAL....
    or you might work at a company that has a rule that all data relations have to be handled by a dba and exist in the database....
    or it might depend on the amount of if you have 10,000 customers each with 10,000 wouldn't want to pull one big dataset...and you really wouldn't pull 2 datasets at once and relate them either...both would mean a massive amt of data getting returned...
    usually with master/detail scenarios...the common practice would be to have 2 separate functions in the BAL as well as 2 separate stored procs....
    like in BAL...a "Customers" class with a  "GetAllCustomers" function that returns the customer list for the grid using one stored proc...and then a "GetCustomerOrders(ByVal CustomerID) As Orders" function that would populate your Orders object(s), but still use a stored proc that takes in the CustomerID as the param....

    Author Comment

    thanks a lot.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now