Two SELECTS, One query

Question

I want to pull down information from two tables from a db. One row from A, and the rows in B with an FK to the row I pulled from A.

I'd like to make this a single stored proc with two select statements, rather than having to make two calls to the DB.

I know several ways to pull the information from a single select...but can't remember how to get data from multiple selects. Googling has proven to be difficult, since I'm having trouble coming up with nouns/verbs to describe the situation that don't describe a million other things.

Can someone point me in the right direction?

(To keep things simple, I know to use "using" statements and such...I just need the basic idea of the approach).

using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand com = new SqlCommand(commandString, conn))
    {
        <somehow get multiple select's of data here in one call>
    }
}

Answer 1

You need to use M.A.R.S, which allow you to load DataTable from multiple selects as one DataTableCollection.

Answer 2

var reader = com.ExecuteReader();

while(reader.Read())
{
  //do operations for the first select here
}

reader.NextResult();

while(reader.Read())
{
  //do operations for the second select here
}

Note: there may be syntax error, not checked. But the point is, use SqlDataReader.NextResult().

Answer 3

Maybe I'm misunderstanding what you're trying to do but can't you use a join and a datareader to get this info?

roughly (in your command's using statement)

select a.foo, b.bar from a, b where a.id = 2 and b.foo = a.foo;

using( DbDataReader reader = com.executeReader() )
{
    while( reader.read() )
    {
        myA.foo = reader[0].toString();
        myB.bar = reader[1].toString();
    }
}

Answer 4

If you're used to using the SqlDataReader then you just need to have your stored procedure or sql statement perform multiple selects and call NextResult() to move to the next result set:

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand(commandString, conn);
    // Add parameters here
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // This will read the first result set
        while(reader.Read())
        { 
            // Read data
        }

        // This will read the second result set
        if (!reader.NextResult())
        {
            throw new ApplicationException("Only one result set returned");
        }

        while (reader.Read())
        {
            // Read data
        }
    }
}

If you're used to using the data adapter returning data tables then all you need to do is have your data adapter fill a dataset and extract the result sets from the Tables property:

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    SqlDataAdapter da = new SqlDataAdapter(commandString, conn);
    DataSet ds = new DataSet();
    da.Fill(ds);

    DataTable firstResult = ds.Tables[0];
    DataTable secondResult = ds.Tables[1];
}