How to use VFPOLEDB to get DBF information

Question

I can use GetSchemaTable and GetXMLSchema to get information about field types, sizes etc. from a Foxpro DBF opened with VFPOLEDB but can not get any information pertaining to what indexes are in the tables/CDX.

I dont want to use the indexes, just the criteria on which the index is built to aid me in generating the SQL commands to create the tables on a SQL server and import the data.

I could do a DISPLAY STRUCTURE output to a text file on all of the tables and parse it in VB.NET but I am hoping there is something I am overlooking as I am not that familiar with VB.NET/OLEDB syntax yet.

Answer 1

Little bit of research given away these results. I have started with having a look at ForeignKey.FKTableSchema Property and unfortunately not a .NET property. Later on things looked good when I found OleDbSchemaGuid.Indexes Field and everything looked good until I ran the application and got the Method is not supported by this provider. Eventually the following article lit up the way,

GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Indexes - How to access included columns on index

and this finding

The OleDb and Odbc providers do not provide a built-in catalog method that will return non-key ("Included") index columns.

However it was some really interesting suggestion which let to writing this little Console application for you to harvest the indexes available in the table. This is achieved by directly querying the schema table from SQL. The following example is on Employees table of famous Northwind sample database. Here you go,

//Open a connection to the SQL Server Northwind database.
var connectionString =
    "Provider=SQLOLEDB;Data Source=SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;Encrypt=False;TrustServerCertificate=False";

using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();

    var select = "SELECT " +
        "    T.name                  AS TABLE_NAME" +
        "  , IX.name                 AS INDEX_NAME" +
        "  , IC.index_column_id      AS IX_COL_ID" +
        "  , C.name                  AS COLUMN_NAME" +
        "  , IC.is_included_column   AS INCLUDED_NONKEY" +
        "  " +
        "FROM " +
        "    sys.tables T " +
        "    INNER JOIN sys.indexes IX" +
        "        ON T.object_id = IX.object_id     " +
        "    INNER JOIN sys.index_columns IC" +
        "        ON IX.object_id = IC.object_id " +
        "        AND IX.index_id = IC.index_id " +
        "    INNER JOIN sys.columns C" +
        "        ON IC.object_id = C.object_id " +
        "        AND IC.column_id = C.column_id " +
        "  " +
        "WHERE T.name = 'Employees'" +
        "ORDER BY IC.index_column_id";
    OleDbCommand cmd = new OleDbCommand(@select, connection);
    cmd.CommandType = CommandType.Text;
    var outputTable = new DataSet("Table");
    var my = new OleDbDataAdapter(cmd).Fill(outputTable);

    foreach (DataTable table in outputTable.Tables)
    {
        foreach (DataRow myField in table.Rows)
        {
            //For each property of the field...
            foreach (DataColumn myProperty in table.Columns)
            {
                //Display the field name and value.
                Console.WriteLine(myProperty.ColumnName + " = " +
                                  myField[myProperty].ToString());
            }
            Console.WriteLine();
        }
    }
}
Console.ReadLine();

and finally the results,

TABLE_NAME = Employees
INDEX_NAME = PK_Employees
IX_COL_ID = 1
COLUMN_NAME = EmployeeID
INCLUDED_NONKEY = False

TABLE_NAME = Employees
INDEX_NAME = LastName
IX_COL_ID = 1
COLUMN_NAME = LastName
INCLUDED_NONKEY = False

TABLE_NAME = Employees
INDEX_NAME = PostalCode
IX_COL_ID = 1
COLUMN_NAME = PostalCode
INCLUDED_NONKEY = False

However, later on by removing the restrictions I managed to get over the Method is not supported by this provider. error and ended up summing it up like this shorter solution,

//Open a connection to the SQL Server Northwind database.
var connectionString =
    "Provider=SQLOLEDB;Data Source=SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;Encrypt=False;TrustServerCertificate=False";

using (OleDbConnection cnn = new OleDbConnection(connectionString))
{
    cnn.Open();
    DataTable schemaIndexess = cnn.GetSchema("Indexes",
        new string[] {null, null, null});
    DataTable schemaIndexes = cnn.GetOleDbSchemaTable(
        OleDbSchemaGuid.Indexes,
        new object[] {null, null, null});

    foreach (DataRow myField in schemaIndexes.Rows)
    {
        //For each property of the field...
        foreach (DataColumn myProperty in schemaIndexes.Columns)
        {
            //Display the field name and value.
            Console.WriteLine(myProperty.ColumnName + " = " +
                              myField[myProperty].ToString());
        }
        Console.WriteLine();
    }


    Console.ReadLine();
}

Which results to a longer output to sort out but part of it would be

TABLE_CATALOG = Northwind
TABLE_SCHEMA = dbo
TABLE_NAME = Employees
INDEX_CATALOG = Northwind
INDEX_SCHEMA = dbo
INDEX_NAME = LastName
PRIMARY_KEY = False
UNIQUE = False
CLUSTERED = False
TYPE = 1
FILL_FACTOR = 0
INITIAL_SIZE =
NULLS =
SORT_BOOKMARKS = False
AUTO_UPDATE = True
NULL_COLLATION = 4
ORDINAL_POSITION = 1
COLUMN_NAME = LastName
COLUMN_GUID =
COLUMN_PROPID =
COLLATION = 1
CARDINALITY =
PAGES = 1
FILTER_CONDITION =
INTEGRATED = False

Answer 2

Perfect! Everything I needed to extract. Since it was in vb.net section I posted my rough code, I filtered the fields returned so I could list a few here. It returns all pertinent information relating to the indexes, even complex ones created with expressions. All tables in the path provided in the Connection String with CDX indexes will be returned.

 TABLE_NAME = schematest
 INDEX_NAME = char3ascen
 NULLS = 1
 EXPRESSION = char3ascen

 TABLE_NAME = schematest
 INDEX_NAME = expressn
 NULLS = 2
 EXPRESSION = LEFT(char1null,4)+SUBSTR(char2,4,2)

 TABLE_NAME = schematest
 INDEX_NAME = multifld
 NULLS = 2
 EXPRESSION = char1null+char2

 TABLE_NAME = customer
 INDEX_NAME = zip
 NULLS = 1
 EXPRESSION = zip


    Private Sub GetIndexInfo_Click(sender As Object, e As EventArgs) Handles GetIndexInfo.Click
    Dim cnnOLEDB As New OleDbConnection
    Dim SchemaTable As DataTable
    Dim myField As DataRow
    Dim myProperty As DataColumn
    Dim ColumnNames As New List(Of String)
    Dim strConnectionString = "Provider=vfpoledb;Data Source=D:\ACW\;Collating Sequence=general;DELETED=False"
    cnnOLEDB.ConnectionString = strConnectionString
    cnnOLEDB.Open()
    ColumnNames.Add("TABLE_NAME")
    columnnames.Add("INDEX_NAME")
    columnnames.Add("NULLS")
    columnnames.Add("TYPE")
    columnnames.Add("EXPRESSION")

    SchemaTable = cnnOLEDB.GetSchema("Indexes")
    'For Each myProperty In SchemaTable.Columns
    For Each myField In SchemaTable.Rows
        For Each myProperty In SchemaTable.Columns
            If ColumnNames.Contains(myProperty.ColumnName) Then
                Console.WriteLine(myProperty.ColumnName & " = " & myField(myProperty).ToString)
            End If
        Next
        Console.WriteLine()
    Next
    Console.ReadLine()
    DGVSchema.DataSource = SchemaTable

End Sub