asked on

Beginner's question: C# visual studio .net 2003 and sql, how to select records based on pattern matching

I am learning C# using VS .net 2003 and want to know how to display records in an Access database based on specific search terms.
What I am using as a learning model is the following article in MSDN Library:
Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query

The code to pass the parameter to MSDE to display only records with specific State (such as CA) is:
oleDbDataAdapter1.SelectCommand.Parameters["state"].Value = txtStateParameter.Text;

So far, I managed to use another Access database and search on another field and can pattern match using %SearchTerm% fine.

Now I want to improve this search. Let say when user enter "red shirt", I want to display records that has "red" and "shirt" in any order. Not just records that has "red shirt". How do I pass this to the sql select statement to achieve my result. I know how to split the search string to 2 strings "red" and "shirt".

Thanks.

stu_pb

You will need to use an OR and two Search Patterns.

(%red%shirt% OR %shirt%red%)

You will have to add another parameter to the query and use the second string.

Good Luck!

rj94070

ASKER

Thanks, Stu. I basically know this but the question is how do I pass this to the sql Select statement.

The sql statement use a question mark ? as parameter placeholder.
WHERE items LIKE ?
is the condition.

I could pass %red shirt% to it without problem and it parsed "WHERE items LIKE %red shirt%" fine.

But I don't know how to pass (%red%shirt% OR %shirt%red%) because the Select statement will be:

WHERE items LIKE (%red%shirt% OR %shirt%red%)
although this looks perfectly reasonable it didn't work. Don't know exactly why.

Besides, another BIG problem with this solution is: what to do when I have 4 or more words to search for? The number of ORs will grow exponentially!!! because words can be in any order.

I would like actual code if possible.

Eduard Ghergu

You can use something like :
WHERE (items LIKE (%red%shirt%)) OR (items LIKE (%shirt%red%))

Also, have a look at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_154e.asp

Greetings!

rj94070

ASKER

ghergu,
Again this won't work because of the reason I mentioned earlier: let's say user enter a search string with 16 words in it. I will need over 65 thousand OR's!!!
The only acceptable way is to generate a dataset that has the records containing the first word, then search this dataset and create a new one that contains the 2nd word, and so on.

My problem is how to do this using C#, VS .NET and the oledbdataadapter...
Thanks.

stu_pb

You could try this:

SELECT * FROM TABLE
WHERE
items LIKE (%red%) AND
items LIKE (%shirt%) AND
.
.
.

This way you will only have to have one AND for each word you are searching for, this will prevent the exponential growth of ORs.

Here is some code to create the OleDbDataAdapter in C#:

OleDbConnection conn = new OleDbConnection(connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand query = new OleDbCommand("SELECT * FROM TABLE WHERE (items LIKE %@Param1%) AND (items LIKE %@Param2%)", conn);
query.Parameters.Add(new OleDbParameter("@Param1","red");
query.Parameters.Add(new OleDbParameter("@Param2","shirt");
adapter.SelectCommand = new OleDbCommand(query, conn);
adapter.Fill(dataset);
return dataset;

Here is where I got the basis for this code: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbdataadapterclasstopic.asp

Good Luck!

rj94070

ASKER

Stu,
I think we are getting somewhere now with the Add method. Give me a day or two to implement the code and test it. Because the number of search words varies, I will have to dynamically generate the query string (ie, I cannot hard code it as having only 2 LIKEs.
Thanks.

ASKER CERTIFIED SOLUTION

stu_pb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rj94070

ASKER

Stu,
I haven't had time to code and test but I believe you pointed me to the right direction to research therefore you should receive full credit right away.
Thanks.

stu_pb

I just looked at my last post, and there is a slight error in it. I have posted the revised code below. I changed the following statement:

query.CommandText = "SELECT * FROM TABLE WHERE (items LIKE %@Param0%)"; // Changed Param1 to Param0

Here is all the code.

OleDbCommand query = new OleDbCommand();
query.Connection = conn;

for (int i = 0;i < parameterCount;i++)
{
if (i == 0)
query.CommandText = "SELECT * FROM TABLE WHERE (items LIKE %@Param1%)";
else
query.CommandText += "AND items( LIKE %@Param" + i.ToString + "%)";

query.Parameters.Add(new SqlParameter("@Param" + i.ToString(),queryStringArray[i]);
}

Thanks for the points and Good Luck!