2

I'm trying to delete a series of rows in a MySQL table from a list of ID's in C#. There's an employeeID row in the table. Basically my question is what kind of syntax would I use?

Zack_074
  • 157
  • 1
  • 5
  • 12

3 Answers3

5

You would probably use an IN clause in your DELETE:

DELETE FROM `EmployeeTable` WHERE EmployeeID IN (2, 3, 4, 5, ...)

This could be implemented with the String.Join method to generate the list:

var query = "DELETE FROM `EmployeeTable` WHERE EmployeeID IN (" +
    String.Join(",", myArray) + ")";
mellamokb
  • 56,094
  • 12
  • 110
  • 136
  • Yes, I'm using the IN clause, but I'm using a list rather than an array. It's not working when I use myList.ToString(). – Zack_074 Apr 20 '12 at 02:47
  • Try `String.Join(",", myList.ToArray())` – mellamokb Apr 20 '12 at 02:51
  • Huh. Apparently it's not a list now that I look at it. It's a DataGridRowViewSelectedRowCollection. I'm using a datagridrowview to display the contents of the MySQL table. How would it work with that? – Zack_074 Apr 20 '12 at 03:14
  • You may just have to loop through them and populate a list/array yourself. Not sure how to get the ID from that, may have to reference docs. – mellamokb Apr 20 '12 at 03:16
  • It will allow me once I have the index, like say if I were to use a for loop, but that's not possible in a string. Would it just be more efficient to do a foreach statement and make separate queries? – Zack_074 Apr 20 '12 at 20:51
4

If you are using Dapper it would look something like this:

int[] ids = new int[]{1,2,3};
DbConnection cn = ... //open connection here
cn.Execute("delete from Employee where employeeID in @ids", new {ids});
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
-1

delete from employee where employeeId in( 2,4,3,2,34 )

Orn Kristjansson
  • 3,435
  • 4
  • 26
  • 40