c# Using Parameters.AddWithValue in SqlDataAdapter

The string used to initialize the SqlDataAdapter becomes the value for the CommandText property inside 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 quotes around the parameter placeholder.
  • Second, add the wildcard character directly to 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 be the source for 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 + "%";

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)