protected internal TableCollection GetTables(Database database)
{
TableCollection tables = new TableCollection();
DataAccessProviderFactory dataAccessProviderFactory = new DataAccessProviderFactory(Server.ProviderType);
IDbConnection connection = dataAccessProviderFactory.CreateConnection(Server.ConnectionString);
if(connection.State == ConnectionState.Closed)
{
connection.Open();
}
connection.ChangeDatabase(database.Name);
DataSet ds;
if (Server.ProviderType != DataProviderType.Oracle)
{
connection.ChangeDatabase(database.Name);
ds = TableSchema(dataAccessProviderFactory, connection);
}
else
{
ds = TableSchema(dataAccessProviderFactory, connection, database);
}
connection.Close();
/* Changed by Ferhat */
if (ds.Tables.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
tables.Add(CreateTable(database, row));
}
}
return tables;
}
protected ColumnStrategy()
{
_dataAccessProvider = new DataAccessProviderFactory(Server.ProviderType);
_connection = _dataAccessProvider.CreateConnection(Server.ConnectionString);
_columns = new ColumnCollection();
_keys = new KeyCollection();
}
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 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 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 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 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 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 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;
}
private static int GetTableId(string tablename, DataAccessProviderFactory dataProvider, IDbConnection connection)
{
IDbCommand sqlCommand = dataProvider.CreateCommand(@"SELECT c.oid," +
@"n.nspname, " +
@"c.relname " +
@"FROM pg_catalog.pg_class c " +
@"LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " +
@"WHERE pg_catalog.pg_table_is_visible(c.oid) " +
@"AND c.relname ~ '^" + tablename + "$' " +
@"ORDER BY 2, 3;", connection);
return Convert.ToInt32(sqlCommand.ExecuteScalar());
}
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 internal DatabaseCollection GetDatabases()
{
DatabaseCollection databases = new DatabaseCollection();
DataAccessProviderFactory dataAccessProviderFactory = new DataAccessProviderFactory(Server.ProviderType);
IDbConnection connection = dataAccessProviderFactory.CreateConnection(Server.ConnectionString);
DataSet ds = DatabaseSchema(dataAccessProviderFactory, connection);
foreach (DataRow row in ds.Tables[0].Rows)
{
databases.Add(CreateDatabase(row, databases));
}
return databases;
}
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;
}
/* Add by Ferhat */
protected internal TableCollection GetViews(Database database)
{
var tables = new TableCollection();
var dataAccessProviderFactory = new DataAccessProviderFactory(Server.ProviderType);
var connection = dataAccessProviderFactory.CreateConnection(Server.ConnectionString);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
connection.ChangeDatabase(database.Name);
var ds = ViewSchema(dataAccessProviderFactory, connection);
connection.Close();
if (ds.Tables.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
tables.Add(CreateTable(database, row));
}
}
return tables;
}
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;
}
protected override DataSet ViewSchema(DataAccessProviderFactory dataAccessProvider, IDbConnection connection)
{
return new DataSet();
}
private void uiTestConnectionButton_Click(object sender, EventArgs e)
{
if (uiProviderTypeSelection.SelectedIndex >= 0)
{
DataAccessProviderFactory dataAccessProvider = new DataAccessProviderFactory(((DataAccessProviderInfo) uiProviderTypeSelection.SelectedItem).ProviderType);
IDbConnection connection = dataAccessProvider.CreateConnection(uiConnectionStringComboList.Text.Trim());
try
{
connection.Open();
MessageBox.Show("Connection Succesfull");
}
catch (Exception e1)
{
MessageBox.Show(e1.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
}
protected virtual DataSet TableSchema(DataAccessProviderFactory dataAccessProvider,IDbConnection connection, Database database)
{
throw new NotImplementedException();
}
protected abstract DataSet ViewSchema(DataAccessProviderFactory dataAccessProvider, IDbConnection connection);
protected abstract DataSet KeySchema(Table table, DataAccessProviderFactory dataAccessProvider, IDbConnection connection);