How to properly set timeout of QueryAsync for OdbcConnection?

Question

I am trying to execute query to ODBC database, but when there is a lot of users trying to execute the query, it returns the error:

ERROR [08003] [Sybase][ODBC Driver]Connection not open: not connected to SQL database
ERROR [08001] [Sybase][ODBC Driver]Unable to connect to database server

Here's the example of query:

using (OdbcConnection db = new OdbcConnection(DSNs["kxp"]))
{
    db.ConnectionTimeout = dbConnectionTimeout;
                
    IEnumerable<KxpWarehouse> productions = (await db.QueryAsync<KXPWarehous>(stmt, new { queryDate }))
        .Select(idl => new KxpWarehouse
        {
            name = idl.name,
            stamp = idl.stamp,
            queue1 = idl.queue1,
            queue2 = idl.queue2,
            arrived = idl.arrived,
            remains = idl.remains,
            total = idl.total,
        }).ToList();
    List<KxpWarehouse> kxpWarehouseInfo = new List<KxpWarehouse>();

    foreach (var item in productions)
    {
        byte[] bytes = Encoding.GetEncoding(1251).GetBytes(item.name);
        item.name = Encoding.GetEncoding(866).GetString(bytes);
        bytes = Encoding.GetEncoding(1251).GetBytes(item.stamp);
        item.stamp = Encoding.GetEncoding(866).GetString(bytes);
        kxpWarehouseInfo.Add(item);
    }
    return kxpWarehouseInfo;
}

I was trying to set OdbcConnection.ConnectionTimeout but it seems not to be working in my situation and I guess I need to set the timeout for query

Answer 1

• OdbcConnection.ConnectionTimeout doesn't affect timeout for executing queries - only sets timeout for establishing a connection to the database server

added CommandTimeout of OdbcCommand object used to execute the query:

using (OdbcConnection db = new OdbcConnection(DSNs["kxp"]))
{
    db.ConnectionTimeout = dbConnectionTimeout;

    using (OdbcCommand cmd = new OdbcCommand(stmt, db)) // creating the object
    {
        cmd.CommandTimeout = queryTimeout; // setting query timeout

        cmd.Parameters.AddWithValue("queryDate", queryDate); // add query parameters

        // Execute asynchronously + fetch results
        IEnumerable<KxpWarehouse> productions = (await cmd.ExecuteReaderAsync())
             .Select(idl => new KxpWarehouse
             {
                 name = idl.name,
                 stamp = idl.stamp,
                 queue1 = idl.queue1,
                 queue2 = idl.queue2,
                 arrived = idl.arrived,
                 remains = idl.remains,
                 total = idl.total,
             }).ToList();

        List<KxpWarehouse> kxpWarehouseInfo = new List<KxpWarehouse>();
        foreach (var item in productions)
        {
            byte[] bytes = Encoding.GetEncoding(1251).GetBytes(item.name);
            item.name = Encoding.GetEncoding(866).GetString(bytes);
            bytes = Encoding.GetEncoding(1251).GetBytes(item.stamp);
            item.stamp = Encoding.GetEncoding(866).GetString(bytes);
            kxpWarehouseInfo.Add(item);
        }
        return kxpWarehouseInfo;
    }
}

queryTimeout = timeout value in seconds