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();
}
}
}
}
