當資料來源為XML時，當你轉換成dataset時，會有資料變成多個table的情況

透過 LINQ 去 Join 2 個DataTable，並回傳DataTable

Join

1
2
3
4
5
6
7
8
9
10
11
12
13
var myTable = from PricedItineraryTB in myDS.Tables["PricedItinerary"].AsEnumerable()
                          join AirItineraryTB in myDS.Tables["AirItinerary"].AsEnumerable() 
                                    on PricedItineraryTB.Field<int>("PricedItinerary_Id") equals AirItineraryTB.Field<int>("PricedItinerary_Id")
                          select new
                          {
                              PricedItinerary_Id = PricedItineraryTB.Field<int>("PricedItinerary_Id"),
                              DirectionInd = AirItineraryTB.Field<string>("DirectionInd")
                          };
DataTable dt = LinqQueryToDataTable(myTable);
return dt;

要用for或是foreach組成Datatable是一件很麻煩的事情
將此方法可以寫成一個component或是method，下次用到就簡單多了

IEnumerable轉成DataTable

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
public static DataTable LinqQueryToDataTable<T>(IEnumerable<T> query)
        {
            DataTable tbl = new DataTable();
            PropertyInfo[] props = null;
            foreach (T item in query)
            {
                if (props == null) //尚未初始化
                {
                    Type t = item.GetType();
                    props = t.GetProperties();
                    foreach (PropertyInfo pi in props)
                    {
                        Type colType = pi.PropertyType;
                        //針對Nullable<>特別處理
                        if (colType.IsGenericType && colType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }
                        //建立欄位
                        tbl.Columns.Add(pi.Name, colType);
                    }
                }
                DataRow row = tbl.NewRow();
                foreach (PropertyInfo pi in props)
                {
                    row[pi.Name] = pi.GetValue(item, null) ?? DBNull.Value;
                }
                tbl.Rows.Add(row);
            }
            return tbl;
        }

參考

參考