Closing an OLE DB connection opened from within a function

Question

Backed myself in a corner....

Used a piece of code I found on the web and can't figure out how to close this connection. The returned OleDbcommand objCommand remains open after processing. I need to it close so I can delete the file after I have pulled the data from it. (Don't want them hanging around on the server.)

This has to be easier than the 100 of lines of code that I have tried to do this. This function opens the connection.

Protected Function ExcelConnection() As OleDbCommand
    Dim fileName As String = Session("newUploadedFile")

    ' Connect to the Excel Spreadsheet
    Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & Server.MapPath(String.Format("~/Upload/{0}", fileName)) & ";" & _
          "Extended Properties=Excel 8.0;"

    ' create your excel connection object using the connection string
    Dim objXConn As New OleDbConnection(xConnStr)
    objXConn.Open()
    ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
    ' the "table name" is the name of the worksheet within the spreadsheet
    ' in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$]

    Dim objCommand As New OleDbCommand("SELECT Name FROM [Sheet1$]", objXConn)

    Return objCommand
End Function

I have tried...

  ExcelConnection.connection.close()

along with about 40 other attempts to recreate the Command and then close it.

Could really use some help on this one.

Why do you need to return a command object? Use `Using` statement for all objects that implement `IDisposable` (like the connection or the command) and don't return the command but the data you want to have (f.e. as `DataTable` or `List(Of CustomClass)`). — Tim Schmelter, Jan 15 '13 at 21:51
Its called from multiple locations. Only became an issue when I started coding to delete the speadsheet file. Makes perfect sense, I'll look into the update,not sure why the web example didn't do it that way. — htm11h, Jan 15 '13 at 21:53

score 1 · Accepted Answer · edited May 23 '17 at 10:24

This is probably not the best way to do this, but if you really must do it this way consider defining and opening the connection in the calling routine and passing it into this routine as a parameter. It can then be closed in the calling routing, thus...

Sub Main()

    Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & Server.MapPath(String.Format("~/Upload/{0}", fileName)) & ";" & _
        "Extended Properties=Excel 8.0;"

    Dim objXConn As New OleDbConnection(xConnStr)
    objXConn.Open()
    Dim ObjCommand As New OleDbCommand = ExcelConnection(objXConn)

    'Whatever other operations you want to do with your returned OleDbCommand
    ...

    objXConn.Close()

End Sub

Function ExcelConnection(PassedConnection As OleDbConnection) As OleDbCommand
    Dim fileName As String = Session("newUploadedFile")
    Dim objCommand As New OleDbCommand("SELECT Name FROM [Sheet1$]", PassedConnection)
    Return objCommand
End Function

I posted something similar to this here... Best fastest way to read an Excel sheet

Closing an OLE DB connection opened from within a function

1 Answers1