Foxpro database

[M.Waqas Aslam]

hello!
i am developing a simple application which will get data from different database like mssql , access , foxpro , and after getting data it will create 4 text files tows.txt , customer.txt , product.text , trasection.txt ,
prob is this that i am very new to foxpro , i dont know abc of it , and also the names of columns are very strange to me , like Id_t = invoice date , so my question is , how can i get full structure of foxpro db , or is it possible to easily import all the tables into mssql then just fetch data.

Regards

[TnTinMN]

FoxPro uses a variant of the old dBase(Click Here) file format. There is a program (free trial) called DBF Manager that works well with these file types.

You can find it here:
Click Here

[gusano79]

I occasionally work with .NET applications that connect to FoxPro databases; we use the OLE DB drivers.

As for the column names, that's a choice on the part of the database designer--though if I remember correctly, FoxPro column names can't be very long; that's why the cryptic abbreviations.

[M.Waqas Aslam]

thanks both of you , but still i am not able to connect my .dbf file with vb.net. i dont know how to use these drivers. please if possible then can you explain it.
i am using this code to connect , please check this

import system.data.odbc


'----------------------------------------
dim con as new odbcconnection("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=227;Dbq='C:\TITEM.dbf'")

' and this one

dim con as new odbcconnection("Driver={Microsoft Visual FocPro Driver};UID=;PWD;SourceDB='C:\TITEM.dbf';SourceType=DBF;Exclusive=NO;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes")

but both the strings are not working for me , can you please provide some example code ?

Best Regards

[taskman]

Setup an ODBC connection through the Control Panel - Administrative tools - ODBC. Make that work first. Then use that to replace the vast majority of what is in your odbcconectionstring. You have unnecessary elements in there that might be gumming things up, such as UID= and PWD with no parameter. If there is a .DBC file in the folder, that is your database container - connect to it. If not, they are probably just free tables with no database container. Your best bet is to successfully connect via ODBC first.

[gusano79]

http://www.connectionstrings.com/ is a useful resource if you're having trouble getting the connection strings right. Check out their FoxPro page.

[gusano79]

both the strings are not working for me

If you want specific help, give us specific problems; "not working" doesn't tell us much. Does the runtime throw an exception? Any error messages?

[taskman]

@gusano79 - re: connectionstrings.com - great resource. Thanks for sharing it.

[TnTinMN]

Here is an example that shows four methods to access a "dbf" file. Since you started with OBDC and wanted info on that, that section is quite long. Personally, I would use the ACE driver, but to each their own. The simplest/most flexible for "dbf" is the visual foxpro driver. I cover handling deleted records in the "dbf" because this is an area that can cause those that need to access them quite a bit of headaches. Delete records are not deleted from the file until in is "packed".

Public Class Form1

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

      'My dBase files are in the application folder
      Dim AppDir As String = My.Application.Info.DirectoryPath

      '=======================================================
      'Open Database Connectivity (ODBC) DSN-Less Connection
      'see: http://support.microsoft.com/kb/310985

      Dim connSB As New Odbc.OdbcConnectionStringBuilder()
      With connSB
         'these parameters are called "Connection String Attributes"
         'They are driver specific.  

      'see: Initializing the dBASE Database Driver
      '     http://office.microsoft.com/en-us/access-help/initializing-the-dbase-database-driver-HP001032158.aspx

      'see: SQLConfigDataSource (dBASE Driver) 
      '     http://msdn.microsoft.com/en-us/library/windows/desktop/ms710137%28v=vs.85%29.aspx

         .Add("DRIVER", "Microsoft dBase Driver (*.dbf)")
         .Add("DefaultDir", AppDir) 'Path to dbase files
         .Add("FIL", "dBase 5.0") 'File type dBase III, dBase IV, or dBase 5
         .Add("DriverID", 533) '21 (dBASE III), 277 (dBASE IV), 533 (dBASE 5.0)

         'Do not load deleted records.  
         'Tag would be better named "IgnoreDeleted"
         'where 0 = Off, 1 = On
         'My experience is that this tag does not work unless the application is running 
         'with full administrator privilages
         'a work around is presented next
         .Add("DELETED", 0)

         .Add("CollatingSequence", "ASCII") 'can be: ASCII (the default) or International.
         'The number of background threads for the engine to use. 
         'This value is 3 and cannot be changed. 
         .Add("THREADS", 3)
         .Add("STATISTICS", 0) 'For the dBASE driver, determines whether table size statistics are approximated.
         .Add("READONLY", "FALSE")
         .Add("PageTimeout", 5)
         .Add("SafeTransactions", 0)
         .Add("UserCommitSync", "Yes")

      End With

      Dim OdbcConn As New Odbc.OdbcConnection(connSB.ToString)
      OdbcConn.Open()
      Dim OdbcCmd As New Odbc.OdbcCommand

      'In the CommandText, the table name is the name of the file minus the extension:
      '  i.e. sales.dbf >>> sales

      OdbcCmd.CommandText = "Select * From Sales"
      OdbcCmd.Connection = OdbcConn
      Dim OdbcReader As Odbc.OdbcDataReader = OdbcCmd.ExecuteReader
      Dim dt1 As New DataTable
      dt1.Load(OdbcReader)
      DataGridView1.DataSource = dt1
      OdbcConn.Close()
      OdbcConn.Dispose()

      '=======================================================
      'Open Database Connectivity (ODBC) DSN on the fly Connection
      '************************************************
      'Warning:  This method modifies the Registry.
      '          It adds several keys that are deleted
      '          by calling dBaseDSN.DeleteDSN(mydsn)
      '************************************************
      Dim mydsn As String = dBaseDSN.CreateUserDSN(AppDir, dBaseDSN.DBase.V)


      Dim OdbcConn_b As New Odbc.OdbcConnection("DSN=" & mydsn)
      OdbcConn_b.Open()
      Dim OdbcCmd_b As New Odbc.OdbcCommand

      'In the CommandText, the table name is the name of the file minus the extension:
      '  i.e. sales.dbf >>> sales

      OdbcCmd_b.CommandText = "Select * From Sales"
      OdbcCmd_b.Connection = OdbcConn_b
      Dim OdbcReader_b As Odbc.OdbcDataReader = OdbcCmd_b.ExecuteReader
      Dim dt1b As New DataTable
      dt1b.Load(OdbcReader_b)
      DataGridView1b.DataSource = dt1b
      OdbcConn_b.Close()
      OdbcConn_b.Dispose()
      dBaseDSN.DeleteDSN(mydsn)

      '=======================================================
      'Visual FoxPro Driver
      'Download at: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=14839

      'Here to show deleted rows, add "Deleted=0;". 
      '"Delete=1;" fails, but appears to be the default
      Dim connstr As String = "Provider=vfpoledb;Data Source=sales.dbf;Collating Sequence=machine;"

      Dim OleDbConn As New OleDb.OleDbConnection(connstr)
      Dim OleDbCmd As New OleDb.OleDbCommand()
      OleDbCmd.Connection = OleDbConn
      OleDbCmd.CommandText = "Select * From Sales"
      OleDbConn.Open()
      Dim OleDbReader As OleDb.OleDbDataReader
      OleDbReader = OleDbCmd.ExecuteReader
      Dim dt2 As New DataTable
      dt2.Load(OleDbReader)
      DataGridView2.DataSource = dt2
      OleDbConn.Close()
      OleDbConn.Dispose()

      '=======================================================
      'Microsoft ACE (Access) 
      'For DBASE File, just put the path in for the Data Source

      connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & AppDir & "';Extended Properties=dBase 5.0;"
      Dim conn As New OleDb.OleDbConnection(connstr)

      Dim cmd As New OleDb.OleDbCommand()
      cmd.Connection = conn

      cmd.CommandText = "Select * From sales"

      conn.Open()
      OleDbReader = cmd.ExecuteReader
      Dim dt3 As New DataTable
      dt3.Load(OleDbReader)
      DataGridView3.DataSource = dt3
      conn.Close()
      conn.Dispose()

   End Sub

End Class

Class dBaseDSN
   Public Shared Function CreateUserDSN(ByVal DefaultDirectory As String, _
                                        ByVal version As DBase, _
                                        Optional ByVal UID As String = "", _
                                        Optional ByVal CollatingSequence As String = "ASCII", _
                                        Optional ByVal GetDeletedRows As Boolean = True) As String

      Dim DeletedRows(0) As Byte
      If GetDeletedRows Then DeletedRows(0) = 0 Else DeletedRows(0) = 1

      Dim Fil As String
      Dim DriverID As Integer
      Dim Description As String
      Select Case version
         Case DBase.III
            Fil = "dBase III" : DriverID = 21 : Description = Fil
         Case DBase.IV
            Fil = "dBase IV" : DriverID = 277 : Description = Fil
         Case DBase.V
            Fil = "dBase 5.0" : DriverID = 533 : Description = "dBase 5"
         Case Else
            Throw New ArgumentException("Invalid Dbase Version")
      End Select

      Dim UserSID As String = System.Security.Principal.WindowsIdentity.GetCurrent.Owner.Value
      Dim DSN As String = IO.Path.GetFileNameWithoutExtension(IO.Path.GetRandomFileName)
      Dim MainKey As String = Microsoft.Win32.Registry.Users.ToString & "\" & UserSID & "\Software\ODBC\ODBC.INI\" & DSN
      Microsoft.Win32.Registry.SetValue(MainKey, "Driver", "C:\\WINDOWS\\system32\\odbcjt32.dll")
      Microsoft.Win32.Registry.SetValue(MainKey, "DefaultDir", DefaultDirectory, Microsoft.Win32.RegistryValueKind.String)
      Microsoft.Win32.Registry.SetValue(MainKey, "Description", Description, Microsoft.Win32.RegistryValueKind.String)
      Microsoft.Win32.Registry.SetValue(MainKey, "DriverID", DriverID, Microsoft.Win32.RegistryValueKind.DWord)
      Microsoft.Win32.Registry.SetValue(MainKey, "FIL", Fil, Microsoft.Win32.RegistryValueKind.String)
      Microsoft.Win32.Registry.SetValue(MainKey, "SafeTransactions", 0, Microsoft.Win32.RegistryValueKind.DWord)
      Microsoft.Win32.Registry.SetValue(MainKey, "UID", UID, Microsoft.Win32.RegistryValueKind.String)

      Dim Engines As String = MainKey & "\Engines\XBase"

      Microsoft.Win32.Registry.SetValue(Engines, "CollatingSequence", CollatingSequence, Microsoft.Win32.RegistryValueKind.String)
      Microsoft.Win32.Registry.SetValue(Engines, "Deleted", DeletedRows, Microsoft.Win32.RegistryValueKind.Binary)
      Microsoft.Win32.Registry.SetValue(Engines, "ImplicitCommitSync", "", Microsoft.Win32.RegistryValueKind.String)
      Microsoft.Win32.Registry.SetValue(Engines, "PageTimeout", 5, Microsoft.Win32.RegistryValueKind.DWord)
      Microsoft.Win32.Registry.SetValue(Engines, "Statistics", New Byte() {0}, Microsoft.Win32.RegistryValueKind.Binary)
      Microsoft.Win32.Registry.SetValue(Engines, "Threads", 0, Microsoft.Win32.RegistryValueKind.DWord)
      Microsoft.Win32.Registry.SetValue(Engines, "UserCommitSync", "Yes", Microsoft.Win32.RegistryValueKind.String)

      Return DSN

   End Function

   Public Shared Sub DeleteDSN(ByVal DSN As String)
      Dim UserSID As String = System.Security.Principal.WindowsIdentity.GetCurrent.Owner.Value
      Dim Users As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.Users
      Dim Base As Microsoft.Win32.RegistryKey = Users.OpenSubKey(UserSID & "\Software\ODBC\ODBC.INI", True)
      Base.DeleteSubKeyTree(DSN)
      Base.Close()
   End Sub

   Public Enum DBase
      III
      IV
      V
   End Enum
End Class

[M.Waqas Aslam]

thanks all of you , i solved my prob , my prob was that i do not make free my foxpro tables from the dbc . i found code in c#, and after making dll , i am using it ,here is a code , to free the foxpro table from the dbc.

private void FreeFoxTable(string cTableName)
        {
            string dataDIR = Path.GetDirectoryName(cTableName);
            string connString = @"Provider=vfpoledb.1;Data Source=" + dataDIR;
            string CDXFile = Path.ChangeExtension(cTableName, "CDX");
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand("EXECS([USE " + cTableName + "])", conn);
            conn.Open();

            try
            {
                // Use the table. If it's a member of a (missing) DBC we get
                // an error cannot open file .... <something>.dbc
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                if (err.Message.ToLower().Contains("cannot open file") && err.Message.ToLower().Contains("dbc"))
                {
                    // Free the DBF from the DBC container.
                    // We use the FoxPro command "FREE TABLE" to do this
                    // and execute it through the VFP OLE DB Provider
                    // usign the FoxPro EXECS() function.
                    cmd.CommandText = "EXECS([FREE TABLE " + cTableName + "])";
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    conn.Dispose();
                    cmd.Dispose();
                }
            }

            // Delete any index file that may be associated with the table being checked.
            // This prevents a "variable not found" error opening the table if there happens
            // to be an index expression that uses long field names which may be no longer
            // valid since we removed the DBF from the DBC.
            if (File.Exists(CDXFile)){
                File.Delete(CDXFile);
            }
        } 

and this one.

private void button1_Click(object sender, EventArgs e)
        {
            string dataDIR = System.Environment.CurrentDirectory + "\\VFPDATA\\";
            // Function to clear DBC membership from DBF
            FreeFoxTable(dataDIR + "DBCDBF.dbf");
            try
            {
                string connString = @"Provider=vfpoledb.1;Data Source=" + dataDIR;
                OleDbConnection conn = new OleDbConnection(connString);
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM DBCDBF", conn);
                DataTable table = new DataTable("table");
                da.Fill(table);
                dataGridView1.DataSource = table;
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }

        }

this is not my good all credit goes to author of this code,
thanks again all of you for your time, thread is solved.

Regards

[taskman]

But - wait a second - if you figured out how to connect and operate on the DBC, wasn't your problem already solved before freeing the table? And won't freeing the table from the DBC have possible negative repurcussions on the application that created and maintains that table/DBC? The DBC adds extended attributes to its tables.

[TnTinMN]

taskman,
I believe that the OP found the this article: "Error message "Cannot open file <path>\xxx.DBC" reading Microsoft Visual FoxPro tables via the VFP OLE DB Provider" that shows the posted code solution. It is for dealing with the situation where you do not have the "dbc" file.

I question if this would even be necessary if you where to use one of the other drivers to access the records. I doubt that they would be aware of the "backlink" record in the dbf.

+1 for the concerns you raised.