Filtrovaný SELECT příkaz   zodpovězená otázka

VB.NET, ASP.NET WebForms, SQL, Databáze

Dobrý den, ve své asp.net web forms aplikaci mám stránku s výsledky vyhledávání. Data, podle kterých filtruju mám v QueryStringu. Takto například vypadá URL stránky: localhost:49778/sportovci-vyhledavani?s=1&r=&sp=&g=&af=&at=&pf=&pt=&n=&c=. Sice je tam většina QueryStringů prázdných (měli by se automaticky odstranit, mám v plánu to udělat), ale to nevadí, protože ne všechny musí být vyplněné. Takže by se měly zobrazit hodnoty, kde je QueryStrin s roven 1 - tedy uživatel, který má k sobě přiřazený sport s id 1.

Sloupce Age a Practise mají být mezi nějakými hodnotami, proto parametry @AgeFrom a @AgeTo...

Problém bude v SELECT příkazu, napsal jsem ho podle toho, co jsem nastudoval na internetu (W3school...), ale asi není dobře.

Neporadil by mi prosím někdo, jak to správně napsat ten SELECT nebo nějaké jiné řešení?

HTML:

<asp:ListView ID="profilesListView" runat="server" >
                <ItemTemplate>
                    <div class="sportovci_item">
                        <asp:HiddenField ID="ProfileId" runat="server" Value='<%# Eval("Profiles.ProfileId") %>' />
                        <h2>
                            <asp:Literal ID="Name" runat="server" Text='<%# Eval("Profiles.Name") %>' Mode="Encode" /></h2>
                        <small>
                            <asp:Literal ID="Specialization" runat="server" Text='<%# Eval("Specialization") %>' Mode="Encode" /></small>
                        <p class="sportovci_sports">
                            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SportovniPlanetaCS %>"
                                SelectCommand="SELECT Sports.Sport FROM UsersSports JOIN sports ON UsersSports.SportId = Sports.SportId WHERE (ProfileId = @ProfileId)">
                                <SelectParameters>
                                    <asp:ControlParameter Name="ProfileId" Type="Int32" ControlID="ProfileId" />
                                </SelectParameters>
                            </asp:SqlDataSource>
                            <asp:Repeater ID="SportsRepeater" runat="server" DataSourceID="SqlDataSource2">
                                <ItemTemplate>
                                    <asp:Literal ID="VIPSports" runat="server" Text='<%# Eval("Sports.Sport") %>' Mode="Encode" />
                                </ItemTemplate>
                            </asp:Repeater>
                        </p><asp:Image ID="ProfileImg" runat="server" ImageUrl='<%# Eval("ProfilesProPicUrl")%>' CssClass="sportovci_item_img" />
                        <br />
                        <ajaxToolkit:Rating ID="sportovci_item_rating" CssClass="sportovci_item_rating" CurrentRating='<%# Eval("UsersSports.Rating") %>' runat="server" MaxRating="5" ReadOnly="true" StarCssClass="sportovciRatingStar" WaitingStarCssClass="savedRatingStar" FilledStarCssClass="sportovciFilledRatingStar" EmptyStarCssClass="sportovciEmptyRatingStar" />
                    </div>
                </ItemTemplate>
            </asp:ListView>   

Code behind:

 Using conn As New SqlConnection(connStr)
                Dim cmd As SqlCommand = conn.CreateCommand()
                cmd.CommandText = "SELECT Profiles.ProfileId,Profiles.ProPicUrl, Profiles.Name, Profiles.Specialization, UsersSports.Rating " + _
                                 "FROM Profiles " + _
                                 "JOIN UsersSports ON Profiles.ProfileId = UsersSports.ProfileId " + _
                                "WHERE (UsersSports.SportId = @SportId) AND (@Region IS NULL OR Region = @Region) AND (@Specialization IS NULL OR Specialization =  @Specialization)" + _
                                        "AND (@Sex IS NULL OR Sex = @Sex) AND (@AgeFrom IS NULL OR Age BETWEEN @AgeFrom AND @AgeTo) AND (@PractiseFrom IS NULL OR UsersSports.Practise BETWEEN @PractiseFrom AND @PractiseTo)" + _
                                        "AND (@Name IS NULL OR Profiles.Name LIKE '%@Name%') AND (@City IS NULL OR City LIKE @City)" + _
                                "ORDER BY UsersSports.Rating ASC"
                cmd.Parameters.Add("@SportId", SqlDbType.Int).Value = Request.QueryString("s").ToString()
                cmd.Parameters.AddWithValue("@Region", Request.QueryString("r"))
                cmd.Parameters.AddWithValue("@Specialization", Request.QueryString("sp"))
                cmd.Parameters.AddWithValue("@Sex", Request.QueryString("g"))
                cmd.Parameters.AddWithValue("@AgeFrom", Request.QueryString("af"))
                cmd.Parameters.AddWithValue("@AgeTo", Request.QueryString("at"))
                cmd.Parameters.AddWithValue("@PractiseFrom", Request.QueryString("pf"))
                cmd.Parameters.AddWithValue("@PractiseTo", Request.QueryString("pt"))
                cmd.Parameters.AddWithValue("@Name", Request.QueryString("n"))
                cmd.Parameters.AddWithValue("@City", Request.QueryString("c"))
                conn.Open()
                Dim dt As New DataTable
                Dim adp As New SqlDataAdapter(cmd)
                adp.Fill(dt)
                profilesListView.DataSource = dt
                profilesListView.DataBind()
                conn.Close()
            End Using
nahlásit spamnahlásit spam 0 odpovědětodpovědět

Ačkoliv bych to takhle nikdy nenapsal (Selecty rovnou ve stránce, neošetření parametrů, atd.) tak v kódu vidím dvě chyby:

1) Při načtení hodnoty z QueryString bude pro všechny prázdný parametry hodnota rovna prázdnému stringu, v dotaze se ovšem čeká při nevyplnění parametru NULL.

Řešit se to dá buď v SQL pomoci NULLIF např.:

(NULLIF(@Region,'') IS NULL OR Region = @Region)

nebo lépe již v .NETu převést prázdný string na Convert.DBNull tj.

cmd.Parameters.AddWithValue("@Region", IIf(string.IsNullOrEmpty(Request.QueryString("r")), Convert.DBNull, Request.QueryString("r"))

2) U podmínky na @Name je použit LIKE, zde je špatně dán parametr, protože '%@Name%' je v SQL celé varchar hodnota. Znaky '%' jsou tedy potřeba do stringu přidat již v .NETu tj:

(@Name IS NULL OR Profiles.Name LIKE @Name)

cmd.Parameters.AddWithValue("@Region", IIf(string.IsNullOrEmpty(Request.QueryString("r"), Convert.DBNull, "%" + Request.QueryString("r") + "%")
nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět

Díky za odpověď, vypadá to, že to funguje. Pro příště už budu vědět.

Když jste říkal, že byste to takhle nikdy nenapsal, jak byste to tedy udělal?

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Obecně rozdělit kód na více celků do samostatných vrstev - tříd, oddělit odpovědnosti jednotlivých tříd. Už se to tady řešilo viz. http://www.dotnetportal.cz/forum/tema/26...

konkrétně pro tenhle příklad bych si uměl představit:

-Vrstva pro přístup do DB (nějaká nadstavba nad ADO.NET)

-Datová vrstva s objekty modelů, které drží typově načtená data - Users, Profiles, Sports

-Typová třída hodnot filtrů.

-BusinessLayer, controllery s funkcemi pro načtení konkrétních dat, logika filtrování.

-Prezentační vrstva - aspx stránka s ListView.

nahlásit spamnahlásit spam 2 / 2 odpovědětodpovědět
                       
Nadpis:
Antispam: Komu se občas házejí perly?
Příspěvek bude publikován pod identitou   anonym.
  • Administrátoři si vyhrazují právo komentáře upravovat či mazat bez udání důvodu.
    Mazány budou zejména komentáře obsahující vulgarity nebo porušující pravidla publikování.
  • Pokud nejste zaregistrováni, Vaše IP adresa bude zveřejněna. Pokud s tímto nesouhlasíte, příspěvek neodesílejte.

přihlásit pomocí externího účtu

přihlásit pomocí jména a hesla

Uživatel:
Heslo:

zapomenuté heslo

 

založit nový uživatelský účet

zaregistrujte se

 
zavřít

Nahlásit spam

Opravdu chcete tento příspěvek nahlásit pro porušování pravidel fóra?

Nahlásit Zrušit

Chyba

zavřít

feedback