Asynchronus methods in .net core api

Question

Asynchronus methods in .net core api

.net-core dapper

Question

I am using Dapper to connect .net core api with Oracle DB.I have the following methods.

public IDbConnection GetConnection()
    {
        var connectionString = configuration.GetSection("ConnectionStrings").GetSection("AsyncDB").Value;
        var conn = new OracleConnection(connectionString);
        return conn;
    }

public Object GetProducts()
    {
        //throw new System.NotImplementedException();
        object result = null;
        try
        {
            OracleDynamicParameters dynamicParameters = new OracleDynamicParameters();
            dynamicParameters.Add("EMPCURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

            var conn = this.GetConnection();
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            if (conn.State == ConnectionState.Open)
            {
                var query = "SPGETPRODUCTINFO";
                result = SqlMapper.Query(conn, query, param: dynamicParameters, commandType: CommandType.StoredProcedure);
            }
        }
        catch (Exception e)
        {
            throw e;
        }

        return result;
    }

The procedure SPGETPRODUCTINFO return rows from the database.

What are the ways to turn the method into asynchronus method using async/await keywords?

Saiful Islam

Popular Answer

You make this method async the same way you turn any other method async. You mark the method as async, have it return a Task if it returns void or Task where T is the class it was previously returning. Change it to use a synchronous versions of any methods that are available and await the results.

Your exception handling here is not good. Firstly, you're doing throw e; where e is a caught exception. Don't do that. If you need to rethrow a caught exception, just do throw;. That way you preserve your stack trace. Also, This try/catch is totally pointless. If you're not going to take any remedial action or log anything, there's no point. You can remove the try/catch completely from this code.

You're not handling your database connection properly. OracleConnection implements IDisposable. You need to either use a using statement or dispose of it in a finally block.

The class name says, GetProducts but you're just returning an object. That's not very good. Now any consuming code is going to have to cast it to the correct type in order to use it. The whole point of Dapper is that it can map your query to a strongly typed object. Based on the name of this method, you should be returning an IEnumerable or something like that.

You're checking the state of your database connection right after you obtain it. There's no point in that. If you obtain a fresh connection, you can assume it's closed and that you need to open it.

Taking all of that into consideration, you should get something like this:

public IDbConnection GetConnection()
{
    var connectionString = configuration.GetSection("ConnectionStrings").GetSection("AsyncDB").Value;
    var conn = new OracleConnection(connectionString);
    return conn;
}

public Task<IEnumerable<Product>> GetProducts()
{
    OracleDynamicParameters dynamicParameters = new OracleDynamicParameters();
    dynamicParameters.Add("EMPCURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

    const string query = "SPGETPRODUCTINFO";

    using (var conn = this.GetConnection())
    {
        await conn.OpenAsync();
        var products = await conn.QueryAsync<Product>(query, param: dynamicParameters, commandType: CommandType.StoredProcedure);
        return products;
    }
}

Now that GetProducts is async, any method above that calls it needs to be async and changed to return a Task, and await it. And any method that calls that need to be async and changed to return a Task, and await it. All the way up the call stack until you get to an event handler or the main method for your application.

mason

View more on Stack Overflow

DerviÅŸ KayÄ±mbaÅŸÄ±oÄŸlu · Accepted Answer · 12/18/2018 2:22:57 PM

First of all, your method is needed to be converted to Async

public Object GetProducts()

to

public async Task<Object> GetProductsAsync();
public async Task<Object> GetProductsAsync(CancellationToken cancellationToken = default(CancellationToken));

If a method supports Async implementation, general conventions shows that method name ends with Async keyword.

You can check methods whether they have Async Implementation. For Example, if SqlMapper.Query has Async Implementation, it is generally like SqlMapper.QueryAsync.

Futhermore Async Implementations has return type Task. in order to handle Async Implementation you can do this

result = await SqlMapper.QueryAsync(conn, query, param: dynamicParameters, commandType: CommandType.StoredProcedure);

Also, If you want your Method supports Cancellation, then you need to have CancellationToken cancellationToken = default(CancellationToken) as parameter and your method will be

result = await SqlMapper.QueryAsync(conn, query, param: dynamicParameters, commandType: CommandType.StoredProcedure, cancellationToken: cancellationToken);

Asynchronus methods in .net core api

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator