Question Critique code for determining ACE or JET providers for OleDBConnection string

[kageboshi]

Hi all. I just finished this piece of code and wanted some thoughts on it. Like if there are ways to do this more effectively than how I have it.

The whole idea is that if this app is used on a computer without ACE then determine if Jet is installed. Let me know what you think. I have searched these forums and google for ways of doing this but not much luck.

        'determine if ACE or Jet data provider is installed
        Dim aceExists As String = My.Computer.FileSystem.SpecialDirectories.ProgramFiles & _
                                    "\Common Files\Microsoft Shared\OFFICE12\ACECORE.dll"
        Dim jetExists As String = Environment.SystemDirectory & "\msjet40.dll"

        If File.Exists(aceExists) Then 'If ACE provider is installed
            'fExt contains the file extension of the file the user select
            If fExt = "xlsx" Then
                strConnection = New OleDbConnection("Provider='Microsoft.ACE.OLEDB.12.0'; Data Source='" & pathandFile & " '; " & "Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";")
            ElseIf fExt = "xls" Then
                strConnection = New OleDbConnection("Provider='Microsoft.ACE.OLEDB.12.0'; Data Source='" & pathandFile & " '; " & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";")
            End If
        ElseIf File.Exists(jetExists) Then 'If ACE is not found, determine if Jet is installed
            If fExt = "xls" Then
                strConnection = New OleDbConnection("Provider='Microsoft.JET.OLEDB.4.0'; Data Source='" & pathandFile & " '; " & "Extended Properties='Excel 8.0;HDR=No;IMEX=1';")
            ElseIf fExt = "xlsx" Then
                Dim er As String = "Can not read data from xlsx file format. Please install 2007 Office System Driver."
                Dim dlgRes As DialogResult
                dlgRes = MessageBox.Show(er, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                If Windows.Forms.DialogResult.OK Then
                    Me.fileImport() 'File open dialog box
                End If
            End If
        Else
            Dim er As String = "Could not find either Jet or Ace data providers. Please install 2007 Office System Driver."
            Dim dlgRes As DialogResult
            dlgRes = MessageBox.Show(er, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Close()
        End If

 

[cjard]

Looks workable, bit messy but it's probably similar to how i'd do it:

'setup code
Private Dictionary(Of String, String) conStrs = new Dictionary(Of String, String)

Sub New()
  Dim pathToAceDll As String = ...
  If File.Exists(pathToAceDll)
    conStrs("xlsxace") = "Provider='Microsoft.ACE.OLEDB.12.0'; Data Source='{0}'; " & "Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";"
    conStrs("xlsace") = "Provider='Microsoft.ACE.OLEDB.12.0'; Data Source='{0}'; " & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";"
  End If

  Dim pathToJetDll as String = ...
  If File.Exists(pathToJetDll)
    conStrs("xlsjet") = "Provider='Microsoft.JET.OLEDB.4.0'; Data Source='{0}'; " & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";"
  End If

End Sub()


Function GetConnection(filePath as String) as OleDbConnection
  Dim conStr as String
  Dim filExt as String = Path.GetExtenstion(filePath)
  If conStrs.ContainsKey(filExt & "ace") Then
    conStr = conStrs(filExt & "ace")
  Else If conStrs.ContainsKey(filExt & "jet") Then
    conStr = conStrs(filExt & "jet")
  Else
    Throw Exception("Machine doesnt have the necessary drivers")
  End If

  Return New OleDbConnection(string.Format(conStr, filePath))
End If

Is how i'd do it

 

[kageboshi]

Yeah I've realized that and tried to clean it up a bit since my first post...I'm new to vb.net, most of my experience is in C++, so I am unfamiliar with vb.net specific optimization and features.
I tried the data provider factory but it didn't list ACE or JET. Just Oledb, odbc, sql, etc.

With the file extension I removed the fExt global variable and just used the pathandFile global variable with the string function ends with

If pathandFile.EndsWith("xlsx") Then
...
ElseIf pathandFile.EndsWith("xls") Then

Is there any significant performance difference between using string.endswith() and declaring a variable to be assigned a file extension? I mean, I know with modern processors, cache, bus, and ram speeds memory performance isn't too central with something as trivial as determining a file extension, but which one would be optimal?

"Provider='Microsoft.ACE.OLEDB.12.0'; Data Source='{0}';

what is the '{0}' for? I've come across it several times but never really looked into it...of course it appears to be a placeholder for a variable...


Interesting. So I can replace the dialog with a "Throw Exception?" I'll have to check that out. Thanks.

 

[cjard]

I tried the data provider factory but it didn't list ACE or JET. Just Oledb, odbc, sql, etc.

I think the DPF would enumerate all the data providers for a given library and selec tone suitable for reading the file concerned, but I've never used it so I persisted with your notion of finding the DLLs. Not very futureproof though

With the file extension I removed the fExt global variable and just used the pathandFile global variable with the string function ends with

It's not a global variable

If pathandFile.EndsWith("xlsx") Then
...
ElseIf pathandFile.EndsWith("xls") Then

Youre assuming that the extension of a file is found at the end of the name. If working with file extensions, I prefer to use the built in functions to get a file extension. Maybe one day I'll be coding on a system that supports .net but keeps extensions elsewhere than the end of the name of the file. Additionally, your code will attempt to process a file with the name "hellotherexls" -> Just because the last 3 characters of a string are xls, doesnt mean that the file has an xls extension

Is there any significant performance difference between using string.endswith() and declaring a variable to be assigned a file extension?

I think youre getting confused over why I did what I did. Path.GetExtension returns the extension of the specified file. I don't care how it does it, I just want the extension. This is different to your asking if a particular string ends with some characters

I mean, I know with modern processors, cache, bus, and ram speeds memory performance isn't too central with something as trivial as determining a file extension, but which one would be optimal?

You are spending more time worrying about something than can ever be saved regardless of which is optimal. If it takes a microsecond difference between two methods and you spend 5 minutes worrying about it, the operation must be performed 300 million times before it's taken the amount of time you spent worrying about it

what is the '{0}' for? I've come across it several times but never really looked into it...of course it appears to be a placeholder for a variable...

String.Format Method (System)

Interesting. So I can replace the dialog with a "Throw Exception?" I'll have to check that out. Thanks.

No. You're a C++ programmer; you know what an exception is. It aborts processing of the current method and returns immediately to the caller, unwinding the stack until it finds a handler. The handler is in higher level UI code, and catches the exception, and shows a dialog saying that no suitable drivers were found

 

[kageboshi]

Ah I see. Thank you.

I think the DPF would enumerate all the data providers for a given library and selec tone suitable for reading the file concerned, but I've never used it so I persisted with your notion of finding the DLLs. Not very futureproof though

So far it works with XP and Vista business. I had at first looked into reading the registry, but Vista can be a real pain in regards to security permissions, and this program is going to be run on multiple computers in a Windows network domain with user accounts defined in Active Directory.


Clarification: fExt was a global variable for my code that contained the file extension.

The file will either be xls or xlsx

         Dim openFD As New OpenFileDialog
        'Open file dialog box and show only xls or xlsx files
        openFD.Filter = "Excel Files (*.xls, *.xlsx)|*.xls;*.xlsx"
        openFD.Title = "Open an Excel File"

That is the only way for the user to select the file to be used; but I do understand the reasoning to use GetExtension() over EndsWith(). Thanks.