none
Multiple ResultSets with Dynamic SQL RRS feed

  • Question

  • Can one return multiple resultsets without using a stored procedure. I have a SQL string that I run and it returns multiple results, but I can't make it a stored proc because I use c# code to put it together and I don't want to port that code to SQL. All examples I find on the net use stored procs.
    There is always a way. You just have to find it.
    Monday, March 14, 2011 10:06 AM

Answers

  • Hi TakalaneSesame;

    You can only do that in Linq to SQL using stored procedure. But you can do it using ADO .Net, see the code snippet.

    using System.Data.SqlClient;
    
    string connStr = @"Your connection string here";
    
    SqlConnection con = new SqlConnection( connStr );
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    // Seperate query with semi-colon
    cmd.CommandText = "SELECT * FROM Customers AS c Where c.City = 'London'; SELECT * FROM Products AS p WHERE p.UnitsInStock > 6 ";
    con.Open();
    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    
    // Read through the first result set
    while( dr.Read() )
    {
      // Just showing only the first two colums
      Console.WriteLine(dr.GetString(0) + " <=> " + dr.GetString(1));
    }
    
    // Advance the data reader to the next result set
    dr.NextResult();
    
    // Read through the second result set
    while( dr.Read() )
    {
      Console.WriteLine( dr.GetInt32( 0 ) + " <=> " + dr.GetString( 1 ) );
    }
    
    dr.Close();
    

    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, March 14, 2011 4:54 PM