Search Form - how to implement?

[VBCrazyCoder]

I need to develop a search screen for an object. I know I need to build my SQL statement in code rather than a Stored Procedure (which sucks), but is there any examples or methodology on how to implement the build of the SQL statement. I want to allow searching by any number of fields and also allow "starts with", "ends with", "contains" type of searching for each field.

Thanks.

[nemaroller]

Phew, what a fun exercise that will be!

I guess what you're asking is:

if I had x number of tables each with their respective fields, and wanted to Search all of them for a given value?

Is that accurate?

[VBCrazyCoder]

Not exactly. I will have to search more than 1 table as my object contains values from more than one table. So I want to allow the user to search by any of the properties in the object, and return a collection of that object type.

[Pirate]

This is an example of how to do advanced search using multiple keywords .

[nemaroller]

This is going to be a work in progress for me... so if anyone else has done this code before... please feel free to jump in:

Step 1) We're going to need represent a map of the data.. iterating from 1 for a parent level to n representing the last child of that parent.

So if we have a field called City... we need to identify that field with a table, and the owner table with its parent table if any.


Step 2) To create the map, we need to get all the tables first using GetOleDbSchemaTable method of OleDbConnection for OLE DB data sources or by using Select sysobjects for SQL Server.

Then we need to use DataReader's GetSchemaTable.. so we can examine the table's setup.

Step 3) Somehow we need to map the data in the database to our program's objects. Let's take a scenario where the user wants to return all orders from John Doe.
In the Database
We might have a table called CustomerOrders that simply lists a customerID and an OrderID.

The order would have an order number, the customer would have a name. They are linked to foreign fields... we should be able to devise a way to find the datarows in the two tables and return both rows.
In our program

The hard part of course is translating that data into a useable object.

I can envision a XML mapping that could be changed as new fields are added to tables over the lifecycle of the program.

So we would need a person to modify that XML file so we translate that an ORDER search will scan the CustomerORders table, and return information from the Customer's table and the Order's table.

<Search>
--<Name>Customer Order Search</Name>
-----<ProgramObject>Orders</ProgramObject>
------<ParentTable>
------<Name>CustomerOrders</Name>
------<Children>
----------<ChildTable>
------------------<Name>Customers</Name>
------------------<SQLStatement>Select * From Customers where ID = [parameter]</SqlStatement>
-----------</ChildTable>
------------<ChildTable>
-------------------<Name>Orders</Name>
-------------------<SQLStatement>Selet * From ORders where ID = [parameter]</SQLStatement>
--------------</ChildTable>
--------</Children>
---------</ParentTable>
--</Search>



Phew.. I'm tired already.
(more to come in about a day)

[nemaroller]

Step 1...

http://support.microsoft.com/default.aspx?kbid=309488

that'll keep anyone busy for a few days..