protected override DataSet TableSchema(DataAccessProviderFactory dataProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
IDbCommand sqlString = dataProvider.CreateCommand("SELECT tablename FROM pg_tables WHERE schemaname = 'public'",connection);
sqlString.CommandType = CommandType.Text;
IDbDataAdapter da = dataProvider.CreateDataAdapter();
da.SelectCommand = sqlString;
da.Fill(ds);
return ds;
}
protected override DataSet DatabaseSchema(DataAccessProviderFactory dataProviderFactory, IDbConnection connection)
{
DataSet ds = new DataSet();
IDbCommand sqlString = dataProviderFactory.CreateCommand("SELECT datname FROM pg_database",connection);
sqlString.CommandType = CommandType.Text;
IDbDataAdapter da = dataProviderFactory.CreateDataAdapter();
da.SelectCommand = sqlString;
da.Fill(ds);
return ds;
}
protected override DataSet TableSchema(DataAccessProviderFactory dataAccessProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
IDbCommand sqlString = dataAccessProvider.CreateCommand("show tables", connection);
sqlString.CommandType = CommandType.Text;
IDbDataAdapter da = dataAccessProvider.CreateDataAdapter();
da.SelectCommand = sqlString;
da.Fill(ds);
return ds;
}
protected override DataSet KeySchema(Table table, DataAccessProviderFactory dataAccessProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
IDbCommand sqlSp = dataAccessProvider.CreateCommand("show index from "+ table.Name,connection);
sqlSp.CommandType = CommandType.Text;
IDbDataAdapter da = dataAccessProvider.CreateDataAdapter();
da.SelectCommand = sqlSp;
da.Fill(ds);
return ds;
}
protected override DataSet TableSchema(DataAccessProviderFactory dataProvider, IDbConnection connection, Database database)
{
var ds = new DataSet();
var sqlString = dataProvider.CreateCommand("SELECT OWNER, TABLE_NAME FROM all_tables where OWNER = '" + database.Name + "'",connection);
sqlString.CommandType = CommandType.Text;
var da = dataProvider.CreateDataAdapter();
da.SelectCommand = sqlString;
da.Fill(ds);
return ds;
}
protected override DataSet KeySchema(Table table, DataAccessProviderFactory dataProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
IDbCommand sqlCommand = dataProvider.CreateCommand("SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)" +
"FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i " +
"WHERE c.oid = '" + GetTableId(table.Name, dataProvider, connection) + "' AND c.oid = i.indrelid AND i.indexrelid = c2.oid " +
"ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",connection);
sqlCommand.CommandType = CommandType.Text;
IDbDataAdapter da = dataProvider.CreateDataAdapter();
da.SelectCommand = sqlCommand;
da.Fill(ds);
return ds;
}
protected override DataSet TableSchema(DataAccessProviderFactory dataAccessProvider, IDbConnection connection)
{
var ds = new DataSet();
var sqlQry = dataAccessProvider.CreateCommand("SELECT s.name AS [SCHEMA], t.name AS [NAME], t.type AS type " +
"FROM sys.tables t " +
"INNER JOIN sys.schemas s ON t.schema_id = s.schema_id " +
"ORDER BY s.name, t.name",connection);
sqlQry.CommandType = CommandType.Text;
var da = dataAccessProvider.CreateDataAdapter();
da.SelectCommand = sqlQry;
da.Fill(ds);
return ds;
}
protected override DataSet DatabaseSchema(DataAccessProviderFactory dataAccessProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
IDbCommand sqlSp = dataAccessProvider.CreateCommand("SELECT name AS DATABASE_NAME, 0 AS DATABASE_SIZE, NULL AS REMARKS FROM master.dbo.sysdatabases WHERE HAS_DBACCESS(name) = 1 ORDER BY name", connection);
sqlSp.CommandType = CommandType.Text;
// IDbCommand sqlSp = dataAccessProvider.CreateCommand("sp_databases", connection);
// sqlSp.CommandType = CommandType.StoredProcedure;
IDbDataAdapter da = dataAccessProvider.CreateDataAdapter();
da.SelectCommand = sqlSp;
da.Fill(ds);
return ds;
}
protected override DataSet ViewSchema(DataAccessProviderFactory dataAccessProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
IDbCommand sqlSp = dataAccessProvider.CreateCommand("sp_tables", connection);
sqlSp.CommandType = CommandType.StoredProcedure;
IDbDataParameter param = dataAccessProvider.CreateParameter();
param.ParameterName = "@table_type";
param.Value = "'VIEW'";
sqlSp.Parameters.Add(param);
IDbDataAdapter da = dataAccessProvider.CreateDataAdapter();
da.SelectCommand = sqlSp;
da.Fill(ds);
return ds;
}
protected override DataSet ColumnSchema(Table table, DataAccessProviderFactory dataAccessProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
IDbCommand sqlSp = dataAccessProvider.CreateCommand("sp_columns",connection);
sqlSp.CommandType = CommandType.StoredProcedure;
IDbDataParameter param = dataAccessProvider.CreateParameter();
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
param.ParameterName = "@table_name";
param.Value = table.Name;
sqlSp.Parameters.Add(param);
IDbDataAdapter da = dataAccessProvider.CreateDataAdapter();
da.SelectCommand = sqlSp;
da.Fill(ds);
return ds;
}
protected override DataSet ColumnSchema(Table table, DataAccessProviderFactory dataProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
int tableId = GetTableId(table.Name,dataProvider, connection);
IDbCommand sqlCommand = dataProvider.CreateCommand("SELECT a.attname,t.typname as atttype, " +
"(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d " +
"WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)as attdef, a.attlen, a.atttypmod,a.attnotnull, a.attnum " +
"FROM pg_catalog.pg_attribute a, pg_catalog.pg_type t " +
"WHERE a.attrelid = '" + tableId + "' AND a.attnum > 0 AND NOT a.attisdropped " +
"AND t.oid = a.atttypid " +
"ORDER BY a.attnum",connection);
sqlCommand.CommandType = CommandType.Text;
IDbDataAdapter da = dataProvider.CreateDataAdapter();
da.SelectCommand = sqlCommand;
da.Fill(ds);
return ds;
}
protected override DataSet KeySchema(Table table, DataAccessProviderFactory dataAccessProvider, IDbConnection connection)
{
DataSet dsPkeys = new DataSet();
IDbCommand sqlSp = dataAccessProvider.CreateCommand("sp_pkeys",connection);
sqlSp.CommandType = CommandType.StoredProcedure;
IDbDataParameter param = dataAccessProvider.CreateParameter();
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
param.ParameterName = "@table_name";
param.Value = table.Name;
sqlSp.Parameters.Add(param);
IDbDataParameter schemaParameter = dataAccessProvider.CreateParameter();
schemaParameter.Direction = ParameterDirection.Input;
schemaParameter.DbType = DbType.String;
schemaParameter.ParameterName = "@table_owner";
schemaParameter.Value = table.Schema;
sqlSp.Parameters.Add(schemaParameter);
IDbDataAdapter da = dataAccessProvider.CreateDataAdapter();
da.SelectCommand = sqlSp;
da.Fill(dsPkeys);
foreach(DataRow row in dsPkeys.Tables[0].Rows)
{
Key key = new Key();
key.Name = row["PK_NAME"].ToString();
key.ColumnName = row["COLUMN_NAME"].ToString();
key.IsPrimary = true;
_Keys.Add(key);
}
DataSet ds = new DataSet();
sqlSp = dataAccessProvider.CreateCommand("sp_fkeys",connection);
sqlSp.CommandType = CommandType.StoredProcedure;
param = dataAccessProvider.CreateParameter();
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
param.ParameterName = "@pktable_name";
param.Value = table.Name;
sqlSp.Parameters.Add(param);
da = dataAccessProvider.CreateDataAdapter();
da.SelectCommand = sqlSp;
da.Fill(ds);
ds.Merge(dsPkeys);
return ds;
}
protected override DataSet KeySchema(Table table, DataAccessProviderFactory dataProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
String schemaQuery = "SELECT acc.COLUMN_NAME, " +
"ac.CONSTRAINT_NAME, " +
"ac.CONSTRAINT_TYPE " +
"FROM ALL_CONS_COLUMNS acc " +
"JOIN ALL_CONSTRAINTS ac " +
"ON ac.OWNER = acc.OWNER " +
"AND ac.TABLE_NAME = acc.TABLE_NAME " +
"AND ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME " +
"where acc.owner = '" + table.ParentDatabase.Name + "' " +
"and acc.Table_NAME = '" + table.Name + "'";
IDbCommand sqlCommand = dataProvider.CreateCommand(schemaQuery,connection);
sqlCommand.CommandType = CommandType.Text;
IDbDataAdapter da = dataProvider.CreateDataAdapter();
da.SelectCommand = sqlCommand;
da.Fill(ds);
return ds;
}
protected override DataSet ColumnSchema(Table table, DataAccessProviderFactory dataProvider, IDbConnection connection)
{
DataSet ds = new DataSet();
String schemaQuery = "SELECT atc.OWNER, " +
"atc.TABLE_NAME, " +
"atc.COLUMN_NAME, " +
"atc.DATA_TYPE, " +
"atc.DATA_LENGTH, " +
"atc.DATA_PRECISION, " +
"atc.DATA_SCALE, " +
"atc.NULLABLE, " +
"atc.COLUMN_ID, " +
"acc.CONSTRAINT_NAME, " +
"ac.CONSTRAINT_TYPE, " +
"ac.R_CONSTRAINT_NAME, " +
"ac.INDEX_NAME " +
"FROM ALL_TAB_COLUMNS atc " +
"LEFT OUTER JOIN ALL_CONS_COLUMNS acc " +
"ON acc.OWNER = atc.OWNER " +
"AND acc.TABLE_NAME = atc.TABLE_NAME " +
"AND acc.COLUMN_NAME = atc.COLUMN_NAME " +
"LEFT OUTER JOIN ALL_CONSTRAINTS ac " +
"ON ac.OWNER = acc.OWNER " +
"AND ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME " +
"WHERE atc.OWNER = '" + table.ParentDatabase.Name + "' " +
"AND atc.TABLE_NAME = '" + table.Name + "' " +
"ORDER BY TABLE_NAME asc";
IDbCommand sqlCommand = dataProvider.CreateCommand(schemaQuery,connection);
sqlCommand.CommandType = CommandType.Text;
IDbDataAdapter da = dataProvider.CreateDataAdapter();
da.SelectCommand = sqlCommand;
da.Fill(ds);
return ds;
}