Sorry to come to the party late, but I really don't recommend what's been done here..
All that has been achieved with the stored procedure is shifting the string concatenation to the database, and youre still reliant on the database converting parameters to a string datatype. Further youre building a query that forces wildcards on the start and end of the term, which can be slow, much slower than letting the users specify the wildcard themselves if they need it, and youre including all the search terms. Adding the stored procedure is just a level of indirection; it's not needed to enable the query.
I'd have code like this:
'i'll explain why 1=1 in a moment
Dim cmd As New SqlCommand
cmd.CommandText = "SELECT * FROM tblPerson WHERE 1=1"
If Not String.IsNullOrEmpty(firstNameTextBox) Then
cmd.CommandText = cmd.CommandText & " AND firstname = @firstname"
cmd.Parameters.AddWithValue("@firstname", firstNameTextBox.Text)
End If
If Not String.IsNullOrEmpty(lastNameTextBox) Then
cmd.CommandText = cmd.CommandText & " AND lastname = @lastname"
cmd.Parameters.AddWithValue("@lastname", lastNameTextBox.Text)
End If
If isAliveCombo.Value <> "" Then
cmd.CommandText = cmd.CommandText & " AND isAliveBoolean = @isAlive"
cmd.Parameters.AddWithValue("@isAlive", isAliveCombo.Value = "Yes")
End If
We need the 1=1 because every other clause is ANDed on. and its a syntax error to say:
SELECT * FROM table
WHERE AND lastname = @lastname
we need a useless clause in there that wont affect the results but makes our concatentating life easy, otherwise we get into messy code, of "if the number of parameters is zero, dont put the AND otherwise do put the and"
Notice that i took your combo which apparently is 3 state (not specified, yes, no) for "is the person alive" and compared its value with "Yes" to give a boolean in the query assuming your table column is a boolean
At the end of this you will have a query that specifies only the parameters filled in.. 3 of 5 text boxes filled? your query will have 3 clauses (plus the useless 1=1) and 3 parameters
-
You also have the option of writing the whole query in advance:
SELECT * FROM tblPerson WHERE lastname LIKE @lastname AND firstname LIKE @firstname AND (isAlive = @isAlive OR @isAlive IS NULL)
Note that this form of query, because it is fixed (not dynamic), can be programmed into a tableadapter. The database might have more trouble optimizing it though, possibly leading to slower performance or wrong index selection.
Because it is fixed, put the values into variables then call the relevant fill method
If the user doesnt specify a last name, set it to a value of ;%':
Dim last as String = lastnameTextbox.text
if String.IsNullOrEmpty(last) Then
last = "%"
end if
(Similar code for first)
If the user doesnt specify the isAlive, set it to null:
Dim isalive as Nullable(Of Boolean) = nothing
If NotString.IsNullOrEmpty(isaliveCombo.SelectedText) Then
isalive = (isaliveCombo.SelectedText = "Yes") 'perform comparison with "yes" and store resulting boolean
end if
Call the tableadapter search:
myTA.FillByVarious(theDataTable, first, last, isalive)
-
remember that you can take the former style and use a dataadapter to fill your table..
