Accessing 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 have a portion of the complete data. Now I need to write a query such that I am able to fetch the complete record from the two tables. As an example say table 1 has Index_no,emp_ID and contact no. Table 2 has index_no, emp_name, salary and dept. The index_no is same for the same record portion in each table. Following is the code to fetch all records where salary <20000.

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 with this is that I am getting only one record info from table2 while for table 1 I get all the desired record info. The do-while loop seems to terminate after one iteration only.

Are both databases within the same database server instance? Or are the databases in two different instances, maybe even on different machines? — ntziolis, Mar 27 '12 at 12:20
Have you debugged the code? What's the value of array1.Length? — Claudio Redi, Mar 27 '12 at 12:21
@ntziolis no the databases are on different server instances — ritz, Mar 27 '12 at 12:30
@ClaudioRedi array1 is defined to be of size 20, however when i run this fragment of code the do-while loop terminates after 1 iteration. — ritz, Mar 27 '12 at 12:32
ok, and why does it finalize? i > array1.Length or there is any error? — Claudio Redi, Mar 27 '12 at 12:45

score 5 · Accepted Answer · edited May 23 '17 at 12:04

It depends, what two different databases means. If the two databases run on the same SQL-Server, it is easy to access the table of the other database

 SELECT * FROM OtherDb.dbo.TableOnOtherDb

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

In both cases you can use a JOIN query to join the two tables. Then you will 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 involved records is small, then querying the second table only once by using a IN clause would be far more efficient, if you cannot link the two servers for some reason.

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 of doing the second loop would be (not do while)

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

Tables are on different databases, are you taking that into account? — StackOverflower, Mar 27 '12 at 12:46
@OlivierJacot-Descombes If I can store the index_nos returned from table1 in an array and use it after IN in the query my problem would be solved, but dont know whether it is allowed. SELECT * FROM table2 WHERE Index_no IN array1[i]; — ritz, Mar 27 '12 at 15:57
No this is not allowed of cause. You will have to create your SQL dynamically. See my edit. — Olivier Jacot-Descombes, Mar 27 '12 at 16:14
@OlivierJacot-Descombes Thanx a lot...you've helped me in a big way :-) — ritz, Mar 27 '12 at 17:17

score 2 · Answer 2 · answered Mar 27 '12 at 12:20

2

Why don't you write a stored procedure which joins both the ables and gives back the results? You can connect to the second db using linked server or you can refer the second table using the dbname.tablename format.

answered Mar 27 '12 at 12:20

Scorpion-Prince

3,574
3
18
24

Accessing two databases using Data Reader

2 Answers2