DataReader. Close timeout exception caused by large data in asp.net

Due to the complexity of customs services and huge data volumes, when performing custom SQL queries, you need to limit the amount of data returned to the result set DataSet. Microsoft's DataAdpter Fill method cannot meet its needs, therefore, you have to implement it by yourself, and then implement the IDataReader conversion method to DataSet. After that, you can find that you are calling DataReader. during Close, the system always reports a "timeout exception ". After carefully reading the MSDN description of the Close method, I found such a passage in the remarks:

The Close method fills in the output parameter values, return values, and RecordsAffected, increasing the time used to Close SqlDataReader for processing large or complex queries. If the return value is not important to the number of records affected by the query, you can call the Cancel method of the associated SqlCommand object before calling the Close method to reduce the time required to disable SqlDataReader.

In order to calculate the RecordsAffected value, DataReader will continue to read the remaining records until the record is read. The Cancel method that previously executed the Command can solve this problem.

Below is my test function code (for reference only ):

The code is as follows:

Copy code

Public static void FillDataSet2 (DataSet ds, DbCommand cmd, int pageSize, int pageIndex) { DataTable table = new DataTable (); Using (IDataReader reader = cmd. ExecuteReader (CommandBehavior. CloseConnection )) { Int fieldCount = reader. FieldCount; For (int I = 0; I <fieldCount; I ++) { Table. Columns. Add (reader. GetName (I), reader. GetFieldType (I )); } Ds. Tables. Add (table ); Object [] values = new object [fieldCount]; Int currentIndex = 0; Int startIndex = pageSize * pageIndex; Try { Table. BeginLoadData (); While (reader. Read ()) { If (startIndex> currentIndex ++) Continue; If (pageSize> 0 & (currentIndex-startIndex)> pageSize) Break; Reader. GetValues (values ); Table. LoadDataRow (values, true ); } } Finally { Table. EndLoadData (); // Call the Cancel () method: When reading half of the data, call SqlDataReader. Close () to disable reading. The operation times out. // Msdn explanation: // The Close method fills in the output parameter values, return values, and RecordsAffected, increasing the time it takes to Close SqlDataReader for processing large or complex queries. // If the return value and the number of records affected by the query are not important, you can call the Cancel method of the associated SqlCommand object before calling the Close method to reduce the time required to disable SqlDataReader. Cmd. Cancel (); Reader. Close (); } } }

You can solve the problem more efficiently.

Note: The reason why entity and efficient paging methods are not used is that SQL statements are written by the business personnel themselves, which cannot determine the fields and the statement complexity. Therefore, this method is used.