C# - Asynchronous Data Access base class to retrieve data from the database without blocking the caller

Post date: Mar 26, 2012 9:46:47 PM

You want to have a GUI that is responsive even while performing data accessing, unless that GUI blocking is intended for some reason.

At the same time you want to easily do data access and not think about asynchronous problems.

This library is meant to allow easy creation of such GUI applications that accesses the database.

The idea is to create a library that can be used to asynchronously get data from the database without blocking the caller.

The library should be as easy to use as any data access library.

We will rely on callback methods to get the result back from the library, instead of events or any other method.

A few things to do is:

    1. Provide a method of canceling a running asynchronous call.
    2. Provide some method of using SqlParameters instead of concatenating strings.

using System;using System.Data.SqlClient;using System.Collections.ObjectModel;namespace AsyncronousDataAccess.DataAccess { public class BaseDataAccess { /// <summary> /// Asyncronously get one of T using the supplied <paramref name="sql"/> query string. /// </summary> /// <typeparam name="T">The type that will be retrieved</typeparam> /// <param name="sql">the query string to get the entity</param> /// <param name="itemBuilder">supply the Func that will be used to create one T by reading the fields from a SqlDataReader. The Func should return the created entity.</param> /// <param name="completedCallback">The Action which will be called when the retreiving of the entity is done</param> protected void BeginGetOne<T>(string sql, Func<SqlDataReader, T> itemBuilder, Action<T> completedCallback) { Action a = new Action(() => { T m = GetOne<T>(sql, itemBuilder); completedCallback(m); }); a.BeginInvoke(null, null); } /// <summary> /// Asyncronously get many of T using the supplied <paramref name="sql"/> query string. /// </summary> /// <typeparam name="T">The type that will be retrieved</typeparam> /// <param name="sql">the query string to get the entities</param> /// <param name="itemBuilder">supply the Func that will be used to create one T by reading the fields from a SqlDataReader. The Func should return the created entity.</param> /// <param name="completedCallback">The Action which will be called when the retreiving of the entities is done. /// The parameter to <paramref name="completedCallback"/> action will be the populated observerable collection of T</param> protected void BeginGetMany<T>(string sql, Func<SqlDataReader, T> itemBuilder, Action<ObservableCollection<T>> completedCallback) { Action a = new Action(() => { ObservableCollection<T> items = GetMany(sql, itemBuilder); completedCallback(items); }); a.BeginInvoke(null, null); } protected T GetOne<T>(string sql, Func<SqlDataReader, T> itemBuilder) { using (SqlConnection con = new SqlConnection("")) { using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { return itemBuilder(reader); } } } return default(T); } protected ObservableCollection<T> GetMany<T>(string sql, Func<SqlDataReader, T> itemBuilder) { ObservableCollection<T> items = new ObservableCollection<T>(); using (SqlConnection con = new SqlConnection("")) { using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { items.Add(itemBuilder(reader)); } } } return items; } }}

This is an example of how to create a Data Access class to get Customers using the Asynchronous library

using System;using AsyncronousDataAccess.Entitites;using System.Collections.ObjectModel;using System.Data.SqlClient;namespace AsyncronousDataAccess.DataAccess { public class CustomerDataAccess : BaseDataAccess { private Func<SqlDataReader, Customer> CreateCustomer = reader => { return new Customer { CustomerId = reader.GetInt32(0), Name = reader.GetString(1), DateCreated = reader.GetDateTime(2) }; }; public void BeginGetAllCustomers(Action<ObservableCollection<Customer>> callback) { BeginGetMany("select CustomerId, Name, DateCreated from Customer", CreateCustomer, callback); } public void BeginGetCustomerById(int customerId, Action<Customer> callback) { BeginGetOne("select CustomerId, Name, DateCreated from Customer where CustomerId = 1 ", CreateCustomer, callback); } }}

Example ViewModel that uses the CustomerDataAccess asynchronous methods

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.ComponentModel;using System.Collections.ObjectModel;using AsyncronousDataAccess.Entitites;using AsyncronousDataAccess.DataAccess;namespace AsyncronousDataAccess { public class MainWindowViewModel : INotifyPropertyChanged { private ObservableCollection<Customer> _customers; public ObservableCollection<Customer> Customers { get { return _customers; } set { _customers = value; OnPropertyChanged("Customers"); } } public void LoadCustomers() { // Call and do not wait for the result. The result will come to us! CustomerDataAccess da = new CustomerDataAccess(); da.BeginGetAllCustomers(custsResult => { Customers = custsResult; }); } private void OnPropertyChanged(string propertyName) { if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(propertyName)); } } public event PropertyChangedEventHandler PropertyChanged; }}