TPL Tasks & Stored procedures

问题:

I was wondering if it is possible to call a number of different stored procedures with the same parameters asynchronously by using tasks and then waiting for all the results to return.

I have the following:

private Task<DataTable> DataBaseCall(string procedureName, params Pair[] where)
    {
        DataTable data = new DataTable();
        SqlConnection connection = new SqlConnection(connStr);

        SqlCommand command = new SqlCommand(procedureName, connection);
        connection.Open();

        for (int i = 0; i < where.Length; i++)
        {
            command.Parameters.Add(where[i].First.ToString(), where[i].Second.ToString());
        }

        var readerTask = Task<SqlDataReader>.Factory.FromAsync(command.BeginExecuteReader, command.EndExecuteReader, null);
        return readerTask.ContinueWith(t =>
            {
                var reader = t.Result;
                try
                {
                    reader.Read();
                    data.Load(reader);
                    return data;
                }
                finally
                {
                    reader.Dispose();
                    command.Connection.Close();
                    command.Connection.Dispose();
                    command.Dispose();
                }
            });
    }

Which I call with:

private void SetReportVariables(string reportName, string[] storedProcedureName, string _clientGroup, string _clientCode, string _finYear, string _period)
    {
       Task[] tasks = new Task[storedProcedureName.Length];

        for (int i = 0; i < storedProcedureName.Length; i++)
        {
            List<Pair> parameters = new List<Pair>();
            parameters.Add(new Pair("@ClientGroup", _clientGroup));
            parameters.Add(new Pair("@ClientCode", _clientCode));
            parameters.Add(new Pair("@FinYear", _finYear));

            tasks[i] = DataBaseCall(storedProcedureName[i], parameters.ToArray());
        }
        Task.WaitAll(tasks);

        ...........Do something with the DataTables.........
    }

I have three questions.

1. Can anyone tell me if this is a good way to do this?
2. Any idea's why my _finYear variable seems sometimes be omitted, which causes an error.
3. Can I return the datatable from the task?

Thanks

Mike

回答1:

1. There's nothing fundamentally wrong with this approach.
2. You don't show where _finYear comes from in your code, but based on the code that you do show I don't see any reason for it to not be passed through correctly to the sproc.
3. Sure you can return the DataTable like that. It's not safe to access concurrently from multiple threads, but it can be handed across threads no problem.

The only minor bug in your code is that you should have another try/finally in your continuation handling logic because it's possible for t.Result to throw an exception if there was a problem with the async call to Begin/EndExecuteReader and that would leave you not disposing of the command and connection. So this would be better:

readerTask.ContinueWith(t =>            
{                
    try
    {
        var reader = t.Result;                

        try                
        {                    
            reader.Read();
            data.Load(reader);

            return data;
        }
        finally
        {                    
            reader.Dispose();
        }            
    }
    finally
    {
        command.Connection.Close();
        command.Connection.Dispose();
        command.Dispose();                
    }
});