Post
Ask Question

How To Retrieve Data From Two Different Database Tables In ASP.NET

Introduction

In this blog, I am going to discuss how to retrieve data from two different databases and display them into a GridView control. I will also use jQuery data table plugin for searching, sorting, and paging. Then, I will demonstrate the process step by step.

Step 1

Create two different databases and tables in both the databases.

INDIA DATABASE

  1. CREATE DATABASE IndiaDB  
  2.   
  3. use IndiaDB  
  4.   
  5. CREATE TABLE [dbo].[Employee](  
  6.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  7.     [Name] [nvarchar](50) NULL,  
  8.     [Position] [nvarchar](50) NULL,  
  9.     [Office] [nvarchar](50) NULL,  
  10.     [Salary] [nvarchar](50) NULL,  
  11.     [Country] [nvarchar](50) NULL  
  12. ) ON [PRIMARY]  

USA DATABASE

  1. CREATE DATABASE USADB  
  2.   
  3. use USADB  
  4.   
  5. CREATE TABLE [dbo].[Employee](  
  6.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  7.     [Name] [nvarchar](50) NULL,  
  8.     [Position] [nvarchar](50) NULL,  
  9.     [Office] [nvarchar](50) NULL,  
  10.     [Salary] [nvarchar](50) NULL,  
  11.     [Country] [nvarchar](50) NULL  
  12. ) ON [PRIMARY]  

Step 2

Create an empty web application project in Visual Studio. Double-click on webconfig and add the database connections to it.

  1. <connectionStrings>  
  2.     <add name="INDIADB" connectionString="data source=FARHAN\SQLEXPRESS; database=IndiaDB; integrated security=true;"/>  
  3.     <add name="USADB" connectionString="data source=FARHAN\SQLEXPRESS; database=USADB; integrated security=true;"/>  
  4.   </connectionStrings>  

Step 3

Create a web form, right-click on project, and add a new item. Choose web form and give it a name. Add some script and style of bootstrap 4 and jQuery plugins for datatable functionality in the head section of web form.

  1. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">  
  2. <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
  3. <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>  
  4. <link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap4.min.css" />  
  5. <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js" type="text/javascript"></script>  
  6. <script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js" type="text/javascript"></script>  

Write the script for jQuery data table appended to GridView control.

  1. <script type="text/javascript">  
  2.         $(document).ready(function () {  
  3.             $("#EmployeeGridView").prepend($("<thead></thead>").append($(this).find("tr:first"))).dataTable();  
  4.         });  
  5.  </script>  

Step 4

Drag and drop the GridView control to bind and display the data.

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.         <div class="container py-4">  
  4.             <h4 class="text-uppercase text-center">How to retrieve data from different database in asp.net</h4>  
  5.             <asp:GridView ID="EmployeeGridView" HeaderStyle-CssClass="bg-primary text-white" CssClass="table table-bordered" runat="server"></asp:GridView>  
  6.         </div>  
  7.     </form>  
  8. </body>  

Step 5

Right-click View code and write the following C# code to retrieve the data from the database.

  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Configuration;  
  5.   
  6. namespace RetrieveDataFromDifferentDatabase_Demo  
  7. {  
  8.     public partial class RetrieveData : System.Web.UI.Page  
  9.     {  
  10.         protected void Page_Load(object sender, EventArgs e)  
  11.         {  
  12.             if (!IsPostBack)  
  13.             {  
  14.                 BindGrid();  
  15.             }  
  16.         }  
  17.   
  18.         private void BindGrid()  
  19.         {  
  20.             string INDIADB = ConfigurationManager.ConnectionStrings["INDIADB"].ConnectionString;  
  21.             string USADB = ConfigurationManager.ConnectionStrings["USADB"].ConnectionString;  
  22.             SqlConnection con = new SqlConnection(INDIADB);  
  23.             SqlDataAdapter da = new SqlDataAdapter("Select*from Employee",con);  
  24.   
  25.             DataSet ds1 = new DataSet();  
  26.             da.Fill(ds1);  
  27.   
  28.             con =new SqlConnection(USADB);  
  29.             da.SelectCommand.Connection = con;  
  30.   
  31.             DataSet ds2 = new DataSet();  
  32.             da.Fill(ds2);  
  33.   
  34.             ds1.Merge(ds2);  
  35.   
  36.             EmployeeGridView.DataSource = ds1;  
  37.             EmployeeGridView.DataBind();  
  38.   
  39.         }  
  40.     }  
  41. }  

Step 6

Run the project by pressing "Ctrl+F5".

ASP.NET