Execute stored procedures in parallel

Question

I have these 2 methods

public DataTable GetData1(int Id)
{
    DataTable dt = new DataTable();

    using (SqlConnection sqlcon = new SqlConnection(database.Connection.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("spGetData1", sqlcon))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter() { ParameterName = "@id", Value = Id});

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        }
    }

    return dt;
}

public DataTable GetData2(int Id)
{
    DataTable dt = new DataTable();

    using (SqlConnection sqlcon = new SqlConnection(database.Connection.ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("spGetData2", sqlcon))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter() { ParameterName = "@id", Value = Id});

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        }
    }
    return dt;
}

and I would like to execute them at once and also get the data for further processing.

I tried something like

var task1 = Task.Factory.StartNew(() => database.Data.GetData1(1));
var task2 = Task.Factory.StartNew(() => database.Data.GetData2(2));

var taskList = new List<Task> { task1, task2 };

Task.WaitAll(taskList.ToArray());

but on the last line it crashes with

there is one or more errors.`

The inner exception is

Object reference not set to an instance of an object.

Stack trace

at System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)

The connectionString is obtain from System.Data.Entity.DbContext.Database class

 public class DatabaseRepository : IDisposable
    {
        DbContext dbContext;

        public DatabaseRepository()
        {
             dbContext = new DbContext("connection string ...");
             Data = new DataRepository(dbContext.Database);

        }
        public DataRepository Data { get; set; }
}

but the error is the same even i set connection string manually, so i dont think the error is here.

   using (SqlConnection sqlcon = new SqlConnection("connection string ..."))
   {
      using (SqlCommand cmd = new SqlCommand("spGetData2", sqlcon))
      {
       ...
      }
   }

How can I do that? I see some examples use Async return type, but I don't want to duplicate these methods.

Answer 1

The database.Connection.ConnectionString is a static string otherwise you cant compile due to a "An object reference is required for the non-static field, method, or property".

With that in mind, its not the Connection String that's un-intstatiated because its static... and even if you purposely initialized the static string to Null then the error message would be:

InnerException = {"The ConnectionString property has not been initialized."}

Here is a repro and the error cannot be produced unless your GetData Methods are in empty objects:

namespace database
{
public class Program
{
    static void Main(string[] args)
    {
        //WORKS!!
        var repro = new database.Data();
        var task1 = Task.Factory.StartNew(() => repro.GetData1(3));
        var task2 = Task.Factory.StartNew(() => repro.GetData2(5));
        var taskList = new List<Task> { task1, task2 };
        Task.WaitAll(taskList.ToArray());

        //FAILS WITH ERROR REPORTED!!
        repro = null;
        var task1 = Task.Factory.StartNew(() => repro.GetData1(3));
        var task2 = Task.Factory.StartNew(() => repro.GetData2(5));
        var taskList = new List<Task> { task1, task2 };
        Task.WaitAll(taskList.ToArray());
    }
}

class Data
{
    private string connectionString = "Server=.;Database=CRUD_Sample;Integrated Security=True;Asynchronous Processing = True;";
    public DataTable GetData1(int Id)
    {
        DataTable dt = new DataTable();
        using (SqlConnection sqlcon = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("Get_CustomerbyID", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@id", Value = Id });
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
            }
        }
        return dt;
    }

    public DataTable GetData2(int Id)
    {
        DataTable dt = new DataTable();
        using (SqlConnection sqlcon = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("Get_CustomerbyID", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@id", Value = Id });
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
            }
        }
        return dt;
    }
}
}

Debugging

How do you find the source of a NullReferenceException? Apart from looking at the exception itself - the key is a NRE will be thrown exactly at the location where it occurs then you hover your mouse over the variables on the Line Of Code and see which object is null.