Timeout issue when using sql helper(Microsoft.Appl

Question

I am having timeout issues when dealing with long sql queries, the Dataset which timesout for long queries is :

static public DataSet Getxxxx(Guid xxxx)
{
    DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "GetAllxx", new SqlParameter("@productxx", productxx));

    return ds;
}

Where can i set timeout , I am using Microsoft application block version 2.0.

Answer 1

The Data Access Application Block SqlHelper has been phased out in favour of 'Database', so you'll need to explicitly create a DbCommand and pass it through to Database.ExecuteDataSet. You can then set the CommandTimeout property, to override the default of 30 seconds. e.g. this sets the timeout to 200 seconds:

using (DbCommand command = this.Database.GetStoredProcCommand("GetAllxx"))
{
    Database.AddInParameter(command, "@productxx", DbType.Int32, productxx);
    command.CommandTimeout = 200;
    return Database.ExecuteDataSet(command);
}