locked
Objectdatasource with unpredictable number of input parameters RRS feed

  • Question

  • User-1438039540 posted

    I'm trying to use an objectdatasource and populate it with a query that has "or" in its where statement.  eg: select * from customers where customer_id = "34" or "35" or "36"
    The problem is... I don't know ahead of time how many of these "or" conditions there will be - the user can choose as many or as few as they want.  It seems that if I want to use an objectdatasource tied to a tableadpater I'm forced to know the number of parameters ahead of time so that I can build the objectdatasource's input parameters in advance.  I apologize if this is not clear, but could use any help.  I need to add parameters and condition them as "or"... any help would be greatly appreciated.

    e.inputparameters.add doesn't work, because the key (in this case "id") is the same for each parameter because the where statment has "or"s....


    Friday, December 10, 2010 12:45 AM

Answers

  • User1633691049 posted

    Hi,

    To filter on a list of customer_id's should be translated to SQL language in a IN (34,35,36) expression. I did build up an example (no grid attached to the ObjectDataSource) that demonstrate how the filter expression should be used.

    SelectInList.ASPX file:

        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="MySelect" TypeName="TestProject.SelectInList" OnObjectCreating="ObjectDataSource1_ObjectCreating" OnSelecting="ObjectDataSource1_Selecting">
            <SelectParameters>
                <asp:Parameter Name="Filter" Type="object" ConvertEmptyStringToNull="true" />
            </SelectParameters>
        </asp:ObjectDataSource>
        <asp:Label ID="MySelectStatement" runat="server"></asp:Label>


    SelectInList.ASPX.cs Code behind:

            protected void Page_Load(object sender, EventArgs e)
            {
                this.ObjectDataSource1.Select();
            }
    
            protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
            {
                e.InputParameters["Filter"] = new SelectFilterCriteria(34, 35, 36);
            }
    
            public void MySelect(SelectFilterCriteria Filter)
            {
                if (Filter != null && Filter.CustomerIDs.Length > 0)
                {
                    System.Text.StringBuilder sb = new System.Text.StringBuilder();
                    for(int i = 0; i < Filter.CustomerIDs.Length; i++)
                    {
                        sb.Append(Filter.CustomerIDs[i]);
                        if (i < Filter.CustomerIDs.Length - 1) sb.Append(",");
                    }
    
                    this.MySelectStatement.Text = string.Format("SELECT * FROM [Customers] WHERE customer_id IN ({0})", sb.ToString());
                }
            }
    
            protected void ObjectDataSource1_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
            {
                e.ObjectInstance = this;
            }


    SelectFilterCriteria class:

        public class SelectFilterCriteria
        {
            public int[] CustomerIDs = new int[0];
            
            public SelectFilterCriteria() {}
            
            public SelectFilterCriteria(params int[] customerIDs)
            {
                this.CustomerIDs = customerIDs;
            }
        }


    My datasource select method is build in the code-behind. The select method itself is an instance method, so to be able to access the controls in the page, I had to instruct the ObjectDataSource that ObjectInstance that provides my Select method is the current instance of my test page (this is done via ObjectCreating event handler). In practice you don't need the ObjectCreating event handler.

    Note: TypeName=TestProject.SelectInList is the namespace qualified class name of the page code-behind. In my case the namespace is TestProject and the code-behind class name is SelectInList.

    MySelect method shows on screen the select statement that you need to build.

    I hope this will help.

    Cheers,

    Florin



    ObjectDataSource1_ObjectCreating
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 13, 2010 5:46 AM

All replies

  • User1633691049 posted

    Hi,

    To solve your problem you should create a SelectFilterCriteria Class that carries all the user options and define only one select parameter for you ObjectDataSource.

    public class SelectFilterCriteria
    {
    public int[] CustomerIDs;

    public SelectFilterCriteria() {}

    public SelectFilterCriteria(params int[] customerIDs)
    {
    this.CustomerIDs = customerIDs;
    }
    }


    The ObjectDataSource might look like this:

        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetCustomers">
    <SelectParameters>
    <asp:Parameter Name="Filter" Type="Object" ConvertEmptyStringToNull="true" />
    </SelectParameters>


    Where GetCustomers method should have this declaration:

    GetCustomers(SelectFilterCriteria Filter) { }

    GetCustomers should use the SelectFilterCriteria inner array to build the "or" conditions.

    You can set the user filter criteria on ObjectDataSource Selecting event:

            void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
            {
                SelectFilterCriteria filter = new SelectFilterCriteria();
               // set here the user selected options
               e.InputParameters["Filter"] = filter;
            }



    I hope this will help.

    Cheers,

    Florin




    Friday, December 10, 2010 2:40 AM
  • User1401801381 posted

    Hi,


    one way to achieve that is to use a list of id's as a parameter for the select method you use with your objectdatasource.

    so if you have your objectdatasource


    <asp:ObjectDataSource runat="server" ID="yourdataSource"
    TypeName="namespace.class"
    SelectMethod="SelectMethod"/>


    and your SelectMethod


    public [returnType] SelectMethod(list<typeofID> parameterName)



    you can add your selectparameter


    e.InputParameters.Add("parameterName", listofIDs);



    Friday, December 10, 2010 2:57 AM
  • User-1438039540 posted

    I'll attempt both approaches today and see if they do the trick.   I appreciate your taking the time to respond.

    Friday, December 10, 2010 10:18 AM
  • User-1438039540 posted

    After looking at both ideas, I see how they could pass the paramters I need. 

    But how do I convey the "or" between the parameters?  or in a different case... the "and" that may be between the parameters?

    Thanks again for the time you're taking.


    Friday, December 10, 2010 10:35 AM
  • User-1438039540 posted

    attempting the second method resuts in the following exception:  Item has already been added. Key in Dictionary "customer_id"  Key being added: "customer_id"

    So that one is not working for me.   Also using either method doesn't appear to allow me to set the "or" condition.

    Still stuck, but thanks for your suggestion it may be usefull elsewhere in my application.

    Friday, December 10, 2010 7:45 PM
  • User1633691049 posted

    Hi,

    To filter on a list of customer_id's should be translated to SQL language in a IN (34,35,36) expression. I did build up an example (no grid attached to the ObjectDataSource) that demonstrate how the filter expression should be used.

    SelectInList.ASPX file:

        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="MySelect" TypeName="TestProject.SelectInList" OnObjectCreating="ObjectDataSource1_ObjectCreating" OnSelecting="ObjectDataSource1_Selecting">
            <SelectParameters>
                <asp:Parameter Name="Filter" Type="object" ConvertEmptyStringToNull="true" />
            </SelectParameters>
        </asp:ObjectDataSource>
        <asp:Label ID="MySelectStatement" runat="server"></asp:Label>


    SelectInList.ASPX.cs Code behind:

            protected void Page_Load(object sender, EventArgs e)
            {
                this.ObjectDataSource1.Select();
            }
    
            protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
            {
                e.InputParameters["Filter"] = new SelectFilterCriteria(34, 35, 36);
            }
    
            public void MySelect(SelectFilterCriteria Filter)
            {
                if (Filter != null && Filter.CustomerIDs.Length > 0)
                {
                    System.Text.StringBuilder sb = new System.Text.StringBuilder();
                    for(int i = 0; i < Filter.CustomerIDs.Length; i++)
                    {
                        sb.Append(Filter.CustomerIDs[i]);
                        if (i < Filter.CustomerIDs.Length - 1) sb.Append(",");
                    }
    
                    this.MySelectStatement.Text = string.Format("SELECT * FROM [Customers] WHERE customer_id IN ({0})", sb.ToString());
                }
            }
    
            protected void ObjectDataSource1_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
            {
                e.ObjectInstance = this;
            }


    SelectFilterCriteria class:

        public class SelectFilterCriteria
        {
            public int[] CustomerIDs = new int[0];
            
            public SelectFilterCriteria() {}
            
            public SelectFilterCriteria(params int[] customerIDs)
            {
                this.CustomerIDs = customerIDs;
            }
        }


    My datasource select method is build in the code-behind. The select method itself is an instance method, so to be able to access the controls in the page, I had to instruct the ObjectDataSource that ObjectInstance that provides my Select method is the current instance of my test page (this is done via ObjectCreating event handler). In practice you don't need the ObjectCreating event handler.

    Note: TypeName=TestProject.SelectInList is the namespace qualified class name of the page code-behind. In my case the namespace is TestProject and the code-behind class name is SelectInList.

    MySelect method shows on screen the select statement that you need to build.

    I hope this will help.

    Cheers,

    Florin



    ObjectDataSource1_ObjectCreating
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 13, 2010 5:46 AM
  • User-1438039540 posted

    I appreciate very much your response.  Your suggested approach is very similar to what I've already started putting together, except that I moved to a SQLDATASOURCE in order to gain a little bit more flexibility with generating and then using the select statements.  I'll continue to study your approach as well.

    Thank you for taking the time to respond.

    Monday, December 13, 2010 4:08 PM