public DataTable LoadTables(string cn)
{
DataTable metaData = new DataTable();
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
try
{
Provider.VistaDB.VistaDBDatabase db = OpenDatabase(cn);
db.Connect();
string[] list = new string[0];
db.EnumTables(ref list);
if (list != null)
{
foreach (string tableName in list)
{
Provider.VistaDB.VistaDBTable table = new Provider.VistaDB.VistaDBTable(db, tableName);
table.Open();
metaData.Rows.Add(new object[] { table.TableName, "" });
table.Close();
}
}
db.Close();
}
catch {}
return(metaData);
}
public DataTable LoadIndexes(string cn, string databaseName, string tableName)
{
DataTable metaData = new DataTable();
metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("INDEX_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("INDEX_NAME", Type.GetType("System.String"));
metaData.Columns.Add("UNIQUE", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLLATION", Type.GetType("System.Int16"));
metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
try
{
Provider.VistaDB.VistaDBDatabase db = OpenDatabase(cn);
db.Connect();
Provider.VistaDB.VistaDBTable table = new Provider.VistaDB.VistaDBTable(db, tableName);
table.Open();
string[] indexes = new string[0];
table.EnumIndexes(out indexes);
bool active;
int orderIndex;
bool unique;
bool primary;
bool descending;
string keyExp;
if (indexes != null)
{
foreach (string index in indexes)
{
table.GetIndex(index, out active, out orderIndex, out unique, out primary, out descending, out keyExp);
if (orderIndex != 0) // && keyExp != "PrimaryKey" && keyExp != "PRIMARY_KEY")
{
if (keyExp != null && keyExp != string.Empty)
{
string[] columns = keyExp.Split(new char[] { ';' });
foreach (string colName in columns)
{
metaData.Rows.Add(new object[]
{
databaseName,
tableName,
databaseName,
index,
unique,
descending ? 2 : 1,
colName
});
}
}
}
}
}
table.Close();
db.Close();
}
catch {}
return(metaData);
}
public DataTable LoadColumns(string cn, string tableName)
{
DataTable metaData = new DataTable();
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.Int32"));
metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String"));
metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.Boolean"));
metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.Int32"));
metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.Int32"));
metaData.Columns.Add("DATA_TYPE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.Int32"));
metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.Int32"));
metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.Int32"));
metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.Int32"));
metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
metaData.Columns.Add("IS_PRIMARY_KEY", Type.GetType("System.Boolean"));
try
{
Provider.VistaDB.VistaDBDatabase db = OpenDatabase(cn);
db.Connect();
Provider.VistaDB.VistaDBTable table = new Provider.VistaDB.VistaDBTable(db, tableName);
table.Open();
for (int ordinal = 0; ordinal < table.ColumnCount(); ordinal++)
{
Provider.VistaDB.VistaDBColumn c = table.Columns[ordinal];
bool b = false;
string colName = c.Name;
string def = table.GetDefaultValue(colName, out b);
int width = c.ColumnWidth;
int dec = c.ColumnDecimals;
int length = 0;
int octLength = width;
if (c.Identity)
{
// While I'll see their point this is not typically how it is done
def = "";
}
string type = c.VistaDBType.ToString();
switch (type)
{
case "Character":
case "Varchar":
length = width;
width = 0;
dec = 0;
break;
case "Currency":
case "Double":
break;
default:
width = 0;
dec = 0;
break;
}
metaData.Rows.Add(new object[]
{
table.TableName,
c.Name,
ordinal,
c.AllowNull,
def == string.Empty ? false : true,
def,
c.Identity,
1,
(int)c.IdentityStep,
c.VistaDBType.ToString(),
width,
dec,
length,
octLength,
c.ColumnDescription,
c.PrimaryKey
});
}
table.Close();
db.Close();
}
catch {}
return(metaData);
}
public DataTable LoadForeignKeys(string cn, string databaseName, string tableName)
{
DataTable metaData = new DataTable();
metaData.Columns.Add("PK_TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("PK_TABLE_SCHEMA", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_SCHEMA", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("ORDINAL", Type.GetType("System.Int32"));
metaData.Columns.Add("FK_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_COLUMN_NAME", Type.GetType("System.String"));
metaData.Columns.Add("FK_COLUMN_NAME", Type.GetType("System.String"));
try
{
Provider.VistaDB.VistaDBDatabase db = OpenDatabase(cn);
db.Connect();
Provider.VistaDB.VistaDBTable table = new Provider.VistaDB.VistaDBTable(db, tableName);
table.Open();
string[] fkeys = new string[0];
table.EnumForeignKeys(out fkeys);
string foreignKey = "";
string primaryTable = "";
string primaryKey = "";
if (fkeys != null)
{
foreach (string fkey in fkeys)
{
table.GetForeignKey(fkey, out foreignKey, out primaryTable, out primaryKey);
string[] fColumns = foreignKey.Split(new char[] { ';' });
string[] pColumns = primaryKey.Split(new char[] { ';' });
for (int i = 0; i < fColumns.GetLength(0); i++)
{
metaData.Rows.Add(new object[]
{
databaseName,
DBNull.Value,
DBNull.Value,
DBNull.Value,
tableName,
primaryTable,
0,
fkey,
"PKEY",
pColumns[i],
fColumns[i]
}
);
}
}
table.Close();
db.Close();
}
}
catch {}
return(metaData);
}
internal TriggerActiveCollection(VistaDBTable parent_)
{
parent = parent_;
}
internal ColumnCollection(VistaDBTable parent_)
{
parent = parent_;
}
/// <summary>
/// Save table data to another table
/// </summary>
/// <param name="table">VistaDBTable object</param>
/// <returns>True if succed, otherwise false</returns>
public bool SaveToDatabase(VistaDBTable table)
{
if(table == null || !table.Opened || !this.Opened)
throw new VistaDBException(VistaDBErrorCodes.TableNotOpened);
table.DeleteAllRows(false);
lock(this.database.SyncRoot)
{
VistaDBAPI.ivdb_Select((ushort)this.tableId);
return VistaDBAPI.ivdb_ImportToTable(table.TableID, null);
}
}
internal ConstraintActiveCollection(VistaDBTable parent_)
{
parent = parent_;
}
/// <summary>
/// Load data from the table
/// </summary>
/// <param name="table">VistaDBTable object</param>
/// <returns>True if succed, otherwise false</returns>
public bool LoadFromTable(VistaDBTable table)
{
if(table == null || !table.Opened || !this.Opened)
throw new VistaDBException(VistaDBErrorCodes.TableNotOpened);
lock(this.database.SyncRoot)
{
VistaDBAPI.ivdb_Select((ushort)table.TableID);
return VistaDBAPI.ivdb_ImportToTable(this.tableId, null);
}
}
public DataTable LoadIndexes(string cn, string databaseName, string tableName)
{
DataTable metaData = new DataTable();
metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("INDEX_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("INDEX_NAME", Type.GetType("System.String"));
metaData.Columns.Add("UNIQUE", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLLATION", Type.GetType("System.Int16"));
metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
try
{
Provider.VistaDB.VistaDBDatabase db = OpenDatabase(cn);
db.Connect();
Provider.VistaDB.VistaDBTable table = new Provider.VistaDB.VistaDBTable(db, tableName);
table.Open();
string[] indexes = new string[0];
table.EnumIndexes(out indexes);
bool active;
int orderIndex;
bool unique;
bool primary;
bool descending;
string keyExp;
if(indexes != null)
{
foreach(string index in indexes)
{
table.GetIndex(index, out active, out orderIndex, out unique, out primary, out descending, out keyExp);
if(orderIndex != 0) // && keyExp != "PrimaryKey" && keyExp != "PRIMARY_KEY")
{
if(keyExp != null && keyExp != string.Empty)
{
string[] columns = keyExp.Split(new char[] { ';' } );
foreach(string colName in columns)
{
metaData.Rows.Add(new object[]
{
databaseName,
tableName,
databaseName,
index,
unique,
descending ? 2 : 1,
colName
} );
}
}
}
}
}
table.Close();
db.Close();
}
catch {}
return metaData;
}
/// <summary>
/// Creates new table and load data in one database, which is copy of the table from another database
/// </summary>
/// <param name="table">VistaDBTable object</param>
/// <param name="loadData">Load data (true) or no (false)</param>
/// <param name="importIndexes">If True, then indexes will be imported from the source table</param>
/// <returns>True is succed, otherwise false</returns>
public bool CreateFromTable(VistaDBTable table, bool loadData, bool importIndexes)
{
if(this.database == null || !this.database.Connected)
throw new VistaDBException(VistaDBErrorCodes.DatabaseNotOpened);
if(table == null || !table.Opened)
throw new VistaDBException(VistaDBErrorCodes.TableNotOpened);
if(tableId > 0)
this.Close();
short id;
string name;
lock(database.SyncRoot)
{
VistaDBAPI.ivdb_SelectDb(this.database.DatabaseId);
name = (this.tableName == null || this.tableName.Length == 0) ? table.TableName : this.tableName;
id = VistaDBAPI.ivdb_CreateFromTable(table.TableID, name, loadData, importIndexes, null);
if(id > 0)
{
this.tableName = name;
this.tableId = id;
this.CreateColumnInfo();
return true;
}
else
return false;
}
}
public DataTable LoadColumns(string cn, string tableName)
{
DataTable metaData = new DataTable();
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.Int32"));
metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String"));
metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.Boolean"));
metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.Int32"));
metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.Int32"));
metaData.Columns.Add("DATA_TYPE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.Int32"));
metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.Int32"));
metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.Int32"));
metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.Int32"));
metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
metaData.Columns.Add("IS_PRIMARY_KEY", Type.GetType("System.Boolean"));
try
{
Provider.VistaDB.VistaDBDatabase db = OpenDatabase(cn);
db.Connect();
Provider.VistaDB.VistaDBTable table = new Provider.VistaDB.VistaDBTable(db, tableName);
table.Open();
for(int ordinal = 0; ordinal < table.ColumnCount(); ordinal++)
{
Provider.VistaDB.VistaDBColumn c = table.Columns[ordinal];
bool b = false;
string colName = c.Name;
string def = table.GetDefaultValue(colName, out b);
int width = c.ColumnWidth;
int dec = c.ColumnDecimals;
int length = 0;
int octLength = width;
if(c.Identity)
{
// While I'll see their point this is not typically how it is done
def = "";
}
string type = c.VistaDBType.ToString();
switch(type)
{
case "Character":
case "Varchar":
length = width;
width = 0;
dec = 0;
break;
case "Currency":
case "Double":
break;
default:
width = 0;
dec = 0;
break;
}
metaData.Rows.Add(new object[]
{
table.TableName,
c.Name,
ordinal,
c.AllowNull,
def == string.Empty ? false : true,
def,
c.Identity,
1,
(int)c.IdentityStep,
c.VistaDBType.ToString(),
width,
dec,
length,
octLength,
c.ColumnDescription,
c.PrimaryKey
} );
}
table.Close();
db.Close();
}
catch {}
return metaData;
}
public DataTable LoadForeignKeys(string cn, string databaseName, string tableName)
{
DataTable metaData = new DataTable();
metaData.Columns.Add("PK_TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("PK_TABLE_SCHEMA", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_SCHEMA", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("ORDINAL", Type.GetType("System.Int32"));
metaData.Columns.Add("FK_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_COLUMN_NAME", Type.GetType("System.String"));
metaData.Columns.Add("FK_COLUMN_NAME", Type.GetType("System.String"));
try
{
Provider.VistaDB.VistaDBDatabase db = OpenDatabase(cn);
db.Connect();
Provider.VistaDB.VistaDBTable table = new Provider.VistaDB.VistaDBTable(db, tableName);
table.Open();
string[] fkeys = new string[0];
table.EnumForeignKeys(out fkeys);
string foreignKey = "";
string primaryTable = "";
string primaryKey = "";
if(fkeys != null)
{
foreach(string fkey in fkeys)
{
table.GetForeignKey(fkey, out foreignKey, out primaryTable, out primaryKey);
string[] fColumns = foreignKey.Split(new char[] {';'});
string[] pColumns = primaryKey.Split(new char[] {';'});
for(int i = 0; i < fColumns.GetLength(0); i++)
{
metaData.Rows.Add(new object[]
{
databaseName,
DBNull.Value,
DBNull.Value,
DBNull.Value,
tableName,
primaryTable,
0,
fkey,
"PKEY",
pColumns[i],
fColumns[i]}
);
}
}
table.Close();
db.Close();
}
}
catch {}
return metaData;
}
public DataTable LoadTables(string cn)
{
DataTable metaData = new DataTable();
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
try
{
Provider.VistaDB.VistaDBDatabase db = OpenDatabase(cn);
db.Connect();
string[] list = new string[0];
db.EnumTables(ref list);
if(list != null)
{
foreach(string tableName in list)
{
Provider.VistaDB.VistaDBTable table = new Provider.VistaDB.VistaDBTable(db, tableName);
table.Open();
metaData.Rows.Add(new object[] { table.TableName, "" } );
table.Close();
}
}
db.Close();
}
catch {}
return metaData;
}
/// <summary>
/// Unregister table in list
/// </summary>
/// <param name="table"></param>
internal void UnregisterTable(VistaDBTable table)
{
if(this.tableList != null)
{
int index = tableList.IndexOf(table);
if( index < 0 )
return;
tableList.RemoveAt(index);
}
}
/// <summary>
/// Register table in list
/// </summary>
/// <param name="table">Table object</param>
internal void RegisterTable(VistaDBTable table)
{
if( tableList.IndexOf(table) >= 0 )
return;
tableList.Add(table);
}