时间戳

首页 > 解决方案 > 为什么我不能将数据库中的所有表都读入我的 DataSet？

c# - 为什么我不能将数据库中的所有表都读入我的 DataSet？

问题描述

我正在尝试将 MS-SQL 数据库的所有表读入一个 DataSet，该 DataSet 由一组 DataTables 组成。为此，我编写了这段源代码：

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);
}

我陷入了我Exception的行列da.Fill(dt_tmp);。即时窗口中
的结果是：? ex

{"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)}

这是什么意思（特别是因为我只有一个SqlDataReader）？
这是否意味着我需要关闭SqlAdapter da或什么？（我已经检查过：没有da.Close()方法）
这是否意味着我无法启动 SQL 命令而另一个仍在读取？（我猜这将是一个主要限制）
......

有人有想法吗？
提前致谢

标签： c#sql-serverdatatabledataset

解决方案

感谢Olivier Rogier的文档 URL，我设法找到了解决方案： 它提到该方法隐式使用. 所以我决定删除任何对象的嵌套，这会产生以下代码：
Fill()DataReader
(Sql)DataReader

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);
}

推荐阅读