CODEWITHSUNDEEP
.netsql
Beska
Beska

Reputation: 12686

Two SELECTS, One query

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>
    }
}

Upvotes: 2

Views: 927

Answers (4)

Justin
Justin

Reputation: 86789

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];
}

Upvotes: 4

McAden
McAden

Reputation: 13970

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();
    }
}

Upvotes: 0

Adrian Godong
Adrian Godong

Reputation: 8921

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().

Upvotes: 3

eulerfx
eulerfx

Reputation: 37749

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

Upvotes: 0

Related Questions