#dataadapter

Buy Lightning Splitter Dongle

I wrote this post for those who have no idea about C# database connection. When starting with Visual Studio language like C#/VB.Net, you need to understand the concept of adapter, dataset and data view. SQL connection SQL connection class helps you to build the connection string.Visual studio can build connection string for you or  can create your in App config [Solution Explorer] Use SQL…

There may come times as a developer where, whether you like it or not, you will be forced to read data from Excel spreadsheets (XLS).  One (inefficient) method would be to use interop to create an instance of Excel and use COM methods to open the worksheet and read from it that way.  The code to do this would be something like the following:

        Dim XL As Object = CreateObject("excel.application")         Dim WB As Object = XL.workbooks.open(fileName)         Dim WS As Object = WB.worksheets(1)         '---------------Read worksheet/Do what you have to do---------------         WB.close(False)         XL.quit()         WB = Nothing         XL = Nothing

Avoid that if you can.  Read a spreadsheet like a database to avoid loading Excel.  To grab a worksheet and put it into a datatable do the following:

    Function loadWorksheet(ByVal fileName) As DataTable         Dim conn As String = "provider=microsoft.jet.oledb.4.0;data source=" & fileName & ";extended properties=""excel 8.0;imex=1"""         Dim DA As New OleDb.OleDbDataAdapter("select * from [sheet1$]", conn)         Dim DT As New DataTable         Try             DA.Fill(DT)             Return DT         Catch ex As Exception             'handle the error             Return Nothing         End Try     End Function

imex=1 means a column can contain mixed data types (numbers and text for example).  Without imex=1, your results will automatically determine the data type based on the majority of the first 8 rows in the spreadsheet.  If a majority of the first 8 cells are numbers, any text fields will return null/blank.  For more information on imex and extended properties see http://support.microsoft.com/kb/257819 or google "How To Use ADO with Excel Data from Visual Basic or VBA".