locked
Running SQL Scripts with multiple datasets - capturing output RRS feed

  • Question

  • User-1760775893 posted

    All,

    Lets say I've got the following SQL script:

    PRINT 'test';

    PRINT 'test2';

    SELECT top 5 * from table1;

    SELECT top 5 * from table2;

    PRINT 'test3';

    I need to capture the output from the above. I can get the print statements to return a value (using InfoMessage) but how do you capture the output from the 2 SELECT statements I've specified.

    TIA

    Monday, June 20, 2011 5:17 AM

Answers

  • User-693248168 posted

    If you are executing any insert or update command, then you get the Row affected message.

    You will have to create independent SqlCommand objects for each update/insert.

    SqlCommand 's ExecuteNonQuery() method returns an integer that represents the number of rows affected.

    If you have insert/update commands inside your stored procedure, then you will have to declare new variables and assign them the row count

    Then you will have to return these variables using return variables.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 21, 2011 3:43 AM

All replies

  • User-693248168 posted

    Are you using DataSet to get the values?

    If yes, then

    Table 1 --> 

    DataTable dt=ds.Tables[0];

    DataTable dt2=ds.Tables[1];

    Where ds is the dataset.

    Monday, June 20, 2011 5:36 AM
  • User-1760775893 posted

    Thanks for the response.

    The above script may be different each time, for example

    PRINT 'test';

    PRINT 'test2';

    SELECT top 5 * from table1;

    SELECT top 5 * from table2;

    SELECT top 5 * from table3;  --additional select here

    PRINT 'test3';

    Is there a way to loop through getting all the available datasets from the above (number of dataset may be different each time).


    TIA

    Monday, June 20, 2011 5:48 AM
  • User-693248168 posted

    If you have multiple resultsets from your query, your Dataset will contain multiple tables.

    for(int i=0;i<ds.Tables.Count;i++)

    {

    DataTable dt=ds.Tables[i]  //--> You can loop through each table.

    }

    Monday, June 20, 2011 7:39 AM
  • User-1760775893 posted

    Excellent, just what I was after!

    Thanks again for the assistance, much appreciated.

    Monday, June 20, 2011 8:40 AM
  • User-693248168 posted

    Please mark the post that answered your query so that others can get the solution quickly.

    Monday, June 20, 2011 8:46 AM
  • User-1760775893 posted

    ...although I can return the PRINT messages from the example script above, how would you go about getting the 'row(s) affected' messages?

    Tuesday, June 21, 2011 3:33 AM
  • User-1760775893 posted

    ...although I can return the PRINT messages from the example script above, how would you go about getting the 'row(s) affected' messages?

    Tuesday, June 21, 2011 3:33 AM
  • User-693248168 posted

    If you are executing any insert or update command, then you get the Row affected message.

    You will have to create independent SqlCommand objects for each update/insert.

    SqlCommand 's ExecuteNonQuery() method returns an integer that represents the number of rows affected.

    If you have insert/update commands inside your stored procedure, then you will have to declare new variables and assign them the row count

    Then you will have to return these variables using return variables.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 21, 2011 3:43 AM
  • User3866881 posted

    ...although I can return the PRINT messages from the example script above, how would you go about getting the 'row(s) affected' messages?

    For Selecting statement, You should use directly SqlDataAdapter to hold these contents, and then use DataTable.Rows.Count to get the result.

    Tuesday, June 21, 2011 10:12 PM