CODEWITHSUNDEEP
asp.netvb.net
mlg74
mlg74

Reputation: 520

Delete file from server based on database query asp.net vb

I have a file folder that contains pdf files. The sql database is not directly related to the files. For example, I have "Invoice_co_355_24636.pdf" and "Invoice_co_355_25127.pdf" in the Invoices folder. I query the database and find that Invoice "24636" is not paid and "25127" is marked paid in full, so I would like to delete "Invoice_co_355_25127.pdf" at that point since it is paid.

So what I'm doing is getting all the file names from the folder, parsing each file to get just the last numbers, (which correlate to the database). If the database shows that one or more of the Invoices has been paid, I would like to delete the file.

I have successfully, (below), been able to parse the server file names as

"InvNo" as the parsed file name, (which corelates to the database), and

"InvNoFull", which is the full database file name that needs to be deleted if it is marked as paid in the database.

But after getting the file names and the parsed file names, I do not know how to actually compare it to the database and then delete. Any help is appreciated.

     Dim files() As String = Directory.GetFiles(Server.MapPath("/Contents/Invoices/" + Variable.ToString() + "/Co/" + ddlCo.SelectedValue.ToString() + "/"))
        For Each file As String In files
            Dim InvNo As String = Path.GetFileNameWithoutExtension(file)
            Dim InvNoFull As String = Path.GetFileName(file)
            InvNo = InvNo.Substring(InvNo.LastIndexOf("_") + 1, InvNo.Length - InvNo.LastIndexOf("_") - 1)

            Dim CnStr As String = (ConfigurationManager.ConnectionStrings("ClientConnectionString").ConnectionString)
            Dim adp As SqlDataAdapter = New SqlDataAdapter("select OrderBilling.OrderId from orderBilling Left Outer Join Orders on OrderBilling.OrderId = Orders.OrderId Where Orders.CompanyId = " & ddlCo.SelectedValue.ToString() & " And Orders.OwnerId = " & Variable.ToString() & " And OrderBilling.PaidInFull = 'False'", CnStr)
            Dim ds As DataSet = New DataSet()
            adp.Fill(ds, "outBill")

            For Each Row As DataRow In ds.Tables(0).Rows
                For Each Coll As DataColumn In ds.Tables(0).Columns
                    Dim s As String = Row(Coll.ColumnName).ToString()
                    If s <> InvNo Then
                        Dim FileToDelete() As String
                        FileToDelete = Directory.GetFiles(Server.MapPath("/Contents/Invoices/" + Variable.ToString() + "/Co/" + ddlCo.SelectedValue.ToString() + "/" + InvNoFull))
                        If System.IO.File.Exists(FileToDelete.ToString()) = True Then
                            System.IO.File.Delete(FileToDelete.ToString())
                            'MsgBox("File Deleted")

                        End If
                    End If
                Next
            Next
        Next

With help from Mary but this deletes all the files in the folder, but I want it to delete only the files not returned in the database query:

Private Sub DeletePaidInvoices(OwnerID2 As String, CompanyID As String)
    Dim InvoicePath = "/Contents/Invoices/" & OwnerID2 & "/Co/" & CompanyID & "/"
    Dim files() As String = Directory.GetFiles(Server.MapPath(InvoicePath))
    Dim lst = GetInvoiceInfo(CInt(CompanyID), CInt(OwnerID2))
    For Each file As String In files
        Dim InvNo As String = Path.GetFileNameWithoutExtension(file)
        Dim InvNoFull As String = Path.GetFileName(file)
        InvNo = InvNo.Substring(InvNo.LastIndexOf("_") + 1, InvNo.Length - InvNo.LastIndexOf("_") - 1)
        'Debug.Print(InvNo) 'To check your substring, will not be in release version
        For Each i As Integer In lst
            Dim s As String = i.ToString()
           
            If s <> InvNo Then
                Dim FileToDelete As String
                FileToDelete = "Invoice_co_" & CompanyID & "_" & InvNo & ".pdf"
                If System.IO.File.Exists(Server.MapPath(InvoicePath & FileToDelete.ToString())) = True Then
                    System.IO.File.Delete(Server.MapPath(InvoicePath & FileToDelete.ToString()))

                End If

            End If
        Next
    Next

End Sub

Upvotes: 0

Views: 212

Answers (1)

Mary
Mary

Reputation: 15091

Call your delete method from, say, a button passing the variable from the user interface.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim CompID = ddlCo.SelectedValue.ToString()
    Dim OwnerID = "comes from somewhere"
    DeletePaidInvoices(OwnerID, CompID)
End Sub

The delete method gets the file list. I have no idea about Server.MapPath but I assume you do. Next we get a List(Of Integer) representing the results of you database query (the OrderID's). Next we enter the For loop. I added a Debug line to see what is being returned by SubString code. As you can see it is simpler code to loop through a list.

Private Sub DeletePaidInvoices(OwnerID As String, CompanyID As String)
    Dim InvoicePath = $"/Contents/Invoices/{OwnerID}/Co/{CompanyID}/"
    Dim files() As String = Directory.GetFiles(Server.MapPath(InvoicePath))
    Dim lst = GetInvoiceInfo(CInt(CompanyID), CInt(OwnerID))
    For Each file As String In files
        Dim InvNo As String = Path.GetFileNameWithoutExtension(file)
        Dim InvNoFull As String = Path.GetFileName(file)
        InvNo = InvNo.Substring(InvNo.LastIndexOf("_") + 1, InvNo.Length - InvNo.LastIndexOf("_") - 1)
        Debug.Print(InvNo) 'To check your substring, will not be in release version
        For Each i As Integer In lst
            Dim s As String = i.ToString()
            If s <> InvNo Then
                Dim FileToDelete() As String
                FileToDelete = Directory.GetFiles(Server.MapPath(InvoicePath & InvNoFull))
                If System.IO.File.Exists(FileToDelete.ToString()) = True Then
                    System.IO.File.Delete(FileToDelete.ToString())
                    'MsgBox("File Deleted")
                End If
            End If
        Next
    Next
End Sub

The data retrieval is in a separate function. Use Using blocks to make sure your connections and commands are closed and disposed. Always use parameters with Sql Server. There is a bit of Linq magic at the end to create the list from the DataTable.

Private Function GetInvoiceInfo(CompanyID As Integer, OwnerID As Integer) As List(Of Integer)
    Dim dt As New DataTable
    Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("ClientConnectionString").ConnectionString),
            cmd As New SqlCommand("select OrderBilling.OrderId 
                                        from orderBilling 
                                        Left Outer Join Orders on OrderBilling.OrderId = Orders.OrderId 
                                        Where Orders.CompanyId = @CompanyID 
                                        And Orders.OwnerId = @OwnerID 
                                        And OrderBilling.PaidInFull = @Paid;", cn)
        cmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = CompanyID
        cmd.Parameters.Add("@OwnerID", SqlDbType.Int).Value = OwnerID
        cmd.Parameters.Add("@Paid", SqlDbType.Bit).Value = False
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    Dim lstOrderID As List(Of Integer) = (From dRow In dt.AsEnumerable() Select dRow.Field(Of Integer)(0)).ToList
    Return lstOrderID
End Function

The actual deleting of the files (or moving to a Paid folder) is up to you.

Upvotes: 2

Related Questions