How to use VFPOLEDB to get DBF information

Question

I can use GetSchemaTable and GetXMLSchema to get information about field types, sizes, etc. from FoxF DBFs opened with VFPOLEDB, but cannot get information about which indexes are in the / CDX tables.

I don't want to use indexes, just the criteria by which an index is created to help me create SQL commands to create tables on the SQL server and import data.

I could make DISPLAY STRUCTURE output to a text file in all tables and parse it in VB.NET, but I hope there is something that I am reviewing because I am not familiar with the syntax of VB.NET/OLEDB yet.

Answer 1

Few studies have yielded these results. I started by looking at the ForeignKey.FKTableSchema Property and, unfortunately, not at the .NET property. Later everything looked good when I found OleDbSchemaGuid.Indexes Field and everything looked good until I started the application and got a Method not supported by this provider. In the end, the following article illuminated the path,

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

and this conclusion

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

However, it was some really interesting suggestion that allowed us to write this small console application for collecting indexes available in a table. This is achieved by directly querying the schema table from SQL. The following example is shown in the Employees table of the well-known Northwind 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, by removing the restrictions that I was able to overcome, the Method is not supported by this provider. mistake and ended up summing this up as a 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(); } 

The result is a longer output for sorting, but part of it will 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! All I needed to extract. Since it was in the vb.net section, I sent my rough code, I filtered the returned fields to list a few here. It returns all the relevant information related to indexes, even complex ones created using expressions. All tables in the path specified in the connection string with CDX indices 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