Search

Sep 5, 2008

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.

No comments: