HI WELCOME TO KANSIRIS

How to retrieve data from different databases in asp.net

Leave a Comment

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.



How to retrieve data from different databases in asp.net

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 senderEventArgs 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();
        }
    }
}

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.