Retrieve Data from 2 different Database in Asp.net


Retrieve Data from more than one Database in Asp.net

In this article i'm going to explain how to retrieve data from different database in asp.net and bind data into Gridview control by using c# coding. For that first we need to create 2 Database and name it as DelhiEmployeesDB, PunjabEmployeesDB

Create Database

creating Database with the name of DelhiEmployeesDB, also creating Table with the name of Employ and after create table insert data into Employ Table you can find sql query code in below just copy and paste in SQL Server.

Creating DelhiEmplyeesDB :

 create database DelhiEmployeesDB
 go

Creating DelhiEmplyeesDB :

 create database PunjabEmployeesDB
 go

Creating Employ Table :

This Employ Table is common for both database so i'm repeating this code but you need to this code 2 times to create Employ table in both database i.e { DelhiEmployeesDB and PunjabEmployeesDB }

 Create table Employ
 (
     empID int identity,
     empName nvarchar(50),
     empgender nvarchar(50),
     empsalary int,
     empCity nvarchar(50)
 )

Insert Data into Employ Table for DelhiEmployeesDB

This below query is used to insert sample data into Employ Table which belongs to DelhiEmployeesDB Database.

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')

Insert Data into Employ Table for PunjabEmployeesDB

This below query is used to insert sample data into Employ Table which belongs to PunjabEmployeesDB Database.

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 Design Page

Now Open Visual Studio creating a project with the name of Sample Project and Open WebForm1.aspx page, Drag and Drop a GridView control in WebForm1.aspx page.

<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 Setting

Configure ConnectionString in Web.Config file

 <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

First we need import 3 namespace, the namespace are Configuration, Data, SqlClient

C# Coding : Namespace

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

C# Coding : Bind GridView

To Bind Gridview i'm creating a private method which i named as BindGrid() Method and i'm going to call this method in Page Load Event.

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:

 con = new SqlConnection(constrPunjab);

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

    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);
0 Komentar untuk "Retrieve Data from 2 different Database in Asp.net"

Back To Top