Tuesday, September 15, 2009

Code: Setting Command Timeouts using Data Access Application Blocks

When doing database work in .Net you can set a timeout value in your connection string.  This timeout specifies the length of time (in seconds) to wait for a connection to the database server to be established.  If you want to specify a timeout value for your stored procedure in SQL command to complete, you need to set the time out on your command object.  If you are using Enterprise Library’s Data Access Application Blocks your code should look similar to below

Database db = DatabaseFactory.CreateDatabase("ConnectionStringName");
using (DbCommand cmd = db.GetStoredProcCommand("StoredProcedureName"))
{
    cmd.CommandTimeout = 60;
    int rowsAffected = db.ExecuteNonQuery(cmd);
}