/// <summary>
/// Get all the arguments for a stored procedures (or all sprocs)
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="connection">The open connection.</param>
/// <returns></returns>
protected virtual DataTable StoredProcedureArguments(string storedProcedureName, DbConnection connection)
{
//different collections here- we could just if(IsOracle)
string collectionName = ProcedureParametersCollectionName;
if (!SchemaCollectionExists(connection, collectionName))
{
collectionName = "Arguments";
}
if (ProviderType == SqlType.MySql)
{
collectionName = "Procedure Parameters";
}
else if (ProviderType == SqlType.Oracle)
{
collectionName = "Arguments"; //Oracle, assume packages
}
if (!SchemaCollectionExists(connection, collectionName))
{
return(CreateDataTable(ProcedureParametersCollectionName));
}
string[] restrictions = SchemaRestrictions.ForRoutine(connection, collectionName, storedProcedureName);
var dt = connection.GetSchema(collectionName, restrictions);
dt.TableName = ProcedureParametersCollectionName;
return(dt);
}
private DataTable RunGetSchema(DbConnection connection, string collectionName, string tableName)
{
string[] restrictions = SchemaRestrictions.ForTable(connection, collectionName, tableName);
try
{
return(connection.GetSchema(collectionName, restrictions));
}
catch (DbException exception)
{
//Postgresql throws this nasty error with a restriction. We'll carry on.
Console.WriteLine("Provider returned error for " + collectionName + ": " + exception.Message);
return(CreateDataTable(collectionName));
}
catch (SqlNullValueException exception)
{
//MySQL can't run this without a table (it does a SHOW INDEX FROM table so you get the above error)
Console.WriteLine("Provider returned error for " + collectionName + ": " + exception.Message);
return(CreateDataTable(collectionName));
}
catch (ArgumentException exception)
{
//Intersystems requires table name
Console.WriteLine("Provider returned error for " + collectionName + ": " + exception.Message);
return(CreateDataTable(collectionName));
}
}
/// <summary>
/// Finds the foreign keys.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="connection">The connection.</param>
/// <returns></returns>
protected virtual DataTable ForeignKeys(string tableName, DbConnection connection)
{
string collectionName = ForeignKeysCollectionName;
if (!SchemaCollectionExists(connection, collectionName))
{
collectionName = "Foreign Keys";
if (!SchemaCollectionExists(connection, collectionName))
{
collectionName = "Foreign_Keys";
if (!SchemaCollectionExists(connection, collectionName))
{
return(CreateDataTable(ForeignKeysCollectionName));
}
}
}
if (!SchemaCollectionExists(connection, collectionName))
{
return(CreateDataTable(ForeignKeysCollectionName));
}
string[] restrictions = SchemaRestrictions.ForTable(connection, collectionName, tableName);
try
{
var dt = connection.GetSchema(collectionName, restrictions);
dt.TableName = ForeignKeysCollectionName;
return(dt);
}
catch (ArgumentException)
{
//may not be allowed without tablename
return(CreateDataTable(ForeignKeysCollectionName));
}
}
/// <summary>
/// Retrieve a generic collection.
/// </summary>
/// <param name="collectionName">Name of the collection.</param>
/// <param name="connection">The connection.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
protected DataTable GenericCollection(string collectionName, DbConnection connection, string tableName)
{
if (SchemaCollectionExists(connection, collectionName))
{
return(connection.GetSchema(collectionName, SchemaRestrictions.ForTable(connection, collectionName, tableName)));
}
return(CreateDataTable(collectionName));
}
/// <summary>
/// Get all the stored procedures (owner required for Oracle- otherwise null).
/// </summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
protected virtual DataTable StoredProcedures(DbConnection connection)
{
string collectionName = ProceduresCollectionName;
if (!SchemaCollectionExists(connection, collectionName))
{
return(CreateDataTable(collectionName));
}
string[] restrictions = SchemaRestrictions.ForOwner(connection, collectionName);
return(connection.GetSchema(collectionName, restrictions));
}
/// <summary>
/// Releases unmanaged and - optionally - managed resources
/// </summary>
/// <param name="disposing"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
// free managed resources
if (_restrictions != null)
{
_restrictions.Dispose();
_restrictions = null;
}
}
}
/// <summary>
/// DataTable of all tables for a specific owner
/// </summary>
public DataTable Tables()
{
string collectionName = TablesCollectionName;
using (DbConnection conn = Factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
string[] restrictions = SchemaRestrictions.ForOwner(conn, collectionName);
return(conn.GetSchema(collectionName, restrictions));
}
}
/// <summary>
/// Get all the packages (Oracle only concept- returns empty DataTable for others)
/// </summary>
public DataTable Packages()
{
using (DbConnection conn = Factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
string collectionName = PackagesCollectionName;
if (!SchemaCollectionExists(conn, collectionName))
{
return(CreateDataTable(collectionName));
}
string[] restrictions = SchemaRestrictions.ForOwner(conn, collectionName);
return(conn.GetSchema(collectionName, restrictions));
}
}
/// <summary>
/// Gets the sequences (if supported, eg Oracle)
/// </summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
protected virtual DataTable Sequences(DbConnection connection)
{
string collectionName = SequencesCollectionName;
if (!SchemaCollectionExists(connection, collectionName))
{
collectionName = "Generators"; //Firebird calls sequences "Generators"
}
if (!SchemaCollectionExists(connection, collectionName))
{
return(CreateDataTable(SequencesCollectionName));
}
string[] restrictions = SchemaRestrictions.ForOwner(connection, collectionName);
var dt = connection.GetSchema(collectionName, restrictions);
dt.TableName = SequencesCollectionName;
return(dt);
}
/// <summary>
/// Finds the foreign key columns.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="connection">The connection.</param>
/// <returns></returns>
protected virtual DataTable ForeignKeyColumns(string tableName, DbConnection connection)
{
string collectionName = ForeignKeyColumnsCollectionName;
if (!SchemaCollectionExists(connection, collectionName))
{
return(CreateDataTable(collectionName));
}
string[] restrictions = SchemaRestrictions.ForTable(connection, collectionName, tableName);
var dt = connection.GetSchema(collectionName, restrictions);
if (dt.TableName != collectionName) //devart postgresql returns a table called IndexColumns.
{
dt.TableName = collectionName;
}
return(dt);
}
/// <summary>
/// Does table exist?
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentException">TableName is null or empty;tableName</exception>
public bool TableExists(string tableName)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentException("TableName is null or empty", "tableName");
}
string collectionName = TablesCollectionName;
using (DbConnection conn = Factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
string[] restrictions = SchemaRestrictions.ForTable(conn, TablesCollectionName, tableName);
var dt = conn.GetSchema(collectionName, restrictions);
//could have same name in different schemas
return(dt.Rows.Count > 0);
}
}
/// <summary>
/// DataTable of all tables for a specific owner
/// </summary>
/// <returns>Datatable with columns OWNER, TABLE_NAME, TYPE</returns>
public DataTable Views()
{
using (DbConnection conn = Factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
string collectionName = ViewsCollectionName;
if (!SchemaCollectionExists(conn, collectionName))
{
collectionName = TablesCollectionName;
}
if (!SchemaCollectionExists(conn, collectionName))
{
return(CreateDataTable(collectionName)); //doesn't exist in SqlServerCe
}
string[] restrictions = SchemaRestrictions.ForOwner(conn, collectionName);
return(conn.GetSchema(collectionName, restrictions));
}
}
/// <summary>
/// Gets the primary keys
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="connection">The connection.</param>
/// <returns></returns>
protected virtual DataTable PrimaryKeys(string tableName, DbConnection connection)
{
string collectionName = PrimaryKeysCollectionName;
if (!SchemaCollectionExists(connection, collectionName))
{
return(CreateDataTable(collectionName));
}
string[] restrictions = SchemaRestrictions.ForTable(connection, collectionName, tableName);
try
{
return(connection.GetSchema(collectionName, restrictions));
}
catch (ArgumentException)
{
//may not be allowed without tablename
return(CreateDataTable(collectionName));
}
}
/// <summary>
/// Get all the arguments for a package (or all packs)
/// Package is only for Oracle - for SqlServer it's all sprocs
/// </summary>
public DataTable PackageStoredProcedureArguments(string packageName)
{
using (DbConnection conn = Factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
//for SqlServer the restriction doesn't apply
string collectionName = ProcedureParametersCollectionName;
if (ProviderType == SqlType.Oracle)
{
collectionName = "Arguments"; //Oracle, we assume you mean packages
}
if (!SchemaCollectionExists(conn, collectionName))
{
return(CreateDataTable(ProcedureParametersCollectionName));
}
string[] restrictions = SchemaRestrictions.ForSpecific(conn, collectionName, packageName, "PACKAGENAME");
var dt = conn.GetSchema(collectionName, restrictions);
dt.TableName = ProcedureParametersCollectionName;
return(dt);
}
}
/// <summary>
/// Get the columns using GetSchema. Override to get additional stuff from Oracle.
/// </summary>
protected virtual DataTable Columns(string tableName, DbConnection connection)
{
string[] restrictions = SchemaRestrictions.ForTable(connection, ColumnsCollectionName, tableName);
return(connection.GetSchema(ColumnsCollectionName, restrictions));
}