gridview中SQL的复杂过滤 [英] Complex filtering for SQL in gridview

查看：86
本文介绍了gridview中SQL的复杂过滤的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表单（此处查看测试版本： http://www.albemarle.org/nativeplantstest [ ^ ]（必须选择查看ALL，因为选择不起作用）观众可以选择任何显示具有所选特征的已找工厂列表的标准数量 - 我们不关心它们是否选择互斥选项，但这可以构成一个非常复杂的Where子句。使用它们选择的表单字段在VB中很容易实现，但是我不知道如何在Visual Studio 2008 gridview中创建这个where子句（原始程序只是将选定的条件传递给plain.asp作为会话变量（你可以在这里看到那个版本：http://www.albemarle.org/nativeplants [ ^ ]）。



Gridview中显示的6个列是使用Eval语句在代码隐藏中创建的（ text ='<％＃BuildNamesCol（Eval（TheNameCol））％>'/> ）按下受保护的函数BuildNamesCol（ByVal TheNames As String）As String where where TheNameCol 位于gridview的SelectCommand中的Select语句：



I have a form (view test version here: http://www.albemarle.org/nativeplantstest[^] (must choose to view ALL as selection is not working) where the viewer can select any number of criteria to display a list of found plants with the selected characteristics--we don't care if they select mutually exclusive options, but this can make a very complex Where clause. It's easily doable in VB using the form fields they selected, but I don't know how to create this where clause in a Visual Studio 2008 gridview (the original program simply passed the selected criteria off to a "plain" .asp as session vars (you can see that version here: http://www.albemarle.org/nativeplants[^] ).

The 6 displayed columns in the Gridview are created in the codebehind using Eval statements (text='<%# BuildNamesCol(Eval("TheNameCol")) %>' /> ) to massage the info in "Protected Function BuildNamesCol(ByVal TheNames As String) As String" where field TheNameCol is on the Select statement in the gridview's SelectCommand:

SELECT keyID, strToken,(strSciName + '|' + strCommonName + '|' + strCategory + '|' + flgNativeTo + '|' + flgRecommended ) as TheNameCol, [additional fields follow]



和TheNames是连接的字段SQL上的 TheNameCol 。然后可以轻松地将这些cols分离出来并返回到gridview上相应列中的内容。这一切都很好。



但是，鉴于Where子句有多复杂（多达25个左右的条件，大多数都有多个AND和OR语句），我' d FAR宁愿通过后面的代码来做，也就是Eval语句的等价，我猜，但是对于gridviews来说是新手，我根本就不知道这样做的语法。我确实看过FilterExpression和FilterParameter选项，但它似乎不适用于此处。此外，这不是存储过程，FWIW。



任何帮助表示赞赏！



TIA

Elaine



PS这是实际的gridview代码，如果这会有所帮助 - 我在这里通过删除其他模板字段来缩小它以仅显示代码第一列，因为其他列非常相似：




and "TheNames" is the concatenated fields in TheNameCol on the SQL. Those cols are then easily separated out and returned back for what you see in the respective column on the gridview. This all works fine.

However, given how complex the Where clause is (as many as 25 or so conditions, most with multiple AND and OR statements) , I'd FAR rather do it via the code behind, i.e, the equivalent of the Eval statement, I guess, but being new to gridviews, I simply don't know the syntax to do that. I did look at the FilterExpression and FilterParameter options, but it didn't seem like it would apply here. Also, this is not a stored procedure, FWIW.

Any help appreciated!

TIA
Elaine

PS Here's the actual gridview code if that will help--I condensed it here by removing other template fields to show only the code for the first column, as the others are very similar:

<asp:GridView ID="gvPlantList" runat="server" AllowPaging="True" 

        CssClass="bodytype" HorizontalAlign="Center" PageSize="100" 

        UseAccessibleHeader="False" Width="100%" AutoGenerateColumns="False" DataKeyNames="keyID" 

        DataSourceID="SqlDataSource1">

        <columns>
           <asp:TemplateField  HeaderText="Scientific/Common Name"  HeaderStyle-Font-Bold="true"  HeaderStyle-BackColor="#009900" HeaderStyle-ForeColor="White" HeaderStyle-Font-Size="Medium">
                 <itemtemplate>
                  <asp:label ID="lblNames" runat="server" text='<%# BuildNamesCol(Eval("TheNameCol")) %>' />
                 </itemtemplate>
                 <itemstyle horizontalalign="Left" verticalalign="Top" />
           
        </columns>

   
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 

        ConnectionString="<%$ ConnectionStrings:NativePlantsTESTConnectionString %>" 

        SelectCommand="SELECT keyID, strToken,(strSciName + '|' + strCommonName + '|' + strCategory + '|' + flgNativeTo + '|' + flgRecommended ) as TheNameCol, flgEvaluated, flgAvail,  coalesce(flgPNLightFull,'') + '|' + coalesce(flgPNLightPartialSun,'') + '|' + coalesce(flgPNLightShade,'') + '|' + coalesce(flgPNMoistureLow,'') + '|' + coalesce(flgPNMoistureMed,'') + '|' + coalesce(flgPNMoistureHigh,'') + '|' + coalesce(flgPNSeasonalWet,'') + '|' + coalesce(flgPNAquatic,'') as ThePlantNeeds, COALESCE (flgSWGreenRoof, '') + '|' + COALESCE (flgSWWetland, '') + '|' + COALESCE (flgSWSwale, '') + '|' + COALESCE (flgSWRainGarden, '') + '|' + COALESCE (flgSWBioretention, '') + '|' + COALESCE (flgSWRetentionBasinWet, '') + '|' + COALESCE (flgSWDetentionBasinDry, '') + '|' + COALESCE (flgSWStreamBuffer, '') AS TheStormWater,  flgPCEdible, flgPCEasyGrow, coalesce(flgRUErosion,'') + '|' + coalesce(flgRUWildlife,'') + '|' +  coalesce(strCaterpHostPlantComName,'') + '|' + coalesce(strCaterpHostPlantSciName,'') + '|' +  coalesce(flgRULandscape,'') + '|' +  coalesce(flgRUSubdivision,'') + '|' +  coalesce(flgRUHorticulture,'') + '|' +  coalesce(flgRUScreening,'') + '|' +  coalesce(flgRUParkingLot,'') + '|' +  coalesce(flgRUStreetscape,'') + '|' +  coalesce(flgRUOpenSpace,'') +'|' + coalesce(strPNUniqueSoils,'')  AS TheRecUses, coalesce(strPCFoliageColor,'') +'|' + coalesce(strPCFlowerColor,'') +'|' + coalesce(strPCBloomTime,'') +'|' + coalesce(flgPMSeed,'') +'|' + coalesce(flgPMPlug,'') +'|' + coalesce(flgPMRootBall,'') +'|' + coalesce(flgPMStaking,'') +'|' + coalesce(flgGHDeerResistant,'') +'|' + coalesce(flgGHGroundhogResistant,'') +'|' + coalesce(flgGHSpreadRapidly,'') +'|' + coalesce(flgPCGroundCover,'') +'|' + coalesce(strPCHeightLow,'') +'|' + coalesce(strPCHeightHigh,'') +'|' + coalesce(flgPCHeightIncr,'') +'|' + coalesce(flgPCHeightPlus,'') +'|' + coalesce(strPCSpreadLow,'') +'|' + coalesce(strPCSpreadHigh,'') +'|' + coalesce(flgPCSpreadIncr,'') +'|' + coalesce(flgPCSpreadPlus,'') as ThePlantChar, strSpecialUses, strComments, strSource, strPicture1, strThumb1, strThumb2, strThumb3, strThumb4, booActive FROM tblNativePlants WHERE (booActive = 'A') AND (flgEvaluated = 'Y') AND (flgRecommended <> 'N') ORDER BY strSciName">





Elaine



Elaine

推荐答案

ConnectionStrings：NativePlantsTESTConnectionString％>

SelectCommand = SELECT keyID，strToken，（strSciName +'|'+ strCommonName +'|'+ strCategory +'|'+ flgNativeTo +'|'+ flgRecommended）as TheNameCol，flgEvaluated，flgAvail，coalesce（flgPNLightFull，''）+ '|' + coalesce（flgPNLightPartialSun，''）+'|' + coalesce（flgPNLightShade，''）+'|' + coalesce（flgPNMoistureLow，''）+'|' + coalesce（flgPNMoistureMed，''）+'|' + coalesce（flgPNMoistureHigh，''）+'|' + coalesce（flgPNSeasonalWet，''）+'|' + coalesce（flgPNAquatic，''）作为ThePlantNeeds，COALESCE（flgSWGreenRoof，''）+'|' + COALESCE（flgSWWetland，''）+'|' + COALESCE（flgSWSwale，''）+'|' + COALESCE（flgSWRainGarden，''）+'|' + COALESCE（flgSWBioretention，''）+'|' + COALESCE（flgSWRetentionBasinWet，''）+'|' + COALESCE（flgSWDetentionBasinDry，''）+'|' + COALESCE（flgSWStreamBuffer，''）作为TheStormWater，flgPCEdible，flgPCEasyGrow，coalesce（flgRUErosion，''）+'|' + coalesce（flgRUWildlife，''）+'|' + coalesce（strCaterpHostPlantComName，''）+'|' + coalesce（strCaterpHostPlantSciName，''）+'|' + coalesce（flgRULandscape，''）+'|' + coalesce（flgRUSubdivision，''）+'|' + coalesce（flgRUHorticulture，''）+'|' + coalesce（flgRUScreening，''）+'|' + coalesce（flgRUParkingLot，''）+'|' + coalesce（flgRUStreetscape，''）+'|' + coalesce（flgRUOpenSpace，''）+'|' + coalesce（strPNUniqueSoils，''）AS TheRecUses，coalesce（strPCFoliageColor，''）+'|' + coalesce（strPCFlowerColor，''）+'|' + coalesce（strPCBloomTime，''）+'|' + coalesce（flgPMSeed，''）+'|' + coalesce（flgPMPlug，''）+'|' + coalesce（flgPMRootBall，''）+'|' + coalesce（flgPMStaking，''）+'|' + coalesce（flgGHDeerResistant，''）+'|' + coalesce（flgGHGroundhogResistant，''）+'|' + coalesce（flgGHSpreadRapidly，''）+'|' + coalesce（flgPCGroundCover，''）+'|' + coalesce（strPCHeightLow，''）+'|' + coalesce（strPCHeightHigh，''）+'|' + coalesce（flgPCHeightIncr，''）+'|' + coalesce（flgPCHeightPlus，''）+'|' + coalesce（strPCSpreadLow，''）+'|' + coalesce（strPCSpreadHigh，''）+'|' + coalesce（flgPCSpreadIncr，''）+'|' + coalesce（flgPCSpreadPlus，''）为ThePlantChar，strSpecialUses，strComments，strSource，strPicture1，strThumb1，strThumb2，strThumb3，strThumb4，booActive FROM tblNativePlants WHERE（booActive ='A'）AND（flgEvaluated ='Y'）AND（flgRecommended< ;>'N'）ORDER BY strSciName >
ConnectionStrings:NativePlantsTESTConnectionString %>" SelectCommand="SELECT keyID, strToken,(strSciName + '|' + strCommonName + '|' + strCategory + '|' + flgNativeTo + '|' + flgRecommended ) as TheNameCol, flgEvaluated, flgAvail, coalesce(flgPNLightFull,'') + '|' + coalesce(flgPNLightPartialSun,'') + '|' + coalesce(flgPNLightShade,'') + '|' + coalesce(flgPNMoistureLow,'') + '|' + coalesce(flgPNMoistureMed,'') + '|' + coalesce(flgPNMoistureHigh,'') + '|' + coalesce(flgPNSeasonalWet,'') + '|' + coalesce(flgPNAquatic,'') as ThePlantNeeds, COALESCE (flgSWGreenRoof, '') + '|' + COALESCE (flgSWWetland, '') + '|' + COALESCE (flgSWSwale, '') + '|' + COALESCE (flgSWRainGarden, '') + '|' + COALESCE (flgSWBioretention, '') + '|' + COALESCE (flgSWRetentionBasinWet, '') + '|' + COALESCE (flgSWDetentionBasinDry, '') + '|' + COALESCE (flgSWStreamBuffer, '') AS TheStormWater, flgPCEdible, flgPCEasyGrow, coalesce(flgRUErosion,'') + '|' + coalesce(flgRUWildlife,'') + '|' + coalesce(strCaterpHostPlantComName,'') + '|' + coalesce(strCaterpHostPlantSciName,'') + '|' + coalesce(flgRULandscape,'') + '|' + coalesce(flgRUSubdivision,'') + '|' + coalesce(flgRUHorticulture,'') + '|' + coalesce(flgRUScreening,'') + '|' + coalesce(flgRUParkingLot,'') + '|' + coalesce(flgRUStreetscape,'') + '|' + coalesce(flgRUOpenSpace,'') +'|' + coalesce(strPNUniqueSoils,'') AS TheRecUses, coalesce(strPCFoliageColor,'') +'|' + coalesce(strPCFlowerColor,'') +'|' + coalesce(strPCBloomTime,'') +'|' + coalesce(flgPMSeed,'') +'|' + coalesce(flgPMPlug,'') +'|' + coalesce(flgPMRootBall,'') +'|' + coalesce(flgPMStaking,'') +'|' + coalesce(flgGHDeerResistant,'') +'|' + coalesce(flgGHGroundhogResistant,'') +'|' + coalesce(flgGHSpreadRapidly,'') +'|' + coalesce(flgPCGroundCover,'') +'|' + coalesce(strPCHeightLow,'') +'|' + coalesce(strPCHeightHigh,'') +'|' + coalesce(flgPCHeightIncr,'') +'|' + coalesce(flgPCHeightPlus,'') +'|' + coalesce(strPCSpreadLow,'') +'|' + coalesce(strPCSpreadHigh,'') +'|' + coalesce(flgPCSpreadIncr,'') +'|' + coalesce(flgPCSpreadPlus,'') as ThePlantChar, strSpecialUses, strComments, strSource, strPicture1, strThumb1, strThumb2, strThumb3, strThumb4, booActive FROM tblNativePlants WHERE (booActive = 'A') AND (flgEvaluated = 'Y') AND (flgRecommended <> 'N') ORDER BY strSciName">





Elaine



Elaine


这篇关于gridview中SQL的复杂过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆