What's the best way to add a column to an existing dataset?

Question

Okay, my description is a little long-winded but bear with me.

I have two extremely long queries that I am running on multiple tables, using an INNER JOIN. The only difference between these two queries is that they take different input values for one of the WHERE clauses, which they get from two separate DropDownLists. The results of these queries are identical, save for the time1/time2 columns.

string query1 = "select Name = t1.name, time1=(SELECT hours FROM table3 WHERE street=list1.selectedvalue), t2.address from [table1] t1 INNER JOIN [table2] t2 ON t1.param = t2.param ORDER BY Name";

string query2 = "select Name = t1.name, time1=(SELECT hours FROM table3 WHERE street=list2.selectedvalue), t2.address from [table1] t1 INNER JOIN [table2] t2 ON t1.param = t2.param ORDER BY Name";

(Please don't bother with the syntax of the queries above, I have executed the queries and they both return the expected results.)

I would like to merge these two tables into a single dataset (or any other structure than can be used as a datasource for a gridview). The 'name' column is unique, some others might be too, I can check. I want the final table columns to resemble

Name Time1 Time2 Analysis Address

• The order of the columns doesn't matter very much, but if it's not too much work, I would prefer the order above.
• I would like to carry out mathematical operations on time1 and time2, and accordingly update the analysis column. Am I better off doing this in C# or JS?

Currently, I am using a datatable to collect the results of the two queries. I tried using the Datatable.Merge method, but that actually doubles the number of rows (kind of like an outer join) in spite of naming the columns the same (in the columns.Add(new DataColumn...)

Thanks for looking :) I appreciate your input.

            Datatable dt1 = new datatable;
            Datatable dt2 = new datatable;

            dt1.Columns.Add(new DataColumn("name", typeof(string)));
            dt1.Columns.Add(new DataColumn("time1", typeof(int)));
            dt1.Columns.Add(new DataColumn("time2", typeof(int)));
            dt1.Columns.Add(new DataColumn("analysis", typeof(string)));
            dt1.Columns.Add(new DataColumn("address", typeof(string)));

            SqlDataReader dr = cmd1.ExecuteReader();
            while (dr.Read())
            {
                DataRow drow = new DataRow();
                drow["name"] = dr["name"].ToString();
                drow["time1"] = dr["time1"];
                drow["address"] = dr["CurrentTime"].ToString();
                dt1.Rows.Add(drow);
            }
            dr.Close();

            dr = cmd2.ExecuteReader();
            while (dr.Read())
            {
                DataRow drow = new DataRow();
                drow["time2"] = dr["time1"];
                drow["analysis"] = "I need javascript here";
                dt2.Rows.Add(drow);
            }
            dr.Close();

            dt1.Merge (dt2);
            this.GridView1.DataSource = dt1;
            this.GridView1.DataBind;

Answer 1

The answer can be found here. With a few tweaks, I got it to do exactly what I wanted. http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/f122d7f4-3b7d-4d93-bd0f-8bb57cd990a4/