0

I have 2 DataTables formed by 2 complex queries, the results of which I want to merge and bind to datagridview, in C# code. i did not want to do any joins again of the 2 sqls since its already complex, and I use dynamic sql for this in C# code. (sql server compact - SPs not possible).

the code I used:

dataT = FkDataAccess.GetDataPostsCars(selTmpMPs,fromDateTime, toDateTime);
dataTLocationsTransposed = FkDataAccess.GetLocationDetailsTransposed();
dataTMerged = dataTLocationsTransposed.Copy();
dataTMerged.Merge(dataT);
this.dataGridView1.DataSource = dataTMerged;

dataT result has columns with some data:

<<== marked is the expected row from dataTLocationsTransposed to be merged to dataT

MonitorDateTime     MainGate        SouthAdminBuilding  WestGate    
                    Main-Date det.. South side of admin…west-gate det…<<==  
01.02.2014 13:00    6               5                   3         <-dataT
21.02.2014 14:00    45              6                   8         <-dataT

dataTLocationsTransposed obtains details of the above columns and i tested with 2 queries, Q1 and Q2. Q1 works, added as a new row in the dataT column struct, but it creates 4 other columns names Column1, etc.. in addition to the one shown above.. I need the results just below the dataT column struct.

Q1:

SELECT '', 
MAX(CASE WHEN LocColumn = 'MainGate' THEN LocDescription ELSE '' END), 
MAX(CASE WHEN LocColumn = 'SouthAdminBuilding' THEN LocDescription ELSE '' END), 
MAX(CASE WHEN LocColumn = 'WestGate' THEN LocDescription ELSE '' END) 
FROM MonitorLocationDetails 
WHERE LocColumn IN ('MainGate','SouthAdminBuilding','WestGate')

this produces results like :

col1 col2 col3 col4 MonitorDateTime MainGate SouthAdminBuilding WestGate

expected what i want is without the col1, col2..... but just underneath the specified column names.

So when i modify Q1 to Q2 which also yields result, but it fails at the Merge call from C#.

Q2:

SELECT '' AS MonitorDateTime, 
MAX(CASE WHEN LocColumn = 'MainGate' THEN LocDescription ELSE '' END) 
AS MainGate, 
MAX(CASE WHEN LocColumn = 'SouthAdminBuilding' THEN LocDescription 
ELSE '' END) AS SouthAdminBuilding, 
MAX(CASE WHEN LocColumn = 'WestGate' THEN LocDescription ELSE '' END) 
AS WestGate 
FROM MonitorLocationDetails 
WHERE LocColumn IN ('MainGate','SouthAdminBuilding','WestGate')

query for the other merged datatable dataT is:

SELECT T.MonitorDateTime,T.MainGate,T.SouthAdminBuilding,T.WestGate 
FROM GdRateTempPosts T WHERE T.MonitorDateTime BETWEEN @fromDateTime 
AND @toDateTime ORDER BY T.MonitorDateTime

any help much appreciated. Thank you.

Nish
  • 19
  • 7
  • Can you post both queries being merged? Is it possible that the data types don't match even if the column names do? For example MonitorDateTime is a VARCHAR in your Q2 example. Is it a DATETIME in the other table? – Ty H. Feb 28 '15 at 16:40
  • hi Tv H. i have edited the comment under "query for the other merged datatable dataT" the MonitorDateType in the other data table dataT is of DateTime type. how can i specify this in Q2. there is not datetime for that that one in Q2. in the original table null is allowed on the datetime field – Nish Feb 28 '15 at 16:52
  • Why do you have MAX function in your first query without a GROUP BY clause? – Ty H. Feb 28 '15 at 16:59
  • oh! cause it was working without the group by. why do we need one there? See the related question in http://stackoverflow.com/questions/28755547/display-result-as-1-row-in-transposed-pivoted-result-of-sub-query-in-sql-compact – Nish Feb 28 '15 at 17:01
  • No you don't need a GROUP BY, but you also don't need the MAX function. In this example it is costing cpu cycles without having any affect on the value. Since there is not GROUP BY the query is running a MAX function on a single value. The MAX of 5 will always be five.... etc. – Ty H. Feb 28 '15 at 17:19
  • The reason why I added a MAX was i was getting empty cells due to the fact that it result had multiple rows, when i wanted it as 1 single row. then wewesthemenace suggested to add MAX to get it this way. See stackoverflow.com/questions/28755547/… moreover this table rows are always fixed and has only about 60 rows. If GroupBy is used it would be on which column? 'LocColumn' ? – Nish Feb 28 '15 at 17:56
  • In general, you would GROUP BY all the columns that you aren't using a function like MIN,MAX,AVG, etc. In this case that would give you one row per MonitorDateTime, but because MonitorDateTime is a single value '' or NULL... that means you would only ever get one row from the query. Perhaps that is what you want... if not then you don't need GROUP AND MAX. You might just use COALESCE to replace a NULL value with a default value. – Ty H. Feb 28 '15 at 18:06
  • well. I have the result as 1 row with the query which is what I wanted and I have it. But any ideas to the main issue describe here. I have tried both exps 1 and 2. for details of why I had to use MAX please refer http://stackoverflow.com/questions/28755547/display-result-as-1-row-in-transposed-pivoted-result-of-sub-query-in-sql-compact – Nish Feb 28 '15 at 18:18
  • I've gotta run, but since the experiment worked, we know there must be something different about these two datasets that prevents the merge. The difference is either a column name or a data type. Carefully confirm that these are identical and you may find your answer. Otherwise... Here's another idea, use LINQ Union instead. http://stackoverflow.com/questions/720609/merge-two-object-lists-with-linq – Ty H. Feb 28 '15 at 18:23
  • no i meant it did not work, :-) see my comment to your answer section – Nish Feb 28 '15 at 18:24
  • If you can merge each table to itself successfully that your merge code is written correctly, if not... than not. LINQ may be a lot easier. I'll check in later. – Ty H. Feb 28 '15 at 18:26
  • ok.. I had tried experiment 1 by merging each dataT to itself(works) and then tried 2. with the original Merge. (nothing returned) – Nish Feb 28 '15 at 18:29
  • Hi Ty. Thanks for the hint with LINQ. I made it to work finally. created 2 List items and added the datarows of each datatable 1 and 2 to those. then performed a Union of the lists and CopToDataDataTable and it worked. I will post the answer with code separately – Nish Feb 28 '15 at 22:07

2 Answers2

0

T.MonitorDateTime appears to be a DATETIME value

'' AS MonitorDateTime is a VARCHAR

My guess is that it won't merge these unless the data types match. Cast all your dates as VARCHAR or use NULL instead of ''.

Ty H.
  • 905
  • 8
  • 8
  • I used NULL eg: SELECT NULL AS MonitorDateTime, .. followed by the rest.. still no go.. what else can i try? – Nish Feb 28 '15 at 17:02
  • 1. Run an experiment... instead of merging the two different queries... merge each one to itself just to see if it works. If it doesn't than we know the problem has nothing to do with column names or data types... if it does then... – Ty H. Feb 28 '15 at 17:21
  • 2. Return an explicit date in that MonitorDateTime column instead of NULL or ''. Return 1/1/1900 for example and see if that fixes the merge. – Ty H. Feb 28 '15 at 17:22
  • casting all the other date data into varchar will affect my dynamic chart and so on.. cause it used the datatable as datetime type. and i dont want to mess it up – Nish Feb 28 '15 at 17:24
  • tried experiment 1 by merging each dataT to itself(works) and then tried 2. with the original Merge. (nothing returned) :-( – Nish Feb 28 '15 at 17:35
0

Turned out to work using LINQ Union. Created 2 List objects and added the datarows of each datatable 1 and 2 to those. then performed a Union of the lists and CopToDataDataTable() and it worked.

dataTLocationsTransposed =  FkDataAccess.GetLocationDetailsTransposed(selectedPostCombined);
            List<DataRow> list1 = new List<DataRow>();
            foreach (DataRow dr in dataT.Rows)
            {
                list1.Add(dr);
            }
            List<DataRow> list2 = new List<DataRow>();
            foreach (DataRow dr in dataTLocationsTransposed.Rows)
            {
                list2.Add(dr);
            }
            dataTMerged = list2.Union(list1).CopyToDataTable();
            this.dataGridView1.DataSource = dataTMerged;
Nish
  • 19
  • 7