Display Multiple Table Field Data in a Single DataGrid

Introduction

This article helps you to display multiple table fields in a single grid.

Database Design

Here is the SQl to create the sample database:

CREATE TABLE [dbo].[US_Status] (
    [USST_StatusId] [int] IDENTITY (1, 1) NOT NULL ,
    [USST_Name] [varchar] (50) NOT NULL ,
    [USST_Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[US_Type] (
    [USTY_TypeID] [int] IDENTITY (1, 1) NOT NULL ,
    [USTY_Name] [varchar] (50) NOT NULL ,
    [USTY_Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[US_User] (
    [USUS_UserID] [int] IDENTITY (1, 1) NOT NULL ,
    [USUS_Email] [varchar] (100) NOT NULL ,
    [USUS_Password] [varchar] (100) NOT NULL ,
    [USUS_FirstName] [varchar] (100) NOT NULL ,
    [USUS_LastName] [varchar] (100) NOT NULL ,
    [USUS_CheckVal] [varchar] (100) NOT NULL ,
    [USST_StatusID] [int] NOT NULL ,
    [USTY_TypeID] [int] NOT NULL ,
    [USUS_Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO

Using the Code

I use the BindData method below to retrieve data from the database. This method consists of:

1. A DataSet - a collection of DataTable objects
2. DataRelation - Represents the parent\child relationship between two DataTable objects

In this method, we have created two data relations:

• User_StatusRelation: Parent table is USST_Status which has the primary key
• User_TypeRelation : Parent table is USST_Type which has the primary key

Sub BindData()
    Dim ConString As String = "Data Source=AARTHANA-BF2A86\SQLEXPRESS;" & _ 
                              "Initial Catalog=Ec2;Integrated Security=True"
    Dim conn As New SqlConnection(ConString)
    Dim cmd As SqlCommand
    conn.Open()
    cmd = New SqlCommand("select * from US_User", conn)
    adapater = New SqlDataAdapter(cmd)
    adapater.Fill(DB, "US_User")
    cmd.CommandText = "select * from US_Status"
    adapater.Fill(DB, "US_Status")
    cmd.CommandText = "select * from US_Type"
    adapater.Fill(DB, "US_Type")
    statusview = DB.Tables("US_Status")
    tempdataview = DB.Tables("US_Type")
    User_Statusrelation = New Data.DataRelation("", _
       DB.Tables("US_Status").Columns("USST_StatusID"), _
       DB.Tables("US_User").Columns("USST_StatusID"), True)
    DB.Relations.Add(User_Statusrelation)
    User_Typerelation = New Data.DataRelation("", _
       DB.Tables("US_Type").Columns("USTY_TypeID"), _
       DB.Tables("US_User").Columns("USTY_TypeID"), True)
    DB.Relations.Add(User_Typerelation)
    DbGrid.DataSource = DB
    DbGrid.DataBind()
End Sub

Interesting Facts

The interesting part of our HTML code is:

<asp:TemplateColumn HeaderText="Status"><ItemTemplate >
 <%#BindJob(Container.DataItem)%>
 </ItemTemplate>
<EditItemTemplate >
<asp:DropDownList ID="statuslist" runat ="server" 
   DataTextField="USST_Name" DataValueField ="USST_StatusId" 
   DataSource =<%#statusview%> >
</asp:DropDownList>
</EditItemTemplate>
<FooterTemplate >
<asp:DropDownList ID="statuslistAdd"
    runat ="server" DataTextField="USST_Name" 
    DataValueField ="USST_StatusId"
    DataSource =<%#statusview%> />
</FooterTemplate>
</asp:TemplateColumn>

Wwe are binding the DataGrid value using #BindJob(Container.DataItem), which gives the value of USST_Name from the table US_Status.

Public Function BindJob(ByVal o As Object) As String
    Dim rowin As Data.DataRow
    Dim drv As Data.DataRowView = o
    rowin = drv.Row.GetParentRow(User_Statusrelation)
    Return rowin("USST_Name")
End Function

This code also includes features like:

• Inserting a row in a DataGrid
• Using a DropDownList in a DataGrid