GridView动态添加列并判断绑定数据DataTable的列类型控制展示内容

此篇随笔是2013年根据项目需求开发记录的，不一定符合大众口味，只需了解开发思路，毕竟解决方案多种多样。

下面简单说说需求点吧：

（1）通过下拉列表可以选择一个DataSet（数据集），一个DataSet存在可以互相关联的多个DataTable（数据表格），DataTable数据来源于数据库视图；SQL语句关联比较复杂

（2）一个DataTable（数据表格）存在多个可供选择查询显示的Column（列），支持动态组合

（3）Column（列）同时支持作为查询条件进行并运算

先看效果吧，免得待会看到太多代码失去看下去的兴趣了：

（1）数据库方面

  1 USE [IMSDB]  2 GO  3   4 drop view View_CustomReport_ItemCategory  5 go  6 drop view View_CustomReport_ItemClassification  7 go  8   9 ---- the view total is 21 10 alter view View_CustomReport_BasicInfo 11 as 12     select iig.holding_id as 'Holding_Id', 13            row_number() over(order by iig.item_group_id) as 'Internal_ID', 14            od.dept_name as 'Holder_Department', 15            og.group_name as 'Holder_Group', 16            os.section_name as 'Holder_Section', 17            ou.unit_name as 'Holder_Unit', 18            iig.brand as 'Brand', 19            iig.model as 'Model', 20            iica.name_en as 'Category', 21            iicaSub.name_en as 'Sub____category', 22            iicl.name_en as 'Classification', 23            iiclSub.name_en as 'Sub____classification', 24            iig.item_desc as 'GF272_Description', 25            (case when iig.inventory_flg=1 then 'inventory'  26                  when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag', 27            (select ivc.balance  28             from is_view_item_group ivig 29             inner join is_view_column ivc 30                 on ivig.sheet_id = ivc.sheet_id  31                     and ivig.line_id = ivc.line_no  32                     and ivig.column_id = ivc.column_no 33             where ivig.item_group_id = iig.item_group_id) as 'Current_Qty_Balance', 34            iig.unit_of_qty as 'Unit_of_Qty' 35     from is_item_group iig 36     inner join is_inventory_holding_unit iihu 37         on iig.holding_id = iihu.holding_id 38     left join org_department od 39         on iihu.dept_id = od.dept_id 40     left join org_group og 41         on iihu.group_id = og.group_id 42     left join org_section os 43         on iihu.section_id = os.section_id 44     left join org_unit ou 45         on iihu.unit_id = ou.unit_id 46     left join is_item_category iica 47         on iig.item_cat_id = iica.item_cat_id 48     left join is_item_category iicaSub 49         on iig.item_sub_cat_id = iicaSub.item_cat_id 50     left join is_item_classification iicl 51         on iig.item_classic_id = iicl.item_classific_id 52     left join is_item_classification iiclSub 53         on iig.item_sub_classic_id = iiclSub.item_classific_id 54 go 55  56 alter view View_CustomReport_GF272SheetInfo 57 as 58     select iig.holding_id as 'Holding_Id', 59            ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000', 60            ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000', 61            ivc.line_no as 'Last_GF272_Valid_Line_No000', 62            ivc.column_no as 'Last_GF272_Valid_Column_No000', 63            ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000' 64     from is_item_group iig 65     inner join is_view_item_group ivig 66         on iig.item_group_id = ivig.item_group_id 67     inner join is_view_sheet ivs 68         on ivig.sheet_id = ivs.sheet_id 69     inner join is_view_column ivc 70         on ivs.sheet_id = ivc.sheet_id 71     inner join is_view_line ivl 72         on ivs.sheet_id = ivl.sheet_id 73 go 74  75  76 alter view View_CustomReport_LineItemInformation 77 as 78     select iig.holding_id as 'Holding_Id', 79            od.dept_name as 'Holder_Department', 80            og.group_name as 'Holder_Group', 81            os.section_name as 'Holder_Section', 82            ou.unit_name as 'Holder_Unit', 83            iig.brand as 'Brand', 84            iig.model as 'Model', 85            iica.name_en as 'Category', 86            iicaSub.name_en as 'Sub____category', 87            iicl.name_en as 'Classification', 88            iiclSub.name_en as 'Sub____classification', 89            iig.item_desc as 'GF272_Description', 90            (case when iig.inventory_flg=1 then 'inventory'  91                  when iig.inventory_flg=0 then 'Non inventory' end) as 'Inventory_Item_Flag', 92            ivs.unit_ref_no as 'Last_GF272_Valid_Unit_Ref_No000', 93            ivs.sheet_no as 'Last_GF272_Valid_Sheet_No000', 94            ivc.line_no as 'Last_GF272_Valid_Line_No000', 95            ivc.column_no as 'Last_GF272_Valid_Column_No000', 96            ivl.commodity_code_or_ref_no as 'Last_GF272_Valid_Commodity_Code__Reference_No000' 97     from is_item_group iig 98     inner join is_inventory_holding_unit iihu 99         on iig.holding_id = iihu.holding_id100     left join org_department od101         on iihu.dept_id = od.dept_id102     left join org_group og103         on iihu.group_id = og.group_id104     left join org_section os105         on iihu.section_id = os.section_id106     left join org_unit ou107         on iihu.unit_id = ou.unit_id108     left join is_item_category iica109         on iig.item_cat_id = iica.item_cat_id110     left join is_item_category iicaSub111         on iig.item_sub_cat_id = iicaSub.item_cat_id112     left join is_item_classification iicl113         on iig.item_classic_id = iicl.item_classific_id114     left join is_item_classification iiclSub115         on iig.item_sub_classic_id = iiclSub.item_classific_id116     inner join is_view_item_group ivig117         on iig.item_group_id = ivig.item_group_id118     inner join is_view_sheet ivs119         on ivig.sheet_id = ivs.sheet_id120     inner join is_view_column ivc121         on ivs.sheet_id = ivc.sheet_id122     inner join is_view_line ivl123         on ivs.sheet_id = ivl.sheet_id124 go125 126 alter view View_CustomReport_IndividualItemBasicInfo127 as128     select iig.holding_id as 'Holding_Id',129            ii.serial_no as 'Serial_No000',130            ii.barcode_no as 'IMS_Barcode_No000',131            ii.self_assign_id as 'Self_Assign_ID',132            ii.detailed_desc as 'Detailed_Description',133            (case when ii.item_status='RJ' then 'Reject'134                  when ii.item_status='TP' then 'Temp'135                  when ii.item_status='DR' then 'Draft'136                  when ii.item_status='N' then 'Normal'137                  when ii.item_status='UN' then 'Unserviceable'138                  when ii.item_status='TI' then 'Transfer'139                  when ii.item_status='TO' then 'TransferOutOfICAC'140                  when ii.item_status='D' then 'Disposed'141                  when ii.item_status='WO' then 'WriteOff'142                  when ii.item_status='SI' then 'SurplusItem'143                  when ii.item_status='R' then 'Repair'144                  when ii.item_status='TL' then 'TransferLedger' end) as 'Item_Status',145            ii.remarks as 'Remarks',146            ii.remarks2 as 'Remarks2',147            ii.remarks3 as 'Remarks3',148            ii.remarks4 as 'Remarks4'149     from is_item ii150     inner join is_item_group iig151         on ii.item_group_id = iig.item_group_id152 go153 154 alter view View_CustomReport_Location155 as156     select iig.holding_id as 'Holding_Id',157            (case when ii.head_quarter_flg=1 then 'Yes'158                  when ii.head_quarter_flg=0 then 'No' end) as 'Headquarter_Flag',159            il.loc_name as 'Location_District',160            il2.loc_name as 'Location_Building',161            il3.loc_name as 'Location_Floor',162            il4.loc_name as 'Location_Room',163            ii.loc_area as 'Location_Area'164     from is_item ii165     inner join is_item_group iig166         on ii.item_group_id = iig.item_group_id167     left join is_location il168         on ii.loc_district = il.loc_id169     left join is_location il2170         on ii.loc_building = il2.loc_id171     left join is_location il3