ASP:SqlDataSource timeout problem final solution

I opened dozens of webpages, looking for a solution to the ASP:SqlDataSource timeout problem, but I talked a little bit here and there, and none of them were complete. However, it finally managed to piece together a solution to the problem.
I'm just talking briefly here.

Types of timeout errors

Check according to the timeout error types officially provided by Microsoft. The documents are as follows

https://learn.microsoft.com/zh-cn/troubleshoot/sql/connect/timeout-expired-error

There are two possible timeout issues for database queries made:

  • Connection timeout (15 seconds by default)
  • Query or command timeout (30 seconds by default)

If the connection timed out, your error stack will probably look like this:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt,Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnectionowningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfoserverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)  
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

And if the query or command timed out, the stack you reported is probably like this:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()

In this way, you will know which of your timeout problems is the problem.

How to solve

Connection timed out

If it is a connection timeout, it is very easy to solve, basically most of the posts are also how to solve this.
It is to increase the value of the connection timeout parameter.
For example this ASP:SqlDataSource template:

<asp:SqlDataSource runat="server" ID="SqlDataSource1" 
	ConnectionString='<%$ ConnectionStrings:ConnectionString %>' 
	SelectCommand="select * from user" >
</asp:SqlDataSource>

We all know that the value in ConnectionString here is to take out the connection string named ConnectionString in the web.config configuration file, so the first method is to add the Connection Timeout setting to the configuration in web.config:

<connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=.;Initial Catalog=user;
    	User ID=xxx;Password=xxx;Connection Timeout=100000" providerName="System.Data.SqlClient"/>
</connectionStrings>

Here the connection timeout is set to 100,000 seconds

The second method is to modify the connection string of the asp:SqlDataSource control in the corresponding C# file:

SqlDataSource1.ConnectionString = SqlDataSource1.ConnectionString
	 + "Connection Timeout=100000;";

query timeout

I thought that the query timeout could be changed in the same way as the connection timeout, but it didn't work.
After checking, because the ConnectionString connection string is only for connection, not for query, it is also impossible to set the query timeout time.

I went to stackoverflow, and someone asked this question, and then found it from a question

e.Command.CommandTimeout = 300;

This answer, so where to use it?
I tried to use it directly in the c# event, but it didn't work.
After checking the document, this e corresponds to the event class sqlDataSourceSelectingEventArgss
, then the case is closed.

First, add this to the control (I personally only have queries so add query events):

<asp:SqlDataSource runat="server" ID="SqlDataSource1" 
	ConnectionString='<%$ ConnectionStrings:ConnectionString %>' 
	SelectCommand="select * from user"
	OnSelecting="SqlDataSource1_Selecting"  >
</asp:SqlDataSource>

Corresponding C# code:

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.CommandTimeout = 300;//The command timeout is 300 seconds
    }

completed

It actually took 3 days to solve this problem

Tags: C# ASP.NET

Posted by AbraCadaver on Sun, 20 Nov 2022 01:31:08 +1030