Creating a SQL Statement with multiple WHERE conditions

[Kamienios]

Hi There.

I am trying to write a Function which will return a data table, filled with a data from a database.

The Function will accept 6 diffrenet parameters as Optional. Each one of them will be an additional condition to be added to a where clause, if it`s provided when the function is beeing called.

Is there any method in .NET to simply add a new condition to the where clause?

Or Should I do it by working on a string?

Thanks.

[spyk3]

Maybe somthing like this:

Public Function where(ByVal cols As String, ByVal equals As String)
        Dim whereStrin As String = ""
        Try
            columns = cols
            values = equals
            whereString = "WHERE (" & build.whereColsAndVals(columns, values) & ")"
        Catch ex As Exception
 
        End Try
        Return whereString
    End Function

just add that to your selection string

[Kamienios]

To use this I must import some libraries.

What libraries should I import?

[spyk3]

Sorry i borrowed that from other code i wrote that has a custom buildstring class

here is the function in that class that is being refered to

Public Function whereColsAndVals(ByVal columns As String, ByVal values As String) As String
        Dim retvalues As String = ""
 
        If columns.Contains(" ") AndAlso values.Contains(",") Then
            columns = columns.Replace(" ", "")
        ElseIf values.Contains(" ") Then
            columns = columns.Replace(" ", ",")
        End If
 
        If values.Contains(" ") AndAlso values.Contains(",") Then
            values = values.Replace(" ", "")
        ElseIf values.Contains(" ") Then
            values = values.Replace(" ", ",")
        End If
 
        Try
            Dim araColumn() As String = Split(columns, ",")
            Dim aravalues() As String = Split(values, ",")
            Dim araRet() As String = Split(columns, ",")
 
            If araColumn.Count = 1 AndAlso aravalues.Count = 1 Then
                retvalues = columns & " = '" & values & "'"
            Else
                Dim i As Integer = 0
                While i < araColumn.Count
                    araRet(i) = araColumn(i) & " = '" & aravalues(i) & "'"
                    i = i + 1
                End While
                retvalues = Join(aravalues, "), (")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return retvalues
    End Function

Then just change the line from previous code:

Change line = whereString = "WHERE (" & build.whereColsAndVals(columns, values) & ")"
Change too = whereString = "WHERE (" & whereColsAndVals(columns, values) & ")"

Wrote it a long time ago but whatever

[spyk3]

Then u can make your select statement somthing like

selectStatement = "SELECT * FROM Database " &  where(columnValues, equalValues
 
or you could custumize ever part of the string to have its own string manipulation and have a final result like so:
selCommand = selectString & fromString & whereString

[techgnome]

*sigh*

How about parameters?

the SQL looks like this:

SELECT your_fields_go_here 
FROM _your_table_
WHERE 
  ((Field1 = @Parameter1) or (@Parameter1 IS NULL))
  AND ((Field2 = @Parameter2) or (@Parameter2 IS NULL))
  AND ((Field3 = @Parameter3) or (@Parameter3 IS NULL))
  AND ((Field4 = @Parameter4) or (@Parameter4 IS NULL))
  AND ((Field5 = @Parameter5) or (@Parameter5 IS NULL))
  AND ((Field6 = @Parameter6) or (@Parameter6 IS NULL))

Then you can add your parameters. If it isn't set, then simply pass in NULL as its value, otherwise pass in the value of the variable.

-tg

[Tom.Net]

Parameters are definitely a better choice then concatenating your query strings. However searching for null values in a where clause loses the optimazation that table indexes provide. I would suggest creating several Stored Procedures each defining exactly the query you want to use and then just calling the appropiate stored procedure.

[keystone_paul]

Parameters are definitely a better choice then concatenating your query strings. However searching for null values in a where clause loses the optimazation that table indexes provide. I would suggest creating several Stored Procedures each defining exactly the query you want to use and then just calling the appropiate stored procedure.

Stored Procedures are not necessarily a good idea - for a start they aren't supported at all in some database providers, and even if they are supported that ties you to a specific database provider so you can't easily change databases in the future.

[Tom.Net]

I simply have to disagree. Of course it cant be used if type of database provider he is using doesnt allow it but there are many benefits to using them if he can. Specially compared to attempting to pad parameters full of null values and searching for nulls.

[keystone_paul]

I simply have to disagree. Of course it cant be used if type of database provider he is using doesnt allow it but there are many benefits to using them if he can. Specially compared to attempting to pad parameters full of null values and searching for nulls.

I said "not necessarily a good idea", not "never a good idea".

[techgnome]

Parameters are definitely a better choice then concatenating your query strings. However searching for null values in a where clause loses the optimazation that table indexes provide. I would suggest creating several Stored Procedures each defining exactly the query you want to use and then just calling the appropiate stored procedure.

exqueeze me? How is the solution I provided ineeficient? All it's doing is checkign null values OF THE PARAMETERS... not the table data. Plus if there are 6 possible parameters... that's..... quite a few permutations... are you seriously suggesting writing a query for each and every possible combination? And then you'd have to write squirrely code to figure out which one to call.... how is that any better? That's nuts is what it is.

-tg