Doesn't work th SqlCommand with multiple queries

Question

With one SELECT query, the code seems to add to the listbox correctly, but when I add another query, the listbox doesn't show anything anymore, and it seems that that rdr[3] does not exists (Contact has 3 columns and Numar_contact has one column (should't it be this one the rdr[3]?))

string connString = @"database=Agenda_db; Data Source=Marian-PC\SQLEXPRESS; Persist Security Info=false; Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);

try {
   conn.Open();
   SqlCommand cmd = new SqlCommand("SELECT * FROM Contact;"+ "SELECT * FROM Numar_contact", conn)

   SqlDataReader rdr = cmd.ExecuteReader();

   while (rdr.Read())
   {
       listBox1.Items.Add(rdr[0].ToString() + ' ' + rdr[1].ToString() + ' ' + rdr[2].ToString()+' '+ rdr[3].ToString());
   }

   rdr.Close();

Answer 1

Join your queries with a UNION. The way you've got it now, it'll return two results sets.

SELECT [col1], [col2] FROM Contact
UNION ALL
SELECT [col1], [col2] FROM Numar_contact

As DJ KRAZE pointed out in a comment, it might not be a bad idea to wrap this in a sproc or a TVF. But this will work too.

Edit:

I just learned via comments that the two tables are actually unrelated. In light of that, I'd be tempted to use two SqlCommands with two, distinct foreach loops. But if you're sold on this way,

SELECT id_contact, nume_contact, prenume_contact FROM Contact
UNION ALL
SELECT id_contact, numar, NULL FROM Numar_contact

This will align the data from the two tables, but where the second table doesn't have a [prenume_contact] it will select NULL. I might have mixed up the column positions here, since I don't really understand what those names are meant to represent.

Edit 2:

string connString = @"database=Agenda_db; Data Source=Marian-PC\SQLEXPRESS; Persist Security Info=false; Integrated Security=SSPI";
using (SqlConnection conn = new SqlConnection(connString))
{
    try
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Contact", conn))
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                listBox1.Items.Add(rdr[0].ToString() + " " + rdr[1].ToString() + " " + rdr[2].ToString());
            }
        }

        using (SqlCommand cmd2 = new SqlCommand("SELECT * FROM Numar_contact", conn))
        using (SqlDataReader rdr2 = cmd.ExecuteReader())
        {
            while (rdr2.Read())
            {
                listBox1.Items.Add(rdr2[0].ToString() + " " + rdr2[1].ToString());
            }
        }
    }
    catch { }
}

Edit 3, thanks to insight from Scott Chamberlain:

On the other hand, you might want to perform a JOIN of some kind, most commonly an INNER JOIN. Note that this is an entirely different operation from any we've talked about before.

SELECT Contact.id_contact, Contact.nume_contact, Contact.prenume_contact, Numar_contact.numar
FROM Contact
INNER JOIN Numar_contact on Contact.id_contact = Numar_contact.id_contact

This will tie the two tables together, returning a record for each contact-numar_contact. Again, this is definitely not the same as doing a UNION. Make sure you're aware of the difference before you pick which you want.

Use this if your second table contains data that relates many-to-one to the first table.

Answer 2

Thanks to your comment, what you are wanting to do is JOIN the tables.

SELECT Contact.id_contact, nume_contact, prenume_contact, numar
FROM Contact
INNER JOIN Numar_contact on Contact.id_contact = Numar_contact.id_contact

That will combine the two tables in to four columns where id_contact matches in both tables.

You may want a INNER JOIN or a LEFT JOIN depending on if you want rows to show up only when there is a item in the 2nd table or show up anyway and just make the 4th column DBNull.Value.

Answer 3

Yes you can.

Here is an example from the MSDN I've modified to use your code - you need to move the reader to the Next ResultSet

string connString = @"database=Agenda_db; Data Source=Marian-PC\SQLEXPRESS; Persist Security Info=false; Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);
SqlCommand myCommand = new SqlCommand("SELECT * FROM Contact; SELECT * FROM Numar_contact", conn);
SqlDataReader myReader ;

int RecordCount=0; 

try
{
    myConnection.Open();
    myReader = myCommand.ExecuteReader();

    while (myReader.Read())
    {
        //Write logic to process data for the first result.
         RecordCount = RecordCount + 1;
    }
    MessageBox.Show("Total number of Contacts: " + RecordCount.ToString());

    bool moreResults = myReader.NextResult();   // <<<<<<<<<<< MOVE TO NEXT RESULTSET

    RecordCount = 0;

    while (moreResults && myReader.Read())
    {
        //Write logic to process data for the second result.
        RecordCount = RecordCount + 1;
    }
    MessageBox.Show("Total number from Numar_contacts: " + RecordCount.ToString());
}
catch(Exception ex) 
{
   MessageBox.Show(ex.ToString());
}
finally
{
    conn.Close(); // Could be replaced with  using statement too
}