Retrieving Data from Multiple Databases in ASP.NET

Introduction:

In today's digital era, managing data efficiently is crucial for the success of any application. In this article, we will delve into the process of retrieving data from multiple databases in ASP.NET and binding it to a GridView control using C# coding. To illustrate the process, we'll create two databases, namely DelhiEmployeesDB and PunjabEmployeesDB, and demonstrate how to seamlessly merge and display data from both databases in a unified GridView.

Creating Databases and Employ Table:

To get started, we need to create the databases and a common Employ table shared by both databases. Execute the following SQL queries to create the databases:

-- Creating DelhiEmployeesDB
CREATE DATABASE DelhiEmployeesDB
GO

-- Creating PunjabEmployeesDB
CREATE DATABASE PunjabEmployeesDB
GO

Now, let's create the Employ table. This table will contain information such as employee ID, name, gender, salary, and city. Since it's common for both databases, you'll need to execute the table creation script twice, once for each database:

-- Creating Employ Table
CREATE TABLE Employ
(
    empID int identity,
    empName nvarchar(50),
    empgender nvarchar(50),
    empsalary int,
    empCity nvarchar(50)
)

Inserting Sample Data:

Next, insert some sample data into the Employ table for both DelhiEmployeesDB and PunjabEmployeesDB using the following SQL queries:

-- Inserting Data into Employ Table for DelhiEmployeesDB
Insert into Employ values ('Aditya Kashyap', 'Male', 16000, 'Delhi')
Insert into Employ values ('Tarun Arora', 'Male', 12500, 'Delhi')
Insert into Employ values ('Arvind Kapoor', 'Male', 11000, 'Delhi')
Insert into Employ values ('Bala Subramanyam', 'Male', 15000, 'Delhi')
Insert into Employ values ('Kejriwal', 'Male', 15000, 'Delhi')
Insert into Employ values ('Shruthi', 'Female', 14500, 'Delhi')

-- Inserting Data into Employ Table for PunjabEmployeesDB
Insert into Employ values ('Joginder Singh', 'Male', 16000, 'Chandigarh')
Insert into Employ values ('Sarbjit', 'Male', 4000, 'Chandigarh')
Insert into Employ values ('Punit Kaur', 'Male', 7000, 'Chandigarh')
Insert into Employ values ('Ganenesh', 'Male', 15000, 'Chandigarh')
Insert into Employ values ('Meera', 'Female', 25000, 'Chandigarh')
Insert into Employ values ('Geet Dhillon', 'Female', 45000, 'Chandigarh')
Insert into Employ values ('Shruthi', 'Female', 14500, 'Chandigarh')
Insert into Employ values ('Dolly', 'Female', 14500, 'Chandigarh')

HTML Markup: Creating the Design Page

Now, open Visual Studio and create a project named "Sample Project." Open the WebForm1.aspx page and drag-drop a GridView control onto it. Use the provided HTML markup to structure the GridView and define its columns.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4">
            <Columns>
                <asp:TemplateField HeaderText="Employee Name">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("empName") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Gender">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("empgender") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Salary">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("empsalary") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Employee City">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("empCity") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
          </Columns>
            <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
            <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
            <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
            <RowStyle BackColor="White" ForeColor="#003399" />
            <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
            <SortedAscendingCellStyle BackColor="#EDF6F6" />
            <SortedAscendingHeaderStyle BackColor="#0D4AC4" />
            <SortedDescendingCellStyle BackColor="#D6DFDF" />
            <SortedDescendingHeaderStyle BackColor="#002876" />            
</asp:GridView>

Configuration: Database Settings

Configure the connection strings in the Web.Config file to establish connections with the databases. Add the following lines within the <connectionStrings> section:

 <connectionStrings>
    <add name="dbDel" connectionString="server=.; database=DelhiEmployeesDB; integrated security=true"/>
    <add name="dbPun" connectionString="server=.; database=PunjabEmployeesDB; integrated security=true"/>
 </connectionStrings>

C# Coding: Bind GridView Control

Incorporate the necessary C# code to bind the GridView control with data from both databases. Import the required namespaces and create a private method, BindGrid(), to be called in the Page_Load event. This method retrieves data from both databases and merges it into a single DataSet for seamless display in the GridView.

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

private void BindGrid()
{
    string constrDelhi = ConfigurationManager.ConnectionStrings["dbDel"].ConnectionString;
    string constrPunjab = ConfigurationManager.ConnectionStrings["dbPun"].ConnectionString;
    string strQuery = "select * from Employ";
    SqlConnection con = new SqlConnection(constrDelhi);
    SqlDataAdapter da = new SqlDataAdapter("strQuery", con);
    DataSet dsDelhi = new DataSet();
    da.Fill(dsDelhi);
    con = new SqlConnection(constrPunjab);
    da.SelectCommand.Connection = con;
    DataSet dsPunjab = new DataSet();
    da.Fill(dsPunjab);

    dsDelhi.Merge(dsPunjab);

    GridView1.DataSource = dsDelhi;
    GridView1.DataBind();
}

Note:

Here i'm using same con to establish new connection for PunjabEmployeesDB to get Punjab Employ Data.

 con = new SqlConnection(constrPunjab);


    da.SelectCommand.Connection = con;
    DataSet dsPunjab = new DataSet();
    da.Fill(dsPunjab);

da.SelectCommand means which indicate to SqlDataAdapter use Same "Select * from Employ" query but use constrPunjab connection to get data from this database because this Database also have same Employ Table. Create new DataSet to fill Punjab Employ Data in this DataSet (dsPunjab).

Now we have 2 DataSet which named as dsDelhi and dsPunjab, we are going to make this 2 DataSet to one DataSet by using Merger Method which is precompiled method in DataSet Class.

    dsDelhi.Merge(dsPunjab);

Conclusion:

In this comprehensive guide, we've walked through the process of retrieving data from multiple databases in ASP.NET and binding it to a GridView control using C# coding. By creating databases, defining a common table, and seamlessly merging data, you can enhance the efficiency of your ASP.NET applications when dealing with distributed data sources.