Get file ext. from database

jds1207

I am working on a vb.net program that will search the directory for files. Currently, I search for files with a specific ext by entering the ext in textbox. Now, I want to be able to search the directory for the ext from a database tbl instead of entering the extensions into a textbox. I have a tbl in access called tblRestrFileExtension which has the following columns: ExtID (AutoNumber) ExtType: .wmv;.wav;.mp3;.exe Here is my project code: Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click lblStatus.Text = "Scanning Please Wait....." Application.DoEvents() Dim enteredFilePaths As String() = My.Settings.Path.Split(New String() {";"}, StringSplitOptions.RemoveEmptyEntries) Dim patterns As String() = TextBox2.Text.Split(New String() {";"}, StringSplitOptions.RemoveEmptyEntries) 'Declare a string array to store the file paths that are not found in the database Dim dv As DataView = dt.DefaultView Dim i As Integer For Each path As String In enteredFilePaths Dim strFilter As String = "ExcludePath LIKE '" & path & "%' AND (ExcludeFileName LIKE '%" & patterns(0) & "'" For int As Integer = 1 To patterns.GetUpperBound(0) strFilter += " OR ExcludeFileName LIKE '%" & patterns(int) & "'" Next strFilter += ")" dv.RowFilter = strFilter dv.Sort = "ExcludePath ASC" For i = 0 To dv.Count - 1 Me.ListBox2.Items.Add(dv(i)("ExcludeFileName")) Next i Next Dim fileName As String patterns = Array.ConvertAll(patterns, New Converter(Of String, String)(AddressOf ConvertFilters)) ListBox1.BeginUpdate() ListBox1.Items.Clear() Dim RootDirs As New List(Of String) 'Check to see if there there are any Root Directory path in Textbox1 string For idx As Integer = 0 To enteredFilePaths.Length - 1 'Bypass the drive letter so that any drive can be used If enteredFilePaths(idx).Substring(1).Trim = ":\" Then 'Get all the root folders Dim RootFolders As New System.IO.DirectoryInfo(enteredFilePaths(idx)) 'Add all the foilers to the RootDirs array list For Each dir As System.IO.DirectoryInfo In RootFolders.GetDirectories() RootDirs.Add(dir.FullName) Next 'C

Dave Kreskowiak

OK. You've got a huge spaghetti problem here. For example, just look at the single line of code in your Form's Load event handler.

GetConnectionString()

Now, I'm all for self-documenting code and the name you chose for this method is pretty decent, but I would expect a method with that name to actually return a connection string. Your GetConnectionString method does everything BUT return a connection string! Why do you have a method with this name creating a DataTable, creating columns in it, opening a connection, executing an SQL command, filling that DataTable, and NOT returning a connection string? You have this problem throughout this bowl of noodles you call code and it's this very problem that's causing your issues now that you want to change one little piece of functionality. This stuff should be broken down into more methods, seperating and encapsulating functionality. You got the first method name correct, GetConnectionString. Now make it actually return a connection string!

Public Class DatabaseLayer
Private Shared Function GetConnectionString(ByVal filename As String) As String
' Build the full path to the specified database file and see if this
' file exists
Dim fullPath As String = Path.Combine(Application.StartupPath, filename)
If Not File.Exists(fullPath) Then
Throw New FileNotFoundException("Unable to locate the database file {0} in the application folder.")
End If

    ' Get the connection string template
    Dim connStringTemplate As String = My.Settings.ConnectionString

    ' Build and return the completed connection string
    Return String.Format(connStringTemplate, Path.Combine(Application.StartupPath, filename))
End Function

Private Shared Function CreateDatabaseConnection(ByVal filename As String) As OleDbConnection
    ' Get a connection string for the specified database file
    Dim connString As String = GetConnectionString(filename)

    ' Return an OleDbConnection object
    Return New OleDbConnect(connString)
End Function

Private Shared Function GetFileExcludeTableSelectCommand() As OleDbCommand
    Dim selectSql As String = "SELECT " & \_
            "ExcludeID, ExcludePath, ExcludeFileName FROM tblExclude"
    Return New OleDbCommand(selectSql)
End Function

Private Shared Function CreateFileExcludeTableDataAdapter(ByVal selectCommand As OleDbCommand, ByVal connect

jds1207

Ok, this is very helpful but it still does not solve the problem. Database Table ExtID (AutoNumber) ExtType: .wmv;.wav;.mp3;.exe Currently my program search for file extensions by entering the extension into a textbox, for example wav;mp3;exe. Whatever extension is typed into textbox the program will look for those extensions. How can I search for specific file extensions from the ExtType column in database istead of entering the ext in the textbox? I have the field where all of the extensions are in one column.

Dave Kreskowiak

jds1207 wrote:

this is very helpful but it still does not solve the problem.

Wanna bet?? The problem you're running into is that all of this code is attached to a button event handler. You have to break this code out so it's not dependant on the UI click events at all. The code needs to be refactored to seperate the functionality from that TextBox. The search function needs to be able to stand on its own and do it's job just by passing the extensions to a method to start the search. Once you seperate this search code from the UI, your problem is 95% solved.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007