CODEWITHSUNDEEP
c#.netvisual-foxpro
Johan G
Johan G

Reputation: 1013

Read data from Visual Fox Pro database with C#

I am able to make a Data Connection to a Visual Fox Pro database, from it I need to 2 tables. How do I join the 2 tables then retrieve the data in C#?

Upvotes: 2

Views: 12345

Answers (2)

DRapp
DRapp

Reputation: 48139

First, I would download Microsoft's Visual FoxPro OleDb provider.

Once downloaded and installed, you can use it for connecting to the PATH where the database tables are located. Additionally, if the app is using a "database" where the tables are properly linked, you can explicitly include the database name too.

using System.Data;
using System.Data.OleDb;

public class YourClass
{
   public DataTable GetYourData()
   {
      DataTable YourResultSet = new DataTable();

      OleDbConnection yourConnectionHandler = new OleDbConnection(
          "Provider=VFPOLEDB.1;Data Source=C:\\SomePath\\;" );

      // if including the full dbc (database container) reference, just tack that on
//      OleDbConnection yourConnectionHandler = new OleDbConnection(
//          "Provider=VFPOLEDB.1;Data Source=C:\\SomePath\\NameOfYour.dbc;" );


      // Open the connection, and if open successfully, you can try to query it
      yourConnectionHandler.Open();

      if( yourConnectionHandler.State == ConnectionState.Open )
      {
         OleDbDataAdapter DA = new OleDbDataAdapter();
         string mySQL = "select A1.*, B1.* "
                     + " from FirstTable A1 "
                     + "      join SecondTable B1 "
                     + "         on A1.SomeKey = B1.SomeKey "
                     + " where A1.WhateverCondition ";  // blah blah...

         OleDbCommand MyQuery = new OleDbCommand( mySQL, yourConnectionHandle );

         DA.SelectCommand = MyQuery;

         DA.Fill( YourResultSet );

         yourConnectionHandle.Close();
      }

      return YourResultSet;
   }
}

Upvotes: 6

Manny
Manny

Reputation: 1034

In case someone is looking for a somewhat complete test. You will need to download the VFPOLEDB driver from Microsoft.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                OleDbConnection connection = new OleDbConnection(
        "Provider=VFPOLEDB.1;Data Source=F:\\Gutters\\Data\\database.dbc;"
    );
                connection.Open();
                DataTable tables = connection.GetSchema(
                    System.Data.OleDb.OleDbMetaDataCollectionNames.Tables
                );

                foreach (System.Data.DataRow rowTables in tables.Rows)
                {
                    Console.Out.WriteLine(rowTables["table_name"].ToString());
                    DataTable columns = connection.GetSchema(
                        System.Data.OleDb.OleDbMetaDataCollectionNames.Columns,
                        new String[] { null, null, rowTables["table_name"].ToString(), null }
                    );
                    foreach (System.Data.DataRow rowColumns in columns.Rows)
                    {
                        Console.Out.WriteLine(
                            rowTables["table_name"].ToString() + "." +
                            rowColumns["column_name"].ToString() + " = " +
                            rowColumns["data_type"].ToString()
                        );
                    }
                }

                string sql = "select * from customer";
                OleDbCommand cmd = new OleDbCommand(sql, connection);

                DataTable YourResultSet = new DataTable();
                OleDbDataAdapter DA = new OleDbDataAdapter(cmd);

                DA.Fill(YourResultSet);
            }
        }
    }

Upvotes: 1

Related Questions