The Code Slinger

October 22, 2008

LINQDataSource and Optional Parameters

Filed under: Uncategorized — Pete @ 1:20 pm
Tags: , ,

The combination of LINQDataSource and GridView makes a nice quick way to provide basic search capabilities for your users.  Providing a user the ability to search on multiple criteria for a single entity is quick and painless.  However, I ran into an issue where one of the columns I was searching by allowed NULL in the table. 

My fields looked something like:

                <tr>

                    <td>Company:&nbsp; </td>

                    <td><asp:TextBox ID=”txtCompNameSearch” runat=”server”></asp:TextBox></td>

                </tr>

                <tr>

                    <td>Phone: &nbsp;</td>

                    <td><asp:TextBox ID=”txtPhoneSearch” runat=”server”></asp:TextBox></td>

                </tr>

My LINQDataSource:

 

    <asp:LinqDataSource ID=”ldsCompanies” runat=”server”

        ContextTypeName=”DataContext”

        TableName=”REG_Companies” EnableUpdate=”True”

        Where=”CompanyName.StartsWith(@CompanyName) &amp;&amp; Phone.StartsWith(@PhoneNumber)”>

        <WhereParameters>

            <asp:ControlParameter ConvertEmptyStringToNull=”false” ControlID=”txtCompNameSearch” Name=”CompanyName”

                PropertyName=”Text” Type=”String” />

            <asp:ControlParameter ConvertEmptyStringToNull=”false” ControlID=”txtPhoneSearch” Name=”PhoneNumber”

                PropertyName=”Text” Type=”String” />

        </WhereParameters>

    </asp:LinqDataSource>

If both CompanyName and Phone fields didn’t allow nulls, this would work just fine.  However, like my scenario, the Phone field (for whatever reason) allowed NULL values in the database, this would still work but if you left txtPhoneSearch empty, it would only return records in which the Phone field was NOT NULL.

So, the next logical thing to do would be to change the ConvertEmptyStringToNull value to true for that parameter.  Ah, but if you do that you get the following error when you try to load the page the code is on:

No applicable method ‘StartsWith’ exists in type ‘String’

So, this is what I came up with to fix it so you can search optionally on columns that allow nulls using the standard ControlParameter in the LINQDataSource.  Leave the ConvertEmptyStringToNull=”false” as you would with non-nullable columns.  However, modify your Where clause to the following:

        Where=”CompanyName.StartsWith(@CompanyName) &amp;&amp; (@PhoneNumber == String.Empty || (@PhoneNumber != String.Empty &amp;&amp; Phone.StartsWith(@PhoneNumber)))”>

You’re effectively just giving the query an option to exit early if the @PhoneNumber parameter is empty, but if it is not, then you’re adding the comparison to the dynamic SQL logic.

Til next time….keep on slangin!

Advertisements

4 Comments »

  1. Nice post. Its most helpful one.

    Comment by vinkas — February 16, 2009 @ 4:29 am | Reply

  2. I was about ready to write my gridview from scratch until I found this post, thanks!

    Comment by Tim E. — February 18, 2009 @ 11:05 am | Reply

  3. hey , is your phonenumber value nullable in dbml file ???

    Comment by popcornfantasiesan — September 4, 2011 @ 8:22 pm | Reply

  4. Gracias Amigo!

    Comment by Julio — July 26, 2012 @ 4:15 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: