Passing conditional parameters to Database.SqlQuery

Question

I have been doing the following to pass parameters with my sql query:

var retval = context.MyObject.SqlQuery(sql.ToString(),
                                                 new SqlParameter("@ProductId", productId),
                                                 new SqlParameter("@CustomerId", customerId)).ToList();

I ran into a scenario where @CustomerId will not always be used in my sql string. Meaning I used an if statement to either add (Where p.CustomerId = @CustomerId) or keep it out.

I realized that I can't pass my parameters in directly to the SqlQuery function now. Instead I have to create an object list of parameters and then pass it in:

        SqlParameter param1 = new SqlParameter("@ProductId", productId);
        SqlParameter param2 = new SqlParameter("@CustomerId", customerId);
        object[] parameters = new object[] { param1, param2 };

How can I make it so I can use an if statement to either add the CustomerId parameter to my parameters array or not?

Eric · Accepted Answer · 2015-06-15 14:53:25Z

3

You can edit your query to this

SELECT * FROM Product p 
WHERE 
    p.ProductId = @ProductId 
    AND (@CustomerId IS NULL OR p.CustomerId = @CustomerId)

Then you pass DBNull.Value to @CustomerId if it is not used

answered Jun 15, 2015 at 14:53

Eric

5,74319 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthew Peterson Over a year ago

That is how I am doing all of my queries going forward, but I am bringing over a lot of functions from an older application and would prefer keeping them the same. If there is no way to do this then I will have to update all of the queries to use where clauses like you specified.

Nico · Accepted Answer · 2015-06-15 15:06:39Z

You can try using a sql command instead as it allows using collections of parameters. But this might require you to change your code structure a bit.

        int a = 1;
        SqlCommand sql = new SqlCommand();
        sql.CommandText = "SELECT * FROm somwhere";

        List<SqlParameter> lstParams = new List<SqlParameter>();
        if (a == 1)
        {`enter code here`
            SqlParameter sqlParam1 = new SqlParameter();
            lstParams.Add(sqlParam1);
        }
        else if (a == 2)
        {
            SqlParameter sqlParam2 = new SqlParameter();
            lstParams.Add(sqlParam2);
        }

        sql.Parameters.AddRange(lstParams.ToArray());

        sql.BeginExecuteReader();

Collectives™ on Stack Overflow

Passing conditional parameters to Database.SqlQuery

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related