Click here to Skip to main content
15,356,327 members
Home / Discussions / Database
   

Database

 
AnswerRe: Multiple Queries in one Stored procedure Pin
PIEBALDconsult18-Feb-15 4:00
professionalPIEBALDconsult18-Feb-15 4:00 
GeneralRe: Multiple Queries in one Stored procedure Pin
sudevsu18-Feb-15 4:08
Membersudevsu18-Feb-15 4:08 
GeneralRe: Multiple Queries in one Stored procedure Pin
sudevsu18-Feb-15 4:10
Membersudevsu18-Feb-15 4:10 
GeneralRe: Multiple Queries in one Stored procedure Pin
Corporal Agarn18-Feb-15 4:33
professionalCorporal Agarn18-Feb-15 4:33 
AnswerRe: Multiple Queries in one Stored procedure Pin
Wendelius18-Feb-15 4:37
mveWendelius18-Feb-15 4:37 
GeneralRe: Multiple Queries in one Stored procedure Pin
sudevsu18-Feb-15 5:11
Membersudevsu18-Feb-15 5:11 
GeneralRe: Multiple Queries in one Stored procedure Pin
Wendelius18-Feb-15 6:41
mveWendelius18-Feb-15 6:41 
SuggestionRe: Multiple Queries in one Stored procedure Pin
Richard Deeming18-Feb-15 5:43
mveRichard Deeming18-Feb-15 5: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 6:21
Membersudevsu18-Feb-15 6:21 
GeneralRe: Multiple Queries in one Stored procedure Pin
Wendelius18-Feb-15 6:42
mveWendelius18-Feb-15 6:42 
AnswerRe: Multiple Queries in one Stored procedure Pin
Smart00324-Feb-15 23:18
professionalSmart00324-Feb-15 23:18 
AnswerRe: Multiple Queries in one Stored procedure Pin
Abdulnazark9-Mar-15 7:08
MemberAbdulnazark9-Mar-15 7:08 
AnswerRe: Multiple Queries in one Stored procedure Pin
deepankarbhatnagar11-Mar-15 0:02
professionaldeepankarbhatnagar11-Mar-15 0:02 
QuestionMYSQL SUM SEVERAL COLUMN VALUES Pin
KipkoechE17-Feb-15 10:29
MemberKipkoechE17-Feb-15 10:29 
AnswerRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
Richard Andrew x6417-Feb-15 11:13
professionalRichard Andrew x6417-Feb-15 11:13 
GeneralRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
KipkoechE17-Feb-15 17:47
MemberKipkoechE17-Feb-15 17:47 
AnswerRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
Richard Andrew x6418-Feb-15 4:58
professionalRichard Andrew x6418-Feb-15 4:58 
GeneralRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
KipkoechE18-Feb-15 11:30
MemberKipkoechE18-Feb-15 11:30 
GeneralRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
Richard Andrew x6419-Feb-15 4:55
professionalRichard Andrew x6419-Feb-15 4:55 
GeneralRe: MYSQL SUM SEVERAL COLUMN VALUES Pin
KipkoechE19-Feb-15 19:45
MemberKipkoechE19-Feb-15 19:45 
QuestionSQL server 12: Displaying column cell values in single cell Pin
Swap917-Feb-15 2:35
MemberSwap917-Feb-15 2:35 
SuggestionRe: SQL server 12: Displaying column cell values in single cell Pin
ZurdoDev17-Feb-15 2:54
professionalZurdoDev17-Feb-15 2:54 
GeneralRe: SQL server 12: Displaying column cell values in single cell Pin
Swap917-Feb-15 2:58
MemberSwap917-Feb-15 2:58 
GeneralRe: SQL server 12: Displaying column cell values in single cell Pin
ZurdoDev17-Feb-15 3:00
professionalZurdoDev17-Feb-15 3:00 
GeneralRe: SQL server 12: Displaying column cell values in single cell Pin
Swap917-Feb-15 3:15
MemberSwap917-Feb-15 3: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.