How to create an asynchronous method with ADO.NET

Question

Anubhav Kumar · Answer

To create an asynchronous method with ADO.NET, you can use the asynchronous methods provided by the ADO.NET classes, such as
ExecuteReaderAsync, ExecuteNonQueryAsync, and ExecuteScalarAsync for commands, as well as
OpenAsync for connections.
Example: Asynchronous Method to Fetch Data
This example demonstrates how to fetch data from a database using SqlConnection and
SqlCommand.
Code Example:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
public class DatabaseHelper
{
    private readonly string _connectionString;
    public DatabaseHelper(string connectionString)
    {
        _connectionString = connectionString;
    }
    public async Taskandlt;DataTableandgt; GetDataAsync(string query)
    {
        var dataTable = new DataTable();
        try
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // Open connection asynchronously
                await connection.OpenAsync();
                using (var command = new SqlCommand(query, connection))
                {
                    // Execute the command asynchronously
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        // Load the data into a DataTable
                        dataTable.Load(reader);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            // Handle exceptions (log or rethrow as needed)
            Console.WriteLine($"An error occurred: {ex.Message}");
            throw;
        }
        return dataTable;
    }
}
Explanation
SqlConnection.OpenAsync()
Opens the database connection asynchronously to avoid blocking the calling thread.
SqlCommand.ExecuteReaderAsync()
Executes the SQL query asynchronously and returns a DbDataReader.
DataTable.Load()
Loads the data from the DbDataReader into a DataTable.
and 
Example 2:
public static async Task Main(string[] args)
{
    var connectionString = "your-database-connection-string";
    var query = "SELECT * FROM YourTable";
    var dbHelper = new DatabaseHelper(connectionString);
    var data = await dbHelper.GetDataAsync(query);
    // Display the fetched data
    foreach (DataRow row in data.Rows)
    {
        Console.WriteLine(string.Join(", ", row.ItemArray));
    }
}
Notes:
Ensure your database connection string is configured properly.Always use using blocks or explicitly dispose IDisposable resources (e.g.,
SqlConnection and SqlCommand) to prevent resource leaks.Use exception handling to catch and log errors.Ensure your database supports asynchronous operations; older versions might not fully support it.
This approach prevents blocking the main thread, which is vital in applications with UI or high concurrency.

Tanisha Soni · Answer

One of the most essential skills when developing applications with scalable architecture in.NET is asynchronous programming. The asynchronous operation is also supported by ADO.NET where performance improvement is essential, such as during data access. Below is a structured guide to creating an asynchronous method with ADO.NET:
1. Understanding Asynchronous Programming
Non-blocking methods make it possible for applications to undertake other tasks while waiting for a database operation to take place. This is particularly useful in applications that rely on UI that is responsive or dealing with a high concurrency, such as Web applications.
2. Advantages of Asynchronous ADO.NET
Improved Scalability: reduction of the number of threads seeking the same request, thus allowing more requests at once.Enhanced Responsiveness: Does not keep the UI thread locked while testing desktop or mobile applications.Efficient Resource Utilization: Helps in freeing other operations during I/O waiting time by reducing their resource holding time.
3. Asynchronous Methods of ADO.NET Sections
a. Async/Await Pattern
This pattern is used to write code that is non-blocking but flows linearly and is easy to read. The async keyword signifies a method as an asynchronous one, and await makes a pause on the execution until a task is accomplished.
b. Asynchronous Technologies in ADO.NET
ADO.NET provides asynchronous counterparts for common methods:
There is also OpenAsync when opening a database connection.ExecuteReaderAsync for the execution of a query and getting the data.SqlCommand: ExecuteNonQueryAsync for executing commands that don’t return anything.ExecuteScalarAsync of ADO for getting a number.
4. Asynchronous Method Creation Process
a. Define the Async Method
Public asynchronously from get list string customer names
{
Listandlt;stringandgt; customerNames new();
and  and  string connectionString = ”Your Connection String”;
string query = “SELECT Name FROM Customers”;
as SqlConnection connection = new SqlConnection(connectionString);
{
and  and  and  and and expect connection.OpenAsync();
During using (SqlCommand command = new SqlCommand(query, connection))
{
if ((SqlDataReader reader = await command.ExecuteReaderAsync()))and 
{
as long as we await reader. ReadAsync() // Reads a row asynchronously
{and reader.GetInt32(0);and 
and  and  and  and  and  and  and customerNames.Add(reader.GetString(0)); }}}}
and  and  return customerNames;}
b. Initialize the Connection
Use the SqlConnection object for connection management and call OpenAsync:
as follows using (SqlConnection connection= new SqlConnection(connectionString))
{ before connection.OpenAsync(); }
c. Commands must also be executed asynchronously.
Execute database commands using asynchronous methods:
For retrieving data:
Using SqlCommand cmd = New SqlCommand(query, connection)
{ awaiting (SqlDataReader reader = await command.ExecuteReaderAsync())
{
That can be done while (await reader). ReadAsync())
{System.out.println(((String)reader.get("Name"))); and  }}}
For insert/update/delete:
with (SqlCommand command = new SqlCommand(String.Format(@”
INSERT INTO Customers (Name) VALUES (@name")
]), connection))
{
command.Parameters.AddWithValue("@name", "sharvari");
int rowsAffected = command. ExecuteNonQuery();
Console. WriteLine($"{rowsAffected} row(s) inserted.”;
}
For retrieving a single value:
using (SqlCommand command = new SqlCommand(count_query, connection))
{
int count = (int)await command. ExecuteScalarAsync(); asyncronously
Console.WriteLine($"Total Customers: {count}");
}
a. Define the Async Method
Begin the marking of the method with the async keyword. For example, using async Task or async Taskandlt;Tandgt; as a return type means it will be an asynchronous method.
b. Initialize the Connection
Perform all the operations in the middle tier using a DbConnection object such as SqlConnection to connect to the database. To open the connection it is required to call the OpenAsync method.
c. Command Execution Asynchronously
Make use of the other methods of asynchronous that are being provided in the DbCommand object. Depending on the command for the operation, implement the use of ExecuteReaderAsync, ExecuteNonQueryAsync, and ExecuteScalarAsync.
5. Best Practices
Connection Management: Using statements must be employed to check whether connections are closed.Cancellation Tokens: Support cancellation to invite users to cancel lengthy operations undergoing in the program.Error Handling: When dealing with transient failures, the best approach should be to incorporate sound measures for managing error circumstances.
6. Conclusion
In ADO.NET, asynchronous methods have a positive influence on the overall performance of contemporary.NET applications. With the use of async and await patterns in combination with async methods used in ADO.NET, programmers can design relevant and non-susceptible to blocking database calls.

forum

How to create an asynchronous method with ADO.NET

Revati S Misra

Can you answer this question?

2 Answers

Example: Asynchronous Method to Fetch Data

Explanation

Example 2:

Notes:

Liked By