I am still trying to wrap my head around good programming practice and just wrote this routine to read in an Excel file and return all the sheets into a dataset:Public Shared Function ReadExcelIntoDataSet(ByVal FileName As String, Optional ByVal TopRowHeaders As Boolean = False) As DataSet    Try        Dim retval As New DataSet        Dim strConnString As String = Provider=Microsoft.ACE.OLEDB.12.0;Data Source= & _                                        FileName & ;Extended Properties=Excel 12.0;IMEX=1; & _                                        HDR= & If(TopRowHeaders, Yes, No) & ;        Using oleExcelConnection = New OleDb.OleDbConnection(strConnString)            oleExcelConnection.Open()            Using oleExcelCommandString = New OleDb.OleDbCommand(, oleExcelConnection)                Using oleExcelAdapter = New OleDb.OleDbDataAdapter(oleExcelCommandString)                    Dim dtSchema As DataTable = oleExcelConnection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)                    For Each dr As DataRow In dtSchema.Rows                        Dim Sheetname As String = dr.Item(TABLE_NAME).ToString                        If Sheetname.EndsWith($) Then                            oleExcelCommandString.CommandText = Select * From [ & Sheetname & ]                            oleExcelAdapter.Fill(retval, Sheetname)                        End If                    Next                    Return retval                End Using            End Using        End Using    Catch ex As Exception        Dim ErrorData As String = Unable to Read in Data from the Excel File  & FileName & vbCrLf & _                                   Module: Utility > ReadExcelIntoDataSet        Throw New Exception(ErrorData & vbCrLf & ERROR Data: & vbCrLf & ex.Message)    End TryEnd FunctionMy first questions are:Is the use of the Using statements good or is it some kind of over-kill and should this maybe be handled in a Finally statement in stead?Is this the best method to read in an Excel File (Given that they can be either .xls or .xlsx) in the fastest possible way?What is the best way to pass the error up to the calling code? Did my Catch statement do that well enough??
Using Statement for OleDB - Is this overkill?
vb.net
The Using is good practice and not overkill. Trying to dispose the resources in the Finally part has the disadvantage, that you don't know when the exception happened and which resources have been assigned by that time. The Using statement does this automatically for you internally: If resouce <> Nothing Then resource.Dispose() End IfYou need different connection strings for the different Excel types. You can use this function:Private Function GetConnectionString(ByVal filename As String) As String    Const xlsConnString As String = Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;    Const xlsxConnString As String = Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;    If Path.GetExtension(filename).Length = 5 Then ' .xlsx, .xltx        Return String.Format(xlsxConnString, filename)    Else ' .xls, .xlt        Return String.Format(xlsConnString, filename)    End IfEnd FunctionYou can either propagate the exception or return Nothing. In my opinion, your version does it very well.