Obtain multiple result sets through DataReader

We know that the DataReader of ado.net provides a method to read rows from the database only into the stream. Generally, we process a result set. In fact, through the NextResult method of DataReader, We Can batch process the T-SQL, that is, we can get to process two or more result sets. The following is a simple example to describe this function.
1. Data Table
We still use the Person table in this article. In the Person table, we define self-increasing IDs, surnames, names, heights, and weights. Their corresponding fields are the same as the attribute names in the object.

2. entity class

Public class Person
{
Public int Id {get; set ;}
Public string FirstName {get; set ;}
Public string LastName {get; set ;}
Public double Weight {get; set ;}
Public double Height {get; set ;}

/// <Summary>
/// Total number of records
/// </Summary>
Public long TotalCount {get; set ;}
}

It should be noted that the TotalCount attribute is used to calculate the total number of records of qualified persons based on the statistical needs, and is not an actual field in the Person table.

3. Data entity Conversion

Code

Public class SqlHelper
{
Private const string strSqlCon = @ "Data Source =. \ sqlexpress; Initial Catalog = TestDb; User Id = sa; Password = 123456 ;";

/// <Summary>
/// Convert data table items to corresponding entities (the total number of records in the two result sets of the Operation)
/// </Summary>
/// <Typeparam name = "T"> </typeparam>
/// <Param name = "objType"> </param>
/// <Param name = "strSelectSql"> </param>
/// <Returns> </returns>
Public static IList <T> Select <T> (Type objType, string strSelectSql) where T: class, new ()
{
IList <T> listModels = new List <T> ();
Using (SqlConnection sqlConn = new SqlConnection (strSqlCon ))
{
SqlConn. Open ();
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = sqlConn;
Cmd. CommandText = strSelectSql;
IDataReader rdr = cmd. ExecuteReader ();
Hashtable htColumns = CreateHashColumnName (rdr );

While (rdr. Read ())
{
Object obj = Activator. CreateInstance (objType );
PropertyInfo [] properties = objType. GetProperties ();
Foreach (PropertyInfo propInfo in properties)
{
If (htColumns. Contains (propInfo. Name. ToUpper () = false)
{
Continue;
}
Int index = rdr. GetOrdinal (propInfo. Name );
If (index>-1 & rdr. GetValue (index )! = System. DBNull. Value)
PropInfo. SetValue (obj, rdr. GetValue (index), null );
}
T model = default (T );
Model = obj as T;
ListModels. Add (model );
}

Bool isNext = rdr. NextResult (); // move forward to the next result set
HtColumns = CreateHashColumnName (rdr); // change the corresponding column name of hashtable
While (rdr. Read () & isNext)
{
For (int I = 0; I <listModels. Count; I ++)
{
PropertyInfo [] props = listModels [I]. GetType (). GetProperties ();
Foreach (PropertyInfo pi in props)
{
If (htColumns. Contains (pi. Name. ToUpper () // retrieve the total number of records
{
Int index = rdr. GetOrdinal (pi. Name );
If (index>-1 & rdr. GetValue (index )! = System. DBNull. Value)
Pi. SetValue (listModels [I], rdr. GetValue (index), null );
Break; // determine whether the break needs to be commented out based on the actual situation.
}
}
}
}
}
Return listModels;
}

Private static Hashtable CreateHashColumnName (IDataReader rdr)
{
Int len = rdr. FieldCount;
Hashtable ht = new Hashtable (len );
For (int I = 0; I <len; I ++)
{
String columnName = rdr. GetName (I). ToUpper (); // case insensitive
String columnRealName = rdr. GetName (I );
If (ht. ContainsKey (columnName) = false)
{
Ht. Add (columnName, columnRealName );
}
}
Return ht;
}

}

In the code above, the annotations are clearly written. After the NextResult method is used, we can obtain the columns of the result set again (In the example, the columns are kept in hashtable ), assign values to an object based on the method and principle used to obtain the result set for the first time.

4. frontend call
It is easy to get an entity set through a semicolon-containing SQL statement.

Code

// Separate multiple SQL statements;
String SQL = "SELECT TOP 10 [Id], [FirstName], [LastName], [Weight], [Height] FROM Person (NOLOCK); SELECT COUNT (Id) AS TotalCount FROM Person (NOLOCK )";
IList <Person> listPersons = SqlHelper. Select <Person> (typeof (Person), SQL); // two result sets are operated.

 

There is a picture with the truth:

We can clearly see that the total number in the test database is 1999918.

Finally, I would like to ask some questions about the recently used linq2sql: Many people talk about the Skip... take paging (essentially a secondary top). It seems that the total number of records (TotalCount) cannot be obtained directly, but the paging effect is determined by the total number of records in normal projects. In addition to taking the Count directly through the linq query, I don't know if there are any other better methods, such as the window function row number, or as described in this article, what does batch processing work for one query?