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

Question

Hi,

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.

Thanks R.

Answer 1

Have you looked into LINQ?

http://msdn.microsoft.com/en-us/netframework/aa904594.aspx

Answer 2

Look at the DataRelation class. It is what is used in a DataSet to relate two DataTables together.

Answer 3

Perhaps you're looking for an orm solution like Entity Framework, NHibernate or Linq to SQL?

Appologies if I've misunderstood the question.

Answer 4

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;

Answer 5

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.

Answer 6

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

Answer 7

Hi friends i can any one help me merging 2 random tables into one and displaying in gridview for example
table 1
Sid SName Dno

and
table 2
Dno Dname Daddress

i want to Merge & display these 2 tables as one table in datagridview as following

Sid SName Dno Dname Daddress

please Help me

the program which i wrote till is like this try to change it to get out put

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace DatabAse
{
    public partial class Form2 : Form
    {
        DataTable dt1, dt2;
        DataSet ds;
        DataRow r;
        DataColumn c1, c2, c3, c4, c5, c6;
        public Form2()
        {
            InitializeComponent();
            ds = new DataSet();

        }

        private void button1_Click(object sender, EventArgs e)
        {
            r = dt1.NewRow();
            r[0] = textBox4.Text;
            r[1] = textBox5.Text;
            r[2] = textBox6.Text;
            dt1.Rows.Add(r);
            MessageBox.Show("Row added ");
        }

        private void button5_Click(object sender, EventArgs e)
        {
            dt1 = new DataTable();
            dt1.TableName = "Table1";
            dt2 = new DataTable();
            dt2.TableName = "Table2";

            c1 = new DataColumn(textBox1.Text);
            c2 = new DataColumn(textBox2.Text);
            c3 = new DataColumn(textBox3.Text);

            c4 = new DataColumn(textBox10.Text);
            c5 = new DataColumn(textBox11.Text);
            c6 = new DataColumn(textBox12.Text);

            dt1.Columns.Add(c1); dt1.Columns.Add(c2); dt1.Columns.Add(c3);
            dt2.Columns.Add(c4); dt2.Columns.Add(c5); dt2.Columns.Add(c6);
            //dt1.PrimaryKey=c3;
            //dt2.PrimaryKey=c4;
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);

            MessageBox.Show("Table's created ");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            r = dt2.NewRow();
            r[0] = textBox7.Text;
            r[1] = textBox8.Text;
            r[2] = textBox9.Text;
            dt2.Rows.Add(r);
            MessageBox.Show("Row added ");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = dt1;
            dataGridView2.DataSource = dt2;
        }
}

if any one has output please tell me Thanks in advance geels