Open FoxPro Table in VB.net 2005

Question

I need to open foxpro free tables in vb.net using the oledb connection.

But... i only need to get the column names. I don't really need to 'select' anything. I am trying to dynamically browse through all our free tables and set up a listing of every column from every file and xref that to another free table that contains a description of each column.

I have a working model now, but it requires that I do...

SELECT TOP 1 FROM "File" ORDER BY 1

But on the largest table, it takes over two minutes just to read in the first record and there are over 250 tables. Overall, it takes between 15 and 20 minutes.

Or, is there another way to only get the first record of the table without using 'ORDER BY'?

Here's what I have so far. "File" is passed in as a parameter.
It would contain info like "C:\data\table1.dbf"

Dim filePath As String
filePath = IO.Path.GetDirectoryName(file)
myOledbConnection = New OleDbConnection("Provider=VFPOLEDB.1;Data Source=" & filePath & ";Collating Sequence=MACHINE")
myOledbCommand = New OleDbCommand
myOledbDataAdapter = New OleDbDataAdapter
Dim fields, from, order As String

fields = "select top 1 *"
from = " from " & file
order = " order by 1"

myOledbCommand.CommandText = fields & from & order
myOledbCommand.Connection = myOledbConnection

myOledbDataAdapter.SelectCommand = myOledbCommand
myOledbDataAdapter.Fill(dt)

I then take the datatable (dt) and loop through to get the column information.

I would like it to be as quick as Visual Studio is when I create a dataset and load all tables from the directory through the wizard. It is able to very quickly find all the column information without reading in the data from the table.

Let me know if you need more information.

Thanks.

Answer 1

Why do you need to get any records at all? You should be able to say:

SELECT * FROM "File" where 1 = 0

This will give you an empty result set, and should also give you column data.

You might also want to look into the GetOleDbSchemaTable method on the OleDbConnection class, as it will allow you to get information about the schema of the database without having to perform a query.

You can also use the Microsoft ADO Extensions for DLL and Security through COM interop (mxADOX.dll) to get the schema information as well.

Answer 2

I have not tried this/. But, it looks like the way to go.

Specifically the "GetSchema" method on OleDbConnection instance. http://msdn.microsoft.com/en-us/library/ms254934(VS.80).aspx