override internal void LoadForView() { try { string query = "select * from information_schema.columns where table_catalog = '" + this.View.Database.Name + "' and table_schema = '" + this.View.Schema + "' and table_name = '" + this.View.Name + "' order by ordinal_position"; NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.View.Database.Name); DataTable metaData = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(metaData); cn.Close(); metaData.Columns["udt_name"].ColumnName = "TYPE_NAME"; metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE"; if (metaData.Columns.Contains("TYPE_NAME")) { f_TypeName = metaData.Columns["TYPE_NAME"]; } if (metaData.Columns.Contains("TYPE_NAMECOMPLETE")) { f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"]; } PopulateArray(metaData); } catch {} }
override internal void LoadAll() { try { string query = "select * from information_schema.views where table_schema = '" + this.Database.SchemaName + "'"; NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name); DataTable metaData = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(metaData); cn.Close(); PopulateArray(metaData); } catch {} }
override internal void LoadAll() { try { string query = "SELECT routine_definition as PROCEDURE_DEFINITION, specific_name, " + "routine_name as PROCEDURE_NAME, routine_schema as PROCEDURE_SCHEMA, routine_catalog as PROCEDURE_CATALOG " + "from information_schema.routines where routine_schema = '" + this.Database.SchemaName + "' and routine_catalog = '" + this.Database.Name + "'"; NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name); DataTable metaData = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(metaData); cn.Close(); PopulateArray(metaData); } catch {} }
override internal void LoadAll() { try { // New PostgreSQL sorted query from Michael McKean string query = "select * from information_schema.tables where table_type = 'BASE TABLE' " + " and table_schema = '" + this.Database.SchemaName + "' ORDER BY table_name;"; /*string query = "select * from information_schema.tables where table_type = 'BASE TABLE' " + * " and table_schema = '" + this.Database.SchemaName + "'";*/ NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name); DataTable metaData = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(metaData); cn.Close(); PopulateArray(metaData); } catch {} }
internal override void LoadAll() { string query = "select * from information_schema.domains where domain_catalog = '" + this.Database.Name + "' and domain_schema = '" + this.Database.SchemaName + "'"; NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name); DataTable metaData = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(metaData); cn.Close(); metaData.Columns["udt_name"].ColumnName = "DATA_TYPE"; metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE"; if (metaData.Columns.Contains("TYPE_NAMECOMPLETE")) { f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"]; } PopulateArray(metaData); }
private void _LoadAll(string query1, string query2) { NpgsqlConnection cn = null; try { cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name); DataTable metaData1 = new DataTable(); DataTable metaData2 = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query1, cn); adapter.Fill(metaData1); adapter = new NpgsqlDataAdapter(query2, cn); adapter.Fill(metaData2); DataRowCollection rows = metaData2.Rows; int count = rows.Count; for (int i = 0; i < count; i++) { metaData1.ImportRow(rows[i]); } PopulateArrayNoHookup(metaData1); if (metaData1.Rows.Count > 0) { string catalog = this.Table.Database.Name; string schema; string table; string[] cols = null; string q; string query = "SELECT a.attname as COLUMN from pg_attribute a, pg_class c, pg_namespace n " + "WHERE a.attrelid = c.oid AND c.relnamespace = n.oid " + "AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind = 'r' "; foreach (ForeignKey key in this) { //------------------------------------------------ // Primary //------------------------------------------------ cols = ParseColumns(key._row["PK_COLS"] as string); schema = key._row["PK_TABLE_SCHEMA"] as string; table = key._row["PK_TABLE_NAME"] as string; q = query; q += "AND n.nspname = '" + schema + "' AND c.relname = '" + table + "' AND attnum IN("; for (int i = 0; i < cols.GetLength(0); i++) { if (i > 0) { q += ','; } q += cols[i].ToString(); } q += ") ORDER BY attnum;"; DataTable metaData = new DataTable(); adapter = new NpgsqlDataAdapter(q, cn); adapter.Fill(metaData); for (int i = 0; i < cols.GetLength(0); i++) { key.AddForeignColumn(catalog, "", table, metaData.Rows[i]["COLUMN"] as string, true); } //------------------------------------------------ // Foreign //------------------------------------------------ cols = ParseColumns(key._row["FK_COLS"] as string); schema = key._row["FK_TABLE_SCHEMA"] as string; table = key._row["FK_TABLE_NAME"] as string; q = query; q += "AND n.nspname = '" + schema + "' AND c.relname = '" + table + "' AND attnum IN("; for (int i = 0; i < cols.GetLength(0); i++) { if (i > 0) { q += ','; } q += cols[i].ToString(); } q += ") ORDER BY attnum;"; metaData = new DataTable(); adapter = new NpgsqlDataAdapter(q, cn); adapter.Fill(metaData); for (int i = 0; i < cols.GetLength(0); i++) { key.AddForeignColumn(catalog, "", table, metaData.Rows[i]["COLUMN"] as string, false); } } } } catch {} cn.Close(); }
override internal void LoadForTable() { NpgsqlConnection cn = null; try { string query = "select * from information_schema.columns where table_catalog = '" + this.Table.Database.Name + "' and table_schema = '" + this.Table.Schema + "' and table_name = '" + this.Table.Name + "' order by ordinal_position"; cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name); DataTable metaData = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(metaData); metaData.Columns["udt_name"].ColumnName = "TYPE_NAME"; metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE"; if (metaData.Columns.Contains("TYPE_NAME")) { f_TypeName = metaData.Columns["TYPE_NAME"]; } if (metaData.Columns.Contains("TYPE_NAMECOMPLETE")) { f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"]; } PopulateArray(metaData); // IsAutoKey logic // query = "SELECT a.attname AS column_name, c2.relname AS seq_name " + // "FROM pg_class c1, pg_class c2, pg_namespace n, pg_depend d, pg_attribute a " + // "WHERE n.nspname = '" + this.Table.Schema + "' AND c1.relname = '" + this.Table.Name + "' " + // "AND c2.relkind = 'S' AND c1.relnamespace = n.oid " + // "AND d.refobjid = c1.oid AND c2.oid = d.objid " + // "AND a.attrelid = c1.oid AND d.refobjsubid = a.attnum"; query = @"SELECT a.attname AS column_name, substring(pg_get_expr(ad.adbin, c.oid) " + @"FROM '[\'""]+(.+?)[\'""]+') AS seq_name " + "FROM pg_class c, pg_namespace n, pg_attribute a, pg_attrdef ad " + "WHERE n.nspname = '" + this.Table.Schema + "' AND c.relname = '" + this.Table.Name + "' " + "AND c.relnamespace = n.oid " + "AND a.attrelid = c.oid AND a.atthasdef = true " + "AND ad.adrelid = c.oid AND ad.adnum = a.attnum " + @"AND pg_get_expr(ad.adbin, c.oid) LIKE 'nextval(%'"; DataTable seqData = new DataTable(); adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(seqData); DataRowCollection rows = seqData.Rows; if (rows.Count > 0) { string colName; for (int i = 0; i < rows.Count; i++) { colName = rows[i]["column_name"] as string; PostgreSQL8Column col = this[colName] as PostgreSQL8Column; col._isAutoKey = true; query = "SELECT min_value, increment_by FROM \"" + rows[i]["seq_name"] + "\""; adapter = new NpgsqlDataAdapter(query, cn); DataTable autokeyData = new DataTable(); adapter.Fill(autokeyData); Int64 a; a = (Int64)autokeyData.Rows[0]["min_value"]; col._autoInc = Convert.ToInt32(a); a = (Int64)autokeyData.Rows[0]["increment_by"]; col._autoSeed = Convert.ToInt32(a); } } cn.Close(); } catch { if (cn != null) { if (cn.State == ConnectionState.Open) { cn.Close(); } } } }