Need to return SqlDataReader with asynchronous database request

Question

The following method executes a query in a SQL Server database:

private bool authenticated(SqlConnection conn)
{
    string query = "some sql query";
    SqlCommand cmd = new SqlCommand(query, conn);

    // the intent is to change this to: SqlDataReader reader = await cmd.ExecuteReaderAsync();
    SqlDataReader reader = cmd.ExecuteReader();

    if (!reader.HasRows)
    {
       return false;
    }

    while (reader.Read())
    {
        // do some stuff
       /*
        if (some condition)
            return true;
       */
    }
}

The method call to execute the query needs to be changed to an asynchronous version. However, the method would need to be changed to an async. Such methods cannot return anything other than void or Task. Writing another method just for the query execution has been considered, but aysnc methods cannot take ref parameters. What is the best way out of this?

As a side note, the SqlConnection class is not thread-safe. Be careful not to reuse it while an asynchronous request is pending. Also make sure that the SqlConnection instance is disposed when it's no longer needed. Otherwise you may end up with a connection leakage, and a starved connection pool. — Theodor Zoulias
– Theodor Zoulias, Commented Apr 14, 2020 at 15:44

Frank Nielsen · Accepted Answer · 2020-04-14 12:30:13Z

2

Task<T> can return what you need.

private async Task<bool> authenticated(SqlConnection conn)
{
    string query = "some sql query";
    SqlCommand cmd = new SqlCommand(query, conn);

    // the intent is to change this to: SqlDataReader reader = await cmd.ExecuteReaderAsync();
    SqlDataReader reader = await cmd.ExecuteReaderAsync();

    if (!reader.HasRows)
    {
       return false;
    }

    while (reader.Read())
    {
        // do some stuff
        if (some condition)
            return true;
    }
}

answered Apr 14, 2020 at 12:30

Frank Nielsen

1,5661 gold badge10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need to return SqlDataReader with asynchronous database request

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related