How use WHERE in SqlDataAdapter in C#

Question

How use WHERE in SqlDataAdapter in C#?

I want get name in a textbox and use that at query but it wont work .

SqlConnection sqlconnection = new SqlConnection("Server=Behnam\\Accounting;Initial Catalog=Accounting;Integrated Security=TRUE");

DataTable dt = new DataTable();
string _search_name = txt_search.Text;

SqlDataAdapter SDA = new SqlDataAdapter("SELECT dbo.tbl_user.field1,dbo.tbl_user.field2 FROM tbl_user WHERE dbo.tbl_user.name=_search_name ", sqlconnection);

SDA.Fill(dt);

dataGridView1.DataSource = dt;

Answer 1

Prepare the command text and use a parameter for the value of your search. Then use that command text to initialize a new SqlCommand. Fill the parameter value with AddWithValue and pass the SqlCommand to the constructor of the SqlDataAdapter.

string cmdText = "SELECT dbo.tbl_user.field1,dbo.tbl_user.field2 " +  
                 "FROM tbl_user WHERE dbo.tbl_user.name=@search_name"
SqlCommand cmd = new SqlCommand(cmdText, sqlconnection);
cmd.Parameters.AddWithValue("@search_name", _search_name);
SqlDataAdapter SDA = new SqlDataAdapter(cmd);

The SqlDataAdapter will store your command as the SelectCommand property and will use the passed in SqlCommand to execute the query to retrieve the records from the database.

Keep in mind that AddWithValue is a shortcut with some drawbacks. For example it pass Always a string as a nvarchar parameter with size equal to the actual lenght of the variable. This effectively reduces the performance of the Sql Server Optimizer.

This is a very enlightening article on the issue

Answer 2

So, you were pretty close, you just needed to define a parameter inside the query and then add that parameter. However, in the following code block I've also conveniently recommended a more appropriate approach to using the classes needed to get the data (pun intended). The using statement here ensures that the objects get disposed of properly after you are done using them (man I just can't stop with the puns!)

using (SqlConnection c = new SqlConnection(connString))
{
    c.Open();
    using (SqlDataAdapter sda = new SqlDataAdapter(
        "SELECT dbo.tbl_user.field1, dbo.tbl_user.field2 FROM tbl_user " +
        "WHERE dbo.tbl_user.name= @name", c))
    {
        sda.SelectCommand.Parameters.AddWithValue("@name", txt_search.Text);
        DataTable dt = new DataTable();
        sda.Fill(dt);
    }
}