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
- CREATE DATABASE IndiaDB
-
- use IndiaDB
-
- CREATE TABLE [dbo].[Employee](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [Name] [nvarchar](50) NULL,
- [Position] [nvarchar](50) NULL,
- [Office] [nvarchar](50) NULL,
- [Salary] [nvarchar](50) NULL,
- [Country] [nvarchar](50) NULL
- ) ON [PRIMARY]
USA DATABASE
- CREATE DATABASE USADB
-
- use USADB
-
- CREATE TABLE [dbo].[Employee](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [Name] [nvarchar](50) NULL,
- [Position] [nvarchar](50) NULL,
- [Office] [nvarchar](50) NULL,
- [Salary] [nvarchar](50) NULL,
- [Country] [nvarchar](50) NULL
- ) ON [PRIMARY]
Step 2
Create an empty web application project in Visual Studio. Double-click on webconfig and add the database connections to it.
- <connectionStrings>
- <add name="INDIADB" connectionString="data source=FARHAN\SQLEXPRESS; database=IndiaDB; integrated security=true;"/>
- <add name="USADB" connectionString="data source=FARHAN\SQLEXPRESS; database=USADB; integrated security=true;"/>
- </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.
- <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
- <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
- <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
- <link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap4.min.css" />
- <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js" type="text/javascript"></script>
- <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.
- <script type="text/javascript">
- $(document).ready(function () {
- $("#EmployeeGridView").prepend($("<thead></thead>").append($(this).find("tr:first"))).dataTable();
- });
- </script>
Step 4
Drag and drop the GridView control to bind and display the data.
- <body>
- <form id="form1" runat="server">
- <div class="container py-4">
- <h4 class="text-uppercase text-center">How to retrieve data from different database in asp.net</h4>
- <asp:GridView ID="EmployeeGridView" HeaderStyle-CssClass="bg-primary text-white" CssClass="table table-bordered" runat="server"></asp:GridView>
- </div>
- </form>
- </body>
Step 5
Right-click View code and write the following C# code to retrieve the data from the database.
- using System;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
-
- namespace RetrieveDataFromDifferentDatabase_Demo
- {
- public partial class RetrieveData : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!IsPostBack)
- {
- BindGrid();
- }
- }
-
- private void BindGrid()
- {
- string INDIADB = ConfigurationManager.ConnectionStrings["INDIADB"].ConnectionString;
- string USADB = ConfigurationManager.ConnectionStrings["USADB"].ConnectionString;
- SqlConnection con = new SqlConnection(INDIADB);
- SqlDataAdapter da = new SqlDataAdapter("Select*from Employee",con);
-
- DataSet ds1 = new DataSet();
- da.Fill(ds1);
-
- con =new SqlConnection(USADB);
- da.SelectCommand.Connection = con;
-
- DataSet ds2 = new DataSet();
- da.Fill(ds2);
-
- ds1.Merge(ds2);
-
- EmployeeGridView.DataSource = ds1;
- EmployeeGridView.DataBind();
-
- }
- }
- }
Step 6
Run the project by pressing "Ctrl+F5".