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>
<addname="USADB"connectionString="server=.;
database=USADB; integrated security=true"/>
<addname="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.
usingSystem;
usingSystem.Configuration;
usingSystem.Data;
usingSystem.Data.SqlClient;
namespaceDemo
{
publicpartialclassWebForm1 : System.Web.UI.Page
{
protectedvoidPage_Load(objectsender,
EventArgse)
{
stringUSADBCS=
ConfigurationManager.ConnectionStrings["USADB"].ConnectionString;
stringUKDBCS=
ConfigurationManager.ConnectionStrings["UKDB"].ConnectionString;
SqlConnectioncon=newSqlConnection(USADBCS);
SqlDataAdapterda=newSqlDataAdapter("select * from Employees", con);
DataSetds1=newDataSet();
da.Fill(ds1);
con=newSqlConnection(UKDBCS);
da.SelectCommand.Connection=con;
DataSetds2=newDataSet();
da.Fill(ds2);
ds1.Merge(ds2);
GridView1.DataSource=ds1;
GridView1.DataBind();
}
}
}
dot net written test questions answers
8 comments:
Hi Venkat, you are doing a great job by helping the developers/students to learns ,NET very easily. Great working. keep it up.
Reply DeletePawan Kumar
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 ) ??
Reply DeleteThank 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 (-_^)..
Sir Can i Store Multiple Table on one Dataset?
Reply DeleteData 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.
Deleteif you dont want to store more than one table then datatable is good option for single table record.
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:
Reply DeleteValues DB1 - Values DB2
-------------------------------------
Table1 Table2
ID | VALUE ID | VALUE
1 | 24 1 | 100
And with the JOIN
----------------------------------------
ID | VALUEDB1 | VALUEDB2
1 | 24 | 100
when table name id different what i use on this coda ( da.SelectCommand.Connection = con;)
Reply DeleteSqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["UKDBCS"].ConnectionString);
DeleteSqlDataAdapter 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();
Keep up the good work.Thanks for the awesome tutorials
Reply DeleteIt would be great if you can help share these free resources
[フレーム]