J. Schmidt J. Schmidt - 3 months ago 45
C# Question

Designing an async data provider with BeginExecuteReader in C#

I am new to the async/await model in C# 5 so you will have to be patient with my ignorance/lack of understanding.

I would like to upgrade my Singleton designed data provider to perform an asynchronous call to a stored procedure then return the data using BeginDataReader and EndDataReader methods in the System.Data namespace.

Here is an example of the structure that I am trying to build but it is not waiting for the data to return:

public class DataProvider{
private static DataProvider instance;
public static DataProvider Instance
{
get
{
if (instance == null)
{
lock (typeof(DataProvider))
{
instance = new DataProvider();
}
}
return instance;
}
}

public virtual async void ExecuteDataReaderAsync(string StoredProcedureName, AsyncCallback callback, params object[] Parameters)
{
InitDatabase();
var connection = new SqlConnection(databaseControllers[connectionStringName].ConnectionString);
var cmd = new SqlCommand();

cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = dbPrefixName + StoredProcedureName;
await connection.OpenAsync();

SqlCommandBuilder.DeriveParameters(cmd);
if (cmd.Parameters.Count - 1 > Parameters.Count())
throw new InvalidOperationException("The number of parameters provided does not match the number of parameters in the stored procedure. There are " + Parameters.Count().ToString() + " parameters provided, however the stored procedure requires " + cmd.Parameters.Count.ToString() + " parameters.");

for (int i = 0; i < Parameters.Count(); i++)
{
cmd.Parameters[i + 1].Value = Parameters[i];
}

cmd.BeginExecuteReader(new AsyncCallback(callback), cmd);
}
}

public class SubDataProvider : DataProvider
{
private static volatile SubDataProvider instance = new SubDataProvider();
public static SubDataProvider Instance
{
get
{
if (instance == null)
{
lock (typeof(SubDataProvider))
{
if (instance == null)
instance = new SubDataProvider();
}
}
return instance;
}
}

////
//// THIS IS WHERE I GET LOST
////

public async Task<List<Models.MyData>> GetDataAsync(bool IncludeDeleted = false)
{
List<Models.MyData> temp = new List<MyData>();
ExecuteDataReaderAsync("GetData", delegate (IAsyncResult result)
{

var database = (SqlCommand)result.AsyncState;

using (IDataReader reader = database.EndExecuteReader(result))
{
while (reader.Read())
{
temp.Add(FillData(reader));
}

}

if (database.Connection.State == ConnectionState.Open)
database.Connection.Close();

}, false);
return temp;
}
}


public class BusinessController
{
private static volatile BusinessController _instance = new BusinessController();

public static BusinessController Instance
{
get
{
if (_instance == null)
{
lock (typeof (BusinessController))
{
_instance = new BusinessController();
}
}
return _instance;
}
}

public async Task<List<Models.MyData>> GetAllAsync(bool IncludeDeleted = false)
{
return await SubDataProvider.Instance.GetDataAsync(IncludeDeleted);;
}
}


// DEMO
internal class Program
{
private static void Main(string[] args)
{
var x = BusinessController.Instance.GetAllAsync(false);
}
}


My ultimate goal is to get this data back to an asynchronous WebApi but I currently am stuck at not getting any data back. The temp variable gets filled but it never actually returns the filled object.

Where am I going wrong?

Thank you so much in advance!

Answer

I don't have your exact codebase here but something like this would be close to what you need:

Inside class DataProvider I renamed ExecuteDataReaderAsync to GetDataReaderAsync

public virtual async Task<IDataReader> GetReaderAsync(string StoredProcedureName, params object[] Parameters)
{
    InitDatabase();
    var connection = new SqlConnection(databaseControllers[connectionStringName].ConnectionString);
    var cmd = new SqlCommand
    {
        Connection = connection,
        CommandType = CommandType.StoredProcedure,
        CommandText = dbPrefixName + StoredProcedureName
    };

    await connection.OpenAsync();

    SqlCommandBuilder.DeriveParameters(cmd);
    if (cmd.Parameters.Count - 1 > Parameters.Count())
        throw new InvalidOperationException("The number of parameters provided does not match the number of parameters in the stored procedure. There are " + Parameters.Count().ToString() + " parameters provided, however the stored procedure requires " + cmd.Parameters.Count.ToString() + " parameters.");

    for (int i = 0; i < Parameters.Count(); i++)
    {
        cmd.Parameters[i + 1].Value = Parameters[i];
    }

    var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
    return reader;
}

And used the reader in GetDataAsync inside class SubDataProvider

public async Task<List<MyData>> GetDataAsync(bool IncludeDeleted = false)
{
    List<MyData> temp = new List<MyData>();
    using (var reader = await GetReaderAsync("GetData"))
    {
        while (reader.Read())
        {
            temp.Add(FillData(reader));
        }
    }
    return temp;
}

I can't test the solution with what I have here but if you opt for sending back a SqlDataReader instead of a IDataReader, you'd have a ReadAsync() method at your disposal too. But since you already have an async method for that now, it would be just fine to use reader.Read(). I ditched the callback as it's better to stay with the Task-Asynchronous Paradigm here.

Hope this helps. :)

Update

From @Scott Chamberlain in the comments, one other suggestion would be to keep the reader in DbDataReader level instead of IDataReader so you can still be generic and have access to methods like ReadAsync(). The suggestion deemed worthy, thus added in the answer here.