Egyre gyakrabban merül fel az igény, hogy egy SQL lekérdezés egyik paramétereként azonos típusú, ám ismeretlen számú értéket kell átadnunk. Például egy terméket CheckBoxList segítségével több csoportba sorolhat a felhasználó vagy épp egy keresésnél választhatunk több kategória közül. Az SQL Server korábbi verzióinál tipikusan úgy oldottuk meg ezt a feladatot, hogy az értékeket egyetlen string változóban adtuk át, melyben XML vagy egyszerű separator karakterekkel elválasztott értékek szerepeltek. Az SQL Server 2008-tól kezdve viszont már közvetlenül adhatunk át tábla típusú paramétert is.

Egy olyan oldalt akartam készíteni, amely a Northwind adatbázisból azokat a Customereket listázza ki, akik a kiválasztott ország valamelyikében vannak:

ASP.NET szinten az oldal nagyon egyszerű. Fent van egy CheckBoxList, amit egy SqlDataSource tölt fel:

    <asp:CheckBoxList ID="cblCountries" runat="server" DataSourceID="sdsCountries" 
        DataTextField="Country" />
        
    <asp:SqlDataSource ID="sdsCountries" runat="server" 
        ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
        SelectCommand="SELECT DISTINCT TOP 5 Country FROM Customers ORDER BY Country">
    </asp:SqlDataSource>    

Alatta található egy GridView, amit szintén egy SqlDataSource segítségével töltök fel:

    <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="True" 
        DataSourceID="sdsCustomers" EmptyDataText="Válasszon országot!" />

    <asp:SqlDataSource ID="sdsCustomers" runat="server" 
        ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
        SelectCommand="GetCustomersInCountries" SelectCommandType="StoredProcedure" 
        OnSelecting="sdsCustomers_Selecting">
        <SelectParameters>
            <asp:Parameter Name="SelectedCountries" />
        </SelectParameters>
    </asp:SqlDataSource>

A GridViewt a GetCustomersInCountries tárolt eljárás (lásd később) fogja feltölteni, ami egy SelectedCountries nevű paramétert vár. Ebbe szeretnénk betölteni a fenti listából kiválasztott országok neveit.

Itt rögtön újabb ékes bizonyítékát láthatjuk annak, hogy túl nagy a Microsoft: az ASP.NET csapat nem tudta, mit csinál az ADO.NET Team :) Tábla típusú paraméterek használatához ugyanis egy olyan paramétert kell adnunk az SqlCommandhoz, amelyben az SqlDbType értéke SqlDbType.Structured. Az ilyen típusú paraméter értékeként pedig egy DataTable-t kell megadni, ami táblaként fog megérkezni az SQL Serverhez. Ezt az ADO.NET csapat jól kitalálta.

Azonban ASP.NET-ben a parancs paramétereinek inicializálását az SqlDataSource végzi, így neki kellene tudnunk megmondani, hogy a SelectedCountries paraméter tábla típusú. Csakhogy az asp:Parameter elemben átadható TypeCode attribútum felsorolt típusából kimaradt a Structured érték! Azaz szerintem a feladatot nem lehet deklaratívan megoldani, ami nekem személy szerint nagyon fáj :(

Nézzük mi kell az SQL Server oldalán! Először is definiálnunk kell egy új típust. Én Itemsnek neveztem el, semmi köze nincs az országokhoz, 15 karakteres sztringekből tud akármennyit tárolni (lehetne több oszlopa is):

    -- Sajat tipus letrehozasa
    CREATE TYPE dbo.Items AS TABLE 
    (
        Item nvarchar( 15 )
    )
    GO

Ezek után létrehozhatjuk a tárolt eljárásunkat, amelynek Items típusú bemenő paramétere lesz:

    -- Tarolt eljaras letrehozasa
    CREATE PROC dbo.GetCustomersInCountries @SelectedCountries Items READONLY AS
    (
        SELECT ContactName, Country, City
        FROM Customers
        WHERE Country IN
        (
            SELECT Item FROM @SelectedCountries
        )
    )
    GO

Fontos, hogy a tábla típusú bemenő paraméter csak READONLY lehet.

Ezek után TSQL-ből ki is lehet próbálni, például így:

    -- Teszteles TSQL-bol
    DECLARE @Countries Items
    INSERT INTO @Countries ( Item )
        VALUES ( 'Argentina' ), 
               ( 'Germany' ),
               ( 'Finland' )
    SELECT * FROM @Countries
    EXEC dbo.GetCustomersInCountries @Countries

Már csak az maradt hátra, hogy CheckBoxListből kiolvassuk a beikszelt országokat és átadjuk őket a tárolt eljárásnak. Erre kiváló pillanat az SqlDataSource OnSelecting eseménye, itt ugyanis közvetlenül hozzáférünk az SqlCommandhoz:

    protected void sdsCustomers_Selecting( object sender, SqlDataSourceSelectingEventArgs e )
    {
        DataTable dt = new DataTable();
        dt.Columns.Add( "item", typeof( string ) );

        foreach( ListItem item in this.cblCountries.Items )
        {
            if( item.Selected )
            {
                dt.Rows.Add( item.Text );
            }
        }

        e.Command.Parameters[ "@SelectedCountries" ].Value = dt;
    }

Mindez akkor fog lefutni, amikor a felhasználó rákattint a Szűrés gombra:

    protected void btnFilter_Click( object sender, EventArgs e )
    {
        this.gvCustomers.DataBind();
    }

Bár SQL Server oldalon elő kell kicsit készíteni ezt a megoldást a CREATE TYPE hívásával, ami csak SQL Server 2008-on fog működni, mégis átláthatóbb, és gyanítom gyorsabb is a megoldás, mint a korábbi string összefűzős megközelítés.