Dataset Sometimes has no tables after Fill

Question

We have a winforms application calling a stored procedure every few seconds. The stored procedure always returns a resultset with 0 or more rows and the client fills a dataset. Once every few days or so we are finding that the dataset has no tables in it, and we can't figure out why. The process of firing the stored procedure does happen in a thread, but the database connection and execution of the stored procedure all happens within that same thread, so there isn't any data being passed between threads here.

Has anyone experienced behavior like this before?

Here is the relevant code, it's part of a larger application, so I've tried to include only the relevant bits.

//Used by the main application to poll a database in a thread
public class Poller
{
    private Thread thProcess_m;
    private readonly Action<Exception> actOnError_m;
    private readonly RuntimeData pRuntime_m;
    private readonly DataAccessLayer dalDB_m;

    private bool bRetry_m;

    public Poller(RuntimeData pData, Action<Exception> actOnError)
    {
        this.thProcess_m = new Thread(Main);
        this.thProcess_m.Name = "Main ScriptOr Polling Thread";

        this.actOnError_m = actOnError;
        this.dalDB_m = new DataAccessLayer(ConfigurationState.ConnectionStrings.DB);
    }

    public void Start()
    {
        this.thProcess_m.Start();
    }

    protected override void Main()
    {
        while (this.pRuntime_m.Running)
        {
            try
            {
                int iQueued;
                Task pTask = this.dalDB_m.GetNextTask(out iQueued);
            }
            catch (Exception ex)
            {
                this.actOnError_m(ex);
            }
        }
    }
}

public class RuntimeData
{
    private bool bRunning_m;
    public bool Running
    {
        get
        {
            return bRunning_m;
        }
        set
        {
            bRunning_m = value;
        }
    }
}

public class DataAcessLayer
{
    public Task GetNextTask(out int iQueued)
    {
        iQueued = 0;
        Task tskNext = null;

        SqlCommand cmdNextTask = new SqlCommand();
     cmdNextTask.CommandType = System.Data.CommandType.StoredProcedure;
        cmdNextTask.CommandText = "pGetNextTask";        

        cmdNextTask.Connection = new System.Data.SqlClient.SqlConnection();
     cmdNextTask.Connection.ConnectionString = "my connection string";
        cmdNextTask.Connection.Open();

        DataSet dsNextTask = new DataSet();

        try
        {
            System.Data.SqlClient.SqlDataAdapter sqlNextTask = new System.Data.SqlClient.SqlDataAdapter(cmdNextTask);            
         sqlNextTask.Fill(dsNextTask);
        }
        finally
        {
            cmdNextTask.Connection.Close();
        }

        tskNext = LoadTask(dsNextTask);
        if (dsTask.Tables[0].Rows.Count > 0)
        {
            iQueued = (int)dsTask.Tables[0].Rows[0]["Queued"];
        }
        return tskNext;
    }

    protected Task LoadTask(DataSet dsTask)
    {
        Task tskNext = null;

        if (dsTask == null)
        {
            throw new ArgumentNullException("LoadTask DataSet is null.");
        }

        if (dsTask.Tables == null)
        {
            throw new NullReferenceException("LoadTask DataSet.Tables is null.");
        }

        //Here's where the exception is being thrown
        if (dsTask.Tables.Count == 0)
        {
            throw new ArgumentOutOfRangeException("LoadTask DataSet.Tables.Count == 0.");
        }

        if (dsTask.Tables[0].Rows.Count > 0)
        {
            DataRow drTask = dsTask.Tables[0].Rows[0];

            tskNext = new InteriorHealth.ScriptOr.ScriptTask((int)(drTask["id"]));
            tskNext.Name = pRow["Name"].ToString();
        }
        return pTask;
    }
}

Answer 1

One possibility where something similar has happened to me is where the stored procedure call doesn't return at all. If it timed out, it wouldn't return anything and the process wouldn't reach the point of filling the dataset at all. But without seeing some sample code of what you're trying to do it would be difficult to offer a valid answer.

Answer 2

The only times I've ever experience something like this happening to me were when I had error handling logic in my stored procedure that wasn't so great at handling errors.

For instance:

IF EXISTS(SELECT * FROM SOMEWHERE WHERE MYTHING = @WHATIWANT)
BEGIN
   //select stuff and do weird things
END

IF @@ERROR <> 0
BEGIN
  SET @MYWEIRDERRORTRAPPER = @ERROR
END

In this case you basically swallow the exception in the stored procedure. This is a really bad practice, IMO, but I've inherited code like this in the past.

Answer 3

Exactly how certain are you that this.actOnError_m(ex) is never called?

Also, you're not properly handling IDisposable, which is one of the first things I look for when things start getting strange. Try this:

public Task GetNextTask(out int iQueued)
{
    iQueued = 0;

    Task tskNext;
    using (SqlCommand cmdNextTask = new SqlCommand())
    {
        cmdNextTask.CommandType = System.Data.CommandType.StoredProcedure;
        cmdNextTask.CommandText = "pGetNextTask";

        using (SqlConnection connection = new System.Data.SqlClient.SqlConnection())
        {
            cmdNextTask.Connection = connection;
            cmdNextTask.Connection.ConnectionString = "my connection string";
            cmdNextTask.Connection.Open();

            using (DataSet dsNextTask = new DataSet())
            {
                using (
                    System.Data.SqlClient.SqlDataAdapter sqlNextTask =
                        new System.Data.SqlClient.SqlDataAdapter(
                            cmdNextTask))
                {
                    sqlNextTask.Fill(dsNextTask);
                }

                tskNext = LoadTask(dsNextTask);
                if (dsNextTask.Tables[0].Rows.Count > 0)
                {
                    iQueued = (int) dsNextTask.Tables[0].Rows[0]["Queued"];
                }
            }
        }
    }
    return tskNext;
}

Not that I think this is the solution, but it's one less thing to worry about.

Answer 4

I moved the instantiation of DataAccessLayer out of the constructor and into the Main() method. I haven't seen the problem since, so I am thinking it solved the problem. I think the issue was that the constructor happens in the main application thread, so the DataAccessLayer instance was outside of the thread. Moving the instantiation into the Main() method allows the thread to own the object.