Accessing FoxPro Database using OleDB

[Glenn Wilson]

Hi,

I am currently working on a project in which I need to interrogate a FoxPro
database and present some results from the database in a web browser. I am
using VS.NET 2003, Framework 1.1. I build a Select statement and then use
the Select statement to query a table in the FoxPro database. The problem
is, the query is taking a very long time to run from my .NET code. Running
the same query from inside Visual Fox Pro is 20 times faster. Does anyone
know what I can do from my .NET code to make the query much faster? The
query string I use follows this pattern:

SELECT * FROM myTable WHERE UPPER(myField) = myValue

Thanks in advance

Glenn

 

[Paul Clement]

¤ Hi,
¤
¤ I am currently working on a project in which I need to interrogate a FoxPro
¤ database and present some results from the database in a web browser. I am
¤ using VS.NET 2003, Framework 1.1. I build a Select statement and then use
¤ the Select statement to query a table in the FoxPro database. The problem
¤ is, the query is taking a very long time to run from my .NET code. Running
¤ the same query from inside Visual Fox Pro is 20 times faster. Does anyone
¤ know what I can do from my .NET code to make the query much faster? The
¤ query string I use follows this pattern:
¤
¤ SELECT * FROM myTable WHERE UPPER(myField) = myValue

Are you using an OLEDB or ODBC DataReader to do this?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Glenn Wilson]

Hi Paul,

I am using OLEDB. The line that runs really slow is
"adpFoxPro.Fill(dtFoxPro)":

Dim adpFoxPro As New OleDb.OleDbDataAdapter(m_strQueryText, m_strConnection)
Dim dtFoxPro As New DataTable
Try
adpFoxPro.Fill(dtFoxPro)
Return dtFoxPro
Catch ex As Exception
Throw New FoxProDBException("FoxProDB Exception " & _
"- Unable to run query. Error: " & ex.Message)
End Try

I am sure the problem lies with the way that indexing is used in FoxPro, but
I have no idea how to work with Indexes in FoxPro when creating SQL queries
using .NET.

Glenn

 

[Paul Clement]

¤ Hi Paul,
¤
¤ I am using OLEDB. The line that runs really slow is
¤ "adpFoxPro.Fill(dtFoxPro)":
¤
¤ Dim adpFoxPro As New OleDb.OleDbDataAdapter(m_strQueryText, m_strConnection)
¤ Dim dtFoxPro As New DataTable
¤ Try
¤ adpFoxPro.Fill(dtFoxPro)
¤ Return dtFoxPro
¤ Catch ex As Exception
¤ Throw New FoxProDBException("FoxProDB Exception " & _
¤ "- Unable to run query. Error: " & ex.Message)
¤ End Try
¤
¤ I am sure the problem lies with the way that indexing is used in FoxPro, but
¤ I have no idea how to work with Indexes in FoxPro when creating SQL queries
¤ using .NET.

I guess what I was wondering was whether you actually had to use a DataTable/DataSet (such as for
filling a DataGrid control) or whether you could use a DataReader, which is
forward-read-only/server-side. It should improve performance.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Bernie Yaeger]

Hi Glenn,

In addition to the advice Paul has offered, a few ideas:
1. can you 'narrow' the query (call only the cols you need)
2. can you 'shorten' the query with the most efficient 'where' clause

You might also want to try to MS VFP driver for .net - it's available at
msdn.microsoft.com.

HTH,

Bernie Yaeger