Hi there,

Brand new user to LLBLGEN pro - i'm evaluating version 2.5 demo right now. (Forgive me if this is the wrong place to post this and if I'm missing something obivous since I'm brand new to this.)

I'm using SQL Server 2005 with case insensitive collation on the database.

For my login process I need to search for matching usernames and password to see if the user can log in. I'd like to have the password as case sensitive. I have tried different variations of the CaseSensitiveCollation = true / false but I can't get this to work for some reason.

My understanding from various forum posts is that if the CaseSensitiveCollation = true, the sql that is generated uses UPPER() around the field. (This in fact makes the match case insensitive).

But is there a way to force it to match the password as case sensitive instead? If I leave CaseSensitiveCollation = false, the query is still matching case-insensitively because the db is using case insensitive collation.

            // Case insensitive match for username
            FieldLikePredicate likePredicate = new FieldLikePredicate(UserFields.UserName, null,username.ToUpper());
            likePredicate.CaseSensitiveCollation = true;

            // Case sensitive match for password
            FieldCompareValuePredicate cvp = new FieldCompareValuePredicate(UserFields.UserPwd,null, ComparisonOperator.Equal);
            cvp.CaseSensitiveCollation = false;
            cvp.Value = password;

            bucket.PredicateExpression.Add(likePredicate);
            bucket.PredicateExpression.AddWithAnd(cvp);

Notice I specified the username.ToUpper() for the compare value.

The sql code generated for this condition looks like this :

    ( UPPER([myDB].[dbo].[tbl_User].[user_name]) LIKE @UserName1 AND [myDB].[dbo].[tbl_User].[user_pwd] = @UserPwd2)

I will eventually shift this to use hashed passwords, but I'm more worried about whether LLBLGen can support this scenario.

Thanks in advance.

dravidian wrote:

My understanding from various forum posts is that if the CaseSensitiveCollation = true, the sql that is generated uses UPPER() around the field. (This in fact makes the match case insensitive).

Copied from manual: If the value to compare is a string, you will get a case sensitive compare if the database is using a case sensitive collation (like Oracle). You can perform case insensitive compares however, by setting the CaseSensitiveCollation property to true prior to passing the predicate to a fetch method like FetchEntityCollection(). This will perform the UPPERCASE variant of the field with the pattern specified. Please note that if you've set CaseSensitiveCollaction to true, you've to specify your pattern in uppercase as well. That means that setting CaseSensitiveCollation to true will perform a CaseInsensitive behavior in a "case sensitive" (CS) collation DB.

dravidian wrote:

But is there a way to force it to match the password as case sensitive instead?

That's different coz your DB is Case Insensitive (CI). So whatever you do at code, will result in case insensitive expression at DB level (see Collation Precedence at SQL2K5 docs).

What you can do is:

A. Collate some needed columns. I don't recommend this because would be a nightmare compare cross-collations columns.

or

B. Collate your DB to Case Sensitive and perform insensitive compares where needed (via CaseSensitiveCollation = true).

or

C. Collate the expression. This is a hint:

// collate a CAST function so the result of the function should be case sensitive.
DbFunctionCall collatedCast = new DbFunctionCall("CAST({0} AS varchar) COLLATE SQL_Latin1_General_CP1_CS_AS", new object[] { CustomerFields.CustomerId });                                  

// use above function to perform a sensitive compare in a insensitive collated DB.
IRelationPredicateBucket filter = new RelationPredicateBucket(CustomerFields.CustomerId.SetExpression(collatedCast) == "alFkI");

Hope helpful

Daelmo, thanks that helps a lot!

I'm going to go with Option C