Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / ASP.NET

Display Multiple Table Field Data in a Single DataGrid

1.22/5 (3 votes)
2 Nov 2007CPOL 1  
Display data from multiple tables 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:

SQL
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
VB
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.NET
<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.

VB
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)