Rewrite using SQL Parameters
How can I rewrite this so that it is using parameters instead of string values? I know how to do it for an "SqlCommand", but I can't seem to work it out here.
protected void searchFill()
{
orderByString = orderByList.SelectedItem.Value;
fieldString = searchTextBox.Text;
string sqlStatement = "SELECT * FROM SecureOrders WHERE fName LIKE @fieldString OR lName LIKE @fieldString OR addr LIKE @fieldString OR addr2 LIKE @fieldString OR city LIKE @fieldString OR state LIKE @fieldString OR zip LIKE @fieldString OR zip LIKE @fieldString OR country LIKE @fieldString OR email LIKE @fieldString OR phone LIKE @fieldString OR ccType LIKE @fieldString OR ccNum LIKE @fieldString OR ccExp LIKE @fieldString OR cwaSource LIKE @fieldString OR cwaJoined LIKE @fieldString OR length LIKE @fieldString OR delivery LIKE @fieldString OR price LIKE @fieldString OR url LIKE @fieldString ORDER BY @orderByString";
using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
{
connection.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlStatement, connection);
dataAdapter.SelectCommand.Parameters.AddWithValue("@fieldString", fieldString);
dataAdapter.SelectCommand.Parameters.AddWithValue("@orderByString", orderByString);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "SecureOrders");
DataView source = new DataView(dataSet.Tables[0]);
DefaultGrid.DataSource = source;
DefaultGrid.DataBind();
connection.Close();
}
}
Here is my other method where I AM using parameters (just to show you I do know how to use them slightly)
protected void updateCheck(bool checkValue, int identity)
{
string checkStatement = "UPDATE dbo.SecureOrders SET processed = @Processed WHERE IdentityColumn LIKE @identity";
using (SqlConnection connection = new SqlConnection(connecti开发者_运维问答onString.ToString()))
using (SqlCommand _check = new SqlCommand(checkStatement, connection))
{
_check.Parameters.Add("@Processed", SqlDbType.Bit).Value = checkValue;
_check.Parameters.Add("@identity", SqlDbType.Int).Value = identity;
connection.Open();
_check.ExecuteNonQuery();
connection.Close();
}
}
Instances of SqlDataAdapter
have a property called SelectCommand
that you can attach parameters to:
var dataAdapter = new SqlDataAdapter(someQuery, someConnection);
dataAdapter.SelectCommand.Parameters.AddWithValue("@someParam", someVar);
精彩评论