c# - Deleting Multiple Rows from an Access Database -

i want delete multiple records access database using array. array loaded dynamically file names.

then query database, , see if database column values matching array values, if not delete it, if matches not delete it.

the problem that: following code deletes records irrespective of in condition.

arrays = directory.getfiles(sdira, "*", searchoption.alldirectories).select(x => path.getfilename(x)).toarray();     fnames.addrange(arrays);       here have use loop didnt me out :( for(int u = 0; u < arrays.length; u++) { oledbcommand sqlcmd = new oledbcommand ("delete table1 name not in ("'+arrays[u]+"')",sqlconnection);    using 1 foreach(string name in arrays)    {        oledbcommand sqlcmd = new oledbcommand("delete table1 name not in ('" + name + "')", sqlconnection);        sqlcmd.executenonquery();                                  }` 

one problem code confusing.

string [] = {"" 'a.jpg', 'b.jpg', 'c.jpg' "} 

first, have double " in beginning,should one.

string [] = {" 'a.jpg', 'b.jpg', 'c.jpg' "} 

then created string array 1 element,

a[0] = "'a.jpg', 'b.jpg', 'c.jpg'"; 

then foreach on natuarly ony executes once resulting in query:

delete table1     name not in ('a.jpg', 'b.jpg', 'c.jpg') 

but when load array dynamically array

a[0] = 'a.jpg'; a[1] = 'b.jpg'; a[1] = 'c.jpg'; 

which execute 3 times in foreach resulting in following 3 queries

delete table1     name not in ('a.jpg') delete table1     name not in ('b.jpg') delete table1     name not in ('c.jpg') 

after second 1 table empty.

you should try instead:

string[] names = { "a.jpg", "b.jpg","c.jpg","j.jpg" }; string allnames = "'" + string.join("','", names) + "'";  oledbcommand sqlcmd = new oledbcommand("delete table1  name not in (" + allnames + ")", sqlconnection);  sqlcmd.executenonquery();  

where names created dynamically ofcause , result in following query matching test:

delete table1     name not in ('a.jpg', 'b.jpg', 'c.jpg') 

my preferred way dynamically fill array use list instead pure array fixed in size , change needs create new array.

you can loop on list eacy array.

list<string> names = new list<string>(); //or user var keyword var names = new list<string>(); 

then use add method add elements, loop needed.

names.add(filename); 

then concatenation:

string allnames = "'" + string.join("','", names.toarray()) + "'"; 

and done.

or use

string[] filepaths = directory.getfiles(@"c:\mydir\", "*.jpg"); string[] names = filepaths.tolist().convertall(n => n.substring(n.lastindexof(@"\") + 1)).toarray();