How to pass user defined type parameter to the stored procedure using SqlDataSource

218 Views Asked by At

We have user defined type in our SQL Server database defined as shown here:

CREATE TYPE Integer_List_tblType_InMemory 
     AS TABLE 
        ( 
            RecordId BIGINT NOT NULL PRIMARY KEY NONCLUSTERED
        ) WITH (MEMORY_OPTIMIZED = ON)

The procedure definition:

CREATE PROCEDURE GET_ListOfOrders 
    (@P_ContactId BIGINT,
     @P_ContactGroupIds Integer_List_tblType_InMemory READONLY,
     @P_ProductIds Integer_List_tblType_InMemory READONLY,
     @P_SearchText NVARCHAR(150),
     @P_CurrentPageIndex BIGINT NULL,
     @P_PageSize BIGINT NULL)

Frontend code:

<asp:SqlDataSource runat="server" ID="sdsOrders"        
    SelectCommand="GET_ListOfOrders" SelectCommandType="StoredProcedure"
    ProviderName="System.Data.SqlClient">
    <SelectParameters>
        <asp:QueryStringParameter Name="P_ContactId" QueryStringField="ContractId" DbType="Int64" />
        <asp:ControlParameter ControlID="txtDocumentSearch" PropertyName="Text" Name="P_SearchText" />
        <asp:SessionParameter SessionField="ContactGroupIds" Name="P_ContactGroupIds" />
        <asp:SessionParameter SessionField="ProductIds " Name="P_ProductIds" />
    </SelectParameters>
</asp:SqlDataSource>

The session hold data tables with unique list of Ids. The values are not getting passed to the procedure. I tried all possible combination.

What am I missing?

If not possible to pass using the SqlDataSource, what is the alternative?

0

There are 0 best solutions below