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?
Add a comment
|
3 Answers
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) + ")";
5 Comments
Zack_074
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().
mellamokb
Try
String.Join(",", myList.ToArray())Zack_074
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?
mellamokb
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.
Zack_074
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?
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});
1 Comment
Zack_074
I'm using .NET connector, can you accommodate your code accordingly?
delete from employee where employeeId in( 2,4,3,2,34 )
1 Comment
Zack_074
I'm using a list instead of those numbers