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;

Use sql parameters as stated in some answers. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx . This prevents sql injection http://en.wikipedia.org/wiki/SQL_injection — Jason Down, Jul 17 '13 at 18:42
@evanmcdonnal: In response to your question (from the comment of your now deleted answer) of how `addwithvalue` prevents sql injection, take a look at this answer: http://stackoverflow.com/questions/3243194/what-sqlcommand-parameters-addwithvalue-really-does/3243210#3243210 — Jason Down, Jul 17 '13 at 19:12

Steve · Accepted Answer · 2013-07-17T18:42:42.270

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

score 3 · Answer 2 · answered Jul 17 '13 at 18:35

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

score 2 · Answer 3 · answered Jul 17 '13 at 18:41

Try this. you were using the string directly in the query which will go undetected.

SqlConnection sqlconnection = new SqlConnection("Server=Behnam\\Accounting;
Initial Catalog=Accounting;Integrated Security=TRUE");
DataTable dt = new DataTable();
SqlDataAdapter SDA = new SqlDataAdapter("SELECT dbo.tbl_user.field1,dbo.tbl_user.field2 FROM tbl_user WHERE dbo.tbl_user.name=@searchName" , sqlconnection);
SDA.SelectCommand.Parameters.AddWithValue("@searchName", txt_search.Text);        
SDA.Fill(dt);
dataGridView1.DataSource = dt;

I just deleted my comment and gave you +1 now ;) – Jason Down Jul 17 '13 at 18:44 — Jason Down, Jul 17 '13 at 18:44

How use WHERE in SqlDataAdapter in C#

3 Answers3