The string used to initialize the SqlDataAdapter becomes the CommandText
of the SelectCommand property of the SqlDataAdapter.
You could add parameters to that command with this code
da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE @search",
_mssqlCon.connection);
da.SelectCommand.Parameters.AddWithValue("@search","%" + txtSearch.Text + "%");
- First, remove the single quote around the parameter placeholder.
- Second, add the wildcard character directly in the Value parameter of
AddWithValue
You have asked to use AddWithValue, but remember that, while it is a useful shortcut, there are also numerous drawbacks and all well documented.
- First: Can we stop using AddWithValue() already? where the
author discuss how AddWithValue could give back wrong results in your
queries - Second: How Data Access Code Affects Database Performance where
the author presents evidences of strong performance problems for
AddWithValue
So, the same code without AddWithValue and using the Object and Collection Initializers syntax could be written as
da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE @search",
_mssqlCon.connection);
da.SelectCommand.Parameters.Add(new SqlParameter
{
ParameterName = "@search",
Value = "%" + txtSearch.Text + "%",
SqlDbType = SqlDbType.NVarChar,
Size = 2000 // Assuming a 2000 char size of the field annotation (-1 for MAX)
});
and, an even more simplified and one liner version of the above is:
da.SelectCommand.Parameters.Add("@search",SqlDbType.NVarChar,2000).Value = "%" + txtSearch.Text + "%";