1

I am in a application where Parameterized Sql queries are not written. Below is code block

  public List<MyClass> GetData(int Id, IEnumerable<string> state)
    {
        using (var dataContext = new DataContext(_connectionString)) 
            {
            var query = new StringBuilder("SELECT * FROM table");
            query.Append(" Id = ");
            query.Append(Id);
            query.Append(" AND state IN ('");
            query.Append(string.Join("','", state));
            query.Append("')");

            return dataContext.ExecuteQuery<MyClass>(query.ToString()).ToList();
     }

I am refactoring code using parameterized query like this :

    public List<MyClass> GetData(int Id, IEnumerable<string> state)
    {
      using (var dataContext = new DataContext(_connectionString)) 
        {
            var statestring = new StringBuilder("'");
            statestring.Append(string.Join("','", state));
            statestring.Append("'");
             string myStates= statestring.ToString();
            string query = "SELECT * FROM table WHERE Id ={0} AND state IN ({1})";
            return dataContext.ExecuteQuery<MyClass>(query, new object[] {Id, myStates}).ToList();
         }
     }

I get no data on running this query. On debugging i found my query is getting formed like this

  SELECT * FROM table WHERE Id ={0} AND state IN ({1}) where in ({1})

For state I see data as "'error',' warning'". In sql server I run query like this 

   SELECT * FROM table   WHERE Id =34 AND state IN ('error','warning').

Do i need to remove " " around mystate? I tried removing " using trim method and assigning it back to string but it didn't work. I can still see double quotes.

  myStates = myStates.trim('"');

How can parameterize my query better without using any string builder for the same

pankaj
  • 1,030
  • 5
  • 19
  • 41

3 Answers3

1

Alternative suggestion: dapper...

int x = ...;
int[] y = ...
var items = connection.Query<MyClass>(
   "SELECT * FROM table WHERE X = @x AND Y in @y", new {x,y}).AsList();

Dapper will deal with this for you, using an appropriate query for 0, 1, or many items, including (optional configuration options) padding the parameters to avoid query plan saturation (so when you have large lists, you use the same query and query-plan for 47 items, 48 items and 49 items, but possibly a different query for 50), and using string_split if it is supported on your server.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • I am in a legacy app, I don't have freedom to use anything new. What can be solution to my problem? – pankaj Mar 19 '17 at 10:57
  • @sameer I propose "slapping the person who made the the 'don't use anything new' rule" - I suspect that will solve an awful lot of problems (caveat: this is hyperbole - don't actually do that) – Marc Gravell Mar 19 '17 at 22:36
0

I think that you should change the way you pass the parameters:

return dataContext.ExecuteQuery<MyClass>(query, Id, stateString).ToList();

For reference please have a look at the signature of this method, which can be found here.

Christos
  • 53,228
  • 8
  • 76
  • 108
0

To parameterize the in clause every case has to be an individual parameters. So the in clause has to reflect that.

See this similar question: How to pass sqlparameter to IN()?

public List<MyClass> GetData(int Id, IEnumerable<string> state)
{
    using (var dataContext = new DataContext(_connectionString))
    {
        var stateParameterNumbers = Enumerable.Range(1, state.Count())
            .Select(i => string.Format("{{{0}}}", i));

        var stateParameterString = string.Join(",", stateParameterNumbers);

        string query = "SELECT * FROM table WHERE Id ={0} AND state IN (" + stateParameterString + ")";
        return dataContext.ExecuteQuery<MyClass>(query, new object[] { Id }.Concat(state).ToArray()).ToList();
    }
}
Community
  • 1
  • 1
Chris Jansson
  • 401
  • 4
  • 5