ASP.NET C# LLBL Pro Gen2 SelfServicing

hi guys, i found that we can set the CaseInsensitiveCollation for FieldLikePredicate, but we cant do the same to FieldCompareRangePredicate. This causes some problem to my searching, as i would need to do something like SELECT * FROM Books WHERE BookName IN ('llbl gen pro', 'asp.net ProFfesional', 'c# BeGinnER') If i se the FieldCompareRangePredicate, books with tital C# Beginner would not be listed out as C# Beginner is different from c# BeGinnER. Can we do something with this? Thanks for the help

You're right, there aren't such overload. You can go with these workarounds:

A. Use UPPER or LOWER DB function at your predicate. You access that function via a LLBLGenPro DBFunctionCall.

SELECT * FROM Books WHERE UPPER(BookName)
IN ('LLBLGEN PRO', 'ASP.NET PROFESSIONAL', C# BEGINNER')

B. Use FullTextSearch. This forum (HnD) use FullTextSearch.

daelmo wrote:

You're right, there aren't such overload. You can go with these workarounds:

A. Use UPPER or LOWER DB function at your predicate. You access that function via a LLBLGenPro DBFunctionCall.

SELECT * FROM Books WHERE UPPER(BookName)
IN ('LLBLGEN PRO', 'ASP.NET PROFESSIONAL', C# BEGINNER')

B. Use FullTextSearch. This forum (HnD) use FullTextSearch.

Hi daelmo, would u mind pointing out to me where to find more doc. on the DBFunctionCall, it seems very interesting and i would like to know more, thanks

The LLBLGen Pro documentation manual: "Using the generated code -> Calling a database function"

Hi guys, this is the code that i m using in order to achieve the case insensitive by using the UPPER() function in Oracle and .ToUpper() in C#, however, i still cant retrieve what i want, can anyone point out where my mistakes are? thanks

            for (int i = 0; i < strFilterValues.Length; i++)
            {
                strFilterValues[i] = strFilterValues[i].Trim().ToUpper();
            }

            IEntityField field = new EntityField("UserName",
                                    new DbFunctionCall
                                    ("UPPER", new object[] 
                                    { UserFields.UserName }));


            Filter_Group.Add(
                new FieldCompareRangePredicate(
                field, strFilterValues));

Thanks

can anyone point out where my mistakes are?

First thing you can do, is check the generated SQL Query and manually run it against the database to check the results and find out what's wrong or what needs to be modified in the query to achieve the correct set of results. Check the LLBLGen Pro manual's section "Using the generated code -> Troubleshooting and debugging" to know how to get the generated SQL Query.

If you can't work it out, then please post the generated query and the target query.

(EDIT) I personally recommend not using Case Sensetive Collation if you don't need it.

Hi, i m still having problems tracing out the generated sql from LLBL. However, my target query will be what was presented by daelmo earelier:

[quotenick="daelmo"] A. Use UPPER or LOWER DB function at your predicate. You access that function via a LLBLGenPro DBFunctionCall.

SELECT * FROM Books WHERE UPPER(BookName)
IN ('LLBLGEN PRO', 'ASP.NET PROFESSIONAL', C# BEGINNER')

My concern is, how do i actually translate it into Predicate Expression with DBFunctionCall based on the sample code above?

I am using SelfServicing LLBLGenPro 2.0 and C# Thanks for the help.

Hi, appending from the question above, I am trying to achieve the target query inside a UserCollection object, as I m using this UserCollection inside the LLBLProGenDataSource control to be bounded to a GridView. now that i wish to allow users to do a search within this GridView (or in another words search from data from the UserCollection) . For the search, case shall be insensitive, thus i need to use the UPPER() function pairing with the C# string .ToUpper() function. Hope you guys can give an example on this, thanks

Please post the generated SQL query, to be able to know what goes wrong. Please add the following to the .config file:

<system.diagnostics>
    <switches>
        <add name="SqlServerDQE" value="4" />
    </switches>
</system.diagnostics>

Run the application in debug mode, and check the output screen. (ref: LLBLGen Pro manual's section "Using the generated code - Troubleshooting and debugging")

hi guys,

havent tried out the debugging yet, however my team get to a workaround, which might be helpful to some here. What we do is to use the combination of FieldComparePredicate and the AddWithOr method, instead of using FieldCompareRangePredicate

What we did is:

IPredicateExpression Filter = new PredicateExpression();

foreach(string s in searchkeys) { // create the compare predicate FieldComparePredicate cmpPredicate = new FieldComparePredicate (BookFields.Name, ComparisonOperator.Equal, s.ToUpper());

Filter.AddWithOr(cmpPredicate); }

So far we had tried out with more than 100 searchkeys, and it has not hit any of the performance so far. Anyone using this would like to share? Thanks

Another workaround:

Inherit from FieldCompareRangePredicate (eg. FieldCompareCaseInsensitiveRangePredicate), and override the ToQueryText() method.