[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

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
0
xandolph
Asked:
xandolph
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
tigin44Commented:
you should use stored procedures to accomplish this task. That will be easy and fast...
0
 
zadeveloperCommented:
I agree with tigin44
When you execute logic in a stored procedure you accomplish the following things:
1. You use the servers processor
2. You limit traffic travelling from the SQL server to the client workstation - reducing badwith to only the required / relevant data.
3. If a change needs to be made to the logic, you can chage the stored proc and not have to worry about redistributing the application with the new logic.
0
 
xandolphAuthor Commented:
Thanks for the quick answers.
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 ?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
tigin44Commented:
you dont need to join so much table if you dont need them. One option can be using parameter... based on the parameter you may decide which tables to join...
Another option can be using dynamic sql.
Both options when carefully designed can produce good results...
0
 
zadeveloperCommented:
You could dynamicly create the sql in the stored proc - store it as a string and the use the exec command to return the results.
CREATE PROCEDURE up_SearchTest(@FirstName nvarchar(200)=null, @LastName nvarchar(200)=null)
-- Because we have initalised @FirstName and @LastName they are optional parameters from your app

declare @SqlString nvarchar(max)

set @SqlString = 'select * from Person '

if (@FirstName is not null) begin
	set @SqlString = @SqlString + ' WHERE FirstName = ' + @FirstName
end

if (@FirstName is not null) begin
	set @SqlString = @SqlString + ' and LastName = ' + @LastName
end

exec (@SqlString)

Open in new window

0
 
zadeveloperCommented:
This way you can add as many paramaters as you need, only pass in the ones you want to use for the specific query instance from your c# app and can apply the same logic to create the query string
0
 
Gene_CypCommented:
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.
0
 
xandolphAuthor Commented:
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.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now