Async ADO.NET

[Chicagoan2016]

Hi fellow developers,
Has anyone converted their ADO.NET data access code to Async version e.g. Use ExecuteReaderAsync() instead of ExecuteReader() among many other changes, maybe even use Cancellation tokens?

Regards

[StefanOssendorf]

We are using async with EFCore and it's working okay. Async rules make it a bit tricky because you have to either make the ef core operation thread safe (aware) or wait after each property with an async rule attached to ensure not concurrent access.

I'd love to use cancellation tokens but I haven't had the time yet to come up with a proper solution to distinguish between an operation which should be cancelled when the request is cancelled/closed or must run until it's finished even though the caller doesn't get the result.

[tevinch]

Keep the whole call chain asynchronous: the caller awaits IDataPortal<T>.FetchAsync, the attributed [Fetch] method returns async Task and awaits the DAL, and the DAL awaits OpenConnectionAsync, ExecuteReaderAsync and each ReadAsync.

Here is a complete console example with CSLA 10.2.0 and Npgsql 10.0.3. It returns a populated read-only CSLA list, preserves database nulls, and opens a separate connection per operation from a shared data source. Its runnable PostgreSQL checks cover delayed query completion, zero/one/multiple rows, provider errors and a successful later fetch. They also exercise the DAL directly, since the local console portal's internal scheduling alone does not establish asynchronous provider IO.

For the optional cancellation part, the DAL accepts a token and passes it to those provider operations. The example verifies cancellation of an already-running PostgreSQL query and a successful subsequent fetch. It deliberately leaves that token at the DAL boundary: it does not propagate tokens through a remote data portal or choose which business operations must finish after a caller disconnects.

This uses native NpgsqlDataReader with explicit IsDBNull handling; it does not add async methods to SafeDataReader. Other providers and applications with async business rules need their own provider/concurrency checks.

[rockfordlhotka]

I agree with keeping the chain async - on the logical server.

Remember that the data portal itself, as it switches from client to server and back, has its own rules about what needs to be async.

@StefanOssendorf is a big proponent of everything being async, which I generally agree with. However, a lot of folks have WinForms or older WPF apps where switching the client to async can be a lot of work. Because the data portal logically separates the client from the server though, even those apps can use async on the server without triggering the big changes that would be required on the client.

[Chicagoan2016]

Thanks everyone. I am going to look at @tevinch example over the weekend.