Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 983
  • Last Modified:

Dataset Relation

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.
  • 3
  • 2
1 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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<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
dotnet0824Author Commented:
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?  
>>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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

dotnet0824Author Commented:
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
again, it's really up to you and/or circumstances...like if you have 2 very different datasources...like 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 records...like if you have 10,000 customers each with 10,000 orders....you 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....
dotnet0824Author Commented:
thanks a lot.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now