Reputation: 6312

.Net C# DataTables and DataSets, How to relate tables

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.

Upvotes: 3

Answers (8)

ahmad salimi

Reputation: 11

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

Upvotes: 0

user3098137

Reputation: 521

try this here is two table 1. categories & 2. Products

        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

Upvotes: 1

Ron Skufca

Reputation: 2078

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

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;

Upvotes: 10

Lloyd McFarlin

Reputation: 378

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.

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

Upvotes: 2

Simon D.

Reputation: 4481

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:

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.

Upvotes: 0