sql search string

[Barok]

hello

i wrote a little c# sql client and now i need to implement the search function. i need some ideas how to do this, next to hundreds of ifs, wich was my first way to implement it. i have 8 different textboxes to search for, any ideas?

[rpicilli]

HI There.

There is a lot of way to do that. The easyest and fast way is to create Querys and call them from your code.

What type of db access are you using? Dataset, datatables, etc...

Again the best way is to create a Layer for data access into your application.

Into this layer you create the query and call from your code.

If you wanto to query your database table with multiples datas (your txtBox) you can use either way the "=" sign or "LIKE" command.

This is an exemple of quer agains a table named Customer

SELECT Customer.Name, Customer.Age, Customer.Gender, Customer.Active
FROM    Customer
 

This code will give you all the rows of the customer table.

If you want to filter the info get by the query, use something like that

SELECT Customer.Name, Customer.Age, Customer.Gender, Customer.Active
FROM    Customer
WHERE  Customer.Name = @Customer.Name
 

In the above case you should supply the parameter @Customer.Name from your code.

I hope this help you.

[Curtis Rutland]

rpicilli,
the / goes inside the code tag:
[/code]

I fixed it for you this time.

[Barok]

thanks rpicilli

this is the way ive done it, creating querys and call them from code.im working with dataset access.

what im looking for is a algorithm or some ideas to organize the querystring


scenario 1:
you know specific data about the file, the name, the size, date of creation.., do a normal query

scenario 2:
you know nothing about the file, check which boxes are selected and organize the querystring

 
bool extensionset = tBoxSxExtension.Text.Length > 0 ? true : false;
bool sizeset = ...
 
 
if(extensionset && sizeset)
 //do a query with extension and size
elseif(extensionset && dateset)
 //do a query with extension and creation date
else if(extensionset && dateset && referenceset)
 //do a query with extension, creation date and reference number
else if(dateset && customerset)
 //do a query with creation date and customer name
else if(dateset && referenceset && customerset)
 .... up to 30 if's..
 
 

i need to get rid of all those if's^^

[rpicilli]

Hi Barok,

May be I do not understood your question. Either way I'll tell what I've donne.

I have a situation where I've 9 textbox that are use to search a SQL table. I never know which one are filled or if all of them has data.

I don't want to test with IFs so I resolve my problem with the query that is showed below. You can create a query with LIKE statemenst empty. At least on SQL Server.

Let me know if this help you.

 
SELECT     Ass, Author, Cat, Dtq, Dta, Doc, Fic, Loc, Obs, TF1, Tdc, Tip
FROM         TF1
WHERE     (Doc LIKE @Doc) AND 
                 (Tip LIKE @Tip) AND 
                 (Cat LIKE @Cat) AND 
                 (Ass LIKE @Ass) AND 
                 (Author LIKE @Author) AND 
                 (Dtq LIKE @Dtq) AND 
                 (Dta LIKE @Dta) AND 
                 (Loc LIKE @Loc) AND 
                 (Obs LIKE @Obs)
 
 

In the query above, doesn't matter if one or more parameters has values. Of course as much as parameters you have, more accurated will be you results.

[Barok]

thank you very much rpicilli, this is exactly what im looking for.

i tried to filter out those values by code, instead of doing this by the querystring.
i do have sql-server 05 installed on my machine, but never learned enough of this powerfull db. maybe i should read more about it!!

so yes it does help me, thanks a lot!