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

Source: Internet
Author: User


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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.