Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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.



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

dot net written test questions answers

8 comments:

  1. Hi Venkat, you are doing a great job by helping the developers/students to learns ,NET very easily. Great working. keep it up.

    Pawan Kumar

    ReplyDelete
  2. Hii Mr Venkat ; Really interesting Topics about Data Integration .. I was wondering if this code and approach works if we want retrieve data from heterogeneous models databases (ex: DS1 Oracle or MySQL Database, D2:MS SQL Server ) ??
    Thank U a lot for All Ur Videos Tutorial in all categories and specially for this one related to Data Integration and Meta data ..Please Continue (-_^).. 

    ReplyDelete
  3. Sir Can i Store Multiple Table on one Dataset?

    ReplyDelete
    Replies
    1. Data set is client side in memory representation. you can store more than one table inside the dataset. it is work like as database relationship (primary key etc). so same relationship you can store inside dataset.
      if you dont want to store more than one table then datatable is good option for single table record.

      Delete
  4. Hi, thanks, it's perfect, now a question, how would I do if I want to compare two columns of two identical tables but in different databases? for example:

    Values DB1 - Values DB2

    -------------------------------------
    Table1 Table2
    ID | VALUE ID | VALUE
    1 | 24 1 | 100


    And with the JOIN

    ----------------------------------------
    ID | VALUEDB1 | VALUEDB2
    1 | 24 | 100

    ReplyDelete
  5. when table name id different what i use on this coda ( da.SelectCommand.Connection = con;)

    ReplyDelete
    Replies
    1. SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["UKDBCS"].ConnectionString);
      SqlDataAdapter da = new SqlDataAdapter("select * from Dept", con);

      DataSet ds1 = new DataSet();
      da.Fill(ds1);


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

      DataSet ds2 = new DataSet();
      da.Fill(ds2);

      ds1.Merge(ds2);

      GridView1.DataSource = ds1;
      GridView1.DataBind();

      Delete
  6. Keep up the good work.Thanks for the awesome tutorials

    ReplyDelete

It would be great if you can help share these free resources