How use WHERE in SqlDataAdapter in C#
.net, ado.net, c#
Solution
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
Problem
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; ```