开了几十个网页,找ASP:SqlDataSource超时问题的办法,然而东说一点西说一点,没有一个讲的完整的。不过也总算是拼凑出了问题的解决方案。
我这里只是简单讲讲。
超时错误的类型
根据微软官方提供的超时错误类型进行排查,文档如下
https://learn.microsoft.com/zh-cn/troubleshoot/sql/connect/timeout-expired-error
进行的数据库查询可能会有两种超时问题:
- 连接超时(默认为 15 秒)
- 查询或命令超时(默认为 30 秒)
如果是连接超时的话,你报错的堆栈大概会是这样的:
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()
而如果是查询或命令超时的话,你报错的堆栈大概是这样的:
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()
这样下来你就知道你的超时问题的哪个的问题了
如何解决
连接超时
如果是连接超时的话,非常好解决,基本上大部分帖子也是将这个如何解决。
就是将连接超时参数值加大。
比如这个ASP:SqlDataSource模板:
<asp:SqlDataSource runat="server" ID="SqlDataSource1"
ConnectionString='<%$ ConnectionStrings:ConnectionString %>'
SelectCommand="select * from user" >
</asp:SqlDataSource>
我们都知道,这个这里ConnectionString里面的值,是取出web.config配置文件中的名为ConnectionString的连接字符串,那么第一种方法,就是给web.config中的配置,增加Connection Timeout的设置:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.;Initial Catalog=user;
User ID=xxx;Password=xxx;Connection Timeout=100000" providerName="System.Data.SqlClient"/>
</connectionStrings>
这里将连接超时时间设置成了十万秒
第二种方法是在相应C#文件中对该asp:SqlDataSource控件的连接字符串进行修改:
SqlDataSource1.ConnectionString = SqlDataSource1.ConnectionString
+ "Connection Timeout=100000;";
查询超时
查询超时我本来以为可以像连接超时那样一样的方法改的,结果不行。
查了下,因为ConnectionString 连接字符串只管连接,不管查询,所以里面也无法设置查询超时的时间。
去找了stackoverflow上,也有人提这个问题,然后从个提问中找到了
e.Command.CommandTimeout = 300;
这个回答,那么去哪里用呢?
尝试了直接在c#事件里面用,不行。
查了下文档,这个e对应的是sqlDataSourceSelectingEventArgss这个事件类
,那么结案了。
首先,在控件中加入这个(我个人只有查询所以加的查询事件):
<asp:SqlDataSource runat="server" ID="SqlDataSource1"
ConnectionString='<%$ ConnectionStrings:ConnectionString %>'
SelectCommand="select * from user"
OnSelecting="SqlDataSource1_Selecting" >
</asp:SqlDataSource>
对应C#代码:
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.CommandTimeout = 300;//命令超时时间300秒
}
补充
如果没有实时数据的要求的话,设置SqlDataSource 的 EnableCaching=“True”(默认是false)
把查询到的数据加到缓存,效果很好哦
完工
实际上解决这个问题花了3天