In the ensuing debate from the original advisory, a couple of things came up. People asked how things like dynamic searches could be achieved. There are two ways that allow the use of parameters, and I'll present some code.
To write a query that can be used by the dataset designer (i.e. not dynamically changing the content of the string variable holding the SQL) create a query like this (SQLServer syntax, use ? for Access, ?name for MySql and :name for Oracle)
SELECT * FROM table WHERE
(col1 = @col1 OR @col1 IS NULL) AND
(col1 = @col2 OR @col2 IS NULL) AND
(col1 = @col3 OR @col3 IS NULL)
Now just pass a null/Nothing into any parameter you wish to wildcard:
myTableAdapter.FillByXXX(dt, Nothing, "Col2 Value", 3)
You can get a bit more clever with your datatypes too. For strings, some optimisations can be made to have the SQL look simpler:
SELECT * FROM table WHERE
(col1 LIKE @col1 AND col2 LIKE @col2 AND col3 LIKE @col3)
Now just pass a string "%" to any parameter to wildcard it:
myTableAdapter.FillByXXX(dt, "%", "Col2 Value", "3")
-
The next level takes us back to building an SQL string dynamically using string concatenation. The basic premise is that, if you can write a routine that concatenates a value into an SQL string, you can easily write a routine that concatenates a parameter name into a string:
Code:
'pseudocode !
Dim x as New DbCommand("SELECT * FROM table WHERE 1=1 ")
For Each parameter_you_want_to_add
x.CommandText &= " AND columnName = @parametername"
x.Parameters.AddWithValue("@parametername", value)
Next
'real code; Oracle example.
Dim oc as New OracleCommand("SELECT * FROM orders WHERE 1=1 ")
'I normally use bound data, so to ask the user for what they wish to
'search, I clear the datatable currently bound, then add one row into
'which they type their search terms
Dim srchTerms as MyDataRow = myBoundTable.Rows(0)
For Each dc as DataColumn in srchTerms.Table.Columns
If srchTerms.IsNull(dc) Then Continue
Dim cName as String = dc.Name
Dim pName as String = ":p" + dc.Name
Dim pVal as Object = srchTerms.Item(dc)
If TypeOf pVal Is String Then
oc.CommandText += string.Format("AND {0} LIKE {1}", cName, pName)
Else 'can add other type checks here
oc.CommandText += string.Format("AND {0} = {1}", cName, pName)
End If
oc.Parameters.AddWithValue(pName, pVal)
Next
Naturally you don't have to do this in a loop. If you want to make the most complicated search form the world has ever seen, something to rival the query designer in MS Access, then you're going to have to get creative, but when all is done and written, you're going to have a nicely built SQL string:
SELECT * FROM table WHERE 1=1 AND parameter1 = @parameter1 AND …
And a parameters collection with ready to use values.