asp.net 2.0 llblgenpro 2 adapterTemplate

hiya,

I need to provide a single textBox that allows a user to enter a search criteria.

As an example a user could enter "23" (no quotes), in the search textbox, click on 'Search' and the search would return all of the following records...

JobName = "23"

Address = "23 High St"

AssignedTo = "Employee23"

Description = "over 23 cans dumped..."

Some of the above are values from lookup tables.I'm not sure how to approach this.. Do I use predicates or use the designer to build some kind of "search" entity.

Any advice appreciated.

many thanks,

yogi

I would use FieldFullTextSearchPredicate (sqlserver specific) for Fields that contain the value. This requires you have Full Text Indexing enabled on the db/tables/fields. see SQL Server BOL for more info.

You could then use the PredicateExpression.AddWithOr() method to add addtional predicates for lookups.

so the code would look something like this

string input == MyTextBox.Text;
FullTextSearchOperator op = FullTextSearchOperator.Contains;

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldFullTextSearchPredicate(JobFields.JobName, null, op, input));
filter.AddWithOr(new FieldFullTextSearchPredicate(JobFields.Description, null, op, input));
filter.AddWithOr(new FieldFullTextSearchPredicate(AssignedFields.AssignedTo, null, op, input));
filter.AddWithOr(new FieldFullTextSearchPredicate(AddressFields.Address, null, op, input));

int inputAsInteger = 0;
if(int.TryParse(input, out inputAsInteger))
{
     filter.AddWithOr(MyLookupFields.Id == inputAsInteger);
     filter.AddWithOr(MyOtherLookupFields.Id == inputAsInteger);
}

//...pass filter to IRelationPredicateBucket

i haven't worked with SQl Server Full Text Indexing. So there may be some tricks to combinding field indexes or something so you don't need to write so many predicates.

Or a bunch of FieldLikePredicate instances of course But fulltextsearch is a bit faster in some occasions. If you don't have a lot of rows (i.e. not millions) a LIKE query can be ok.

hiya,

Thanks for info.I'll have a lok then make a decision.

yogi

hiya,

Ok, I have the code etc compiling but it doesn't seem to be bringing back any results. I'll keep it simple and simply stick to a single field, that of "job.description", which I know contains a value "wertyui£"

string input = "yui"; //HARD-CODED, so it should work. FullTextSearchOperator op = FullTextSearchOperator.Contains;

IPredicateExpression filter = new PredicateExpression(); filter.AddWithOr(new FieldFullTextSearchPredicate(JobFields.Description, null, op, input));

using (DataAccessAdapter adp = new DataAccessAdapter()) { EntityCollection<JobEntity> jobs = new EntityCollection<JobEntity>();

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(filter);

adp.FetchEntityCollection(jobs, bucket);
txtjobCount.text = jobs.Count;

}

Any ideas? I know that full -text search is enabled, and as I say, there's no errors. When I hard-code, the ENTIRE description, then it works.Why would this be?

The predicate contains a FullTextSearchOperator..

FullTextSearchOperator op = FullTextSearchOperator.Contains;

many thanks,

yogi

Would you please examine the generated SQL query and run it directly against the database and see what will be the result?

Please post the query here as well.

hiya Walaa

Below is the query: If I'm correct, the mportant part is the the WHERE clause?

exec sp_executesql N'SELECT [DbDev].[dbo].[Job].[JobId], [DbDev].[dbo].[Job].[JobName], [DbDev].[dbo].[Job].[Description], [DbDev].[dbo].[Job].[JobCategoryId], [DbDev].[dbo].[Job].[JobLatitude], [DbDev].[dbo].[Job].[JobLongitude], [DbDev].[dbo].[Job].[AddressLine], [DbDev].[dbo].[Job].[PrimaryCity], [DbDev].[dbo].[Job].[SecondaryCity], [DbDev].[dbo].[Job].[PostCode], [DbDev].[dbo].[Job].[County], [DbDev].[dbo].[Job].[Country], [DbDev].[dbo].[Job].[AddressLatitude], [DbDev].[dbo].[Job].[AddressLongitude], [DbDev].[dbo].[Job].[AssignedToUserId], [DbDev].[dbo].[Job].[JobDate], [DbDev].[dbo].[Job].[LastOKDate] AS [LastOkdate], [DbDev].[dbo].[Job].[JobComplete], [DbDev].[dbo].[Job].[JobCompleteDate], [DbDev].[dbo].[Job].[isCallCentre] AS [IsCallCentre] FROM [DbDev].[dbo].[Job]  WHERE ( ( ( Contains([DbDev].[dbo].[Job].[JobName], @JobName11) OR Contains([DbDev].[dbo].[Job].[Description], @Description22))))',N'@JobName11 nvarchar(3),@Description22 nvarchar(3)',@JobName11=N'yui',@Description22=N'yui'

The above is a query that returns no items, because it doesn't match the entire string that's contained in job.Description field.

..yet, i'd have thought that it should only have to match a subsection of the string, because I used the "FullTextSearchOperator.Contains" operator?

Even when I use the "FullTextSearchOperator.FreeText" operator, it still returns no results.

I'm lost.

Any suggestions?

many thanks,

yogi

WHERE ( ( ( Contains([DbDev].[dbo].[Job].[JobName], @JobName11) OR Contains([DbDev].[dbo].[Job].[Description], @Description22))))',N'@JobName11 nvarchar(3),@Description22 nvarchar(3)',@JobName11=N'yui',@Description22=N'yui'

because it doesn't match the entire string that's contained in job.Description field.

The Contains([DbDev].[dbo].[Job].[Description], @Description22)) part should return a result if say Description22 contains bot and Description field contains bottle. What is the value in the Description table which should match when the partial string yui is specified?

hiya,

The value is "wertyuio" I have no idea why it doesn't work.

Any ideas?

cheers,

yogi

SQL2005 wrote:

**CONTAINS (Transact-SQL) ** Is a predicate used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single **words **and phrases, the proximity of words within a certain distance of one another, or weighted matches.

So, as you are finding letters in a word, try using wildcards (xxx). For more info read **SQL Server 2005 Books Online - Querying SQL Server Using Full-Text Search **

hiya David.

I tried using wildcards, eg "yui" It didn't work, regardless of whether I used...

FullTextSearchOperator.FREETEXT or FullTextSearchOperator.CONTAINS

Ok, so where does that leave me?What can I confirm is actually working?

If, when I use the entire word, then the FieldFullTextSearchPredicate works, does that mean that I can confirm that my database is correctly configured?

If so, then it means that there are issues with my code.

I'l included it agin, incase it highlights an obvious issue:

NB: the full value of the target field is "wertyuio"

string input = "*yui*";
FullTextSearchOperator op = FullTextSearchOperator.Freetext;

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldFullTextSearchPredicate(JobFields.JobName, null, op, input));
filter.AddWithOr(new FieldFullTextSearchPredicate(JobFields.Description, null, op, input));

DataAccessAdapter adp = new DataAccessAdapter();
EntityCollection<JobEntity> jobs = new EntityCollection<JobEntity>();

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(filter);

adp.FetchEntityCollection(jobs, bucket);  //it returns NO values

If the above doesn't help, is there sample code that I can compare it with?Or, should I just be using the FieldLikePredicate?

many thanks,

yogi

Please try this:

string input = " '*yui*' ";

(include single quotes in the string...)

Also please check the search code we've implemented in Hnd (http://www.llblgen.com/hnd ) (which is also used by this forum ) This search code uses Sqlserver's full text search and could help you in specifying wildcards and how to deal with user input

hiya,

Ok, using the single quotes doesn't work, but thanks anyway. I'd rather not have had to download the entire hnd, install it then work thru it and find a suitable example (This post is already a few days old)

However, i will now go ahead and do this.I have to say though, i thought that my initial question and subsequent posts were clear enough to have narrowed down the issue sooner.

yogi

yogiberr wrote:

hiya,

Ok, using the single quotes doesn't work, but thanks anyway. I'd rather not have had to download the entire hnd, install it then work thru it and find a suitable example (This post is already a few days old)

However, i will now go ahead and do this.I have to say though, i thought that my initial question and subsequent posts were clear enough to have narrowed down the issue sooner.

yogi

You don't have to install the whole application. Please follow the code path in the source code: - the search form is in Search.aspx and the code behind calls into: BL.Searcher.DoSearch. - BL.Searcher.DoSearch is a method in the BL project, in the Searcher class. - the DoSearch method uses a utility method which does the work you're likely looking for: it chops up the search terms and tries to formulate the terms to use for the different fulltextsearch predicates. This method is Searcher.PrepareSearchTerms. It's the method which can determine the different elements specifyable in the search form to different fulltextsearch elements.

Hope this helps you get started.

It's often a gamble into which direction to look for the answer. If we choose the wrong direction it might take longer to resolve. Please let us know if the code I pointed to is not usable for your problem.

Silly question maybe but you have populated the full text catalog haven't you?

You wouldn't believe I was helping one of our off shore team with a defect related to full text indexing in our catalogs and they couldn't get any results.

hiya,

Thanks for the replies.I'm looking into it.

you have populated the full text catalog haven't you?

hmm, not really sure what you mean here.If you mean:

1) "does the table have data", then yes. 2) "does the query return data when you use the 'FullTextSearchOperator.contains' operator and a 'FieldFullTextSearchPredicate' and use the entire word as the input", then yes.

I asked a similar question in a previous post.So, does the above mean that sqlServer 2005 is configured properly...obviously, at this stage, I'd like to be sure of that.

If the above is not a "deifinitive" test of whether I have configured everything corectly, would there be a simple query that I could run that _would _ confirm?

Thanks folks,

yogi

As said above, you want to filter on fragments, while the fulltextsearch first filters on whole words. As the HnD sourcecode would reveal to you, wildcards have to be enclosed in quotes. so you should use: string input = "\"yui\"";

instead. This is how the full text search works of sqlserver. THis is described in BOL of sqlserver, with the 'CONTAINS' keyword, <prefix_term>' syntax element.

hiya,

Ok, thanks all for the advice.I have it working up to a point. Thanks for bearing with me :-)

yogi