我有一个包含pdf文件的文件夹。sql数据库与文件不直接相关。例如，我在“发票”文件夹中有“ Invoice_co_355_24636.pdf”和“ Invoice_co_355_25127.pdf”。我查询数据库，发现发票“ 24636”未付款，而“ 25127”标记为已全额付款，因此由于要付款，因此我想删除“ Invoice_co_355_25127.pdf”。
因此，我要做的是从文件夹中获取所有文件名，解析每个文件以获取最后一个数字（与数据库相关）。如果数据库显示已支付一张或多张发票，我想删除该文件。
我已经成功地（如下）将服务器文件名解析为
“ InvNo”作为已解析的文件名（与数据库相对应），以及
“ InvNoFull”，这是完整的数据库文件名，如果在数据库中标记为已付费，则需要删除该文件名。
但是在获得文件名和已分析的文件名之后，我不知道如何将其与数据库进行实际比较然后删除。任何帮助表示赞赏。
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
在Mary的帮助下，但这会删除文件夹中的所有文件，但是我希望它仅删除数据库查询中未返回的文件：
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
例如，从通过用户界面传递变量的按钮中调用您的delete方法。
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
delete方法获取文件列表。我不知道Server.MapPath，但我想您知道。接下来，我们得到一个List（Of Integer），它表示您的数据库查询结果（OrderID的）。接下来我们进入For循环。我添加了Debug一行，以查看SubString代码返回的内容。如您所见，遍历列表是更简单的代码。
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
数据检索是一个单独的功能。使用Using块来确保关闭并处置您的连接和命令。始终在Sql Server中使用参数。最后有一点Linq魔术可以从列表创建列表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
文件的实际删除（或移至付费文件夹）由您决定。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句