Delete multiple rows from an Access database

Question

I want to delete multiple entries from an access database using an array. The array is dynamically loaded from file names.

Then I query the database and see if the values ​​of the database column match the values ​​of the array, and then delete them if the matches are not deleted.

The problem is that: Below is the code that deletes all records no matter where in state.

arrays = Directory.GetFiles(sdira, "*", SearchOption.AllDirectories).Select(x => Path.GetFileName(x)).ToArray();
    fnames.AddRange(arrays); 

    here I have use also for loop but that also didnt help me out :( like for(int u = 0; u < arrays.length; u++) { oledbcommand sqlcmd = new oledbcommand ("delete from table1 where name not in ("'+arrays[u]+"')",sqlconnection);
   I am using this one currently foreach(string name in arrays)
   {
       OleDbCommand sqlcmd = new OleDbCommand("delete from table1 where name not in ('" + name + "')", sqlconnection);
       sqlcmd.ExecuteNonQuery();                                  }`

Answer 1

One problem is that your code is confusing.

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

First you have a double "in the beginning, there should be only one.

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

Then it created an array of strings with one element,

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

Then you do foreach on this, which natuarly ony does once, leading to this query:

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

, , ,

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

3 foreach,

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

.

:

string[] names = { "a.jpg", "b.jpg","c.jpg","j.jpg" };
string allNames = "'" + String.Join("','", names) + "'";

OleDbCommand sqlcmd = new OleDbCommand("delete from table1  where name not in (" + allNames + ")", sqlconnection); 
sqlcmd.ExecuteNonQuery(); 

- , , :

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

, , , .

eacy, .

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

add , .

names.Add(filename);

:

string allNames = "'" + String.Join("','", names.ToArray()) + "'";

.

string[] filePaths = Directory.GetFiles(@"c:\MyDir\", "*.jpg");
string[] names = filePaths.ToList().ConvertAll(n => n.Substring(n.LastIndexOf(@"\") + 1)).ToArray();

Answer 2

4 ,

string names = " 'a.jpg', 'b.jpg','c.jpg','j.jpg' ";

string[] names = { "a.jpg", "b.jpg","c.jpg","j.jpg" };

1, 4

: , , - :

    string name = " 'a.jpg', 'b.jpg','c.jpg','j.jpg' ";
    string[] names = name.Split(',').Select(x => x.Trim(' ').Trim('\'')).ToArray();

, , atm

, , , -

IEnumerable<string> filelocations = Directory.GetFiles(sourceDirectory, "*", SearchOption.AllDirectories).Select(x => Path.GetFileName(x));

string [] lok = Directory.GetFiles(sourceDirectory, "*", SearchOption.AllDirectories).Select(x => Path.GetFileName(x)).ToArray();

Answer 3

, , . , , , . , , .

Answer 4

:

string[] names = { "a.jpg", "b.jpg", "c.jpg", "j.jpg" };

:

" 'a.jpg', 'b.jpg','c.jpg','j.jpg' "

Answer 5

, . 'a.jpg'. , "b.jpg", "a.jpg". . : , , IN, , , .

, where, where . where :

where name not in ('a.jpg','b.jpg','c.jpg','d.jpg')

:

where name not in ('a.jpg')

...

where name not in ('b.jpg')

Also, remember that operations are INexpensive and the longer the array is faster, the faster the query grows.

Answer 6

Try using a list if you don't want to create a static-sized array

List<string> names = new List<string>();
names.Add("a.jpg");
names.Add("b.jpg");
names.Add("c.jpg");


foreach (string name in names)
{
   OleDbCommand sqlcmd = new OleDbCommand("delete from table1 
   where name not in (" + name + ")", 
   sqlconnection); 
   sqlcmd.ExecuteNonQuery(); 
}