CODEWITHSUNDEEP
c#async-awaitado.nettask-parallel-library
Muhammad Qasim
Muhammad Qasim

Reputation: 1712

Implementing Async ExecuteNonQuery() for IDBCommand vs SqlCommand.ExecuteNonQueryAsync

I'm using IDBCommand for my database layer in order to switch to other database providers than SqlDataProvider.

SqlCommand has all Async methods like ExecuteNonQueryAsync but IDBCommand does not contains async method.

In future we might need to switch to another data provider other than Sql, I just cannot use SqlCommand directly.

I'm just thinking to implement my own Async versions. Would there be any performance concern if I implement my own? Or Its better to use microsoft implemented Async methods that are available for SqlCommand object?

In the following way, I implemented my Async methods. The following is a Synchronous:

public int ExecuteNonQuery(CommandType commandType, string commandText)
{
    PrepareCommand(commandType, commandText);
    var noOFRowsAffected = _dbCommand.ExecuteNonQuery();
    SetParameters(_dbCommand.Parameters);
    return noOFRowsAffected;
 }

The following is the Async version:

public async Task<int> ExecuteNonQueryAsync(CommandType commandType, string commandText)
{
    PrepareCommand(commandType, commandText);

    var noOFRowsAffected = await Task.Factory.StartNew(() =>
    {
        return _dbCommand.ExecuteNonQuery();
    });

    SetParameters(_dbCommand.Parameters);
    return noOFRowsAffected;
}

Please note that exception handling would be handled by the calling layer.

Can anyone give me the right direction? Is it a correct way to do the async operation or I should use microsoft implemented SqlCommand to use its own methods? Any help is really appreciated. Thanks in advance :)

Upvotes: 2

Views: 3082

Answers (3)

Theodor Zoulias
Theodor Zoulias

Reputation: 43554

You could use the abstract classes inside the System.Data.Common namespace. They do have asynchronous methods. For example:

DbCommand.ExecuteNonQueryAsync()

The System.Data.SqlClient.SqlCommand inherits from this base class, and hopefully the other data providers also do so.

Upvotes: 1

Paulo Morgado
Paulo Morgado

Reputation: 14846

Beware that, although you can leverage data providers to create your objects, you'll still need to account for differences in the SQL for each provider.

If you really want your code to be agnostic, you're better with Entity Framework, NHibertnate or any other ORM.

You should never use Task.Factory.StartNew with async-await. Use Task.Run instead. But not for async over sync, which is also a bad practice.

If you work with DbCommand instead of IDbCommand you already have ExecuteNonQueryAsync.

Upvotes: 2

William Xifaras
William Xifaras

Reputation: 5312

I suggest you use Dapper. No reason to write all that boilerplate ADO.NET code and worry about whether you may change providers. It also supports async operations across the board.

Dapper has no DB specific implementation details, it works across all .NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server.

Tutorial

https://dapper-tutorial.net/async

Upvotes: 1

Related Questions