public TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
var tables = new List <TableSchema>();
var extendedProperties = new List <ExtendedProperty>();
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(tables.ToArray());
}
foreach (string tableName in vistaDb.GetTableNames())
{
IVistaDBTableSchema table = vistaDb.TableSchema(tableName);
if (table == null)
{
continue;
}
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, table.Description));
var tableSchema = new TableSchema(database, table.Name, String.Empty, DateTime.MinValue, extendedProperties.ToArray());
tables.Add(tableSchema);
}
}
return(tables.ToArray());
}
public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
{
var commands = new List <CommandSchema>();
var extendedProperties = new List <ExtendedProperty>();
const string sql = "SELECT PROC_NAME, PROC_DESCRIPTION FROM sp_stored_procedures()";
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row[0].ToString();
string description = row[1].ToString();
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, description));
var command = new CommandSchema(database, name, string.Empty, DateTime.MinValue, extendedProperties.ToArray());
commands.Add(command);
}
}
return(commands.ToArray());
}
public TableKeySchema[] GetTableKeys(string connectionString, TableSchema table)
{
var keys = new List <TableKeySchema>();
var foreignColumns = new List <string>();
var primaryColumns = new List <string>();
var extendedProperties = new List <ExtendedProperty>();
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(keys.ToArray());
}
IVistaDBTableSchema vistaTable = vistaDb.TableSchema(table.Name);
if (vistaTable == null)
{
return(keys.ToArray());
}
foreach (IVistaDBRelationshipInformation vistaKey in vistaTable.ForeignKeys)
{
foreignColumns.Clear();
foreignColumns.AddRange(vistaKey.ForeignKey.Split(
new[] { ';' }, StringSplitOptions.RemoveEmptyEntries));
IVistaDBTableSchema vistaPrimaryTable = vistaDb.TableSchema(vistaKey.PrimaryTable);
if (vistaPrimaryTable == null)
{
continue;
}
primaryColumns.Clear();
//find primary key index
foreach (IVistaDBIndexInformation i in vistaPrimaryTable.Indexes)
{
if (i.Primary)
{
primaryColumns.AddRange(i.KeyExpression.Split(
new[] { ';' }, StringSplitOptions.RemoveEmptyEntries));
}
}
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, vistaKey.Description));
var key = new TableKeySchema(
table.Database, vistaKey.Name,
foreignColumns.ToArray(),
string.Empty, vistaKey.ForeignTable,
primaryColumns.ToArray(),
string.Empty, vistaKey.PrimaryTable,
extendedProperties.ToArray());
keys.Add(key);
}
}
return(keys.ToArray());
}
public ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
{
//Get only the 'tables' of type 'view'. The 'Tables' catlog also includes
//system tables, and regular tables
DataTable dataTable = GetSchemaData(connectionString,
Views,
null /*restrictions[0] - catalog*/,
null /*restrictions[1] - unused*/,
null /*restrictions[2] - table*/,
ViewType /*restrictions[3] - type*/);
var extendedProperties = new List <ExtendedProperty>();
var views = new ViewSchema[dataTable.Rows.Count];
int viewIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
string name = (string)row[TableNameColumn];
bool isUpdatable = !row.IsNull("IS_UPDATABLE") && (bool)row["IS_UPDATABLE"];
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, ""));
extendedProperties.Add(ExtendedProperty.Readonly("CS_IsUpdatable", isUpdatable));
var view = new ViewSchema(database, name, string.Empty, DateTime.MinValue,
extendedProperties.ToArray());
views[viewIndex++] = view;
}
return(views);
}
public ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
{
var views = new List <ViewSchema>();
var extendedProperties = new List <ExtendedProperty>();
const string sql = "SELECT VIEW_NAME, DESCRIPTION, IS_UPDATABLE FROM GetViews()";
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row[0].ToString();
string description = row[1].ToString();
bool isUpdatable = (bool)(row[2] ?? true);
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, description));
extendedProperties.Add(ExtendedProperty.Readonly("CS_IsUpdatable", isUpdatable));
var view = new ViewSchema(database, name, string.Empty, DateTime.MinValue, extendedProperties.ToArray());
views.Add(view);
}
}
return(views.ToArray());
}
public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
{
var columns = new List <ColumnSchema>();
var extendedProperties = new List <ExtendedProperty>();
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(columns.ToArray());
}
IVistaDBTableSchema vistaTable = vistaDb.TableSchema(table.Name);
if (vistaTable == null)
{
return(columns.ToArray());
}
foreach (IVistaDBColumnAttributes vistaColumn in vistaTable)
{
string nativeType = vistaColumn.Type.ToString();
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, vistaColumn.Description));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.ObjectID, vistaColumn.UniqueId));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.DefaultValue, ""));
extendedProperties.Add(ExtendedProperty.Readonly("CS_CodePage", vistaColumn.CodePage));
extendedProperties.Add(ExtendedProperty.Readonly("CS_Encrypted", vistaColumn.Encrypted));
extendedProperties.Add(ExtendedProperty.Readonly("CS_Packed", vistaColumn.Packed));
extendedProperties.Add(ExtendedProperty.Readonly("CS_ReadOnly", vistaColumn.ReadOnly));
bool isIdentity = false;
foreach (IVistaDBIdentityInformation identity in vistaTable.Identities)
{
isIdentity = (identity.ColumnName == vistaColumn.Name);
if (isIdentity)
{
break;
}
}
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.IsIdentity, isIdentity));
var column = new ColumnSchema(table, vistaColumn.Name, GetDbType(nativeType),
nativeType, vistaColumn.MaxLength, 0, 0, vistaColumn.AllowNull,
extendedProperties.ToArray());
columns.Add(column);
}
}
return(columns.ToArray());
}
private ExtendedProperty[] ConvertToExtendedProperties(ADODB.Properties properties)
{
ArrayList extendedProperties = new ArrayList();
for (int i = 0; i < properties.Count; i++)
{
ExtendedProperty extendedProperty = new ExtendedProperty(properties[i].Name, properties[i].Value, GetDbType(properties[i].Type));
extendedProperties.Add(extendedProperty);
}
return((ExtendedProperty[])extendedProperties.ToArray(typeof(ExtendedProperty)));
}
public ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view)
{
DataTable dt = GetSchemaData(connectionString,
Columns,
null /*restrictions[0] - catalog*/,
null /*restrictions[1] - unused*/,
view.Name /*restrictions[2] - table*/,
null /*restrictions[3] - column*/);
var extendedProperties = new List <ExtendedProperty>();
var columns = new ViewColumnSchema[dt.Rows.Count];
int columnIndex = 0;
foreach (DataRow dr in dt.Rows)
{
string name = (string)dr[ColumnsNameColumn];
string nativeType = dr.IsNull(ColumnsTypeColumn) ? "text" : (string)dr[ColumnsTypeColumn];
DbType dbType = DbTypeFromType(connectionString, nativeType);
bool allowDBNull = dr.IsNull(ColumnsNullableColumn) || (bool)dr[ColumnsNullableColumn];
int size = dr.IsNull(ColumnsSize) ? 0 : (int)dr[ColumnsSize];
int precision = dr.IsNull(ColumnsPrecision) ? 0 : (int)dr[ColumnsPrecision];
int scale = dr.IsNull(ColumnsScale) ? 0 : (int)dr[ColumnsScale];
int ordinalPosition = dr.IsNull("ORDINAL_POSITION") ? 0 : (int)dr["ORDINAL_POSITION"];
string edmType = dr.IsNull("EDM_TYPE") ? "String" : (string)dr["EDM_TYPE"];
bool isAutoIncrement = !dr.IsNull("AUTOINCREMENT") && (bool)dr["AUTOINCREMENT"];
bool isUnique = !dr.IsNull("UNIQUE") && (bool)dr["UNIQUE"];
bool hasDefault = !dr.IsNull("COLUMN_HASDEFAULT") && (bool)dr["COLUMN_HASDEFAULT"];
string columnDefault = dr.IsNull("COLUMN_DEFAULT") ? string.Empty : (string)dr["COLUMN_DEFAULT"];
string collation = dr.IsNull("COLLATION_NAME") ? string.Empty : (string)dr["COLLATION_NAME"];
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, ""));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.ObjectID, ordinalPosition));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.IsIdentity, isAutoIncrement));
extendedProperties.Add(ExtendedProperty.Readonly("CS_IsUnique", isUnique)); // Added for backwards compatibility.
extendedProperties.Add(ExtendedProperty.Readonly("CS_HasDefault", hasDefault)); // Added for backwards compatibility.
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.DefaultValue, columnDefault));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Collation, collation));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.SystemType, edmType));
extendedProperties.Add(ExtendedProperty.Readonly("CS_SQLiteType", edmType)); // Added for backwards compatibility.
var col = new ViewColumnSchema(view, name, dbType, nativeType, size,
Convert.ToByte(precision), scale, allowDBNull,
extendedProperties.ToArray());
columns[columnIndex++] = col;
}
return(columns);
}
public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table)
{
var indexes = new List <IndexSchema>();
var memberColumns = new List <string>();
var extendedProperties = new List <ExtendedProperty>();
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(indexes.ToArray());
}
IVistaDBTableSchema vistaTable = vistaDb.TableSchema(table.Name);
if (vistaTable == null)
{
return(indexes.ToArray());
}
foreach (IVistaDBIndexInformation vistaIndex in vistaTable.Indexes)
{
memberColumns.Clear();
foreach (IVistaDBKeyColumn keyColumn in vistaIndex.KeyStructure)
{
memberColumns.Add(vistaTable[keyColumn.RowIndex].Name);
}
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, vistaIndex.Description));
extendedProperties.Add(ExtendedProperty.Readonly("CS_FastTextSearch", vistaIndex.FullTextSearch));
var index = new IndexSchema(table, vistaIndex.Name, vistaIndex.Primary,
vistaIndex.Unique, false,
memberColumns.ToArray(),
extendedProperties.ToArray());
indexes.Add(index);
}
}
return(indexes.ToArray());
}
private ExtendedProperty[] ConvertToExtendedProperties(ADOX.Properties properties)
{
ArrayList extendedProperties = new ArrayList();
string description = "";
for (int i = 0; i < properties.Count; i++)
{
object value;
try
{
value = properties[i].Value;
}
catch (InvalidCastException)
{
//Interface is not supported. This was thrown by trying to load up indexes on the north wind access database.
continue;
}
catch (System.Runtime.InteropServices.COMException)
{
//This gets thrown by the VFP9 ADO provider for older-type FoxPro tables (version 2-2.6a) - the wonderful 0x80004005 - Unspecified error
//TODO: Make sure that this is the provider we're currently using so that we don't eat up exceptions from other types of providers
//NOTE: Maybe add our own expected property that says we've hit this exception
// Reference: http://community.codesmithtools.com/Template_Frameworks/f/67/p/9802/44467.aspx#44467
continue;
}
ExtendedProperty extendedProperty = new ExtendedProperty(properties[i].Name, value, GetDbType(properties[i].Type));
extendedProperties.Add(extendedProperty);
if (extendedProperty.Name == "Description" && extendedProperty.Value != DBNull.Value && extendedProperty.Value != null)
{
description = (string)extendedProperty.Value;
}
}
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.Description, description, DbType.String, PropertyStateEnum.ReadOnly));
return((ExtendedProperty[])extendedProperties.ToArray(typeof(ExtendedProperty)));
}
public TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
//Get only the 'tables' of type 'table'. The 'Tables' catlog also includes
//system tables, and the views
DataTable dt = GetSchemaData(connectionString,
Tables,
null /*restrictions[0] - catalog*/,
null /*restrictions[1] - unused*/,
null /*restrictions[2] - table*/,
TableType /*restrictions[3] - type*/);
var extendedProperties = new List <ExtendedProperty>();
var tables = new TableSchema[dt.Rows.Count];
int tableIndex = 0;
foreach (DataRow dr in dt.Rows)
{
string name = (string)dr[TableNameColumn];
long tableID = dr.IsNull("TABLE_ID") ? 0 : (long)dr["TABLE_ID"];
string tableType = dr.IsNull("TABLE_TYPE") ? "table" : (string)dr["TABLE_TYPE"];
int rootpage = dr.IsNull("TABLE_ROOTPAGE") ? 0 : (int)dr["TABLE_ROOTPAGE"];
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, ""));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.ObjectID, tableID));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.SystemType, tableType));
extendedProperties.Add(ExtendedProperty.Readonly("CS_TableType", tableType)); // Added for backwards compatibility.
extendedProperties.Add(ExtendedProperty.Readonly("CS_RootPage", rootpage));
var table = new TableSchema(database, name, string.Empty, DateTime.MinValue,
extendedProperties.ToArray());
tables[tableIndex++] = table;
}
return(tables);
}
public ParameterSchema[] GetCommandParameters(string connectionString, CommandSchema command)
{
var parameters = new List <ParameterSchema>();
var extendedProperties = new List <ExtendedProperty>();
string sql = string.Format("SELECT PARAM_ORDER, PARAM_NAME, PARAM_TYPE, IS_PARAM_OUT, DEFAULT_VALUE FROM sp_stored_procedures() WHERE PROC_NAME = '{0}'", command.Name);
using (VistaDBConnection connection = GetConnection(connectionString))
using (var adapter = new VistaDBDataAdapter(sql, connection))
using (var table = new DataTable())
{
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
string name = row["PARAM_NAME"].ToString();
var vistaType = (VistaDBType)(row["PARAM_TYPE"] ?? VistaDBType.Unknown);
var isOut = (bool)(row["IS_PARAM_OUT"] ?? false);
string defaultValue = row["DEFAULT_VALUE"].ToString();
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, string.Empty));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.DefaultValue, defaultValue));
var parameter = new ParameterSchema(command, name,
isOut ? ParameterDirection.InputOutput : ParameterDirection.Input,
GetDbType(vistaType.ToString()),
vistaType.ToString(), 0, 0, 0, true,
extendedProperties.ToArray());
parameters.Add(parameter);
}
}
return(parameters.ToArray());
}
public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)
{
if (schemaObject is TableSchema)
{
TableSchema tableSchema = (TableSchema)schemaObject;
string text = $"select cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class where relname='{tableSchema.Name}';";
List <ExtendedProperty> list = new List <ExtendedProperty>();
using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))
{
npgsqlConnection.Open();
using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection))
{
var comment = npgsqlCommand.ExecuteScalar();
list.Add(ExtendedProperty.Readonly("CS_Description", comment?.ToString()));
}
if (npgsqlConnection.State != ConnectionState.Closed)
{
npgsqlConnection.Close();
}
}
return(list.ToArray());
}
if (schemaObject is ColumnSchema)
{
List <ExtendedProperty> list = new List <ExtendedProperty>();
ColumnSchema columnSchema = schemaObject as ColumnSchema;
string text = $"select pg_get_serial_sequence(a.table_name, a.column_name) as EXTRA,a.COLUMN_DEFAULT,a.data_type,c.DeText as description from information_schema.columns as a left join( select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and pg_attr.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attr.atttypid where pg_class.relname = '{columnSchema.Table.Name}' and pg_constraint.contype = 'p')as b on b.colname = a.column_name left join( select attname, description as DeText from pg_class left join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid = pg_attr.attnum where pg_attr.attnum > 0 and pg_attr.attrelid = pg_class.oid and pg_class.relname = '{columnSchema.Table.Name}')as c on c.attname = a.column_name where table_schema = 'public' and table_name = '{columnSchema.Table.Name}' and COLUMN_NAME = '{columnSchema.Name}' order by ordinal_position; ";
using (NpgsqlConnection npgsqlConnection = new NpgsqlConnection(connectionString))
{
npgsqlConnection.Open();
using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(text, npgsqlConnection))
{
using (IDataReader dataReader = npgsqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dataReader.Read())
{
string text2 = dataReader.IsDBNull(0) ? string.Empty : dataReader.GetString(0).ToLower();
string value = dataReader.IsDBNull(1) ? null : dataReader.GetString(1).ToUpper();
string value2 = dataReader.GetString(2).ToUpper();
string description = dataReader.GetString(3);
bool flag = !string.IsNullOrEmpty(text2);
list.Add(new ExtendedProperty("CS_IsIdentity", flag, columnSchema.DataType));
if (flag)
{
list.Add(new ExtendedProperty("CS_IdentitySeed", 1, columnSchema.DataType));
list.Add(new ExtendedProperty("CS_IdentityIncrement", 1, columnSchema.DataType));
}
list.Add(new ExtendedProperty("CS_Default", value, DbType.String));
list.Add(new ExtendedProperty("CS_SystemType", value2, DbType.String));
list.Add(new ExtendedProperty("CS_Sequence", text2.ToUpper(), DbType.String));
list.Add(ExtendedProperty.Readonly("CS_Description", description?.ToString()));
}
if (!dataReader.IsClosed)
{
dataReader.Close();
}
}
}
if (npgsqlConnection.State != ConnectionState.Closed)
{
npgsqlConnection.Close();
}
}
return(list.ToArray());
}
return(new ExtendedProperty[0]);
}
