C# Joining Internal Datatables? RRS feed

  • Question

  • I'm not finding anything truly conclusive on this.  On a previous job I made an inventory program, it went through the stores, put each store on the top of the column and the onhand items as the rows, grouped by the item name.  I'm trying to do something like that on this program, but there's a big difference this time in that I'm doing it with users and there's not a fixed number of them this time.  Is there a "dynamic" way to query something like this from an internal dataset?

    Code snippet from previous:

            public void allOnHand()
                OleDbDataAdapter da = new OleDbDataAdapter
    			("SELECT masterList.Supplier As Vendor, 
    			masterList.item As Item, 
    			masterList.description As Description, 
    			table01.onhand As Store01, 
    			table02.onhand As Store02, 
    			table03.onhand As Stor03, 
    			table04.onhand As Store04, 
    			table05.onhand As Store05, 
    			table06.onhand As Store06 
    			(((((masterList LEFT JOIN table01 ON masterList.item = table01.item)   
    			LEFT JOIN table02 ON masterList.item = table02.item) 
    			LEFT JOIN table03 ON masterList.item = table03.item) 
    			LEFT JOIN table04 ON masterList.item = table04.item) 
    			LEFT JOIN table05 ON masterList.item = table05.item)
    			LEFT JOIN table06 ON masterList.item = table06.item 
    			WHERE (((table01.item)=masterList.item) 
    			OR ((table02.item)=masterList.item) 
    			OR ((table03.item)=masterList.item) 
    			OR ((table04.item)=masterList.item) 
    			OR ((table05.item)=masterList.item) 
    			OR ((table06.item)=masterList.item))", this.oCon);
                DataTable ds = new DataTable();

    It worked because as stated, I knew exactly how many stores there were and what their names were.

    In this case I'm looking for something with a similar result.

    Code Snippet:

    private void snagTasks()
               //Task01 is a DataSet() I set up
                Task01.Tables.Add("AllTasks"); //join these types of tables?
                this.oCon = new OleDbConnection(@"source");
                OleDbDataAdapter da = new OleDbDataAdapter("Select * FROM Tasks", this.oCon);

    Ok, what's happening.  The field called "Resource" is what holds the person's name, but it's a column in the Task table and has many repreats.  I need their name as the column header (not repeated).  This is going into a "calendar" type DGV control i set up.  I need these joined by ScheduledStartTime.  The field I need in the DGV cell is WorkOrder.  So show the WorkOrders next to their ScheduledStartTimes, under the Resource's column. 

    I set up another table to get this started, called Calendar (cuz I'm creative like that lol), and it's only field is "timestamps" which has the almost outlook-style 24 hours worth of time stamps w/ the quarter hours in it.  (11:00 AM, 11:15 AM, 11:30 AM, 11:45 AM, etc.) 

    What I was thinking was iterate through a List<string> that has the names, and add a queried table for each one, and use the join like at the top, something to the effect of "foreach datatable in Task01" and joining them.  Upon typing about 4 characters of that it occurred to me that's likely a horrible idea, so I deleted all 4 characters and posted here hoping for suggestions.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Tuesday, July 21, 2015 2:08 PM

All replies

  • Hi psifreak,

    According to your description, you'd like join the mutiple tables, you want the "Resource" field has no repeats.

    I suggest you have two tables, One table1 has Id and Name field. the other table2 has Id and Sex field.

    SELECT table1.*, table2.Sex FROM table1 JOIN table2 On table1.Id=table2.Id

    If I misunderstand, you could feel free to let me know.

    Youjun Tang

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 24, 2015 6:09 AM
  • That's close.  What I'm going for is more like (and assume I've already filled both DataTables)

    DataTable CalendarTable = new DataTable();
    DataTable TasksTable = new DataTable();
    private void test()
                DataRow[] results = CalendarTable.Select("Calendar.timestamp, " //start time
                    + TasksTable.Columns["ScheduledStartTime"]
                    + "FROM Calendar 

    The huge catch with this, I need names along the top in the columns without knowing how many names.  And I need the field "WorkOrder" placed next to the StartTime (12:00 AM for example)  for each and every one of the 96 time stamps, even if blank.  I'm fine with it only having one name at a time, but I haven't gotten any form of syntax working to mash the data together.

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Tuesday, July 28, 2015 2:43 PM
  • Still working on this.  I can't use the exact expression as printed above because VS doesn't want to let me specify the tables.  I completely loaded an internal DataTable() with all of the info from one database table, and another DataTable() for the second.  My two attempts (side by side...sorta)

    public void test(DateTime start_date) { attempt 1 var query = from u in ds.CalendarTable join ut in ds.TasksTable //no prompt for the fields . just adds a . //below didn't work because I only want to access the database once and that syntax is geared

    ///at querying the database repeatedly // and the above won't let me join tables I created myself DataRow[] results = ds.CalendarTable.Select("Calendar.timestamp, Tasks.Resource, Tasks.WorkOrder" + " FROM Calendar LEFT JOIN TasksTable ON Calendar.timestamp = Tasks.ScheduledStartTime" + " WHERE Tasks.ScheduledStartDate = '" + start_date + "'"); foreach (DataRow row in results) { queriedTasks.Rows.Add(row); //DataTable() I made } }

    May the fleas of a thousand camels feast happily on the lower regions of your enemies. And may their arms be too short to scratch!

    Wednesday, July 29, 2015 6:08 PM