/* ** GetDefaultSchema ** ** History: ** 17-Aug-06 (thoda04) ** Created. */ /// <summary> /// Return the current_user as the default schema name. /// </summary> /// <returns></returns> public string GetDefaultSchema() { if (userSearchOrder == null) // build current_user, dba, $ingres { userSearchOrder = new UserSearchOrder(AdvanConnect); } if (userSearchOrder == null || userSearchOrder.Count == 0) { return(null); } return((string)(userSearchOrder[0])); }
} // FindMissingSchemaName /* ** GetAllCatalogTablesAndViews ** ** History: ** 23-Jun-03 (thoda04) ** Created. */ /// <summary> /// Get a list of all table or view names, qualified by /// "User Tables", "User Views", "All Tables", "All Views". /// </summary> /// <param name="qualification"></param> /// <returns>list of tables or views requested</returns> public ArrayList GetAllCatalogTablesAndViews(string qualification) { if (qualification == null) // if qualificatin is unknown then { return(new ArrayList()); // return empty list as safety check } ArrayList tablesList; // pointer to tables list we are building ArrayList viewsList; // pointer to views list we are building string schemaName = null; string readSchemaName; string readTableName; string readType; qualification = qualification.ToUpper( System.Globalization.CultureInfo.InvariantCulture); switch (qualification) { case "USER TABLES": { if (this.userTablesList != null) // return if already built { return(this.userTablesList); } tablesList = this.userTablesList = new ArrayList(); viewsList = this.userViewsList = new ArrayList(); if (userSearchOrder == null) // build current_user, dba, $ingres { userSearchOrder = new UserSearchOrder(AdvanConnect); } if (userSearchOrder != null || userSearchOrder.Count > 0) { schemaName = (string)this.userSearchOrder[0]; } break; } case "USER VIEWS": { if (this.userViewsList != null) // return if already built { return(this.userViewsList); } tablesList = this.userTablesList = new ArrayList(); viewsList = this.userViewsList = new ArrayList(); if (userSearchOrder == null) // build current_user, dba, $ingres { userSearchOrder = new UserSearchOrder(AdvanConnect); } if (userSearchOrder != null || userSearchOrder.Count > 0) { schemaName = (string)this.userSearchOrder[0]; } break; } case "ALL TABLES": { if (this.allTablesList != null) // return if already built { return(this.allTablesList); } tablesList = this.allTablesList = new ArrayList(); viewsList = this.allViewsList = new ArrayList(); break; } case "ALL VIEWS": { if (this.allViewsList != null) // return if already built { return(this.allViewsList); } tablesList = this.userTablesList = new ArrayList(); viewsList = this.userViewsList = new ArrayList(); break; } default: return(new ArrayList()); // return empty list as safety check } // end switch // We need to build the tables list and views list. // We distinguish between user and all for performance reasons // since all tables/views could be several thousand entries vs. // a couple dozen user tables/views. // We do batch table list and view list building together since // if the user is interested in a table list, we assume they are // interested in a view list on the next mouse click. This // eliminates a second query. // get a list of catalog columns for the specified schema and table // (use tilde to avoid ambiguity with "." in delimited identifiers // when building the key). IDbCommand cmd = AdvanConnect.Connection.CreateCommand(); IDataParameter parm; StringBuilder sb = new StringBuilder( "SELECT DISTINCT table_owner, table_name, table_type " + "FROM iitables " + "WHERE " + "system_use <> 'S' AND " + "table_type in ('T','V') AND " + "table_name NOT LIKE 'ii%' AND " + "table_name NOT LIKE 'II%' " , 200); if (schemaName != null) // "User Tables" or "User Views" { sb.Append("AND table_owner = ? "); // qualify by user parm = cmd.CreateParameter(); parm.Value = schemaName; parm.DbType = DbType.AnsiString; // don't send Unicode cmd.Parameters.Add(parm); } // end if schemaName != null sb.Append("ORDER BY 1, 2"); cmd.CommandText = sb.ToString(); // send the query to the database catalog to get columns IDataReader rdr = null; try { // read the table/view names from the catalog rdr = cmd.ExecuteReader(); while (rdr.Read()) // process list of owners { if (rdr.IsDBNull(0) || rdr.IsDBNull(1) || rdr.IsDBNull(2)) // skip table/view if somehow null { continue; } readSchemaName = rdr.GetString(0).TrimEnd(); readTableName = rdr.GetString(1).TrimEnd(); readType = rdr.GetString(2).TrimEnd(); Catalog.Table catTable = new Catalog.Table(readSchemaName, readTableName); if (readType == "T") { tablesList.Add(catTable); // add table to list } else { viewsList.Add(catTable); // add view to list } } // end while loop through tables/views in catalog } catch (SqlEx /*ex*/) { //Console.WriteLine(ex); throw; } finally { if (rdr != null) { rdr.Close(); } } // The right lists are built now. // Call ourselves recursively to return the desired list. return(GetAllCatalogTablesAndViews(qualification)); } // GetAllCatalogTablesAndViews
/* ** FindMissingSchemaName ** ** History: ** 28-Jan-03 (thoda04) ** Created. */ /// <summary> /// Search catalog for schema name (table_owner) for a /// table name reference that does not contain the schema qualification. /// </summary> public string FindMissingSchemaName(string tableName) { if (userSearchOrder == null) // build current_user, dba, $ingres { userSearchOrder = new UserSearchOrder(AdvanConnect); } if (userSearchOrder == null || userSearchOrder.Count == 0) { return(null); } if (tableName == null || tableName.Length == 0) { return(null); } if (userSearchOrder.Count == 0) // return if no users { return(null); // in list to search on } // (should never happen) // build the query to send to the database catalog IDbCommand cmd = AdvanConnect.Connection.CreateCommand(); StringBuilder sb = new StringBuilder( "SELECT DISTINCT table_owner FROM iitables " + "WHERE table_name = ? AND (", 100); IDataParameter parm = cmd.CreateParameter(); parm.Value = tableName; parm.DbType = DbType.AnsiString; // don't send Unicode cmd.Parameters.Add(parm); int i = 0; foreach (string user in userSearchOrder) { if (i != 0) { sb.Append(" OR "); } i++; sb.Append("table_owner = ?"); parm = cmd.CreateParameter(); parm.Value = user; parm.DbType = DbType.AnsiString; // don't send Unicode cmd.Parameters.Add(parm); } sb.Append(")"); cmd.CommandText = sb.ToString(); // SELECT table_owner FROM iitables WHERE table_name = <tablename> // AND (table_owner = <user> OR table_owner = <dbaname> OR // table_owner = <$ingres>) // send the query to the database catalog to get possible owners IDataReader rdr = null; int ownerIndex; int ownerIndexBest = 9; string tableOwner; try { rdr = cmd.ExecuteReader(); // read the owners with same tablename while (rdr.Read()) // process list of owners { if (rdr.IsDBNull(0)) { continue; } tableOwner = rdr.GetString(0).TrimEnd(); ownerIndex = userSearchOrder.IndexOf(tableOwner); if (ownerIndex != -1 && ownerIndex < ownerIndexBest) { ownerIndexBest = ownerIndex; // a better user found } } rdr.Close(); if (ownerIndexBest != 9) { return((string)userSearchOrder[ownerIndexBest]); } } catch (SqlEx) { throw; } finally { if (rdr != null) { rdr.Close(); } } return(null); // no user found in userSearchOrder for named table } // FindMissingSchemaName