CODEWITHSUNDEEP
c#
ggrewe1959
ggrewe1959

Reputation: 117

Is there a better way to split an array into a string

I am trying to pass an Array to a method and then return a string I can use for a SQL WHERE clause. I have the following and it works just fine. But is there a better way? I am looking for one of the two results;

  1. WHERE (ColumnName IN (12, 34, 56, 78, 90))
  2. WHERE (ColumnName IN ('12', '34', '56', '78', '90'))
    public static string setInSearchFilter(string psSearchFilter, string psColumnName, 
        string[] paObjectValues, bool pbIsString)
    {
        string lsDelimiter = "'", lsRetVal = string.Empty, lsObjectValues = string.Empty;

        if (!pbIsString)
        {
            lsDelimiter = string.Empty;
        }

        if (!string.IsNullOrEmpty(psSearchFilter))
        {
            lsRetVal = psSearchFilter + " AND ";
        }

        for (int i = 0; i <= paObjectValues.GetUpperBound(0); i++)
        {
            lsObjectValues += lsDelimiter + paObjectValues[i] + lsDelimiter;

            if (i < paObjectValues.GetUpperBound(0))
            {
                lsObjectValues += ", ";
            }
        }

        return lsRetVal += "(" + psColumnName + " IN (" + lsObjectValues + "))";
    }

Upvotes: 1

Views: 97

Answers (2)

McGarnagle
McGarnagle

Reputation: 102733

As suggested in the comments, string.Join:

string.Format(
    "({0}" + string.Join("{0},{0}", paObjectValues) + "{0})",
    lsDelimiter 
);

This assumes there's something in the list, so make sure to throw or return if the filter set is empty.

You might also consider doing some kind of validation on the items to prevent SQL injection:

if (paObjectValues.Any(item => !int.TryParse(item))) 
    throw new Exception("Items must be numeric");

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062550

I would add parameters for every option (allowing query-plan re-use and injection-safety), or I would investigate a "split" UDF. Or, better, I'd use something like an ORM or micro-ORM which may often have this type of capability built in. For example, with LINQ you can often use an array/list and Contains. Or with "dapper" you can use a subtle variant of the IN syntax which expands the inputs into separate parameters - specifically: where x.Foo in @bar becomes where x,Foo in (@bar0, @bar1, @bar2) if the "bar" parameter has 3 values.

Upvotes: 1

Related Questions