Part 1 - How to retrieve data from different databases in asp.net

Several of our YouTube channel subscribers faced this question in a written test. 
We have 2 databases
1. USADB - Contains Employees table that stores only US Employees
2. UKDB   - Contains Employees table that stores only UK Employees

Implement an asp.net web page that retrieves data from the Employees table both from USADB and UKDB databases.





SQL Script to 
1. Create USADB and UKDB Databases
2. Create Employees table in both the databases
3. Populate Employees table in both the databases

Create Database USADB

GO

Create Database UKDB

GO

USE USADB

GO

Create table Employees

(

     ID int primary key,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int,

     Country nvarchar(50)

)

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000, 'USA')

Insert into Employees values (2, 'Steve', 'Pound', 'Male', 45000, 'USA')

Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000, 'USA')

Insert into Employees values (4, 'Philip', 'Hastings', 'Male', 45000, 'USA')

USE UKDB

GO

Create table Employees

(

     ID int primary key,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int,

     Country nvarchar(50)

)

Insert into Employees values (5, 'Mary', 'Lambeth', 'Female', 30000, 'UK')

Insert into Employees values (6, 'Valarie', 'Vikings', 'Female', 35000, 'UK')

Insert into Employees values (7, 'John', 'Stanmore', 'Male', 80000, 'UK')

Create a new empty asp.net web application. 

Copy and paste the following 2 connection strings in web.config file.

<connectionStrings>

  <add name="USADB" connectionString="server=.;

    database=USADB; integrated security=true"/>

  <add name="UKDB" connectionString="server=.;

    database=UKDB; integrated security=true"/>

</connectionStrings>

Add a webform to the project. Drag and drop a GridView control on the webform. Copy and paste the following code in the code-behind file.

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

namespace Demo

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            string USADBCS =

                ConfigurationManager.ConnectionStrings["USADB"].ConnectionString;

            string UKDBCS =

                ConfigurationManager.ConnectionStrings["UKDB"].ConnectionString;

            SqlConnection con = new SqlConnection(USADBCS);

            SqlDataAdapter da = new SqlDataAdapter("select * from Employees", con);

            DataSet ds1 = new DataSet();

            da.Fill(ds1);

            con = new SqlConnection(UKDBCS);

            da.SelectCommand.Connection = con;

            DataSet ds2 = new DataSet();

            da.Fill(ds2);

            ds1.Merge(ds2);

            GridView1.DataSource = ds1;

            GridView1.DataBind();

        }

    }

}