MichaelvdV@Atos

Schema information on PIOLEDB tables

Discussion created by MichaelvdV@Atos on Sep 1, 2010
Latest reply on Sep 2, 2010 by michaelh

I'm trying to get the relevant schema information on the PI OLEDB catalogs and tables. My code snippet (below) is discovering the catalogs and tables ok, but not the columns. It only discovers the columns of tables in the piarchive catalog. The other tables (for catalog: pilog, pibatch, etc, etc...) are not returned.

 

Any help would be greatly appreciated! The solution will be answered with a free beer/beverage at the Palace Hotel bar

 

This is my relevant code (prototype code):


 public static List<OleDbTable> Generate(string connString)
        {
            var tables = new List<OleDbTable>();
            using (OleDbConnection conn = new OleDbConnection(connString))
            {

                conn.Open();
                string[] restrictions = new string[4];
                restrictions[3] = "Table";

                DataTable catalogTable = conn.GetSchema("Catalogs");
                foreach (DataRow catalogRow in catalogTable.Rows)
                {
                    restrictions[0] = catalogRow["CATALOG_NAME"].ToString();
                    DataTable schemaTable = conn.GetSchema("Tables", restrictions);
                   
                    foreach (DataRow row in schemaTable.Rows)
                    {
                        var table = new OleDbTable()
                        {
                            Name = row["TABLE_NAME"].ToString(),
                            Catalog = row["TABLE_CATALOG"].ToString(),
                        };

                        table.Members = new Dictionary<string, string>();

                        var tableschema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, table.Name, null });
                       
                        foreach (DataRow schemarow in tableschema.Rows)
                            table.Members.Add(schemarow["COLUMN_NAME"].ToString(), schemarow["DATA_TYPE"].ToString());

                        tables.Add(table);
                    }
                }
           
            }

            return tables;
        }

 

Outcomes