Return DataTable using async .net 4.0

Question

I have a method that returns a datatable. I thought using .net 4.0 I could just async logic and return data. But this code returns null Datatable object. Any ideas what is wrong with this code.

public DataTable GetData(string sql, string connectionName)
{
    DataTable dt = (DataTable)GetDataAsync(sql, connectionName).AsyncState;
    return dt;
}

private async Task<DataTable> GetDataAsync(string sql, string connectionName)
{
    return await TaskEx.Run(() => { return FillData(sql, connectionName); });
}

private DataTable FillData(string sql, string connectionName)
{
    SqlConnection conn = _connections.Where(w => w.ConnectionName == connectionName).Single().Connection;
    SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
    DataSet ds = new DataSet();

    adp.Fill(ds);

    return ds.Tables[0];
}

Answer 1

Firstly, you can't use async / await with .NET 4 or C# 4. It's a new feature in C# 5. There were CTPs which installed on top of .NET 4, but there are definite bugs in those CTPs - don't use them. You should use the full release version of .NET 4.5, which includes the C# 5 compiler. (All this is in Visual Studio 2012.)

Secondly, you're using the wrong property of the task, as Cuong Le showed. The Result property is how you get at the result of a Task<T>.

Thirdly, after making the change to use the Result property, you'd be blocking for the table to be fetched - making it pointless. This:

public DataTable GetData(string sql, string connectionName)
{
    DataTable dt = (DataTable)GetDataAsync(sql, connectionName).Result;
    return dt;
}

... is largely equivalent to:

public DataTable GetData(string sql, string connectionName)
{
    return FillData(sql, connectionName);
}

If you're going to start a task and immediately wait on it, you might as well just call the method synchronously.

Answer 2

My own source code.

public static async Task<DataTable> GetDataTableAsync(this System.Data.Common.DbCommand command, CancellationToken cancellationToken, string tableName = null)
    {
        TaskCompletionSource<DataTable> source = new TaskCompletionSource<DataTable>();
        var resultTable = new DataTable(tableName ?? command.CommandText);
        DbDataReader dataReader = null;

        if (cancellationToken.IsCancellationRequested == true)
        {
            source.SetCanceled();

            await source.Task;
        }

        try
        {
            await command.Connection.OpenAsync();
            dataReader = await command.ExecuteReaderAsync(CommandBehavior.Default);
            resultTable.Load(dataReader);
            source.SetResult(resultTable);
        }
        catch (Exception ex)
        {
            source.SetException(ex);
        }
        finally
        {
            if (dataReader != null)
                dataReader.Close();

            command.Connection.Close();
        }

        return resultTable;
    }

Answer 3

If you want to use async code, then don't block on it. Also, make sure you're using the Async Targeting Pack and not an Async CTP.

private async Task<DataTable> GetDataAsync(string sql, string connectionName)
{
  return await TaskEx.Run(() => { return FillData(sql, connectionName); });
}

private async GetAndProcessDataAsync()
{
  DataTable table = await GetDataAsync("my sql", "my connection name");
  ProcessData(table);
}