DataTable 'Table' already belongs to this DataSet - on a new DataSet?

Question

I have a function that executes SQL queries and returns a generic DataSet of the query results. This function has been working for years in many applications, but today with a specific query I am getting

"Error executing [select top (1) RecordId, SourceCID, SourceID, CaseID, DisposeRequestedDate, DisposeRequestedBy, DisposeApprovedDate, DisposeApprovedBy from mycatalog.dbo.CASD_RetentionManagementDisposalApprovedQueue order by DisposeApprovedDate, RecordID;] A DataTable named 'Table' already belongs to this DataSet."

The function is

    public static DataSet ExecSQLQuery(string sqlQuery)
    {
      mDataSet = new DataSet();
      ...  // setup, but NO references to mDataSet
      try {
         ...  // establish, open connection as "conn"
         mDataAdapter = new SqlDataAdapter(new SqlCommand(sqlQuery, conn));
         mDataAdapter.Fill(mDataSet);
      } catch (Exception ex) {
         //  this traps "A DataTable named 'Table' already belongs to this DataSet."
         Log("Error executing [" + sql + "]" + ex.message);
      }
      finally {
         if (conn != null)
         {
            if (conn.State == ConnectionState.Open)
               conn.Close();
            conn.Dispose();
         }
      }
      return mDataSet;
    }

The calling procedure is basically:

public static ProcessResult ProcessDestructQueue()
{
    // maxRecords is a configuration setting, currently set to 1
            string sql = "select top (" + maxRecords.ToString() + ") RecordId, SourceCID, SourceID, CaseID, DisposeRequestedDate, DisposeRequestedBy, DisposeApprovedDate, DisposeApprovedBy " +
                " from mycatalog.dbo.CASD_RetentionManagementDisposalApprovedQueue " +
                " order by DisposeApprovedDate, RecordID;";
            DataSet ds = null;
    try {
        sWhere = "exec query [ " + sql + " ]";
        ds = SQLUtility.ExecSQLQuery(sql);
        if (!SQLUtility.IsValidDataSet(ds))
        {
            if (!SQLUtility.IsValidButEmptyDataSet(ds))
                throw new Exception("Failed to get a valid data set with [ " + sql + " ]");
            //
            //  no records to process
            //
            return ProcessResult.NOFILESTOPROCESS;
        }
    }
    catch (...) { ... }
    finally {
       if (ds != null)
         ds.Dispose();
    }
    ...
    }

When my configuration parameter is set to 1 (Select top (1) ...), I get the error. When set to 2, (Select top (2) ...), the error does not occur. Is there something unique about "Select top (1)..." or am I doing something wrong? BTW - the table is currently empty, so neither top 1 nor top 2 should return any rows.

As requested - the IsValidDataSet and IsValidButEmptyDataSet methods:

public static bool IsValidDataSet(DataSet ds)
{
    if (ds != null)
        if (ds.Tables.Count > 0)
            if (ds.Tables[0] != null)
                if (ds.Tables[0].Rows.Count > 0)
                    if (ds.Tables[0].Rows[0] != null)
                        if (ds.Tables[0].Rows[0].ItemArray.Length > 0)
                            return true;
    return false;
}
public static bool IsValidButEmptyDataSet(DataSet ds)
{
    if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
        return true;
    return false;
}

As requested - Stack Trace from error: Stack trace: at System.Data.DataTableCollection.RegisterName(String name, String tbNamespace) at System.Data.DataTableCollection.BaseAdd(DataTable table) at System.Data.DataTableCollection.Add(DataTable table) at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithoutKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue) at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at SQLUtility.ExecSQLQuery(String sqlQuery, Boolean ignoreError)

Is maxRecords used anywhere else? What happens right after the call to ExecSQLQuery(in the try block)? Can you post a full, minified example? — Adam
I filled in all relevant code around the two parts. After the call to ExecSQLQuery, I call two functions that tell me whether I have a valid (and non-empty) result, or a valid result, but no rows returned. maxRecords is used in another similar query (against a different table, with different columns). The other query correctly returns with no errors. The error is thrown inside the ExecSQLQuery function - how would code after that return affect that error? — Kim Crosser
Just in case it has any relevance, this code is in a Service on Windows Server 2016, SQL Server 2016 - not in a Windows desktop client application. — Kim Crosser
Please show the other method bodies(IsValidDataSet and IsValidButEmptyDataSet). Also, what is the full stack trace? — Adam
I will need to trap and output the stack trace (tomorrow) - this is a Service running on a remote server. The other methods are now in the original post. — Kim Crosser

Kim Crosser Kim Crosser · Accepted Answer · 2020-11-11T21:16:26

I believe I have figured out what is happening.
My ExecSQLQuery method is not thread-safe (it is a public static method), but I have two independent timers running. If timer 2 fires while the timer 1 process sequence is currently executing inside this method, the second can wind up making a call to this same procedure. When the timer 2 process terminates, the first code resumes, BUT the local variables are now already loaded, resulting in the "'Table' already belongs to the DataSet" error. I added a lot of diagnostic code and found that this was happening only after a lot of executions from both timers firing. I will add a semaphore to prevent one process from executing while the other is already executing.

DataTable 'Table' already belongs to this DataSet - on a new DataSet?

1 Answers