c# Using Parameters.AddWithValue in SqlDataAdapter

Question

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.

Solution 1

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 + "%";

Solution 2

Use da.SelectCommand.Parameters.Add() instead of cmd.Parameters.Add(), here's a sample for dealing with a stored procedure which takes two parameters and second one is a nullable int parameter:

public DataTable GetData(int par1, int? par2)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            string sql = "StoredProcedure_name";
            da.SelectCommand = new SqlCommand(sql, conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            da.SelectCommand.Parameters.Add("@Par1", SqlDbType.Int).Value = par1;
            da.SelectCommand.Parameters.Add("@Par2", SqlDbType.Int).Value = (object)par2?? DBNull.Value;

            DataSet ds = new DataSet();
            da.Fill(ds, "SourceTable_Name");

            DataTable dt = ds.Tables["SourceTable_Name"];

            //foreach (DataRow row in dt.Rows)
            //{
            //You can even manipulate your data here
            //}
            return dt;
        }
    }
}