C# 如何让DataSet返回两个相关的表？

how to make DataSet return two related Tables?

提问by George2

I am using C# + .Net 3.5 + VSTS 2008 + ADO.Net + SQL Server 2008. I have two related tables in SQL Server (foreign key relationship). And I want to load the two tables as two datatables in a dataset. Any reference code to do this?

我正在使用 C# + .Net 3.5 + VSTS 2008 + ADO.Net + SQL Server 2008。我在 SQL Server 中有两个相关的表（外键关系）。我想将这两个表作为数据集中的两个数据表加载。任何参考代码来做到这一点？

thanks in advance, George

提前致谢，乔治

回答by Anuraj

Try this

尝试这个

Dim myAdapter as SqlDataAdapter = new SqlDataAdapter(
      “SELECT * FROM Customers; SELECT * FROM Orders“, connection)

myAdapter.Fill(dsTables)
dsTables.Tables(0).TableName = “Customers“)
dsTables.Tables(1).TableName = “Orders“)

http://pietschsoft.com/post/2004/08/22/Fill-DataSet-with-multiple-Tables-and-update-them-with-DataAdapter.aspx

http://pietschsoft.com/post/2004/08/22/Fill-DataSet-with-multiple-Tables-and-update-them-with-DataAdapter.aspx

回答by Rutesh Makhijani

George,

乔治，

Can you please clarify your question - is it about filling mulitple tables in dataset or specifically realted tables only.

您能否澄清一下您的问题 - 是关于填充数据集中的多个表还是仅填充特定的相关表。

As far loading multiple tables is concerned you can refer to following code (this code is avialable at MSDN http://msdn.microsoft.com/en-us/library/5fd1ahe2.aspx):

就加载多个表而言，您可以参考以下代码（此代码可在 MSDN http://msdn.microsoft.com/en-us/library/5fd1ahe2.aspx 获得）：

static void Main()
 {
   DataSet dataSet = new DataSet();

    DataTable customerTable = new DataTable();
    DataTable productTable = new DataTable();

    // This information is cosmetic, only.
    customerTable.TableName = "Customers";
    productTable.TableName = "Products";

    // Add the tables to the DataSet:
    dataSet.Tables.Add(customerTable);
    dataSet.Tables.Add(productTable);

    // Load the data into the existing DataSet. 
    DataTableReader reader = GetReader();
    dataSet.Load(reader, LoadOption.OverwriteChanges,
        customerTable, productTable);

    // Print out the contents of each table:
    foreach (DataTable table in dataSet.Tables)
       {
        PrintColumns(table);
        }

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
}

private static DataTable GetCustomers()
{
    // Create sample Customers table.
    DataTable table = new DataTable();
    table.TableName = "Customers";

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Name", typeof(string));

    // Set the ID column as the primary key column.
    table.PrimaryKey = new DataColumn[] { idColumn };

    table.Rows.Add(new object[] { 0, "Mary" });
    table.Rows.Add(new object[] { 1, "Andy" });
    table.Rows.Add(new object[] { 2, "Peter" });
    table.AcceptChanges();
    return table;
}

private static DataTable GetProducts()
{
    // Create sample Products table.
    DataTable table = new DataTable();
    table.TableName = "Products";

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID",
        typeof(int));
    table.Columns.Add("Name", typeof(string));

    // Set the ID column as the primary key column.
        table.PrimaryKey = new DataColumn[] { idColumn };

    table.Rows.Add(new object[] { 0, "Wireless Network Card" });
    table.Rows.Add(new object[] { 1, "Hard Drive" });
    table.Rows.Add(new object[] { 2, "Monitor" });
    table.Rows.Add(new object[] { 3, "CPU" });
    table.AcceptChanges();
    return table;
}

private static void PrintColumns(DataTable table)
{
    Console.WriteLine();
    Console.WriteLine(table.TableName);
    Console.WriteLine("=========================");
    // Loop through all the rows in the table:
    foreach (DataRow row in table.Rows)
    {
        for (int i = 0; i < table.Columns.Count; i++)
        {
            Console.Write(row[i] + " ");
        }
        Console.WriteLine();
    }
}

private static DataTableReader GetReader()
{
    // Return a DataTableReader containing multiple
    // result sets, just for the sake of this demo.
    DataSet dataSet = new DataSet();
    dataSet.Tables.Add(GetCustomers());
    dataSet.Tables.Add(GetProducts());
    return dataSet.CreateDataReader();
}

Here is the code for managing parent child relation in data sets

这是管理数据集中父子关系的代码

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataRelation
  Inherits System.Web.UI.Page

  Protected lblDisplay As System.Web.UI.WebControls.Label

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim objConn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet
    Dim dtrParent As DataRow
    Dim dtrChild As DataRow

    objConn = New SqlConnection(ConfigurationSettings.Appsettings("NorthwindConnection"))
    da = New SqlDataAdapter("SELECT * FROM Categories", objConn)
    ds = New DataSet()
    Try
      objConn.Open()
      da.Fill( ds,"Categories")
      da.SelectCommand = New SqlCommand("SELECT * FROM Products", objConn)
      da.Fill(ds, "Products")
    Catch exc As SqlException
      Response.Write(exc.ToString())
    Finally
      objConn.Dispose()
    End Try 
    'Create the Data Relationship
    ds.Relations.Add("Cat_Prod",ds.Tables("Categories").Columns("CategoryID"), _
                            ds.Tables("Products").Columns("CategoryID"))

    'Display the Category and Child Products Within
    For each dtrParent in ds.Tables("Categories").Rows
      lblDisplay.Text &= "<h3>" & dtrParent("CategoryName") & "</h3><ul>"
      For each dtrChild in dtrParent.GetChildRows("Cat_Prod")
        lblDisplay.Text &= "<li>" & dtrChild("ProductName") & "</li>"
      Next
      lblDisplay.Text &= "</ul>"
    Next
  End Sub

End Class 

You can find further explanation over here

你可以在这里找到进一步的解释

回答by Sudheer Kumar

Here is a sample code on how to load a DataSet using LINQ queries Here 2 tables have got a relationship. "dc" is the data context.

下面是关于如何使用 LINQ 查询加载数据集的示例代码 这里 2 个表有关系。“dc”是数据上下文。

            var query = dc.GetTable<Media>().Where(s => s.MediaID == new Guid("A72AA79A-6C40-4D6B-A826-241553FECDFE"));
            var query1 = dc.GetTable<MediaVersion>().Where(s => s.MediaID == new Guid("A72AA79A-6C40-4D6B-A826-241553FECDFE"));
            var query2 = dc.GetTable<RootPath>().Where(s => s.RootPathID == new Guid("62145B2C-BA36-4313-8CA2-0F224F8FE7E8"));


            SqlCommand cmd = dc.GetCommand(query) as SqlCommand;
            //Load first
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds, "Media");
            //Load second
            cmd = dc.GetCommand(query1) as SqlCommand;
            ada.SelectCommand = cmd;
            ada.Fill(ds, "MediaVersion");
            ds.Relations.Add("Med_MedVer", ds.Tables["Media"].Columns["MediaID"],
                       ds.Tables["MediaVersion"].Columns["MediaID"]);

            //Load third independent table
            cmd = dc.GetCommand(query2) as SqlCommand;
            ada.SelectCommand = cmd;
            ada.Fill(ds, "RootPath");