none
Read and delete based on file extension created in database from zip file using c# RRS feed

  • Question

  • hello everyone,

    right now i am developing desktop application which remove specific file based on file extension in zip file. all type of file extension i create in database.

    the problem is i want the file extension to be read from database. The code i write does not read from the database.

    so how the code should be write for delete specific file that can read the file extension(example : ".txt") from database?

    this is my code.

    con.Open();
    com.Connection = con;
    com.CommandText = "SELECT * from tbl_File_Extension";
    SqlDataReader dr = com.ExecuteReader();
    if(dr.Read())
    {
      using (var fileStream = new FileStream(txtFile.Text, FileMode.Open))
      {
        using (var archive = new ZipArchive(fileStream, ZipArchiveMode.Update))
        {
          int index = 0;
          while (index < archive.Entries.Count)
          {
            if (Path.GetExtension(archive.Entries[index].Name) == dr["FileExtension"].ToString())
            {
              archive.Entries[index].Delete();
            }
            else
            {
              index++;
            }
          }
        }
      }  
    }
    con.Close();

    Saturday, May 25, 2019 9:51 AM

All replies

  • Hello,

    • Using SELECT * is unwise, alway indicate the field names.
    • Currently you code is selecting the first record only to perform the delete action, you need a while(dr.Read()) to run through the table.
    • Going with the last bullet, this means using dr.GetString(x) where x is the ordinal position of the field FileExtension so if it's the first field e.g. SELECT FileExtension FROM tbl_file_Extension then use 0.

    Try the following, if something still does not work come back and explain in detail what happen rather than coming back and say "it didn't work".

    Also, when running through the code you can set a breakpoint and step through the code to see what it going on which can be very helpful.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 25, 2019 11:42 AM
    Moderator
  • hi,

    i already did as you teach me. i got some error which it tell me "System.IndexOutOfRangeException: 'Index was outside the bounds of the array.'"

    my database is look like this :


    and the code i fix it as you teach like this:

    con.Open();
    com.Connection = con;
    com.CommandText = "SELECT FileExtension FROM tbl_File_Extension";
    SqlDataReader dr = com.ExecuteReader();
    while(dr.Read())
    {
      using (var fileStream = new FileStream(txtFile.Text, FileMode.Open))
      {
        using (var archive = new ZipArchive(fileStream, ZipArchiveMode.Update))
        {
          int index = 0;
          while (index < archive.Entries.Count)
          {
            if (Path.GetExtension(archive.Entries[index].Name) == dr.GetString(1))
            {
              archive.Entries[index].Delete();
            }
          index++;
          }
        }
      }  
    }
    con.Close();

    The current ordinal position of the field FileExtension is 1.  so the dr.GetString(x) where x is 1. correct me if i wrong.

    Please tell me if the coding still incorrect.




    • Edited by alippp Saturday, May 25, 2019 3:58 PM
    Saturday, May 25, 2019 1:57 PM
  • You are wrong about the index,  below FileExtension is ordinal position 0 in the SELECT while in the database it's 1 which doesn't matter as we are solely interested in index in the SELECT statement.

    SELECT FileExtension FROM tbl_File_Extension


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 25, 2019 4:48 PM
    Moderator
  • i already do the correction for the code like this:

    con.Open();
    com.Connection = con;
    com.CommandText = "SELECT FileExtension FROM tbl_File_Extension";
    SqlDataReader dr = com.ExecuteReader();
    while(dr.Read())
    {
      using (var fileStream = new FileStream(txtFile.Text, FileMode.Open))
      {
        using (var archive = new ZipArchive(fileStream, ZipArchiveMode.Update))
        {
          int index = 0;
          while (index < archive.Entries.Count)
          {
            if (Path.GetExtension(archive.Entries[index].Name) == dr.GetString(0))
            {
              archive.Entries[index].Delete();
            }
          index++;
          }
        }
      }  
    }
    con.Close();

    The code run without error. but the type file that i wanted to delete as mention in database(picture from previous reply) does not delete. i try change the data in database for example: ".txt", .txt or txt. but nothing file with extension(.txt) is delete.

    Sunday, May 26, 2019 12:58 AM
  • Try deleting in reverse order:

    int index = archive.Entries.Count - 1;
    while (index >= 0 )
    {
        if (Path.GetExtension(archive.Entries[index].Name) == dr.GetString(0))
        {
          archive.Entries[index].Delete();
        }
        index--;
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, May 26, 2019 1:07 AM
    Moderator
  • Actually, I think what I just wrote (above) still won't solve all the problems. Maybe this?

    List<string> fileExtensions = new List<string>();
    while (dr.Read())
        fileExtensions.Add(dr.GetString(0));
    con.Close();
    
    using (var fileStream = new FileStream(txtFile.Text, FileMode.Open))
    {
        using (var archive = new ZipArchive(fileStream, ZipArchiveMode.Update))
        {
            int index = archive.Entries.Count - 1;
            while (index >= 0 )
            {
                if (fileExtensions.Contains(Path.GetExtension(archive.Entries[index].Name))
                {
                  archive.Entries[index].Delete();
                }
                index--;
            }
       }
    }  
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, May 26, 2019 1:24 AM
    Moderator
  • still same sir. the code you give me run normally without error. but the process delete not happen yet.

    i try to manipulate the code you give and database. The delete process still not happen.

    Sunday, May 26, 2019 2:34 AM
  • Try writing the following and inspect the Console.WriteLine in the IDE Output window.

    con.Open();
    com.Connection = con;
    com.CommandText = "SELECT FileExtension FROM tbl_File_Extension";
    SqlDataReader dr = com.ExecuteReader();
    while(dr.Read())
    {
      using (var fileStream = new FileStream(txtFile.Text, FileMode.Open))
      {
        using (var archive = new ZipArchive(fileStream, ZipArchiveMode.Update))
        {
          int index = 0;
          while (index < archive.Entries.Count)
          {
            Console.WriteLine($"{Path.GetExtension(archive.Entries[index].Name)}  - {dr.GetString(0)}")
            if (Path.GetExtension(archive.Entries[index].Name) == dr.GetString(0))
            {
              archive.Entries[index].Delete();
            }
          index++;
          }
        }
      }  
    }
    con.Close();
    In the output, do the two values appear as you would expect or not? This can determine if the comparison is at fault or the method used to delete an entry is at fault.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, May 26, 2019 2:48 AM
    Moderator
  • here my output and some comparison with database code and without database code.

    i just change my FileExtension entries from (.1cbu1, .1txt, .txt) to (.txt, .1txt, .txt1).

    picture with database code:

    picture without database code:

    so basically, the output for both database code and without database code is same. now i am confuse.

    picture without database code is what i expected. the output show (.txt) was delete after 2nd loop. 
    • Edited by alippp Sunday, May 26, 2019 4:51 AM
    Sunday, May 26, 2019 4:47 AM
  • Hi alippp,

    Thank you for posting here.

    Based on your description, you want to remove specific file based on file extension in zip file. 

    You could try the following code, the bold code is modified.

                string con = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=mlhelper;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                this.textBox1.Text ="D:\\temp\\t.zip";
                using (SqlConnection connection = new SqlConnection(con))
                {
                    connection.Open();
                    string sql = "select * from Extension";
                    using (SqlCommand cmd = new SqlCommand(sql, connection))
                    {
                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            using (var fileStream = new FileStream(textBox1.Text, FileMode.Open))
                            {
                                using (var archive = new ZipArchive(fileStream, ZipArchiveMode.Update))
                                {
                                    foreach (var item in archive.Entries.ToList())
                                    {
                                        if (item.Name.EndsWith(dr.GetString(1)))
                                        {
                                            item.Delete();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                MessageBox.Show("success");

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, May 27, 2019 6:31 AM
    Moderator
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 29, 2019 7:18 AM
    Moderator