Datable 添加到Dataset 并且重新命名

View Code

1 string strSql = " SELECT * from vSelShipModeAUGReporting Where 1 = 1 " ;
2 string strWhere = string .Empty;
3
4 if ( strShipMode.Length > 0 )
5 {
6 if ( strShipMode.Trim().ToUpper() == Constants.FCL.Trim())
7 {
8 strWhere += " AND RTRIM([ShipMode]) = " + " ' " + strShipMode.Trim().ToUpper() + " ' " ;
9 }
10 else
11 {
12 strWhere += " AND ISNULL(IsNew, 0) = 0 " ;
13 strWhere += " AND RTRIM([ShipMode]) <> " + " ' " + Constants.FCL.Trim() + " ' " ;
14 }
15 }
16 if (strPickupFrom.Trim().Length > 0 )
17 {
18 strWhere += " AND CONVERT(VARCHAR(12),PickupDate,110) >= CONVERT(VARCHAR(12),' " + strPickupFrom.Trim() + " ',110) " ;
19 }
20 if (strPickupTo.Trim().Length > 0 )
21 {
22 strWhere += " AND CONVERT(VARCHAR(12),PickupDate,110) <= CONVERT(VARCHAR(12),' " + strPickupTo.Trim() + " ',110) " ;
23 }
24 strSql += strWhere + " ORDER BY AppointID ASC " ;
25
26 DataSet _result = new DataSet();
27 SQLHelper.FillDataSet(strSql,_result, " tb_AUGReport " );
28
29 // For same appointment, if container no is same, then use average value. (FCL)
30 if ( strShipMode.Trim().ToUpper() == Constants.FCL.Trim() )
31 {
32 string strSqlFCL = " SELECT APPOINTID,CONTAINERNO,COUNT(CONTAINERNO) AS COUNTNUM FROM vSelShipModeAUGReporting WHERE 1 = 1 " ;
33 strSqlFCL += strWhere + " GROUP BY APPOINTID,CONTAINERNO " ;
34
35 System.Data.DataTable dtCalculateSumContainerNO = new DataTable();
36 dtCalculateSumContainerNO = SQLHelper.getSelectData(strSqlFCL).Tables[ 0 ].Copy();
37 dtCalculateSumContainerNO.TableName = " dtCalculateSumContainerNO " ;
38 _result.Tables.Add(dtCalculateSumContainerNO);
39 }
40 // calculate trucking fee by LCL OR FCL
41 System.Data.DataTable dtCalculateSumTruckFee = new DataTable();
42 DateTime dtReceiveFrom = Utility.StringUtil.getMinDateIfEmpty(strPickupFrom);
43 DateTime dtReceiveTo = Utility.StringUtil.getMinDateIfEmpty(strPickupTo);
44 SqlParameter[] objParameters = {
45 new SqlParameter( " @StartDate " ,SqlDbType.SmallDateTime),
46 new SqlParameter( " @EndDate " ,SqlDbType.SmallDateTime),
47 new SqlParameter( " @ShippingMode " ,SqlDbType.VarChar, 5 )
48 };
49
50 if (dtReceiveFrom != DateTime.MinValue )
51 objParameters[ 0 ].Value = dtReceiveFrom;
52 if (dtReceiveTo != DateTime.MinValue )
53 objParameters[ 1 ].Value = dtReceiveTo;
54 objParameters[ 2 ].Value = strShipMode.Trim();
55 dtCalculateSumTruckFee = SQLHelper.dtRunProdure( " CCC_P_SelShipModeAUGReporting " ,objParameters, " tb_CalculateSumTruckFee " ).Tables[ 0 ].Copy();
56
57 System.Data.DataTable dtCalculateSumCONSOTruckFee = new DataTable();
58
59 if ( strShipMode.Trim().ToUpper() == Constants.LCL.Trim() )
60 {
61 // calculate trucking fee by CONSO
62 SqlParameter[] objParameters2 = {
63 new SqlParameter( " @StartDate " ,SqlDbType.SmallDateTime),
64 new SqlParameter( " @EndDate " ,SqlDbType.SmallDateTime),
65 new SqlParameter( " @ShippingMode " ,SqlDbType.VarChar, 5 )
66 };
67 if (dtReceiveFrom != DateTime.MinValue )
68 objParameters2[ 0 ].Value = dtReceiveFrom;
69 if (dtReceiveTo != DateTime.MinValue )
70 objParameters2[ 1 ].Value = dtReceiveTo;
71 objParameters2[ 2 ].Value = Constants.CONSO.Trim();
72 dtCalculateSumCONSOTruckFee = SQLHelper.dtRunProdure( " CCC_P_SelShipModeAUGReporting " ,objParameters2, " tb_CalculateSumCONSOTruckFee " ).Tables[ 0 ];
73 }
74
75 if ( dtCalculateSumCONSOTruckFee.Rows.Count > 0 )
76 {
77 foreach ( DataRow dr in dtCalculateSumCONSOTruckFee.Rows)
78 {
79 dtCalculateSumTruckFee.Rows.Add(dr.ItemArray);
80 }
81 }
82
83 _result.Tables.Add(dtCalculateSumTruckFee);
84
85 // _result = SQLHelper.getSelectData(strSql);
86 return _result;

转载于:https://www.cnblogs.com/zhangchenliang/archive/2011/02/16/1956259.html