4

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.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
dna123
  • 268
  • 3
  • 12

2 Answers2

6

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, will also give you metadata on the projection returned.

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 Data Definition Language and Security through COM interop (mxADOX.dll) to get the schema information as well.

casperOne
  • 73,706
  • 19
  • 184
  • 253
  • Thanks. The 'where 1 = 0' did it. I didn't know about that. Sucker ran faaaast now! I'm going to look into that schema method as well. – dna123 Jan 07 '09 at 21:18
  • Just an update that the getSchema isn't what I need. But the 'Where 1 = 0' is. Thanks a bunch! – dna123 Jan 07 '09 at 22:05
1

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

shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • Thanks for the link. I'll dig into this a little to see if it works. I'll update later with the results. – dna123 Jan 07 '09 at 21:19
  • Using GetSchema returns the following. (I didn't clear out any of the data) TABLE_CATALOG = '' TABLE_SCHEMA = '' TABLE_NAME = 'msg_seq' TABLE_TYPE = 'TABLE' TABLE_GUID = '' DESCRIPTION = '' TABLE_PROPID = '' DATE_CREATED = '12/22/2008 10:51:42 AM' DATE_MODIFIED = '11/12/2008 9:23:36 AM' – dna123 Jan 07 '09 at 22:00
  • Sorry the above is ugly. Those are single quotes after the = sign. If I did the getschema command correctly, it won't work for what I want. Thanks for your suggestion anyway!! – dna123 Jan 07 '09 at 22:01