How to search in an array with npgsql?

Question

I have an array column i would like to search.

The search term is like :

WHERE ARRAY['tag1','tag2'] <@ tags

If i search like this (where donnee is the string of the text to search):

string variable1 = string.Empty;

            string[] tags = donnee.Split(',');

            if (tags.Length > 1)
            {
                foreach (string item in tags)
                {
                    variable1 = variable1 + "'" + item + "',";

                }

                variable1 = variable1.Remove(variable1.Length - 1);
            }
            else
            {
                variable1 = variable1 + "'" + tags[0] + "'";
            }


            NpgsqlCommand cmd = new NpgsqlCommand("SELECT id,name FROM wincorrespondants WHERE (name ILIKE @donnee) OR (ARRAY[@var] <@ tags)  LIMIT 100", conn);

            cmd.Parameters.AddWithValue("donnee", "%" + donnee + "%");
            cmd.Parameters.AddWithValue("var", variable1);

the search don't work on the tag array but the program does not crash if there is a ' in the search string

If i search like this:

string variable1 = string.Empty;

            string[] tags = donnee.Split(',');

            if (tags.Length > 1)
            {
                foreach (string item in tags)
                {
                    variable1 = variable1 + "'" + item + "',";

                }

                variable1 = variable1.Remove(variable1.Length - 1);
            }
            else
            {
                variable1 = variable1 + "'" + tags[0] + "'";
            }


            NpgsqlCommand cmd = new NpgsqlCommand("SELECT id,name FROM wincorrespondants WHERE (name ILIKE @donnee) OR (ARRAY["+variable1+"] <@ tags)  LIMIT 100", conn);

            cmd.Parameters.AddWithValue("donnee", "%" + donnee + "%");

the program crash if there is a ' in the search string but work in other case (the program find the name the user is looking for or the user with the tags)

How do i format my search ?

Answer 1

Rather than trying to pass elements of the array, you should be sending the array itself as a parameter from Npgsql. For example:

var cmd = new NpgsqlCommand("SELECT id,name FROM wincorrespondants WHERE (name ILIKE @donnee) OR (@arr <@ tags)  LIMIT 100", conn);
cmd.Parameters.AddWithValue("arr", new[] {"tag1", "tag2" });