Create SQL parameters programmatically
- by Neo
Another annoying one for me but probably something simple.
I have a number of possible where clauses for a query based on user input, my question is how can I add these programmatically?
For instance:
wherequery = @"WHERE fieldname = @p_FieldName AND ";
if (txtValue.textLength > 0){
wherequery += "fieldname2 = @p_FieldName2 AND ";
}
query = @"SELECT * FROM tabe" + wherequery;
sql = connection.CreateCommand();
sql.CommandText = query;
How would I go about doing the parameters for that? I've tried ArrayLists, Dictionaries and a few other methods but can't find a way of doing it. Ideally I'd want to do something like this:
SqlParameter[] sqlparams;
wherequery = @"WHERE fieldname = @p_FieldName AND ";
if (txtValue.textLength > 0){
wherequery += "fieldname2 = @p_FieldName2 AND ";
sqlparams.Parameters.Add("@p_FieldName2 ", SqlDbType.VarChar).Value = txtValue.text;
}
query = @"SELECT * FROM tabe" + wherequery;
sql = connection.CreateCommand();
sql.CommandText = query;
sql.Parameters.Add(sqlparams);
