How can I use Parameters.AddWithValue with an SqlDataAdapter. Below searching codes.
var da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE '%"+txtSearch.Text+"%'", _mssqlCon.connection);
var dt = new DataTable();
da.Fill(dt);
I rewrote the code like this:
SqlDataAdapter da;
da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE '%@search%'", _mssqlCon.connection);
da.SelectCommand.Parameters.AddWithValue("@search",txtSearch.Text);
var dt = new DataTable();
da.Fill(dt);
but it failed.
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 + "%");
You have asked to use AddWithValue, but remember that, while it is a useful shortcut, there are also numerous drawbacks and all well documented.
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 + "%";