Access two databases using Data Reader

Question

I am trying to develop an application using ADO.NET. I have two tables in two different databases. Each of them has a part of the complete data. Now I need to write a query so that I can get a complete record from two tables. For example, table 1 has Index_no, emp_ID, and pin no. Table 2 has index_no, emp_name, salary, and department. Index_no is the same for the same record in each table. Below is the code to retrieve all records where salary <20,000.

sqlCmd2 = new SqlCommand("SELECT * FROM table1 WHERE Index_No =@Index _No", TestCon); int i = 0; while (reader.Read()) { ListBox1.Items.Add(reader.GetInt32(0) + " - " + reader.GetString(1) + " - " + reader.GetString(2)); // Name.Text += reader["Name"] + "<br />"; ; // Depart.Text += reader["Depart"] + "<br />"; ; array1[i] = reader.GetInt32(0); i++; } sqlCmd2.Parameters.Add("@Index_No", System.Data.SqlDbType.Decimal); i = 0; do { sqlCmd2.Parameters["@Index_No"].Value = array1[i]; reader1 = sqlCmd2.ExecuteReader(); reader1.Read(); ListBox2.Items.Add(reader1.GetString(1)); i++; } while (i < array1.Length); 

The problem is that I get only one record information from table2, and for table 1 I get all the necessary record information. It seems that the do-while loop ends only after one iteration.

Answer 1

It depends on what two different databases mean. If two databases are running on the same SQL server, it is easy to access the table of another database

  SELECT * FROM OtherDb.dbo.TableOnOtherDb 

If not, I would suggest you create a database link between the two databases (see the SO question. How do I create and query related database servers in SQL Server? ). Then you can access another table as above.

In both cases, you can use the JOIN query to join two tables. Then you need only one data reader

 SELECT A.Index_no, A.emp_ID, A.contact_no, B.emp_name, B.salary, B.dept FROM table1 A INNER JOIN table2 B ON A.Index_no = B.Index_no WHERE B.salary < 20000 ORDER BY B.emp_name 

---

UPDATE

If the number of records involved is small, then querying the second table only once with the IN clause will be much more efficient if for some reason you cannot connect the two servers.

 SELECT * FROM table1 WHERE Index_no IN (4,12,17,30,112,167) 

You would create this SQL statement like this

 string[] stringArray = array1 .Select(i => i.ToString()) .ToArray(); string list = String.Join(",", stringArray); string sql = "SELECT * FROM table1 WHERE Index_No IN (" + list + ")"; 

The idiomatic way to execute the second loop would be (not do while )

 for (int i = 0; i < array1.Length; i++) { ... } 

Answer 2

Why don't you write a stored procedure that combines both elements and returns the results? You can connect to the second db using a linked server, or you can link to the second table using the dbname.tablename format.