public DataTable GetTables() { DataTable metaData = new DataTable(); //metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String")); metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_TYPE", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_GUID", Type.GetType("System.String")); metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_PROPID", Type.GetType("System.String")); //metaData.Columns.Add("DATE_CREATED", Type.GetType("System.String")); //metaData.Columns.Add("DATE_MODIFIED", Type.GetType("System.String")); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); foreach (string table in tables) { IVistaDBTableStructure tblStructure = db.TableStructure(table); DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_NAME"] = tblStructure.Name; row["DESCRIPTION"] = tblStructure.Description; } return(metaData); }
public DataTable GetIndexes(string tableName) { DataTable metaData = new DataTable(); //metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String")); //metaData.Columns.Add("INDEX_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("INDEX_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("INDEX_NAME", Type.GetType("System.String")); //metaData.Columns.Add("UNIQUE", Type.GetType("System.String")); //metaData.Columns.Add("CLUSTERED", Type.GetType("System.String")); //metaData.Columns.Add("TYPE", Type.GetType("System.String")); //metaData.Columns.Add("FILL_FACTOR", Type.GetType("System.String")); //metaData.Columns.Add("INITIAL_SIZE", Type.GetType("System.String")); //metaData.Columns.Add("NULLS", Type.GetType("System.String")); //metaData.Columns.Add("SORT_BOOKMARKS", Type.GetType("System.String")); //metaData.Columns.Add("AUTO_UPDATE", Type.GetType("System.String")); //metaData.Columns.Add("NULL_COLLATION", Type.GetType("System.String")); //metaData.Columns.Add("COLLATION", Type.GetType("System.String")); //metaData.Columns.Add("CARDINALITY", Type.GetType("System.String")); //metaData.Columns.Add("PAGES", Type.GetType("System.String")); //metaData.Columns.Add("FILTER_CONDITION", Type.GetType("System.String")); //metaData.Columns.Add("INTEGRATED", Type.GetType("System.String")); metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String")); metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String")); metaData.Columns.Add("INDEX_CATALOG", Type.GetType("System.String")); metaData.Columns.Add("INDEX_NAME", Type.GetType("System.String")); metaData.Columns.Add("UNIQUE", Type.GetType("System.Boolean")); metaData.Columns.Add("COLLATION", Type.GetType("System.Int16")); metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String")); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); IVistaDBTableStructure tblStructure = db.TableStructure(tableName); foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes) { string[] pks = indexInfo.KeyExpression.Split(','); int index = 0; foreach (string colName in pks) { metaData.Rows.Add(new object[] { GetDatabaseName(), tblStructure.Name, GetDatabaseName(), indexInfo.Name, indexInfo.Unique, indexInfo.KeyStructure[index++].Descending ? 2 : 1, colName }); } } return(metaData); }
public DataTable GetForeignKeys(string tableName) { DataTable metaData = new DataTable(); //metaData.Columns.Add("PK_TABLE_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("PK_TABLE_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("PK_TABLE_NAME", Type.GetType("System.String")); //metaData.Columns.Add("FK_TABLE_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("FK_TABLE_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("FK_TABLE_NAME", Type.GetType("System.String")); //metaData.Columns.Add("ORDINAL", Type.GetType("System.String")); //metaData.Columns.Add("UPDATE_RULE", Type.GetType("System.String")); //metaData.Columns.Add("DELETE_RULE", Type.GetType("System.String")); //metaData.Columns.Add("PK_NAME", Type.GetType("System.String")); //metaData.Columns.Add("FK_NAME", Type.GetType("System.String")); //metaData.Columns.Add("DEFERRABILITY", Type.GetType("System.String")); metaData.Columns.Add("PK_TABLE_CATALOG", Type.GetType("System.String")); metaData.Columns.Add("PK_TABLE_SCHEMA", Type.GetType("System.String")); metaData.Columns.Add("FK_TABLE_CATALOG", Type.GetType("System.String")); metaData.Columns.Add("FK_TABLE_SCHEMA", Type.GetType("System.String")); metaData.Columns.Add("FK_TABLE_NAME", Type.GetType("System.String")); metaData.Columns.Add("PK_TABLE_NAME", Type.GetType("System.String")); metaData.Columns.Add("ORDINAL", Type.GetType("System.Int32")); metaData.Columns.Add("FK_NAME", Type.GetType("System.String")); metaData.Columns.Add("PK_NAME", Type.GetType("System.String")); metaData.Columns.Add("PK_COLUMN_NAME", Type.GetType("System.String")); metaData.Columns.Add("FK_COLUMN_NAME", Type.GetType("System.String")); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); IVistaDBTableStructure tblStructure = db.TableStructure(tableName); foreach (IVistaDBRelationshipInformation relInfo in tblStructure.ForeignKeys) { //string[] fColumns = relInfo.ForeignKey.Split(new char[] {';'}); //string[] pColumns = relInfo.p.ForeignKey.pprimaryKey.Split(new char[] {';'}); //for(int i = 0; i < fColumns.GetLength(0); i++) { metaData.Rows.Add(new object[] { GetDatabaseName(), DBNull.Value, DBNull.Value, DBNull.Value, tblStructure.Name, relInfo.PrimaryTable, 0, relInfo.Name, "PKEY", "", //pColumns[i], "" }); //fColumns[i]}); } } return(metaData); }
public void GetDBCompexInfo(string dbName) { try { using (IVistaDBDDA conn = VistaDBEngine.Connections.OpenDDA()) { using (IVistaDBDatabase db = conn.OpenDatabase(dbName, VistaDBDatabaseOpenMode.ExclusiveReadOnly, null)) { Console.WriteLine("METAINFORMATION FOR " + dbName + " DATABASE"); Console.WriteLine("-------------------------------------------"); Console.WriteLine("Table Description: " + db.Description); Console.WriteLine("Row count: " + db.RowCount.ToString()); Console.WriteLine("PageSize: " + db.PageSize.ToString()); Console.WriteLine("Open mode: " + db.Mode.ToString()); Console.WriteLine("Culture: " + db.Culture.ToString()); Console.WriteLine("Case Sensitive: " + db.CaseSensitive.ToString()); ArrayList tables = db.EnumTables(); foreach (string table in tables) { IVistaDBTableStructure tblStructure = db.TableStructure(table); Console.WriteLine("============================================"); Console.WriteLine("Table " + table); Console.WriteLine("============================================"); //columns Console.WriteLine("COLUMNS:"); foreach (IVistaDBColumnAttributes colInfo in tblStructure) { Console.WriteLine("\t" + colInfo.Name); //use colInfo for getting columns metadata } //indexes Console.WriteLine("INDEXES:"); foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes) { Console.WriteLine("\t" + indexInfo.Name); //use indexInfo for getting columns metadata } //constraints Console.WriteLine("CONSTRAINTS:"); foreach (IVistaDBConstraintInformation constrInfo in tblStructure.Constraints) { Console.WriteLine("\t" + constrInfo.Name); //use constrInfo for getting columns metadata } //foreignKeys Console.WriteLine("FOREIGN KEYS:"); foreach (IVistaDBRelationshipInformation relInfo in tblStructure.ForeignKeys) { Console.WriteLine("\t" + relInfo.Name); //use foreignKeys for getting columns metadata } } } } } catch (VistaDBException ex) { } catch { } }
public DataTable GetColumns(string tableName) { DataTable metaData = new DataTable(); //metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_GUID", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_PROPID", Type.GetType("System.String")); //metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_FLAGS", Type.GetType("System.String")); //metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.String")); //metaData.Columns.Add("DATA_TYPE", Type.GetType("System.String")); //metaData.Columns.Add("TYPE_GUID", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.String")); //metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.String")); //metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.String")); //metaData.Columns.Add("DATETIME_PRECISION", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_SET_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_SET_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_SET_NAME", Type.GetType("System.String")); //metaData.Columns.Add("COLLATION_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("COLLATION_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("COLLATION_NAME", Type.GetType("System.String")); //metaData.Columns.Add("DOMAIN_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("DOMAIN_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("DOMAIN_NAME", Type.GetType("System.String")); //metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_LCID", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_COMPFLAGS", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_SORTID", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_TDSCOLLATION", Type.GetType("System.String")); //metaData.Columns.Add("IS_COMPUTED", Type.GetType("System.String")); //metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.String")); //metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.String")); //metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.String")); metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String")); metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String")); metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.Int32")); metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.Boolean")); metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.Boolean")); metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String")); metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.Boolean")); metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.Int32")); metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.Int32")); metaData.Columns.Add("DATA_TYPE_NAME", Type.GetType("System.String")); metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.Int32")); metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.Int32")); metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.Int32")); metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.Int32")); metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String")); metaData.Columns.Add("IS_PRIMARY_KEY", Type.GetType("System.Boolean")); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); IVistaDBTableStructure tblStructure = db.TableStructure(tableName); foreach (IVistaDBColumnAttributes c in tblStructure) { string colName = c.Name; string def = ""; if (tblStructure.Defaults.Contains(colName)) { def = tblStructure.Defaults[colName].Expression; } int width = c.MaxLength; //c.ColumnWidth; int dec = 0; //c.ColumnDecimals; int length = 0; int octLength = width; IVistaDBIdentityInformation identity = null; if (tblStructure.Identities.Contains(colName)) { identity = tblStructure.Identities[colName]; } string[] pks = null; if (tblStructure.Indexes.Contains("PrimaryKey")) { pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(','); } else { foreach (IVistaDBIndexInformation pk in tblStructure.Indexes) { if (pk.Primary) { pks = pk.KeyExpression.Split(','); break; } } } System.Collections.Hashtable pkCols = null; if (pks != null) { pkCols = new Hashtable(); foreach (string pkColName in pks) { pkCols[pkColName] = true; } } switch (c.Type) { case VistaDBType.Char: case VistaDBType.NChar: case VistaDBType.NText: case VistaDBType.NVarchar: case VistaDBType.Text: case VistaDBType.Varchar: length = width; width = 0; dec = 0; break; case VistaDBType.Currency: case VistaDBType.Double: case VistaDBType.Decimal: case VistaDBType.Single: break; default: width = 0; dec = 0; break; } metaData.Rows.Add(new object[] { tblStructure.Name, c.Name, c.RowIndex, c.AllowNull, def == string.Empty ? false : true, def, identity == null ? false : true, 1, identity == null ? 0 : Convert.ToInt32(identity.StepExpression), c.Type.ToString(), width, dec, length, octLength, c.Description, pkCols == null ? false : pkCols.Contains(colName) }); } return(metaData); }