xandolph
asked on
Complex Searches in a SQL DataBase with C# Windows Forms Application
Hi!
I'm working on a Windows Forms based C# application, that manages a SQL database with results of scientific experiments. This database consists of about 100 Tables, some of them fairly complex.
The user should be able to conduct complex searches by defining search filters in a form and specifying what type of object he is looking for, and which values should meet certain criteria (see attached screenshot). The attributes in the filters can come from different tables, and can be Strings or numbers.
How can I accomplish this functionality? Shall I use Stored Procedures on the SQL-Server level, or search through the DataSet in C#? Perhaps someone could point me in the right direction.
Thanks for any help
Xandolph
SearchScreenshot.jpg
I'm working on a Windows Forms based C# application, that manages a SQL database with results of scientific experiments. This database consists of about 100 Tables, some of them fairly complex.
The user should be able to conduct complex searches by defining search filters in a form and specifying what type of object he is looking for, and which values should meet certain criteria (see attached screenshot). The attributes in the filters can come from different tables, and can be Strings or numbers.
How can I accomplish this functionality? Shall I use Stored Procedures on the SQL-Server level, or search through the DataSet in C#? Perhaps someone could point me in the right direction.
Thanks for any help
Xandolph
SearchScreenshot.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alternatively, you can create a library in your C# app, that constructs the queries.
It loses some of the stored procedure advantages but gains in ease of use and flexibility.
It loses some of the stored procedure advantages but gains in ease of use and flexibility.
ASKER
Thanks a lot for the comments, I´ll try the Stored Procedure approach first. I'll split the points and accept zadeveloper's and tigin44's solution.
ASKER
The problem I see with Stored Procesures is this: How can I efficiently combine for example 25 tables where perhaps 100 attributes (columns) shall be "searchable" ? I would need a massive SELECT statement with joins for all the tables and 100 WHERE clauses and 100 parameters in my SP... What if the user defines only 2 search filters, do I have to pass NULL to all the other parameters and work with COALESCE ?