Create SQL parameters programmatically

Question

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

score 1 · Answer 1 · answered Mar 21 '12 at 16:28

1

Rather than programmatically adding the parameters, add them all, but include NULL conditions. For example:

SELECT * FROM tabe
WHERE fieldname=@p_FieldName 
AND (@p_FieldName2 IS NULL OR fieldname2=@p_FieldName2)

sqlparams.Parameters.Add("@p_FieldName2 ", SqlDbType.VarChar).Value = null;
if (txtValue.textLength > 0){
    sqlparams.Parameters("@p_FieldName2").Value = txtValue;
}

Here, if the text length of txtValue is 0, then the @p_FieldName2 parameter is set to null.

Then in the SQL Query the following will ignore the fieldname2=@p_FieldName2 if the value is NULL:

@p_FieldName2 IS NULL OR

answered Mar 21 '12 at 16:28

Curtis

101,612
66
270
352

interesting idea but this would cause additional load on the server to process the query, and this server is already on its last legs :( – Neo Mar 21 '12 at 16:29
3

Voted up, I don't see how this would add additional load. If anything, it's more efficient. You've got a single query with one execution plan, rather than several permutations that must be parsed. – Dan A. Mar 21 '12 at 16:36
ok suppose you have 30 possible clauses, thats 30 null values, also what if you need to use the same field in different ways like a date field? – Neo Mar 21 '12 at 16:42
Well, in that case, maybe dynamic parameters is a better route for you. It's certainly more flexible if you've got complex logic about how parameters get used. – Dan A. Mar 21 '12 at 16:54

Andy Holt · Accepted Answer · 2012-03-21T16:38:33.550

1

I've made a small alteration to your code, which should hopefully point you in the right direction:

    sql = connection.CreateCommand();    
    wherequery = @"WHERE fieldname = @p_FieldName ";
    sql.Parameters.Add(new SqlParameter("@p_FieldName ", "some value for fieldname"));

    if (txtValue.textLength > 0){
        wherequery += " AND fieldname2 = @p_FieldName2 ";
        sql.Parameters.Add(new SqlParameter("@p_FieldName2 ", txtValue.text));
    }
    query = @"SELECT * FROM tabe" + wherequery;

    sql.CommandText = query;

edited Mar 21 '12 at 16:38

answered Mar 21 '12 at 16:32

Andy Holt

572
2
9

1

You've changed the parameters of the `Add` method? I'm mainly a web dev guy, but shouldn't this be `AddWithValue`? And how would this change anything? – Curtis Mar 21 '12 at 16:34
Sorry, missed out the instantiation of a SqlParameter object. I've made an appropriate edit. – Andy Holt Mar 21 '12 at 16:36

score 0 · Answer 3 · answered Mar 21 '12 at 16:26

0

Use a Dictionary<string,object> where the string portion is the key and the object portion is the value.

answered Mar 21 '12 at 16:26

ctorx

6,841
8
39
53

score 0 · Answer 4 · answered Mar 21 '12 at 17:26

If you have a bunch of different possible fields to filter on, you're not getting away from coding them all, in one manner or another.

You could create a class to handle the string building. I stole this idea from work. =)

In pseudocode, it'd basically look like this:

Class WhereObj
{
  //whatever container you want to use to hold the params
  //you could also create a params class and have a list of param objects
  //it'd basically be a constructor and two properties

  private Params(,)

  Public void AddParam(fieldname, value)
  //adds param to Params

  Public string ToSQLString() 
  //loops params and builds string (use stringbuilder!)
  //ex: "where FirstName= 'Neo' and MatrixSequelsSucked = 'true'"
}

This should be a real easy class to code - probably under 50 lines. You should definitely create your own object to handle this, especially if you have to do this in multiple places. You could just write a function if it's only in one place, but I think it's a little cleaner to separate it out into an object.

Create SQL parameters programmatically

4 Answers4