Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Pulling data from two tables into a DataGrid

Hi all,

I have an app that is used for scheduling rooms.  I pull data from a database table (Events) showing which rooms are reserved and when.  This data is then displayed in a DataGrid.  Now I want to pull data from a seperate table (Slots) that contains all of the available time slots for reserving a room. 

My problem is I can not get the data from the two tables to merge into the one DataTable.  I've tried using two DataSets and merging them, and using two DataTables then building a third DataTable to hold the combined results.  All I can get is the data from the Events table to display.

Any suggestions would be greatly appreciated.

Ed Murphy
Wednesday, October 02, 2002

Windows app or Web app? Do you want to display all the information from both tables at once, or a single parent row and all its children?

Mike Gunderloy
Wednesday, October 02, 2002

why don't you just alter your SQL to pull what you need from the DB and perform the join/union in SQL and populate it into one DataTable?  let the SQL do the work for you (faster and better).  Unless there is some reason to keep the data in separate DataTables (do you want to update the DB with some new data inserted into the DataSet?).

Tim
Thursday, October 03, 2002

I'm with Tim, keep as much in the database as you can, learning a little SQL can go a long way.

Ben
Thursday, October 03, 2002

Ed,
Show us a bit of your code and perhaps we can point you in the right direction.

Yes - try doing a join in the db... And for merges to work you need to se the PrimaryKeyt property the tables first.

Dunc
Thursday, October 03, 2002

I would also recommend doing a SQL join, but you can use a DataSet object to acheive the same thing. for example:

// Get Table1 from DB
this.dataSet = new DataSet();
string query = "SELECT pk, Value FROM Table1";
this.dataAdapter = new SqlDataAdapter(query,  connectionString);
this.dataAdapter.Fill(this.dataSet, "Table1");

// Get Table2 from DB
string query2 = "SELECT pk, fk, Value FROM Table2";
this.dataAdapter = new SqlDataAdapter(query2, connectionString);
this.dataAdapter.Fill(this.dataSet, "Table2");

// Create new table
DataTable dt = new DataTable("Table3");
dt.Columns.Add(new DataColumn("Id", typeof(string)));
dt.Columns.Add(new DataColumn("Value", typeof(string)));
dt.Columns.Add(new DataColumn("Value2", typeof(string)));
this.dataSet.Tables.Add(dt);

// Create a DataRelation between the 2 tables.
this.dataSet.Relations.Add("relate", this.dataSet.Tables["Table2"].Columns.["fk"], this.dataSet.Tables["Table1"].Columns.["pk"]);

// Fill in Table2 values.
foreach(DataRow row in this.dataSet.Tables["Table2"].Rows) {
  DataRow parent = row.GetParentRow("relate");
  DataRow current = this.dataSet.Table["Table3"].NewRow();
  current["Id"] = row["pk"];
  current["Value"] = row["Value"];
  current["Value2"] = parent["Value"];
  this.dataSet.Table["Table3"].Rows.Add(current);
}

myDataGrid.DataSource = this.dataSet.Tables["Table3"];
myDataGrid.DataBind();



The above is kinda ugly, hence my recommendation for a simple SQL join.

Nathan Downey
Thursday, October 03, 2002

Yes.  Just because you CAN do joins/filters in DataSets once you get them, doesn't mean that you SHOULD.  In some cases (disconnected DataSets, maybe) you can do this.  In general, though:

* Try and let the DB do as much processing on the data before it sends the result back to the client.  This is what DB's are good at, let the DB do the join/filter/sort and then return to you the data in the format that you like.

* Try to keep as little SQL statements in your source code as possible.  Try and move the SQL into stored procs in the DB and then call the stored procs from your code.  This provides a clean separation between your codebase and the DB logic.  What's easier: doing an extra build because you modified some SQL in the source code, or doing a simple database update of the stored procedure logic?

* If your data objects are starting to get unmanageable, it is time to step back NOW and figure out a new design, rather than just keep adding code and waiting until after the release to fix it.  Remember that bugs and poor design decisions are directly correlated...

Hope this helps...

Tim
Thursday, October 03, 2002

Thanks for all the replies.  This is a web application, not Windows.  I would like to do the join in the database and just pull from the database into my DataGrid.  However, two things are stopping me.  First, I will eventually want to make the DataGrid editable with the an UPDATE statement pushing the changes back to the database.  Second, the data is constantly changing and the SELECT is filtered by dates.  I suppose I can try to build a stored procedure that creates a join between the two tables and recieves the dates as parameters.  Then my DataGrid could just read the table created by the stored procedure.

For those interested my code is below.  I am open to changing the design of the whole project too.

---------------------------------------
private void Page_Load(object sender, System.EventArgs e)
    {
            
      if (!IsPostBack)
        {

        string stDate;
        string enDate;

        // set the default start date
                stDate = DateTime.Now.ToShortDateString();
        // set the default end date
                enDate = DateTime.Now.ToShortDateString();
                
        // Connection string to the rooms database
                        string connectionString = "provider=Microsoft.JET.OLEDB.4.0;" +"data source = c:\\DB/roomsDB.mdb";

    // Build the selection strings
        string eventsString =
                    "Select r.NAME, r.ROOM_NO, s.SLOT_DESC, s.SLOT_ID, ev.DATE, ev.NAME from ROOMS r, SLOTS s, EVENTS ev where r.ROOM_ID=ev.ROOM_ID and s.SLOT_ID = ev.SLOT_ID and ev.DATE between #" +
stDate +"# and #" +
enDate +"# order by ev.Date, s.SLOT_ID ASC";

        string slotsString =
    " Select * from SLOTS order by SLOT_ID ASC";

        // Create the DataAdapter object
        OleDbDataAdapter eventsDa = new OleDbDataAdapter (eventsString, connectionString);

        OleDbDataAdapter slotsDa = new OleDbDataAdapter (slotsString, connectionString);
                
    // Create the events DataSet
        DataSet eventsDs = new DataSet();

    // Fill the events DataSet
    eventsDa.FillSchema(eventsDs, SchemaType.Source, "Events");
    eventsDa.Fill(eventsDs, "Events");

    // Create the slots DataSet
    DataSet ds = new DataSet();

    // Fill the slots DataSet
    slotsDa.Fill(slotsDs, "Slots");

    // Merge the two DataSets
    eventsDs.Merge(slotsDs);

    // Add two DataTables to the DataSet
    DataTable eventsTable = ds.Tables.Add("Events");

    eventsTable.Columns.Add("Location", typeof(string));
                eventsTable.Columns.Add("SlotID", typeof(Int32));
                eventsTable.Columns.Add("Space", typeof(string));
                eventsTable.Columns.Add("Date", typeof(DateTime));
                eventsTable.Columns.Add("Time", typeof(string));
                eventsTable.Columns.Add("EventName", typeof(string));

                DataTable slotsTable = ds.Tables.Add("Slots");

                slotsTable.Columns.Add("SlotID", typeof(Int32));
                slotsTable.Columns.Add("Time", typeof(string));

    // Fill the DataSet
    eventsDa.Fill(ds, "Events");
    slotsDa.Fill(ds, "Slots");

    // Add a relationship from EVENTS to SLOTS
    DataRelation eventsSlotRel = ds.Relations.Add("EventSlots",
    ds.Tables["Events"].Columns["SlotID"],
    ds.Tables["Slots"].Columns["SlotID"], false);

// Use the MakeTable function below to create a new table.
    DataTable resultsTable;
    esultsTable = MakeResultsTable();

    //Once a table has been created, use the NewRow to create a DataRow.
    DataRow myRow;
    myRow = resultsTable.NewRow();

    //Then add the new row to the collection.
    for (int i = 0; i < eventsTable.Rows.Count; i++)
    {
    myRow[0] = ds.Tables["Events"].Rows[0];
    myRow[1] = ds.Tables["Events"].Rows[1];
    myRow[2] = ds.Tables["Events"].Rows[2];
    myRow[3] = ds.Tables["Slots"].Rows[3];
    myRow[4] = ds.Tables["Events"].Rows[4];
                    resultsTable.Rows.Add(myRow);
    }

                DataGrid1.DataSource = resultsTable;
    DataGrid1.DataBind();

                
    }
}

Ed Murphy
Tuesday, October 08, 2002

*  Recent Topics

*  Fog Creek Home