Fix: Timeout expired. The timeout period elapsed C# error

The ‘Timeout expired. The timeout period elapsed’ error occurs when a database operation takes longer than the configured timeout period. This is a common issue with long-running queries, large data operations, or slow database connections.

---

How the Error Happens

This error typically occurs when:

• SQL query takes longer than the default 30-second timeout
• Database is under heavy load
• Network latency affects database communication
• Large data operations (bulk inserts, updates, deletes)
• Complex queries with multiple joins or aggregations

---

Solution 1: Increase Command Timeout

// ✅ Set command timeout explicitly
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    using (var command = new SqlCommand("SELECT * FROM LargeTable", connection))
    {
        // ✅ Increase timeout to 5 minutes (300 seconds)
        command.CommandTimeout = 300;
        
        var reader = command.ExecuteReader();
        // Process results
    }
}

// ✅ For Entity Framework
public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(connectionString, options =>
        {
            // ✅ Set command timeout for EF
            options.CommandTimeout(300); // 5 minutes
        });
    }
}

---

Solution 2: Increase Connection String Timeout

// ✅ Add connection timeout to connection string
string connectionString = "Server=localhost;Database=MyDb;Trusted_Connection=true;" +
                         "Connection Timeout=300;"; // 5 minutes for connection establishment

// ✅ For Entity Framework
string efConnectionString = "Server=localhost;Database=MyDb;Trusted_Connection=true;" +
                           "Connection Timeout=300;Command Timeout=300;";

---

Solution 3: Optimize Database Queries

// ❌ Slow query causing timeout
SELECT * FROM LargeTable WHERE Column LIKE '%searchterm%';

// ✅ Optimized query with proper indexing
SELECT Id, Name, Email FROM Users 
WHERE IsActive = 1 AND CreatedDate >= @StartDate
ORDER BY CreatedDate DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

// ✅ Use pagination for large datasets
public async Task<List<User>> GetUsersAsync(int pageNumber, int pageSize)
{
    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();
    
    using var command = new SqlCommand(@"
        SELECT Id, Name, Email 
        FROM Users 
        ORDER BY Id 
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY", connection);
    
    command.Parameters.AddWithValue("@Offset", (pageNumber - 1) * pageSize);
    command.Parameters.AddWithValue("@PageSize", pageSize);
    command.CommandTimeout = 60; // 1 minute
    
    var users = new List<User>();
    using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        users.Add(new User 
        { 
            Id = reader.GetInt32("Id"),
            Name = reader.GetString("Name"),
            Email = reader.GetString("Email")
        });
    }
    
    return users;
}

---

Solution 4: Use Asynchronous Operations

// ✅ Use async methods to prevent blocking
public async Task<List<DataModel>> GetDataAsync()
{
    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();
    
    using var command = new SqlCommand("SELECT * FROM LargeTable", connection);
    command.CommandTimeout = 120; // 2 minutes
    
    var results = new List<DataModel>();
    using var reader = await command.ExecuteReaderAsync();
    
    while (await reader.ReadAsync())
    {
        results.Add(new DataModel
        {
            Id = reader.GetInt32("Id"),
            Name = reader.GetString("Name")
        });
    }
    
    return results;
}

---

Solution 5: Batch Processing for Large Operations

// ✅ Process large datasets in batches
public async Task ProcessLargeDatasetAsync()
{
    const int batchSize = 1000;
    int offset = 0;
    bool hasMore = true;
    
    while (hasMore)
    {
        using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();
        
        using var command = new SqlCommand(@"
            SELECT TOP (@BatchSize) * 
            FROM LargeTable 
            WHERE Processed = 0 
            ORDER BY Id 
            OFFSET @Offset ROWS", connection);
        
        command.Parameters.AddWithValue("@BatchSize", batchSize);
        command.Parameters.AddWithValue("@Offset", offset);
        command.CommandTimeout = 180; // 3 minutes per batch
        
        var reader = await command.ExecuteReaderAsync();
        var records = new List<Record>();
        
        while (await reader.ReadAsync())
        {
            records.Add(MapRecord(reader));
        }
        
        hasMore = records.Count == batchSize;
        offset += batchSize;
        
        // Process batch
        await ProcessBatchAsync(records);
    }
}

---

Solution 6: Configure Entity Framework Timeout

// ✅ Configure EF timeout globally
public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // ✅ Set default timeout for all operations
        Database.SetCommandTimeout(300); // 5 minutes
    }
    
    // ✅ Override timeout for specific operations
    public async Task<List<ExpensiveResult>> GetExpensiveDataAsync()
    {
        Database.SetCommandTimeout(600); // 10 minutes for this operation
        return await ExpensiveResults.ToListAsync();
    }
}

---

Solution 7: Use Connection Pooling and Proper Disposal

// ✅ Proper connection management
public class DatabaseService : IDisposable
{
    private readonly string _connectionString;
    private bool _disposed = false;
    
    public DatabaseService(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    public async Task<DataTable> ExecuteQueryAsync(string sql, SqlParameter[] parameters = null)
    {
        using var connection = new SqlConnection(_connectionString)
        {
            ConnectionTimeout = 30 // 30 seconds for connection
        };
        
        await connection.OpenAsync();
        
        using var command = new SqlCommand(sql, connection)
        {
            CommandTimeout = 300 // 5 minutes for command
        };
        
        if (parameters != null)
        {
            command.Parameters.AddRange(parameters);
        }
        
        using var adapter = new SqlDataAdapter(command);
        var dataTable = new DataTable();
        adapter.Fill(dataTable);
        
        return dataTable;
    }
    
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    
    protected virtual void Dispose(bool disposing)
    {
        if (!_disposed && disposing)
        {
            // Cleanup resources
        }
        _disposed = true;
    }
}

---

Solution 8: Monitor and Diagnose Performance Issues

// ✅ Add performance monitoring
public class MonitoredDatabaseService
{
    public async Task<T> ExecuteWithMonitoringAsync<T>(Func<Task<T>> operation, string operationName)
    {
        var stopwatch = Stopwatch.StartNew();
        
        try
        {
            var result = await operation();
            Console.WriteLine($"{operationName} completed in {stopwatch.ElapsedMilliseconds}ms");
            return result;
        }
        catch (SqlException ex) when (ex.Number == -2) // Timeout
        {
            Console.WriteLine($"{operationName} timed out after {stopwatch.ElapsedMilliseconds}ms");
            throw;
        }
    }
    
    public async Task<List<User>> GetUsersWithMonitoringAsync()
    {
        return await ExecuteWithMonitoringAsync(async () =>
        {
            using var connection = new SqlConnection(connectionString);
            await connection.OpenAsync();
            
            using var command = new SqlCommand("SELECT * FROM Users", connection);
            command.CommandTimeout = 120; // 2 minutes
            
            var users = new List<User>();
            using var reader = await command.ExecuteReaderAsync();
            
            while (await reader.ReadAsync())
            {
                users.Add(new User { Id = reader.GetInt32("Id"), Name = reader.GetString("Name") });
            }
            
            return users;
        }, "GetUsers");
    }
}

---

Common Causes and Prevention

1. Long-running queries: Optimize queries with proper indexing
2. Network issues: Check network connectivity and latency
3. Database locks: Minimize transaction scope and duration
4. Large result sets: Use pagination and streaming
5. Blocking operations: Use async methods to prevent blocking

---

Best Practices

• Set appropriate timeouts based on operation complexity
• Use connection pooling effectively
• Monitor query performance regularly
• Implement retry logic for transient failures
• Use stored procedures for complex operations
• Consider caching for frequently accessed data