DataPortal.Update failed System.Data.SqlClient.SqlException: Timeout expired

DataPortal.Update failed System.Data.SqlClient.SqlException: Timeout expired

Old forum URL: forums.lhotka.net/forums/t/8548.aspx


Softech posted on Wednesday, February 17, 2010

I am using CSLA 3.0 and .Net Framework 3.0. I am working in Siliverlight application references to WCF service and WCF service references to CSLA classes. My application runs successfully, Insert and update running successfully but sometime I am getting this error of Timeout in DataPortal.Update. When I debug the code Error comes at

privatevoid DataPortal_Insert()
{
...
database.ExecuteNonQuery(command);
}

DataPortal.Update failed (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.SqlInternalConnection.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.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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at ExecuteNonQuery(SqlCommand command) in C:\Projects\OrderMIS\CSLA\Framework\Data\SqlDatabase.cs:line 439
   at OrderDAL.Customers.DataPortal_Insert() in C:\Projects\OrderMIS\OrderDAL\Customers.cs:line 433)

Marjon1 replied on Wednesday, February 17, 2010

Have you attempted to time the command when run manually? 

The default timeout is 30 seconds, you could try to set the CommandTimeout property to be a higher value (or for testing purposes, set it 0 for infinite timeout).

Softech replied on Wednesday, February 17, 2010

Please give me some hint of code where and in which file to set CommandTimeout property.

Marjon1 replied on Wednesday, February 17, 2010

Somewhere within your code, I assume that there will a line where the commandText for the commandObject will be set.

The code may look like this with in your DataPortal_Update()

 

  Using cm As SqlCommand = cn.Connection.CreateCommand

 

        With cm

          .CommandText = "[hrm].[mergeNote]"

          .CommandType = CommandType.StoredProcedure

          .CommandTimeout = 60

 

          .Parameters.AddWithValue("@EmployeeId", EmpId)

          .Parameters.AddWithValue("@Note", _Note)

          .ExecuteNonQuery()

        End With

      End Using

 

Gort replied on Wednesday, January 25, 2012

Is it possible to change the default CommandTimeout value of the SqlCommand object via the config file?  Our connection string has a timeout value in it which is not honored by the SqlCommand.  I don't want to have to manually change every use of the SqlCommand object in order to increase my default timeout from 30 to 60.

Thanks.

RockfordLhotka replied on Wednesday, January 25, 2012

One easy solution is to use a factory object/method to create your command:

public static class CommandFactory
{
  public static SqlCommand GetCommand(SqlConnection cn)
  {
    var result = cn.CreateCommand();
    // initialize command here
    return result;
  }
}

Then just use this method to create your commands app-wide.

Of course maybe not so easy if you already have a lot of code in your codebase...

Gort replied on Tuesday, March 06, 2012

We are getting our SQL timeout errors with the following message: DataPortal.Fetch failed (Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.) 

Is it possible to update the code to be able to tell what object is actually failing? 

Tks.

RockfordLhotka replied on Tuesday, March 06, 2012

The inner exception might provide you with that information. There's usually a lot of info deeper in the exception tree.

Copyright (c) Marimer LLC