locked
Returning Multiple Results From SQL Query RRS feed

  • Question

  • I am running a sql server stored procedure that returns two separate result sets, like so

    Select id, phonenumber from regionone
    Select id, phonenumber from regiontwo

    What I need to be able to do is store the results of each query in a separate data set.  My thought was to load 1 data table with the result then parse the data from a data table  into two data sets.  But questions being how to do such, and/or is their a better way to achieve the same outcome?

     
    Sunday, December 13, 2015 5:22 PM

Answers

  • Since your Stored Proc returns two tables, then changing the Fill to this (getting rid of the second parameter, the name of the DataTable):

    sqlDA.Fill(dsRegionOne);

    ... will fill both of the DataTables in the dsRegionOne DataSet.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by IndigoMontoya Sunday, December 13, 2015 5:56 PM
    Sunday, December 13, 2015 5:52 PM

All replies

  • I'm assuming the following scenario:

    Two DataSets, dsRegionOne and dsRegionTwo.
    And dsRegionOne is initially filled from your Stored Proc.

    This should work:

    DataTable dtTwo = dsRegionOne.Tables[1].Copy();
    dsRegionOne.Tables.Remove(dsRegionOne.Tables[1]);
    dsRegionTwo.Tables.Add(dtTwo);



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Sunday, December 13, 2015 5:41 PM
    Sunday, December 13, 2015 5:40 PM
  • I'm assuming the following scenario:

    Two DataSets, dsRegionOne and dsRegionTwo.
    And dsRegionOne is initially filled from your Stored Proc.

    This should work:

    DataTable dtTwo = dsRegionOne.Tables[1].Copy();
    dsRegionOne.Tables.Remove(dsRegionOne.Tables[1]);
    dsRegionTwo.Tables.Add(dtTwo);



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    I have been using this to populate one dataset with the relevant results, but am not sure how to alter to implement the need for a second dataset and a second result set being returned from my sql query.  Would altering my current syntax with the way you posted above bring it all into two individual data sets?

    sqldbc = new SqlConnection(sqlcon);
    sqlc = new SqlCommand(sqlQuery, sqldbc);
    sqldbc.Open();
    sqlc.CommandTimeout = 0;   
    dsRegionOne = new DataSet();
    sqlDA = new SqlDataAdapter(sqlc);            
    sqlDA.Fill(dsRegionOne, "Data");



    Sunday, December 13, 2015 5:46 PM
  • Since your Stored Proc returns two tables, then changing the Fill to this (getting rid of the second parameter, the name of the DataTable):

    sqlDA.Fill(dsRegionOne);

    ... will fill both of the DataTables in the dsRegionOne DataSet.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by IndigoMontoya Sunday, December 13, 2015 5:56 PM
    Sunday, December 13, 2015 5:52 PM