C# 如何用多个表填充数据集？

How to fill Dataset with multiple tables?

提问by Andriy Zakharko

I'm trying to fill DataSet which contains 2 tables with one to many relationship. I'm using DataReader to achieve this :

我正在尝试填充包含具有一对多关系的 2 个表的 DataSet。我正在使用 DataReader 来实现这一点：

    public DataSet SelectOne(int id)
    {
        DataSet result = new DataSet();
        using (DbCommand command = Connection.CreateCommand())
        {
            command.CommandText = "select * from table1";

            var param = ParametersBuilder.CreateByKey(command, "ID", id, null);
            command.Parameters.Add(param);

            Connection.Open();
            using (DbDataReader reader = command.ExecuteReader())
            {
                result.MainTable.Load(reader);
            }
            Connection.Close();
        }
        return result;
    }

But I've got only one table filled up. How do I achieve my goal - fill both tables?

但我只有一张桌子坐满了。我如何实现我的目标 - 填写两个表格？

I would like to use DataReader instead DataAdapter, if it possible.

如果可能，我想使用 DataReader 代替 DataAdapter。

采纳答案by smoothdeveloper

If you are issuing a single command with several select statements, you might use NextResult method to move to next resultset within the datareader: http://msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult.aspx

如果您发出带有多个选择语句的单个命令，您可以使用 NextResult 方法移动到数据读取器中的下一个结果集：http: //msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult。 aspx

I show how it could look bellow:

我展示了它的外观：

public DataSet SelectOne(int id)
{
    DataSet result = new DataSet();
    using (DbCommand command = Connection.CreateCommand())
    {
        command.CommandText = @"
select * from table1
select * from table2
        ";

        var param = ParametersBuilder.CreateByKey(command, "ID", id, null);
        command.Parameters.Add(param);

        Connection.Open();
        using (DbDataReader reader = command.ExecuteReader())
        {
            result.MainTable.Load(reader);
            reader.NextResult();
            result.SecondTable.Load(reader);
            // ...
        }
        Connection.Close();
    }
    return result;
}

回答by Pranav

Here is very good answer of your question

这是您问题的很好答案

see the example mentioned on above MSDN page :-

请参阅上面 MSDN 页面上提到的示例：-

回答by Mohsen Safari

Filling a DataSet with multiple tables can be done by sending multiple requests to the database, or in a faster way: Multiple SELECT statements can be sent to the database server in a single request. The problem here is that the tables generated from the queries have automatic names Table and Table1. However, the generated table names can be mapped to names that should be used in the DataSet.

可以通过向数据库发送多个请求或以更快的方式使用多个表填充数据集：可以在单个请求中将多个 SELECT 语句发送到数据库服务器。这里的问题是查询生成的表具有自动名称 Table 和 Table1。但是，生成的表名可以映射到应该在 DataSet 中使用的名称。

SqlDataAdapter adapter = new SqlDataAdapter(
      "SELECT * FROM Customers; SELECT * FROM Orders", connection);
adapter.TableMappings.Add("Table", "Customer");
adapter.TableMappings.Add("Table1", "Order");

adapter.Fill(ds);

回答by CularBytes

It is an old topic, but for some people it might be useful:

这是一个古老的话题，但对某些人来说可能有用：

        DataSet someDataSet = new DataSet();
        SqlDataAdapter adapt = new SqlDataAdapter();

        using(SqlConnection connection = new SqlConnection(ConnString))
        {
            connection.Open();
            SqlCommand comm1 = new SqlCommand("SELECT * FROM whateverTable", connection);
            SqlCommand comm2g = new SqlCommand("SELECT * FROM whateverTable WHERE condition = @0", connection);
            commProcessing.Parameters.AddWithValue("@0", "value");
            someDataSet.Tables.Add("Table1");
            someDataSet.Tables.Add("Table2");

            adapt.SelectCommand = comm1;
            adapt.Fill(someDataSet.Tables["Table1"]);
            adapt.SelectCommand = comm2;
            adapt.Fill(someDataSet.Tables["Table2"]);
        }

回答by user5311866

protected void Page_Load(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("data source=.;uid=sa;pwd=123;database=shop");
    //SqlCommand cmd = new SqlCommand("select * from tblemployees", con);
    //SqlCommand cmd1 = new SqlCommand("select * from tblproducts", con);
    //SqlDataAdapter da = new SqlDataAdapter();

    //DataSet ds = new DataSet();
    //ds.Tables.Add("emp");
    //ds.Tables.Add("products");
    //da.SelectCommand = cmd;
    //da.Fill(ds.Tables["emp"]);
    //da.SelectCommand = cmd1;

    //da.Fill(ds.Tables["products"]);
    SqlDataAdapter da = new SqlDataAdapter("select * from tblemployees", con);
    DataSet ds = new DataSet();
    da.Fill(ds, "em");
    da = new SqlDataAdapter("select * from tblproducts", con);
    da.Fill(ds, "prod");

    GridView1.DataSource = ds.Tables["em"];
    GridView1.DataBind();
    GridView2.DataSource = ds.Tables["prod"];
    GridView2.DataBind();
}

回答by Mitesh Gadhiya

         string connetionString = null;
        SqlConnection connection ;
        SqlCommand command ;
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet ds = new DataSet();
        int i = 0;
        string firstSql = null;
        string secondSql = null;

        connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
        firstSql = "Your First SQL Statement Here";
        secondSql = "Your Second SQL Statement Here";
        connection = new SqlConnection(connetionString);

        try
        {
            connection.Open();

            command = new SqlCommand(firstSql, connection);
            adapter.SelectCommand = command;
            adapter.Fill(ds, "First Table");

            adapter.SelectCommand.CommandText = secondSql;
            adapter.Fill(ds, "Second Table");

            adapter.Dispose();
            command.Dispose();
            connection.Close();

            //retrieve first table data 
            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                MessageBox.Show(ds.Tables[0].Rows[i].ItemArray[0] + " -- " + ds.Tables[0].Rows[i].ItemArray[1]);
            }
            //retrieve second table data 
            for (i = 0; i <= ds.Tables[1].Rows.Count - 1; i++)
            {
                MessageBox.Show(ds.Tables[1].Rows[i].ItemArray[0] + " -- " + ds.Tables[1].Rows[i].ItemArray[1]);

            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Can not open connection ! ");
        }

回答by Kashif Faraz

public DataSet GetDataSet()
    {
        try
        {
            DataSet dsReturn = new DataSet();
            using (SqlConnection myConnection = new SqlConnection(Core.con))
            {
                string query = "select * from table1;  select* from table2";
                SqlCommand cmd = new SqlCommand(query, myConnection);
                myConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                dsReturn.Load(reader, LoadOption.PreserveChanges, new string[] { "tableOne", "tableTwo" });
                return dsReturn;
            }
        }
        catch (Exception)
        {
            throw;
        }
    }

回答by Mario Vázquez

Method Loadof DataTableexecutes NextResulton the DataReader, so you shouldn't call NextResultexplicitly when using Load, otherwise odd tables in the sequence would be omitted.

法Load的DataTable执行NextResult的DataReader，所以你不应该叫NextResult使用时明确Load，否则序列中奇数表将被省略。

Here is a generic solution to load multiple tables using a DataReader.

这是使用 .csv 文件加载多个表的通用解决方案DataReader。

// your command initialization code here
// ...
DataSet ds = new DataSet();
DataTable t;
using (DbDataReader reader = command.ExecuteReader())
{
  while (!reader.IsClosed)
  {
    t = new DataTable();
    t.Load(rs);
    ds.Tables.Add(t);
  }
}