5 Replies - 750 Views - Last Post: 31 July 2013 - 01:34 AM Rate Topic: -----

#1 toad87  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 188
  • Joined: 21-May 12

Joining DataTables

Posted 29 July 2013 - 09:11 PM

I need to join my DataTables and display the resulting DataTable in a DataGridView.

I tried using Linq, but it didn't work. The datatable had all the correct columns, but no rows!

I do not want to join the tables on the server side. Reason being, if I were to change data in any of the parent tables, I would have to change it locally, update the corresponding server side table(s), then redownload the joined table.
I think this would take too long and refreshing the datagridview wouldn't look very smooth.

Is there anyway to join my DataTables without using SQL on the server side or LINQ?

Is This A Good Question/Topic? 0
  • +

Replies To: Joining DataTables

#2 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3621
  • View blog
  • Posts: 11,278
  • Joined: 05-May 12

Re: Joining DataTables

Posted 29 July 2013 - 09:28 PM

Unless your data is in the millions of records, or your database is in different continent and you are using slow network link, I doubt that you would notice the difference between letting the server do the join, or doing the join locally. Remember that SQL servers are purpose built to do searches and joins very very quickly.

As for your using LINQ locally, are you sure you got the code for the join correct? Why not post it here and get have at least another pair of eyes verify that you are doing the right thing?
Was This Post Helpful? 0
  • +
  • -

#3 toad87  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 188
  • Joined: 21-May 12

Re: Joining DataTables

Posted 29 July 2013 - 10:03 PM

Here's my Linq code:

DataTable customers = this.brandpk2_UptownLashesDataSet1.Customers;
            DataTable jobs = this.brandpk2_UptownLashesDataSet1.Jobs;
            DataTable employees = this.brandpk2_UptownLashesDataSet1.Employees;
            DataTable appointments = this.brandpk2_UptownLashesDataSet1.Appointments;

            var selectQuery = from a in appointments.AsEnumerable()
                              join c in customers.AsEnumerable()
                              on a.Field<int>("customer_id") equals c.Field<int>("customer_id")
                              join j in jobs.AsEnumerable()
                              on a.Field<int>("job_id") equals j.Field<int>("job_id")
                              join e in employees.AsEnumerable()
                              on a.Field<int>("employee_id") equals e.Field<int>("employee_id")
                              select new
                              {
                                  AppNo = a.Field<string>("appointment_number"),
                                  Date = a.Field<string>("date"),
                                  Time = a.Field<string>("time"),
                                  Customer = c.Field<string>("first_name"),
                                  Job = j.Field<string>("description"),
                                  Price = j.Field<decimal>("price"),
                                  Tip = a.Field<decimal>("tip"),
                                  Employee = e.Field<string>("first_name"),
                                  Status = a.Field<string>("status")
                              };

            this.dataGridViewAppointmentsView.DataSource = null;
            this.dataGridViewAppointmentsView.DataSource = selectQuery.ToList();



The above code doesn't show it, but each datatables adapters' Fill() method has been called.

I will rethink joining on the server side.

This post has been edited by toad87: 29 July 2013 - 10:04 PM

Was This Post Helpful? 0
  • +
  • -

#4 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3621
  • View blog
  • Posts: 11,278
  • Joined: 05-May 12

Re: Joining DataTables

Posted 30 July 2013 - 05:52 AM

Just taking a very quick glance, things seem to be in place for success. I would try with a 2 table join first to see if you are getting data rows back. If that succeeds start building up until you get the failure to get rows back. When you get to the point, examine the data from the last table that you added. Perhaps there is no data there that satisfies the join conditions.
Was This Post Helpful? 0
  • +
  • -

#5 toad87  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 188
  • Joined: 21-May 12

Re: Joining DataTables

Posted 30 July 2013 - 07:49 PM

Hey SkyDiver, thanks for all the help.

I got it working after I realized I had deleted all the data in my Jobs table.

I'm still deciding whether I should keep using Linq or do a server join though.
Was This Post Helpful? 0
  • +
  • -

#6 Charles:)  Icon User is offline

  • D.I.C Regular

Reputation: 142
  • View blog
  • Posts: 346
  • Joined: 26-November 09

Re: Joining DataTables

Posted 31 July 2013 - 01:34 AM

Do a server join. SQL-Server (and all database engines) are purpose built for exactly that sort ot task, and they contain performance tweaks and optimisations that most programmers are incapable of. Trust me, you will not get better performance be re-writing the joining logic on the client side rather than letting the database engine do it.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1