Retrieve data from database view with dynamic where clauses (ConnectionHelper.ExecuteQuery)

Jay Crowe asked on March 6, 2018 18:35

Hi All,

I was just wondering if anyone could help with a problem I am having with regards filtering data returned from a database view using the ConnectionHelper.ExecuteQuery method?

I have successfully retrieved the data from the view and returned that as a DataSet to which I can then access the data table and do whatever with the data returned.

I was hoping to then create this as a method to which I could dynamically pass in where clauses which would then be dynamically added to the sql sent down to the database view and only retrieve me the data I wanted (rather than returning every row). I've tried using QueryDataParameters but these parameters seem like they need to be predefined.

Any help would be greatly appreciated.

Thanks, Jay

database view

Recent Answers

Peter Mogilnitski answered on March 6, 2018 19:08 (last edited on March 6, 2018 19:24)

If you use this method then you can form SQL WHERE like this:

        string where = "UserName NOT LIKE N'public'";
         // Do not select hidden users
        where = SqlHelper.AddWhereCondition(where, "((UserIsHidden IS NULL) OR (UserIsHidden=0))");

        ConnectionHelper.ExecuteQuery("somequeryname", null, where)

Don't forget query must contain ##WHERE##.

P.S. Try to pass null instead parameters if you don't want to use them. If null doesnt work try to pass new QueryDataParameters() instead:

ConnectionHelper.ExecuteQuery("somequeryname", new QueryDataParameters(), where)

Take a look ExecuteQuery Overloads

0 votesVote for this answer Mark as a Correct answer

Jay Crowe answered on March 6, 2018 21:49

Hi Peter,

Thank you for your answer. I will give that a go but I would really like to build them up in a type safe manner.

Thanks, Jay

0 votesVote for this answer Mark as a Correct answer