Load method of DataTable

I found very interesting method of DataTable. There is Load method which loads the IDataReader directly to DataTable. Well this is not interestring I know lolz.

The case is like....

My query or procedure generates 4 different result set, and I have 4 different DataTable. As IDataReader has 4 result set, fatching all result set I have to use NextResult method which put my reader to next result set. like this. [Dont know this??? Have a look at this.]

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
DataTable dt5 = new DataTable();

using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
{
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select * from TABLE1;select * from TABLE1;select * from TABLE2;select * from TABLE3";

    con.Open();
    IDataReader idr = cmd.ExecuteReader();

    dt1.Load(idr);
    idr.NextResult(); // Put DataReader to next result set
    dt2.Load(idr);
    idr.NextResult(); // Put DataReader to next result set
    dt3.Load(idr);
    idr.NextResult(); // Put DataReader to next result set. ERROR
    dt4.Load(idr);                
}

But this get fails at NextResult, why?

Because when you call Load method on DataTable, it automatical set pointer to next result set, we dont need to call NextResult here.

This is interesting thing, lets add few more on it. First we remove the NextResult method.

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
DataTable dt5 = new DataTable();

using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
{
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select * from TABLE1;select * from TABLE2;select * from TABLE3;select * from TABLE4";

    con.Open();
    IDataReader idr = cmd.ExecuteReader();

    dt1.Load(idr);
    dt2.Load(idr);
    dt3.Load(idr);
    dt4.Load(idr);
    dt5.Load(idr);
}

Well this is working fine, can you see the dt5? I am trying to load 5th result set, but there is no such result set produced by my Sql statements, its still working!!!!, this is really interesting. Well its not succesfull operation; but its will make empty DataTable.

Multiple Active Result Sets - Yet another powerful feature of SQL Server 2005

MARS [Multiple Active Result Sets ] is a new SQL Server 2005 feature that allows the user to run more than one SQL batch on an open connection at the same time.

If you for instance wanted to do some processing of the data in your data reader and updating the processed data back to the database you had to use another connection object which again hurts performance. There was no way to use the same opened connection easily for more than one batch at the time. There are of course server side cursors but they have drawbacks like performance and ability to operate only on a single select statement at the time.

SQL Server 2005 team recognized the above mentioned drawback and introduced MARS. So now it is possible to use a single opened connection for more than one batch. A simple way of demonstrating MARS in action is with this code:

string strConn = "Data Source=[DATASOURCE];Initial Catalog=[DATABASE];User ID=[UID];Password=[PWD];MultipleActiveResultSets=true";
string strSql = "select DoctorId, PatientId from [Patient] where DoctorId = {0}";
string strOutput = "<br/>DoctorId:{0} - PatientId{1}";

using (SqlConnection con = new SqlConnection(strConn))
{
    //Opening Connection
    con.Open();

    //Creating two commands form current connection
    SqlCommand cmd1 = con.CreateCommand();
    SqlCommand cmd2 = con.CreateCommand();

    //Set the comment type
    cmd1.CommandType = CommandType.Text;
    cmd2.CommandType = CommandType.Text;

    //Setting the command text to first command
    cmd1.CommandText = "select distinct DoctorId from [Doctor] where HospitalId = 8";



    //Execute the first command
    IDataReader idr1 = cmd1.ExecuteReader();

    while (idr1.Read())
    {
        //Read the first doctor from data source
        int intDoctorId = idr1.GetInt32(0);

        //create another command, which get patients of doctor
        cmd2.CommandText = string.Format(strSql, intDoctorId);

        //Execute the reader
        IDataReader idr2 = cmd2.ExecuteReader();

        while (idr2.Read())
        {
            //Read the doctor and patient
            Response.Write(string.Format(strOutput, idr2.GetInt32(0), idr2.GetInt32(1)));
        }
        //Dont forgot to close second reader, this will just close reader not connection
        idr2.Close();
    }
}

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.