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 il3172     on ii.loc_floor = il3.loc_id173   left join is_location il4174     on ii.loc_room = il4.loc_id175 go176 177 alter view View_CustomReport_PhysicalCheckInfo178 as179   select iic.holding_id as 'Holding_Id',180      icld.update_datetime as 'Last_Physical_Checked_Date',181      icld.check_by as 'Last_Physical_Checked_By',182      (case when iic.check_status='IP' then 'InProgress'183         when iic.check_status='CP' then 'Completed'184         when iic.check_status='SC' then 'SoConfirm' end) as 'Last_Check_Status'185   from is_check_list icl186   inner join is_check_list_detail icld187     on icl.list_id = icld.list_id and icld.is_manual_check=1188   inner join is_inventory_check iic189     on icl.check_id = iic.check_id190 go191 192 alter view View_CustomReport_Aggregate193 as194   select iig.holding_id as 'Holding_Id',195      ii.barcode_no as 'IMS_Barcode_No000_of_Aggregate_Parent'196   from is_item ii197   inner join is_item_group iig198     on ii.item_group_id = iig.item_group_id199   inner join is_item_aggregate_map iiam200     on ii.item_id = iiam.parent_item_id201 go202 203 alter view View_CustomReport_PurchaseInfo204 as205   select iig.holding_id as 'Holding_Id',206      ii.pr_ref_no as 'Purchase_PR_Ref_No000',207      ii.quot_ref_no as 'Purchase_ICAC_Quotation_Ref_No000',208      ii.purchase_mode as 'Purchase_Mode',209      ii.po_no as 'Purchase_PO_No000___Ref',210      ii.invoice_no as 'Purchase_Invoice_No000',211      ii.date_of_order as 'Purchase_Date_of_Order',212      ii.date_of_receipt as 'Purchase_Date_of_Receipt',213      ii.date_of_acceptance as 'Purchase_Date_of_Acceptance',214      icim.contract_month as 'Free_Warranty_Period',215      ii.holder_file_ref as 'Holder_File_Reference',216      ii.contact_person as 'Contact_Person',217      ii.contact_person_post as 'Contact_Person_Position',218      itdi.voucher_type as 'Purchase_Related_Voucher_Type',219      itdi.voucher_no as 'Purchase_Related_Voucher_No000'220   from is_item ii221   inner join is_item_group iig222     on ii.item_group_id = iig.item_group_id223   inner join(224     select item_id,225        datediff(month,contract_start_date,contract_end_date) as 'contract_month'226     from is_contract_item_map227     where contract_id in(228       select min(contract_id) from is_contract_item_map229       group by item_id230     )231   ) as icim232     on ii.item_id = icim.item_id233   inner join(234     select itdi.item_id,itr.voucher_type,itr.voucher_no235     from is_tx_record itr 236     inner join is_tx_detail_item itdi237       on itr.tx_id = itdi.tx_id238     where itdi.tx_id in(239       select min(tx_id) from is_tx_detail_item240       group by item_id241     )242   ) as itdi243     on ii.item_id = itdi.item_id244 go245 246 create function Func_StrArrayStrOfIndex 247 ( 248  @str varchar(1024), --要分割的字符串 249  @split varchar(10), --分隔符号 250  @index int --取第几个元素 251 ) 252 returns varchar(1024) 253 as 254 begin 255  declare @location int 256  declare @start int 257  declare @next int 258  declare @seed int 259  260  set @str=ltrim(rtrim(@str)) 261  set @start=1 262  set @next=1 263  set @seed=len(@split) 264   265  set @location=charindex(@split,@str) 266  while @location<>0 and @index>@next 267  begin 268   set @start=@location+@seed 269   set @location=charindex(@split,@str,@start) 270   set @next=@next+1 271  end 272  if @location =0 select @location =len(@str)+1273   274  return subget='_blank'>string(@str,@start,@location-@start) 275 end 276 go277 alter view View_CustomReport_LoanInfo278 as279   select iig.holding_id as 'Holding_Id',280      (case when ii.loan_flg=1 then 'On Loan'281         when ii.loan_flg=0 then 'In Hand' end) as 'Current_Loan_Status',282      isc.english_name as 'Staff_Name_of_Borrower',283      dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',1) as 'Borrower111s_Department',284      dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',2) as 'Borrower111s_Group',285      dbo.Func_StrArrayStrOfIndex(isc.dept_group_section,'/',3) as 'Borrower111s_Section'286   from is_item ii287   inner join is_item_group iig288     on ii.item_group_id = iig.item_group_id289   left join is_loan_record ilr 290     on ii.loan_id = ilr.loan_id291   left join is_staff_card isc 292     on ilr.borrower_id = isc.staff_card_id293 go294 295 alter view View_CustomReport_IssueInfo296 as297   select iig.holding_id as 'Holding_Id',298      (case when ii.issue_flg=1 then 'Issued'299         when ii.issue_flg=0 then 'Not Issued' end) as 'Current_Issue_Status',300      od.dept_name as 'Issue_Dept',301      og.group_name as 'Issue_Group',302      os.section_name as 'Issue_Section',303      ou.unit_name as 'Issue_Unit'304   from is_item ii305   inner join is_item_group iig306     on ii.item_group_id = iig.item_group_id307   left join org_department od308     on ii.issued_dept_id = od.dept_id309   left join org_group og310     on ii.issued_group_id = og.group_id311   left join org_section os312     on ii.issued_section_id = os.section_id313   left join org_unit ou314     on ii.issued_unit_id = ou.unit_id315 go316 317 alter view View_CustomReport_EndUserInfo318 as319   select iig.holding_id as 'Holding_Id',320      ieur.end_user as 'Current_Item_End____user',321      convert(varchar(10),ieur.assign_date,105)+' to '+convert(varchar(10),ieur.return_date,105) as 'Usage_Date'322   from is_item ii323   inner join is_item_group iig324     on ii.item_group_id = iig.item_group_id325   inner join is_end_user_record ieur326     on ii.item_id = ieur.item_id327 go328 329 alter view View_CustomReport_SurplusInfo330 as331   select iig.holding_id as 'Holding_Id',332      (case when ii.item_status='SI' then 'Yes'333                      else 'No' end) as 'Surplus_Status',334      isipo.[start_date] as 'Surplus_Post_Out_Date_From',335      isipo.end_date as 'Surplus_Post_Out_Date_To'336   from is_item ii337   inner join is_item_group iig338     on ii.item_group_id = iig.item_group_id339   inner join(340     select item_id,[start_date],end_date341     from is_surplus_item_post_out342     where request_id in(343       select max(request_id) from is_surplus_item_post_out344       group by item_id345     )346   ) as isipo347     on ii.item_id = isipo.item_id348 go349 350 alter view View_CustomReport_DisposalInfo351 as352   select iig.holding_id as 'Holding_Id',353      ii.anticipated_date_disposal as 'Anticipated_Disposal_Date',354      (case when idr.disposal_status='R' or idr.disposal_status='V' then 'Not Applicable'355         when idr.disposal_status='D' or idr.disposal_status='W' then 'Requesting'356         when idr.disposal_status='T' then 'Approved by SO'357         when idr.disposal_status='P' then 'Physically Disposed' end) as 'Disposal_Status',358      idr.job_no as 'Dispose_Job_No000',359      idr.dumping_date as 'Date_of_Disposal',360      idr.item_condition as 'Condition_of_Item_in_Disposal',361      idr.disposal_method as 'Disposal_Method',362      itr.voucher_no as 'Disposal_Related_Voucher_No000'363   from is_item ii364   inner join is_item_group iig365     on ii.item_group_id = iig.item_group_id366   inner join is_disposal_record idr367     on ii.item_id = idr.item_id and iig.holding_id = idr.holding_id368   left join is_request ir369     on idr.request_id = ir.request_id370   left join is_tx_record itr371     on ir.out_tx_id = itr.tx_id372 go373 374 alter view View_CustomReport_RepairInfo375 as376   select iig.holding_id as 'Holding_Id',377      (case when ii.item_status='R' then 'Yes'378                     else 'No' end) as 'On_Repair_Status'379   from is_item ii380   inner join is_item_group iig381     on ii.item_group_id = iig.item_group_id382 go383 384 alter view View_CustomReport_ReplaceInfo385 as386   select iig.holding_id as 'Holding_Id',387      (case when ii.replace_flg=1 then 'Yes'388                     else 'No' end) as 'Replaced_Status',389      ii.barcode_no as 'IMS_Barcode_No_of_Replacing_Item',390      itr.voucher_no as 'Voucher_No_for_Replaced_Item'391   from is_item ii392   inner join is_item_group iig393     on ii.item_group_id = iig.item_group_id394   inner join(395     select itdi.item_id,itr.voucher_no396     from is_tx_record itr397     inner join is_tx_detail_item itdi398     on itr.tx_id = itdi.tx_id399     where itdi.tx_id in(400       select max(tx_id) from is_tx_detail_item401       group by item_id402     )403   ) as itr404     on ii.item_id = itr.item_id405 go406 407 alter view View_CustomReport_DelegateMaintUserInfo408 as409   select iig.holding_id as 'Holding_Id',410      (case when ii.maint_dept_id is null then 'No'411         when ii.maint_dept_id=0 then 'No'412                     else 'Yes' end) as 'Delegated_to_others_for_Maintenance',413      od.dept_name as 'Maintenance_Dept',414      og.group_name as 'Maintenance_Group',415      os.section_name as 'Maintenance_Section',416      ou.unit_name as 'Maintenance_Unit'417   from is_item ii418   inner join is_item_group iig419     on ii.item_group_id = iig.item_group_id420   left join org_department od421     on ii.maint_dept_id = od.dept_id422   left join org_group og423     on ii.maint_group_id = og.group_id424   left join org_section os425     on ii.maint_section_id = os.section_id426   left join org_unit ou427     on ii.maint_unit_id = ou.unit_id428 go429 430 alter view View_CustomReport_MaintenanceBasic431 as432   select iig.holding_id as 'Holding_Id',433      ivm.vote_name as 'Maintenance_Vote',434      ii.remarks_maint as 'Remarks_for_Maintenance',435      img.grouping_name as 'Maint000_Grouping',436      iig.gld_contract_no as 'GLD_Contract_No000',437      (case when iig.further_maint_flag=1 then 'Yes'438                        else 'No' end) as 'Needed_to_be_Maintenance_Afterwards'439   from is_item ii440   inner join is_item_group iig441     on ii.item_group_id = iig.item_group_id442   inner join is_maint_grouping img443     on iig.maint_grouping = img.[grouping_id]444   left join is_contract_item_map icim445     on ii.item_id = icim.item_id446   left join is_vote_maintenance ivm447     on icim.vote = ivm.vote_id448 go449 450 alter view View_CustomReport_CurrentMaintenanceInfo451 as452   select iig.holding_id as 'Holding_Id',453      imc.ref_contract_id as 'Current_Maintenance_Contract_User_Reference_No000',454      iv.vendor_name as 'Current_Maintenance_Vendor',455      ii.annual_maint_cost as 'Current_Maintenance_Unit_Cost',456      icim.contract_start_date as 'Current_Maintenance_Coverage_Start_Date',457      icim.contract_end_date as 'Current_Maintenance_Coverage_End_Date',458      iml.level_desc as 'Current_Maintenance_Level',459      imc.bu_date as 'Next_Maintenance_BU_Date'460   from is_item ii461   inner join is_item_group iig462     on ii.item_group_id = iig.item_group_id463   left join is_contract_item_map icim464     on ii.item_id = icim.item_id465   left join is_maintenance_contract imc466     on icim.contract_id = imc.contract_id467   left join is_vender iv468     on icim.vendor_id = iv.vendor_id469   left join is_maint_level iml470     on icim.level_of_maint = iml.level_code471 go472 473 474 alter view View_CustomReport_ContractHeader475 as476   select iig.holding_id as 'Holding_Id',477      imc.ref_contract_id as 'Contract_User_Reference_No000',478      imc.contract_name as 'Contract_Name',479      (case when imc.maint_pr_no='null' then ''480                       else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',481      icim.contract_start_date as 'Contract_Start_Date',482      icim.contract_end_date as 'Contract_End_Date',483      imc.contact_person as 'Contact_Person',484      imc.contact_person_post as 'Contact_Post',485      imc.bu_date as 'BU_Date'486   from is_item ii487   inner join is_item_group iig488     on ii.item_group_id = iig.item_group_id489   left join is_contract_item_map icim490     on ii.item_id = icim.item_id491   left join is_maintenance_contract imc492     on icim.contract_id = imc.contract_id493 go494 495 alter view View_CustomReport_ItemsVendorInfo496 as497   select imc.holding_id as 'Holding_Id',498      iv.vendor_name as 'Maint_Vendor',499      icvm.contact_person as 'Maint_Vendor_Contact_Person',500      icvm.contact_person_post as 'Maint_Vendor_Contact_Person_Position',501      icvm.quotation_no as 'Maint_Vendor_Quotation_No000',502      icvp.maint_op_no as 'Maint_Po_No000__Ref',503      icvp.group_num as 'Payment_Schedule',504      icvp.actual_payment_date as 'Payment_Date',505      icvp.paid_amount_hkd as 'Paid_Amount',506      (case when icvp.payment_status='1' then 'Paid'507         when icvp.payment_status='0' then 'Not Paid' end) as 'Payment_Status',508      icvp.invoice_no as 'Invoice_No000',509      icvp.file_ref_no as 'File_Ref_No000'510   from is_vender iv511   inner join is_contract_vendor_map icvm512     on iv.vendor_id = icvm.vendor_id513   left join (514     select contract_id,515         vendor_id,516         maint_op_no,517         actual_payment_date,518         paid_amount_hkd,519         payment_status,520         invoice_no,521         file_ref_no,522        row_number() over(partition by contract_id,vendor_id 523                 order by actual_payment_date) as 'group_num'524     from is_contract_vendor_payment525   ) as icvp526     on icvm.contract_id = icvp.contract_id and icvm.vendor_id = icvp.vendor_id527   inner join is_maintenance_contract imc528     on icvm.contract_id = imc.contract_id529 go530 531 alter view View_CustomReport_ItemsMaintenanceBasicInfo532 as533   select iig.holding_id as 'Holding_Id',534      ii.serial_no as 'Item_Serial_No000',535      ii.barcode_no as 'IMS_Item_Barcode_No000',536      iig.maint_grouping as 'Maintenance_Grouping',537      ivm.vote_name as 'Maintenance_Vote',538      ii.remarks_maint as 'Remarks_for_Maintenance',539      img.grouping_name as 'Maint000_Grouping',540      iig.gld_contract_no as 'GLD_Contract_No000',541      (case when iig.further_maint_flag=1 then 'Yes'542                        else 'No' end) as 'Needed_to_be_Maintenance_Afterwards',543      (case when imc.maint_pr_no='null' then ''544                       else imc.maint_pr_no end) as 'Maint_PR_Ref_No000',545      icim.cost_total_hkd as 'Maintenance_Unit_Cost',546      icim.paid_total_price_hkd as 'Maintenance_Unit_Actual_Cost',547      icim.contract_start_date as 'Maintenance_Coverage_Start_Date',548      icim.contract_end_date as 'Maintenance_Coverage_End_Date',549      iml.level_desc as 'Maintenance_Level',550      icim.remarks as 'Item_Remarks_in_this_Contract'551   from is_item ii552   inner join is_item_group iig553     on ii.item_group_id = iig.item_group_id554   inner join is_maint_grouping img555     on iig.maint_grouping = img.[grouping_id]556   left join is_contract_item_map icim557     on ii.item_id = icim.item_id558   left join is_vote_maintenance ivm559     on icim.vote = ivm.vote_id560   left join is_maintenance_contract imc561     on icim.contract_id = imc.contract_id562   left join is_maint_level iml563     on icim.level_of_maint = iml.level_code564 go

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

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

关键词：GridView