Why can't I read all tables from a database into my DataSet?

Question

I'm trying to read all the tables of an MS-SQL database into a DataSet, which consists of a group of DataTables. For that, I have written this piece of source code:

try
{   
    DbConnection.Open();
    sqlCommand = DbConnection.CreateCommand();
    sqlCommand.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
    // INFORMATION_SCHEMA.TABLES contains the names of all the tables.

    sqlDataReader = sqlCommand.ExecuteReader();
    cmb_Table_Names.Items.Clear(); // combobox for keeping table names.
    while (sqlDataReader.Read())
    {
        string tmp_Table_Name = sqlDataReader.GetString(0);
        
        cmb_Table_Names.Items.Add(tmp_Table_Name);

        DataTable dt_tmp = new DataTable();
        using (var da = new SqlDataAdapter($"SELECT * FROM {tmp_Table_Name}", 
                                           DbConnection)) 
        {
          da.Fill(dt_tmp); // see "stackoverflow.com/questions/68919147"
        }
        dataSet.Tables.Add(dt_tmp);
    }
    sqlDataReader.Close();
}
catch (Exception ex)
{  
    MessageBox.Show(ex.Message, "Reading DB failed!!!", MessageBoxButton.OK);
}

I'm falling into my Exception at line da.Fill(dt_tmp);.
The result of ? ex in the immediate window is:

{"There is already an open DataReader associated with this Command which must be closed first."}
    Data: {System.Collections.ListDictionaryInternal}
    HResult: -2146233079
    HelpLink: null
    InnerException: null
    Message: "There is already an open DataReader associated with this Command which must be closed first."
    Source: "System.Data"
    StackTrace: "   at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)\r\n   
                    at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)\r\n   
                    at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)\r\n   
                    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n   
                    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   
                    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n   
                    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   
                    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n 
                    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   
                    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)\r\n   
                    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)\r\n   
                    at Database_Handling.MainWindow.Btn_Read_DB_Click(Object sender, RoutedEventArgs e) in <filename>
    TargetSite: {Void ValidateConnectionForExecute(System.Data.SqlClient.SqlCommand)}

What does this mean (especially since I only have one SqlDataReader)?
Does it mean that I need to close the SqlAdapter da or something? (I already checked: there is no da.Close() method)
Does it mean that I can't launch an SQL command while another is still being read? (This would be a major limitation, I guess)
...

Does anybody have an idea?
Thanks in advance

What happens using a [Typed DataSet](https://www.wiley.com/en-us/Beginning+C%23+2005+Databases-p-9780470112373) created by the Visual Studio Designers and no manual code? — , Aug 25 '21 at 12:41
Does this answer your question? [DataAdapter.Fill(Dataset)](https://stackoverflow.com/questions/6532304/dataadapter-filldataset) — , Aug 25 '21 at 12:52
[Populate a DataSet from a DataAdapter](https://learn.microsoft.com/sql/connect/ado-net/populate-dataset-from-dataadapter) • [DataAdapter In C#](https://www.c-sharpcorner.com/article/dataadapter-in-C-Sharp/) — , Aug 25 '21 at 12:54
I'd advise [not to reuse to same SqlConnection object](https://softwareengineering.stackexchange.com/a/142068) as you are doing with `DbConnection`, and just let connection pooling do the job for you. As well as being better practice, this would resolve this specific error too. — GarethD, Aug 25 '21 at 13:21

Dominique · Accepted Answer · 2021-08-25T13:48:38.437

I've managed to find a solution, thanks to the documentation URL from Olivier Rogier:
It mentions that the Fill() method implicitly uses a DataReader.
So I've decided to remove the nesting of any (Sql)DataReader objects, which results in following code:

try
{   
    DbConnection.Open();
    sqlCommand = DbConnection.CreateCommand();
    sqlCommand.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";

    sqlDataReader = sqlCommand.ExecuteReader();
    cmb_Table_Names.Items.Clear();
    while (sqlDataReader.Read())
    {
        cmb_Table_Names.Items.Add(sqlDataReader.GetString(0));
    }
    sqlDataReader.Close(); // <== close first DataReader

    foreach (string tmp_Table in cmb_Table_Names.Items)
    {
        DataTable dt_tmp = new DataTable();
        using (var da = new SqlDataAdapter($"SELECT * FROM [{tmp_Table}]", 
                                           DbConnection))
        {
            da.Fill(dt_tmp); // <== use the second implicit DataReader
        }
        dataSet.Tables.Add(dt_tmp);
    }
}
catch (Exception ex)
{  
    MessageBox.Show(ex.Message, "Reading DB failed!!!", MessageBoxButton.OK);
}

VERY IMPORTANT!!! There are square brackets around the tablename of `SELECT * FROM [{tmp_Table}]`, if not tablenames containing a dot are not recognised (Exception: `System.Data.SqlClient.SqlException`, message: `"Invalid object name "`). — Dominique, Aug 25 '21 at 13:51
You are missing `using` blocks, you should also not cache the connection object — Charlieface, Aug 25 '21 at 17:16

Why can't I read all tables from a database into my DataSet?

1 Answers1