How can I pass the number of parameters, which can vary each time in the sql query? I need to issue the following query for all sorts of parameters: SELELT * FROM table WHERE name IN (value1, value2, value3, value4)

Those. not necessarily only 4 values, there may be 10, and one, and each time their number will vary. I can build a string, as an option, with the necessary values, but how then to transfer it to a parameterized query? In the code itself, I apply all requests to the datagridview element via the tableAdapter: this.запрос_продуктовTableAdapter.Fill(...);

ps I am writing in VS 15 on C #, the database has been connected through the datagridview element, the database itself is a local .mdb file, and I write requests through the query setup wizard.

    3 answers 3

    Try creating a parameter sheet and generate the query text:

      List<string> sqlParams = new List<string>(); sqlParams.Add("value1"); sqlParams.Add("value2"); sqlParams.Add("value3"); sqlParams.Add("value4"); sqlParams.Add("value5"); var sqlCommandText = string.Format("SELELT * FROM table WHERE name IN ({0})", string.Join(",", sqlParams)); MessageBox.Show(sqlCommandText);

    It is necessary to take into account the type of the parameter, for the string one it is necessary to add '- "' value1 '"

       SELECT * FROM table WHERE FIND_IN_SET(name, @parameter);

      Those. Parameter one, and in it some number of checked values ​​through a comma. The type of the @parameter parameter is string, quotation marks and other nonsense is not required, the check is performed for an exact match, i.e. all leading and trailing spaces are significant.

      Ps. If a comma is possible in the name field, the method is not applicable. Quoting is not working here.

      • is this a mysql construct? - Igor
      • Complains about undefined function FIND_IN_SET - Code
      • Well, if the author takes care to indicate the dialect of the server ... and if not, then he is probably for at least some ... - Akina
      • Local server, local Access file with database, I have only been studying the sql link with c # for the second day - Code
      • For MS Access, instead of FIND_IN_SET() , the simplest INSTR() will work using the same method ... only as a separator you will have to choose a character that is guaranteed not to be found in the data and in the search string. Let's say. CHR(0) . - Akina

      It is possible so:

       public static void Select(String connectionString, List<Int32> sqlParams) { using (var connection = new OleDbConnection(connectionString)) { using (var command = connection.CreateCommand()) { command.CommandText = string.Format($"SELELT * FROM table WHERE name IN (@sqlParams)"); command.Parameters.AddRange(new OleDbParameter[] { new OleDbParameter("@sqlParams", sqlParams) }); connection.Open(); command.ExecuteNonQuery(); } } }