.Net C# 数据表和数据集，如何关联表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1122024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
.Net C# DataTables and DataSets, How to relate tables
提问by flavour404
How do you take a couple of data tables and put them in a dataset and relate (that doesn't even sound like correct English) them?
您如何获取几个数据表并将它们放入数据集中并关联（这听起来甚至不像正确的英语）它们？
I know how to create datatables.
我知道如何创建数据表。
采纳答案by Ron Skufca
Here is an example from one of my classes
这是我的一堂课的一个例子
// create the relationship between Booking and Booking_MNI
DataRelation relBookingMNI;
relBookingMNI = new DataRelation("BookingToBookingMNI",dsBooking.Tables["Booking"].Columns["Record_Id"],dsBooking.Tables["Booking_MNI"].Columns["booking_record_id"]);
dsBooking.Relations.Add(relBookingMNI);
dsBooking is my main dataset that contains 2 tables Booking and Booking_MNI Where the Record_Id is the primary key and booking_record_id is the foreign key
dsBooking 是我的主要数据集，包含 2 个表 Booking 和 Booking_MNI 其中 Record_Id 是主键，booking_record_id 是外键
I changed the code below to match my first example. But I think this is what you are looking for. In our production code this will produce the plus "+" symbol to the left of the row which would allow you to drill into the related table. Again I took production code and made it look like the first example so I don't know if it will compile but it should get you going in the right direction.
我更改了下面的代码以匹配我的第一个示例。但我认为这就是你要找的。在我们的生产代码中，这将在行的左侧产生加号“+”符号，这将允许您深入到相关表中。我再次采用生产代码并使其看起来像第一个示例，所以我不知道它是否会编译，但它应该让你朝着正确的方向前进。
DataTable dtBooking = ds.Tables[0];
DataTable dtBooking_MNI = ds.Tables[1];
dtBooking.PrimaryKey = new DataColumn[] {dtBooking.Columns["Record_Id"]};
dtBooking_MNI.PrimaryKey = new DataColumn[] {dtBooking_MNI.Columns["booking_Record_Id"]};
/* Setup DataRelation between the DataTables */
DataColumn[] dcBookingColsArray = new DataColumn[1] {dtBooking.Columns["Record_Id"]};
DataColumn[] dcBookingMNIColsArray = new DataColumn[1] {dtBooking_MNI.Columns["booking_record_Id"]};
DataRelation relBooking_To_MNI = new DataRelation("Booking_To_MNI",dcBookingColsArray,dcBookingMNIColsArray);
ds.Relations.Add(relBooking_To_MNI_Units);
// grid where you want to display the relationship
grdBooking.DataSource = ds;
回答by ist_lion
回答by casperOne
Look at the DataRelation class. It is what is used in a DataSet to relate two DataTables together.
查看 DataRelation 类。它用于在 DataSet 中将两个 DataTable 关联在一起。
回答by NikolaiDante
Perhaps you're looking for an ormsolution like Entity Framework, NHibernateor Linq to SQL?
也许您正在寻找像Entity Framework、NHibernate或Linq to SQL这样的orm解决方案？
Appologies if I've misunderstood the question.
如果我误解了这个问题，请道歉。
回答by Simon D.
If you use Visual Studio 2005 or later try the following: Right-click your project and select "Add/NewItem...", then choose DataSet from the wizard, which will create you some xsd and open the dataset designer. Now you can create multiple tables, add columns to each table and draw relations (foreign key, with/without cascading...) between those tables. in the autogenerated [YourNewDataSet}.Designer.cs-file, you will find the source code for these relations. Something like this:
如果您使用 Visual Studio 2005 或更高版本，请尝试以下操作： 右键单击您的项目并选择“添加/新建项目...”，然后从向导中选择数据集，这将为您创建一些 xsd 并打开数据集设计器。现在您可以创建多个表，向每个表添加列并绘制这些表之间的关系（外键，有/没有级联...）。在自动生成的 [YourNewDataSet}.Designer.cs 文件中，您将找到这些关系的源代码。像这样的东西：
this.relationFK_DataTable2_DataTable1 = new global::System.Data.DataRelation("FK_DataTable2_DataTable1", new global::System.Data.DataColumn[] {
this.tableDataTable2.asdfasColumn}, new global::System.Data.DataColumn[] {
this.tableDataTable1.asdfaColumn}, false);
As always you can strip quite some portion of this code, if you code by hand instead of using the designer.
与往常一样，如果您手动编写代码而不是使用设计器，您可以删除此代码的相当一部分。
回答by Lloyd McFarlin
Let's say you've got your DataTables named "orders" and "orderDetails". You want to create a relationship between them by their OrderNumber columns. We'll assume that orders is the parent and orderDetails is the child. We want to loop through the orders and then print each one's related sub-totals.
假设您有名为“orders”和“orderDetails”的数据表。您希望通过它们的 OrderNumber 列在它们之间创建关系。我们将假设 orders 是父级，而 orderDetails 是子级。我们想遍历订单，然后打印每个订单的相关小计。
DataSet orderData = new DataSet("OrderData");
orderData.Tables.Add(orders);
orderData.Tables.Add(orderDetails);
orderData.Relations.Add("Order_OrderDetails", orders.Columns["OrderNumber"], orderDetails.Columns["OrderNumber"]);
Now, when you want to use that relationship somewhere else in your code:
现在，当您想在代码中的其他地方使用该关系时：
DataRelation orderRelation = orderData.Relations["Order_OrderDetails"];
foreach (DataRow order in orders.Rows)
{
Console.WriteLine("Subtotals for Order {0}:", order["OrderNumber"]);
foreach (DataRow orderDetail in order.GetChildRows(orderRelation))
{
Console.WriteLine("Order Line {0}: {1}", orderDetail["OrderLineNumber"], string.Format("{0:C}", orderDetail["Price"]));
}
}
回答by user3098137
try this here is two table 1. categories & 2. Products
试试这里是两个表 1. 类别和 2. 产品
string query = "SELECT * FROM Categories; SELECT * FROM Products";
SqlConnection con = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter(query,con);
DataSet ds = new DataSet();
da.Fill(ds, "CategoriesAndProducts"); //CategoriesAndProducts dataset
string s = ds.Tables[0].Rows[0]["Name"].ToString();
string s1 = ds.Tables[1].Rows[0]["Name"].ToString();
Console.WriteLine(s); //from categories [0][0] like Electronic
Console.WriteLine(s1); //from Products [0][0] like LG
回答by ahmad salimi
private void CreateRelation()
{
// Get the DataColumn objects from two DataTable objects
// in a DataSet. Code to get the DataSet not shown here.
DataColumn parentColumn =
DataSet1.Tables["Customers"].Columns["CustID"];
DataColumn childColumn =
DataSet1.Tables["Orders"].Columns["CustID"];
// Create DataRelation.
DataRelation relCustOrder;
relCustOrder = new DataRelation("CustomersOrders",
parentColumn, childColumn);
// Add the relation to the DataSet.
DataSet1.Relations.Add(relCustOrder);
}
