How to connect to multiple databases in asp.net using SqlDataReader?

Question

Assume that I have two databases such as “Product” and “people”. The product database has two tables, let’s say table1 and table 2, while people has two tables, let’s say again table1 and table2. I want to get some information from Product.table1 and some from people.table2.

I tried with the following code, but unfortunately it does not work:

    SqlConnection con1 = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Product.mdf;Integrated Security=True");
    SqlConnection con2 = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\People.mdf;Integrated Security=True");

    SqlCommand cmd1 = new SqlCommand("select prod_name, prod_price from product_tbl", con1);
    SqlCommand cmd2 = new SqlCommand("select std_name from student_tbl", con2);

    con1.Open();
    con2.Open();

    SqlDataReader dr1 = cmd1.ExecuteReader();
    SqlDataReader dr2 = cmd2.ExecuteReader();

   // GridView1.DataSource =  How to do it??
    GridView1.DataBind();

Mandar Patil · Answer 1 · 2014-03-06T04:35:07.173

You can either do it as follow :

Retrieve the result from Product DB in dataset1
Retrieve the result from People DB in dataset2
Use DataSet.Merge Method to merge the two data sets in a single dataset say dsProductPeople
Bind dsProductPeople to the grid

OR you can use following example :

// Assumes that customerConnection is a valid SqlConnection object.
// Assumes that orderConnection is a valid OleDbConnection object.
SqlDataAdapter custAdapter = new SqlDataAdapter(
  "SELECT * FROM dbo.Customers", customerConnection);
OleDbDataAdapter ordAdapter = new OleDbDataAdapter(
  "SELECT * FROM Orders", orderConnection);

DataSet customerOrders = new DataSet();

custAdapter.Fill(customerOrders, "Customers");
ordAdapter.Fill(customerOrders, "Orders");

DataRelation relation = customerOrders.Relations.Add("CustOrders",
  customerOrders.Tables["Customers"].Columns["CustomerID"],
  customerOrders.Tables["Orders"].Columns["CustomerID"]);

foreach (DataRow pRow in customerOrders.Tables["Customers"].Rows)
{
  Console.WriteLine(pRow["CustomerID"]);
   foreach (DataRow cRow in pRow.GetChildRows(relation))
    Console.WriteLine("\t" + cRow["OrderID"]);
}

okay, what about if I want to display information that only matched from both datasets based on where-condition, example customers.std_id=orders.std_id — Al-Masruri, Mar 08 '14 at 03:44
The final result will be as follows: prod_name prod_price std_id std_name pen 1 1 Ahmad Ruler 2 2 Rashid English Book 2 1 Ahmad Java Book 5 1 Ahmad Pencil 1 3 Majid — Al-Masruri, Mar 08 '14 at 04:29

How to connect to multiple databases in asp.net using SqlDataReader?

1 Answers1