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.

Bothy

Answer 1

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

Ware

Answer 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;
        }
    }
}

Small

Answer 3

Try this:

mySearchString = "Select * From test Where ([title] LIKE '%' + @title + '%')";
cmd.Parameters.Add("@title", SqlDbType.VarChar, 120);
cmd.Parameters("@title").Value = TextBox1.Text;

Obloquy

Answer 4

I use Repeater for show data

int queryString =int.Parse(Request.QueryString["Id"]);

SqlConnection conn =new SqlConnection("server=.; Database=Northwind; 
Integrated Security=true;");

try{
conn.Open();

SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT ProductID, ProductName, UnitPrice, CategoryID FROM Products WHERE CategoryID =@CategoryID", conn);

dataAdapter.SelectCommand.Parameters.Add("@CategoryID", queryString);
                        DataSet dataSet = new DataSet();
                        dataAdapter.Fill(dataSet);
                        QueryStringProductListRepeater.DataSource = dataSet;
                        QueryStringProductListRepeater.DataBind();
}

catch{
Response.Write("QueryStringProductListRepeater");
}

finally{
conn.Close();
}

Quotha