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
  3. use IndiaDB
  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
  3. use USADB
  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;
  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. }
  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);
  25. DataSet ds1 = new DataSet();
  26. da.Fill(ds1);
  28. con =new SqlConnection(USADB);
  29. da.SelectCommand.Connection = con;
  31. DataSet ds2 = new DataSet();
  32. da.Fill(ds2);
  34. ds1.Merge(ds2);
  36. EmployeeGridView.DataSource = ds1;
  37. EmployeeGridView.DataBind();
  39. }
  40. }
  41. }

Step 6

Run the project by pressing "Ctrl+F5".

ASP.NET