Click here to Skip to main content
15,504,146 members
Home / Discussions / Database
   

Database

 
AnswerRe: Multiple Queries in one Stored procedure Pin
PIEBALDconsult18-Feb-15 5:00
professionalPIEBALDconsult18-Feb-15 5:00 
GeneralRe: Multiple Queries in one Stored procedure Pin
sudevsu18-Feb-15 5:08
sudevsu18-Feb-15 5:08 
GeneralRe: Multiple Queries in one Stored procedure Pin
sudevsu18-Feb-15 5:10
sudevsu18-Feb-15 5:10 
GeneralRe: Multiple Queries in one Stored procedure Pin
Corporal Agarn18-Feb-15 5:33
professionalCorporal Agarn18-Feb-15 5:33 
AnswerRe: Multiple Queries in one Stored procedure Pin
Wendelius18-Feb-15 5:37
mveWendelius18-Feb-15 5:37 
GeneralRe: Multiple Queries in one Stored procedure Pin
sudevsu18-Feb-15 6:11
sudevsu18-Feb-15 6:11 
GeneralRe: Multiple Queries in one Stored procedure Pin
Wendelius18-Feb-15 7:41
mveWendelius18-Feb-15 7:41 
SuggestionRe: Multiple Queries in one Stored procedure Pin
Richard Deeming18-Feb-15 6:43
mveRichard Deeming18-Feb-15 6:43 
You don't need to enable MARS to fetch multiple result-sets from a single query; you just load each result-set sequentially. A SqlDataAdapter will take care of that for you, or you can call IDataReader.NextResult in a loop, loading each result-set as you go.

You only need MARS if you're going to have multiple active data-readers open on the same connection at the same time.

In other words:
C#
// This needs MARS:
using (var connection = new SqlConnection("..."))
using (var outerCommand = new SqlCommand("...", connection))
{
    connection.Open();
    using (IDataReader outerReader = outerCommand.ExecuteReader())
    {
        while (outerReader.Read())
        {
            using (var innerCommand = new SqlCommand("...", connection))
            {
                using (IDataReader innerReader = innerCommand.ExecuteReader())
                {
                    while (innerReader.Read())
                    {
                        ...
                    }
                }
            }
        }
    }
}

// This doesn't:
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
    connection.Open();
    using (IDataReader reader = command.ExecuteReader())
    {
        do
        {
            while (reader.Read())
            {
                ...
            }
        } 
        while (reader.NextResult())
    }
}

// Neither does this:
var dataSet = new DataSet();

using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
    var adapter = new SqlDataAdapter(command);
    adapter.Fill(dataSet);
}




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


GeneralRe: Multiple Queries in one Stored procedure Pin
sudevsu18-Feb-15 7:21
sudevsu18-Feb-15 7:21 
GeneralRe: Multiple Queries in one Stored procedure Pin
Wendelius18-Feb-15 7:42
mveWendelius18-Feb-15 7:42 
AnswerRe: Multiple Queries in one Stored procedure Pin
Smart00325-Feb-15 0:18
professionalSmart00325-Feb-15 0:18 
AnswerRe: Multiple Queries in one Stored procedure Pin
Abdulnazark9-Mar-15 8:08
Abdulnazark9-Mar-15 8:08 
AnswerRe: Multiple Queries in one Stored procedure Pin
deepankarbhatnagar11-Mar-15 1:02
professionaldeepankarbhatnagar11-Mar-15 1:02 
QuestionMYSQL SUM SEVERAL COLUMN VALUES Pin
KipkoechE17-Feb-15 11:29
KipkoechE17-Feb-15 11:29 
AnswerRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
Richard Andrew x6417-Feb-15 12:13
professionalRichard Andrew x6417-Feb-15 12:13 
GeneralRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
KipkoechE17-Feb-15 18:47
KipkoechE17-Feb-15 18:47 
AnswerRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
Richard Andrew x6418-Feb-15 5:58
professionalRichard Andrew x6418-Feb-15 5:58 
GeneralRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
KipkoechE18-Feb-15 12:30
KipkoechE18-Feb-15 12:30 
GeneralRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
Richard Andrew x6419-Feb-15 5:55
professionalRichard Andrew x6419-Feb-15 5:55 
GeneralRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
KipkoechE19-Feb-15 20:45
KipkoechE19-Feb-15 20:45 
QuestionSQL server 12: Displaying column cell values in single cell Pin
Swap917-Feb-15 3:35
Swap917-Feb-15 3:35 
SuggestionRe: SQL server 12: Displaying column cell values in single cell Pin
ZurdoDev17-Feb-15 3:54
professionalZurdoDev17-Feb-15 3:54 
GeneralRe: SQL server 12: Displaying column cell values in single cell Pin
Swap917-Feb-15 3:58
Swap917-Feb-15 3:58 
GeneralRe: SQL server 12: Displaying column cell values in single cell Pin
ZurdoDev17-Feb-15 4:00
professionalZurdoDev17-Feb-15 4:00 
GeneralRe: SQL server 12: Displaying column cell values in single cell Pin
Swap917-Feb-15 4:15
Swap917-Feb-15 4:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.