Multiple Active Results Set

In Azure why we need to think of MARS? As we know the the communication between Client’s browser and SQL Server is a costly round trip hence we always try to reduce the number of calls between client and server.

The below mentioned option is one of the easiest improvement you can make in your code; to avoid multiple calls to DB.

Sample code:

string commandText = @"SELECT Id, ContactId FROM dbo.Subscriptions;

SELECT Id, [Name]
FROM dbo.Contacts;";


List<Subscription> subscriptions = new List<Subscription>();
List<Contact> contacts = new List<Contact>();

using (SqlConnection dbConnection = new SqlConnection(@"Data Source=server;Database=database;Integrated Security=true;"))
using (SqlCommand dbCommand = new SqlCommand(commandText, dbConnection))
{
    dbCommand.CommandType = CommandType.Text;
    dbConnection.Open();

    SqlDataReader reader = dbCommand.ExecuteReader();

    while (reader.Read())
    {
        subscriptions.Add(
            new Subscription()
            {
                Id = (int)reader["Id"],
                ContactId = (int)reader["ContactId"]
            });
    }

    reader.NextResult();

    while (reader.Read())
    {
        contacts.Add(
            new Contact()
            {
                Id = (int)reader["Id"],
                Name = (string)reader["Name"]
            });
    }

    dbConnection.Close();
}

Similarly we can use a stored procedure to return multiple Sql Query results in to DataTable
SqlConnection con=new SqlConnection("YourConnection String");
SqlCommand cmd=new SqlCommand();
SqlDataAdapter da=new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("name of your Stored Procedure", con);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@SuperID", id);//if you have parameters.
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();

After this you can take advantage of different (7) recordsets using

ds.Tables[0]
ds.Tables[1]
ds.Tables[2]
ds.Tables[3]
ds.Tables[4]
ds.Tables[5]
ds.Tables[6]

In the above mentioned ways you can return multiple results in a single DB call.
Happy Programming.

Advertisements
%d bloggers like this: