public DataTable GetIndexes(string tableName) { DataTable metaData = new DataTable(); //metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_SCHEMA", 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_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("INDEX_NAME", Type.GetType("System.String")); //metaData.Columns.Add("UNIQUE", Type.GetType("System.String")); //metaData.Columns.Add("CLUSTERED", Type.GetType("System.String")); //metaData.Columns.Add("TYPE", Type.GetType("System.String")); //metaData.Columns.Add("FILL_FACTOR", Type.GetType("System.String")); //metaData.Columns.Add("INITIAL_SIZE", Type.GetType("System.String")); //metaData.Columns.Add("NULLS", Type.GetType("System.String")); //metaData.Columns.Add("SORT_BOOKMARKS", Type.GetType("System.String")); //metaData.Columns.Add("AUTO_UPDATE", Type.GetType("System.String")); //metaData.Columns.Add("NULL_COLLATION", Type.GetType("System.String")); //metaData.Columns.Add("COLLATION", Type.GetType("System.String")); //metaData.Columns.Add("CARDINALITY", Type.GetType("System.String")); //metaData.Columns.Add("PAGES", Type.GetType("System.String")); //metaData.Columns.Add("FILTER_CONDITION", Type.GetType("System.String")); //metaData.Columns.Add("INTEGRATED", Type.GetType("System.String")); 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")); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); IVistaDBTableStructure tblStructure = db.TableStructure(tableName); foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes) { string[] pks = indexInfo.KeyExpression.Split(','); int index = 0; foreach (string colName in pks) { metaData.Rows.Add(new object[] { GetDatabaseName(), tblStructure.Name, GetDatabaseName(), indexInfo.Name, indexInfo.Unique, indexInfo.KeyStructure[index++].Descending ? 2 : 1, colName }); } } return(metaData); }
public DataTable GetForeignKeys(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("PK_TABLE_NAME", 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("ORDINAL", Type.GetType("System.String")); //metaData.Columns.Add("UPDATE_RULE", Type.GetType("System.String")); //metaData.Columns.Add("DELETE_RULE", Type.GetType("System.String")); //metaData.Columns.Add("PK_NAME", Type.GetType("System.String")); //metaData.Columns.Add("FK_NAME", Type.GetType("System.String")); //metaData.Columns.Add("DEFERRABILITY", Type.GetType("System.String")); 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")); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); IVistaDBTableStructure tblStructure = db.TableStructure(tableName); foreach (IVistaDBRelationshipInformation relInfo in tblStructure.ForeignKeys) { //string[] fColumns = relInfo.ForeignKey.Split(new char[] {';'}); //string[] pColumns = relInfo.p.ForeignKey.pprimaryKey.Split(new char[] {';'}); //for(int i = 0; i < fColumns.GetLength(0); i++) { metaData.Rows.Add(new object[] { GetDatabaseName(), DBNull.Value, DBNull.Value, DBNull.Value, tblStructure.Name, relInfo.PrimaryTable, 0, relInfo.Name, "PKEY", "", //pColumns[i], "" }); //fColumns[i]}); } } return(metaData); }
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 DataTable GetTables() { DataTable metaData = new DataTable(); //metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String")); metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_TYPE", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_GUID", Type.GetType("System.String")); metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_PROPID", Type.GetType("System.String")); //metaData.Columns.Add("DATE_CREATED", Type.GetType("System.String")); //metaData.Columns.Add("DATE_MODIFIED", Type.GetType("System.String")); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); foreach (string table in tables) { IVistaDBTableStructure tblStructure = db.TableStructure(table); DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_NAME"] = tblStructure.Name; row["DESCRIPTION"] = tblStructure.Description; } return(metaData); }
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 PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table) { using (IVistaDBDatabase vistaDb = GetDatabase(connectionString)) { if (vistaDb == null) { return(null); } IVistaDBTableSchema vistaTable = vistaDb.TableSchema(table.Name); if (vistaTable == null) { return(null); } foreach (IVistaDBIndexInformation vistaIndex in vistaTable.Indexes) { if (!vistaIndex.Primary) { continue; } var key = new PrimaryKeySchema( table, vistaIndex.Name, vistaIndex.KeyExpression.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries)); return(key); } } return(null); }
private IVistaDBDatabase GetDatabase(string connectionString) { var builder = new VistaDBConnectionStringBuilder(connectionString); IVistaDBDatabase database = _dda.OpenDatabase(builder.DataSource, builder.OpenMode, builder.Password); return(database); }
DataTable IMyMetaPlugin.GetTables(string database) { DataTable metaData = new DataTable(); IVistaDBDatabase db = null; try { metaData = context.CreateTablesDataTable(); db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); foreach (string table in tables) { IVistaDBTableSchema tblStructure = db.TableSchema(table); DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_NAME"] = tblStructure.Name; row["DESCRIPTION"] = tblStructure.Description; } } finally { if (db != null) { db.Close(); } } return(metaData); }
private IVistaDBDatabase GetDatabase(string connectionString) { var builder = new VistaDBConnectionStringBuilder(connectionString); var password = !String.IsNullOrWhiteSpace(builder.Password) ? builder.Password : null; IVistaDBDatabase database = _dda.OpenDatabase(builder.DataSource, builder.OpenMode, password); return(database); }
public string GetDatabaseName(string connectionString) { using (IVistaDBDatabase vistaDb = GetDatabase(connectionString)) { //HACK: name is the file name? return(Path.GetFileNameWithoutExtension(vistaDb.Name)); } }
public void Open(string connectionString) { if (this.db == null) { this.connectionString = connectionString; this.dda = VistaDBEngine.Connections.OpenDDA(); if (!System.IO.File.Exists(connectionString)) { // create database this.db = this.dda.CreateDatabase(connectionString, true, null, 0, 0, false); #region table structures IVistaDBTableSchema tbSchema = this.db.NewTable("InnerRelations"); tbSchema.AddColumn("id", VistaDBType.BigInt); tbSchema.DefineColumnAttributes("id", false, false, false, false, null, null); tbSchema.AddColumn("nParentId", VistaDBType.BigInt); tbSchema.DefineColumnAttributes("nParentId", false, false, false, false, null, null); tbSchema.AddColumn("aParentId", VistaDBType.BigInt); tbSchema.DefineColumnAttributes("aParentId", false, false, false, false, null, null); tbSchema.AddColumn("qualifierId", VistaDBType.BigInt); tbSchema.DefineColumnAttributes("qualifierId", false, false, false, false, null, null); this.tbInnerRelations = this.db.CreateTable(tbSchema, false, false); tbSchema = this.db.NewTable("RootRelations"); tbSchema.AddColumn("id", VistaDBType.BigInt); tbSchema.DefineColumnAttributes("id", false, false, false, false, null, null); tbSchema.AddColumn("terminalValue", VistaDBType.VarChar); tbSchema.DefineColumnAttributes("terminalValue", false, false, false, false, null, null); tbSchema.AddColumn("qualifierId", VistaDBType.BigInt); tbSchema.DefineColumnAttributes("qualifierId", false, false, false, false, null, null); this.tbRootRelations = this.db.CreateTable(tbSchema, false, false); #endregion #region indexes this.tbInnerRelations.CreateIdentity("id", "1", "1"); this.tbInnerRelations.CreateIndex("findById", "id", true, true); this.tbInnerRelations.CreateIndex("findByParents", "nParentId;aParentId", false, true); this.tbInnerRelations.CreateIndex("findByAParent", "aParentId", false, false); this.tbInnerRelations.CreateIndex("findByQualifier", "qualifierId", false, false); this.tbRootRelations.CreateIdentity("id", "-1", "-1"); this.tbRootRelations.CreateIndex("findById", "id", true, true); this.tbRootRelations.CreateIndex("findByTerminalValue", "terminalValue", false, true); this.tbRootRelations.CreateIndex("findByQualifier", "qualifierId", false, false); #endregion } else { // open existing database this.db = this.dda.OpenDatabase(connectionString, VistaDBDatabaseOpenMode.ExclusiveReadWrite, null); this.tbInnerRelations = this.db.OpenTable("InnerRelations", false, false); this.tbRootRelations = this.db.OpenTable("RootRelations", false, false); } } }
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()); }
public void Close() { if (this.db != null) { this.tbRootRelations.Close(); this.tbInnerRelations.Close(); this.db.Close(); this.db = null; this.dda.Dispose(); this.dda = null; } }
List <string> IPlugin.GetPrimaryKeyColumns(string database, string table) { List <string> primaryKeys = new List <string>(); IVistaDBDatabase db = null; try { using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString)) { db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn)); } IVistaDBTableSchema tblStructure = db.TableSchema(table); string[] pks = null; if (tblStructure.Indexes.Contains("PrimaryKey")) { pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';'); } else { foreach (IVistaDBIndexInformation pk in tblStructure.Indexes) { if (pk.Primary) { pks = pk.KeyExpression.Split(';'); break; } } } if (pks != null) { foreach (string pkColName in pks) { primaryKeys.Add(pkColName); } } } finally { if (db != null) { db.Close(); } } return(primaryKeys); }
DataTable IPlugin.GetTableIndexes(string database, string table) { DataTable metaData = new DataTable(); IVistaDBDatabase db = null; try { metaData = context.CreateIndexesDataTable(); using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString)) { db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn)); } ArrayList tables = db.EnumTables(); IVistaDBTableSchema tblStructure = db.TableSchema(table); foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes) { string[] pks = indexInfo.KeyExpression.Split(';'); int index = 0; foreach (string colName in pks) { DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_CATALOG"] = GetDatabaseName(); row["TABLE_NAME"] = tblStructure.Name; row["INDEX_CATALOG"] = GetDatabaseName(); row["INDEX_NAME"] = indexInfo.Name; row["UNIQUE"] = indexInfo.Unique; row["COLLATION"] = indexInfo.KeyStructure[index++].Descending ? 2 : 1; row["COLUMN_NAME"] = colName; } } } finally { if (db != null) { db.Close(); } } return(metaData); }
public void SetupServer() { string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\Data\\coolstorage.vdb3")); if (File.Exists(path)) { File.Delete(path); } IVistaDBDatabase database = VistaDBEngine.Connections.OpenDDA().CreateDatabase(path, false, null, 0, 0, false); database.Close(); CSConfig.SetDB(new CSDataProviderVistaDB(@"Data Source=" + path)); CSDatabase.ExecuteNonQuery(_sqlCreateTables); }
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()); }
public DataTable GetDatabases() { DataTable metaData = new DataTable(); metaData.Columns.Add("CATALOG_NAME", Type.GetType("System.String")); metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String")); metaData.Columns.Add("SCHEMA_NAME", Type.GetType("System.String")); metaData.Columns.Add("SCHEMA_OWNER", Type.GetType("System.String")); metaData.Columns.Add("DEFAULT_CHARACTER_SET_CATALOG", Type.GetType("System.String")); metaData.Columns.Add("DEFAULT_CHARACTER_SET_SCHEMA", Type.GetType("System.String")); metaData.Columns.Add("DEFAULT_CHARACTER_SET_NAME", Type.GetType("System.String")); DataRow row = metaData.NewRow(); metaData.Rows.Add(row); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, password); row["CATALOG_NAME"] = GetDatabaseName(); row["DESCRIPTION"] = db.Description; return(metaData); }
DataTable IPlugin.GetTables(string database) { DataTable metaData = new DataTable(); IVistaDBDatabase db = null; try { metaData = context.CreateTablesDataTable(); using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString)) { db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn)); } IVistaDBTableNameCollection tables = db.GetTableNames(); foreach (string table in tables) { IVistaDBTableSchema tblStructure = db.TableSchema(table); DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_NAME"] = tblStructure.Name; row["DESCRIPTION"] = tblStructure.Description; } } finally { if (db != null) { db.Close(); } } return(metaData); }
DataTable IPlugin.GetTableColumns(string database, string table) { DataTable metaData = new DataTable(); IVistaDBDatabase db = null; try { metaData = context.CreateColumnsDataTable(); using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString)) { db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn)); } ArrayList tables = db.EnumTables(); IVistaDBTableSchema tblStructure = db.TableSchema(table); foreach (IVistaDBColumnAttributes c in tblStructure) { string colName = c.Name; string def = ""; if (tblStructure.Defaults.Contains(colName)) { def = tblStructure.Defaults[colName].Expression; } int width = c.MaxLength; //c.ColumnWidth; int dec = 0; //c.ColumnDecimals; int length = 0; int octLength = width; IVistaDBIdentityInformation identity = null; if (tblStructure.Identities.Contains(colName)) { identity = tblStructure.Identities[colName]; } string[] pks = null; if (tblStructure.Indexes.Contains("PrimaryKey")) { pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';'); } else { foreach (IVistaDBIndexInformation pk in tblStructure.Indexes) { if (pk.Primary) { pks = pk.KeyExpression.Split(';'); break; } } } System.Collections.Hashtable pkCols = null; if (pks != null) { pkCols = new Hashtable(); foreach (string pkColName in pks) { pkCols[pkColName] = true; } } switch (c.Type) { case VistaDBType.Char: case VistaDBType.NChar: case VistaDBType.NText: case VistaDBType.NVarChar: case VistaDBType.Text: case VistaDBType.VarChar: length = width; width = 0; dec = 0; break; case VistaDBType.Money: case VistaDBType.Float: case VistaDBType.Decimal: case VistaDBType.Real: break; default: width = 0; dec = 0; break; } DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_NAME"] = tblStructure.Name; row["COLUMN_NAME"] = c.Name; row["ORDINAL_POSITION"] = c.RowIndex; row["IS_NULLABLE"] = c.AllowNull; row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true; row["COLUMN_DEFAULT"] = def; row["IS_AUTO_KEY"] = identity == null ? false : true; row["AUTO_KEY_SEED"] = 1; row["AUTO_KEY_INCREMENT"] = identity == null ? 0 : Convert.ToInt32(identity.StepExpression); row["TYPE_NAME"] = c.Type.ToString(); row["NUMERIC_PRECISION"] = width; row["NUMERIC_SCALE"] = dec; row["CHARACTER_MAXIMUM_LENGTH"] = length; row["CHARACTER_OCTET_LENGTH"] = octLength; row["DESCRIPTION"] = c.Description; string type = (string)row["TYPE_NAME"]; row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, length, (short)width, (short)dec); if (c.Type == VistaDBType.Timestamp) { row["IS_COMPUTED"] = true; } row["IS_CONCURRENCY"] = type == "Timestamp" ? true : false; } } finally { if (db != null) { db.Close(); } } return(metaData); }
public void OpenDatabase(bool readOnly) { System.Diagnostics.Debug.Assert(dda == null, "The database is already open"); dda = VistaDBEngine.Connections.OpenDDA(); db = dda.OpenDatabase(Path.Combine(basePath, "game.vdb3"), readOnly ? VistaDBDatabaseOpenMode.NonexclusiveReadOnly : VistaDBDatabaseOpenMode.ExclusiveReadWrite, null); packTable = db.OpenTable("Pack", false, readOnly); markerTable = db.OpenTable("Marker", false, readOnly); cardTable = db.OpenTable("Card", false, readOnly); cardModelCache = new Dictionary<Guid, CardModel>(); setCache = new Dictionary<Guid, Set>(); }
public void CloseDatabase() { packTable.Dispose(); packTable = null; cardTable.Dispose(); cardTable = null; markerTable.Dispose(); markerTable = null; db.Dispose(); db = null; dda.Dispose(); dda = null; cardModelCache = null; setCache = null; }
public void GetDBCompexInfo(string dbName) { try { using (IVistaDBDDA conn = VistaDBEngine.Connections.OpenDDA()) { using (IVistaDBDatabase db = conn.OpenDatabase(dbName, VistaDBDatabaseOpenMode.ExclusiveReadOnly, null)) { Console.WriteLine("METAINFORMATION FOR " + dbName + " DATABASE"); Console.WriteLine("-------------------------------------------"); Console.WriteLine("Table Description: " + db.Description); Console.WriteLine("Row count: " + db.RowCount.ToString()); Console.WriteLine("PageSize: " + db.PageSize.ToString()); Console.WriteLine("Open mode: " + db.Mode.ToString()); Console.WriteLine("Culture: " + db.Culture.ToString()); Console.WriteLine("Case Sensitive: " + db.CaseSensitive.ToString()); ArrayList tables = db.EnumTables(); foreach (string table in tables) { IVistaDBTableStructure tblStructure = db.TableStructure(table); Console.WriteLine("============================================"); Console.WriteLine("Table " + table); Console.WriteLine("============================================"); //columns Console.WriteLine("COLUMNS:"); foreach (IVistaDBColumnAttributes colInfo in tblStructure) { Console.WriteLine("\t" + colInfo.Name); //use colInfo for getting columns metadata } //indexes Console.WriteLine("INDEXES:"); foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes) { Console.WriteLine("\t" + indexInfo.Name); //use indexInfo for getting columns metadata } //constraints Console.WriteLine("CONSTRAINTS:"); foreach (IVistaDBConstraintInformation constrInfo in tblStructure.Constraints) { Console.WriteLine("\t" + constrInfo.Name); //use constrInfo for getting columns metadata } //foreignKeys Console.WriteLine("FOREIGN KEYS:"); foreach (IVistaDBRelationshipInformation relInfo in tblStructure.ForeignKeys) { Console.WriteLine("\t" + relInfo.Name); //use foreignKeys for getting columns metadata } } } } } catch (VistaDBException ex) { } catch { } }
private static void CreatePackTable(IVistaDBDatabase gameDb) { var packSchema = gameDb.NewTable("Pack"); packSchema.AddColumn("id", VistaDBType.UniqueIdentifier); packSchema.DefineColumnAttributes("id", false, false, false, false, null, null); packSchema.AddColumn("name", VistaDBType.NVarChar, 200); packSchema.DefineColumnAttributes("name", false, false, false, false, null, null); packSchema.AddColumn("setId", VistaDBType.UniqueIdentifier); packSchema.DefineColumnAttributes("setId", false, false, false, false, null, null); packSchema.AddColumn("xml", VistaDBType.NText); using (var packTable = gameDb.CreateTable(packSchema, false, false)) { packTable.CreateIndex("PackPK", "id", true, true); packTable.CreateIndex("SetIX", "setId", false, false); packTable.CreateForeignKey("PackSetFK", "setId", "Set", VistaDBReferentialIntegrity.Cascade, VistaDBReferentialIntegrity.Cascade, null); } }
DataTable IPlugin.GetForeignKeys(string database, string tableName) { DataTable metaData = new DataTable(); IVistaDBDatabase db = null; try { metaData = context.CreateForeignKeysDataTable(); using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString)) { db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn)); } ArrayList tables = db.EnumTables(); foreach (string table in tables) { IVistaDBTableSchema tblStructure = db.TableSchema(table); //================================================================== // This works around a change that was made to the VistaDB provider // It's ugly, we know //================================================================== IEnumerator enumerator = null; if (useOldForeignKeyWay) { enumerator = tblStructure.ForeignKeys.GetEnumerator(); } else { try { enumerator = tblStructure.ForeignKeys.Values.GetEnumerator(); } catch { enumerator = tblStructure.ForeignKeys.GetEnumerator(); useOldForeignKeyWay = true; } } // Okay, now that the version issues are over we just use the 'enumerator' while (enumerator.MoveNext()) { IVistaDBRelationshipInformation relInfo = enumerator.Current as IVistaDBRelationshipInformation; if (relInfo.ForeignTable != tableName && relInfo.PrimaryTable != tableName) { continue; } string fCols = relInfo.ForeignKey; string pCols = String.Empty; string primaryTbl = relInfo.PrimaryTable; string pkName = ""; using (IVistaDBTableSchema pkTableStruct = db.TableSchema(primaryTbl)) { foreach (IVistaDBIndexInformation idxInfo in pkTableStruct.Indexes) { if (!idxInfo.Primary) { continue; } pkName = idxInfo.Name; pCols = idxInfo.KeyExpression; break; } } string [] fColumns = fCols.Split(';'); string [] pColumns = pCols.Split(';'); for (int i = 0; i < fColumns.GetLength(0); i++) { DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["PK_TABLE_CATALOG"] = GetDatabaseName(); row["PK_TABLE_SCHEMA"] = DBNull.Value; row["FK_TABLE_CATALOG"] = DBNull.Value; row["FK_TABLE_SCHEMA"] = DBNull.Value; row["FK_TABLE_NAME"] = tblStructure.Name; row["PK_TABLE_NAME"] = relInfo.PrimaryTable; row["ORDINAL"] = 0; row["FK_NAME"] = relInfo.Name; row["PK_NAME"] = pkName; row["PK_COLUMN_NAME"] = pColumns[i]; row["FK_COLUMN_NAME"] = fColumns[i]; row["UPDATE_RULE"] = relInfo.UpdateIntegrity; row["DELETE_RULE"] = relInfo.DeleteIntegrity; } } } } finally { if (db != null) { db.Close(); } } return(metaData); }
DataTable IMyMetaPlugin.GetForeignKeys(string database, string tableName) { DataTable metaData = new DataTable(); IVistaDBDatabase db = null; try { metaData = context.CreateForeignKeysDataTable(); db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); foreach (string table in tables) { IVistaDBTableSchema tblStructure = db.TableSchema(table); foreach (IVistaDBRelationshipInformation relInfo in tblStructure.ForeignKeys) { if (relInfo.ForeignTable != tableName && relInfo.PrimaryTable != tableName) { continue; } string fCols = relInfo.ForeignKey; string pCols = String.Empty; string primaryTbl = relInfo.PrimaryTable; string pkName = ""; using (IVistaDBTableSchema pkTableStruct = db.TableSchema(primaryTbl)) { foreach (IVistaDBIndexInformation idxInfo in pkTableStruct.Indexes) { if (!idxInfo.Primary) { continue; } pkName = idxInfo.Name; pCols = idxInfo.KeyExpression; break; } } string [] fColumns = fCols.Split(';'); string [] pColumns = pCols.Split(';'); for (int i = 0; i < fColumns.GetLength(0); i++) { DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["PK_TABLE_CATALOG"] = GetDatabaseName(); row["PK_TABLE_SCHEMA"] = DBNull.Value; row["FK_TABLE_CATALOG"] = DBNull.Value; row["FK_TABLE_SCHEMA"] = DBNull.Value; row["FK_TABLE_NAME"] = tblStructure.Name; row["PK_TABLE_NAME"] = relInfo.PrimaryTable; row["ORDINAL"] = 0; row["FK_NAME"] = relInfo.Name; row["PK_NAME"] = pkName; row["PK_COLUMN_NAME"] = pColumns[i]; row["FK_COLUMN_NAME"] = fColumns[i]; row["UPDATE_RULE"] = relInfo.UpdateIntegrity; row["DELETE_RULE"] = relInfo.DeleteIntegrity; } } } } finally { if (db != null) { db.Close(); } } return(metaData); }
public DataTable GetColumns(string tableName) { DataTable metaData = new DataTable(); //metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_GUID", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_PROPID", Type.GetType("System.String")); //metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_FLAGS", Type.GetType("System.String")); //metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.String")); //metaData.Columns.Add("DATA_TYPE", Type.GetType("System.String")); //metaData.Columns.Add("TYPE_GUID", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.String")); //metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.String")); //metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.String")); //metaData.Columns.Add("DATETIME_PRECISION", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_SET_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_SET_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("CHARACTER_SET_NAME", Type.GetType("System.String")); //metaData.Columns.Add("COLLATION_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("COLLATION_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("COLLATION_NAME", Type.GetType("System.String")); //metaData.Columns.Add("DOMAIN_CATALOG", Type.GetType("System.String")); //metaData.Columns.Add("DOMAIN_SCHEMA", Type.GetType("System.String")); //metaData.Columns.Add("DOMAIN_NAME", Type.GetType("System.String")); //metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_LCID", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_COMPFLAGS", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_SORTID", Type.GetType("System.String")); //metaData.Columns.Add("COLUMN_TDSCOLLATION", Type.GetType("System.String")); //metaData.Columns.Add("IS_COMPUTED", Type.GetType("System.String")); //metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.String")); //metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.String")); //metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.String")); 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")); IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, ""); ArrayList tables = db.EnumTables(); IVistaDBTableStructure tblStructure = db.TableStructure(tableName); foreach (IVistaDBColumnAttributes c in tblStructure) { string colName = c.Name; string def = ""; if (tblStructure.Defaults.Contains(colName)) { def = tblStructure.Defaults[colName].Expression; } int width = c.MaxLength; //c.ColumnWidth; int dec = 0; //c.ColumnDecimals; int length = 0; int octLength = width; IVistaDBIdentityInformation identity = null; if (tblStructure.Identities.Contains(colName)) { identity = tblStructure.Identities[colName]; } string[] pks = null; if (tblStructure.Indexes.Contains("PrimaryKey")) { pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(','); } else { foreach (IVistaDBIndexInformation pk in tblStructure.Indexes) { if (pk.Primary) { pks = pk.KeyExpression.Split(','); break; } } } System.Collections.Hashtable pkCols = null; if (pks != null) { pkCols = new Hashtable(); foreach (string pkColName in pks) { pkCols[pkColName] = true; } } switch (c.Type) { case VistaDBType.Char: case VistaDBType.NChar: case VistaDBType.NText: case VistaDBType.NVarchar: case VistaDBType.Text: case VistaDBType.Varchar: length = width; width = 0; dec = 0; break; case VistaDBType.Currency: case VistaDBType.Double: case VistaDBType.Decimal: case VistaDBType.Single: break; default: width = 0; dec = 0; break; } metaData.Rows.Add(new object[] { tblStructure.Name, c.Name, c.RowIndex, c.AllowNull, def == string.Empty ? false : true, def, identity == null ? false : true, 1, identity == null ? 0 : Convert.ToInt32(identity.StepExpression), c.Type.ToString(), width, dec, length, octLength, c.Description, pkCols == null ? false : pkCols.Contains(colName) }); } return(metaData); }
/// <summary> /// Turns off auto record identity generation so the record id from the master database can be used. /// </summary> /// <param name="tableName">SQL table name</param> /// <param name="columnName">the identity column</param> /// <param name="enable"></param> public void SetIdentityInsert(string tableName, string columnName, bool enable) { // VistaDB DDA code IVistaDBTable tbl = null; try { IVistaDBDDA DDAObj = VistaDBEngine.Connections.OpenDDA(); string vistaDbPath = (string)AppDomain.CurrentDomain.GetData("DataDirectory"); Debug.WriteLine("FormsSql.SetIdentityInsert VistaDbPath: " + vistaDbPath); // IVistaDBDatabase db = DDAObj.OpenDatabase(System.AppDomain.CurrentDomain.BaseDirectory + "App_Data\\forms.vdb5", VistaDB.VistaDBDatabaseOpenMode.NonexclusiveReadWrite, "aj80995"); IVistaDBDatabase db = DDAObj.OpenDatabase(vistaDbPath + @"\forms.vdb5", VistaDB.VistaDBDatabaseOpenMode.NonexclusiveReadWrite, "P@ssword1"); tbl = db.OpenTable(tableName, false, false); } catch (Exception xcptn) { Debug.WriteLine("FormsSql.SetIdentityInsert Exception: " + xcptn.Message); throw xcptn; } // table1s.AddColumn("ID", VistaDBType.Int); // table1s.DefineColumnAttributes("ID", false, false, false, false, null, null); // table1s.AddColumn("COLINT", VistaDBType.Int); // table1s.DefineColumnAttributes("COLINT", false, false, false, false, null, null); if (tbl.EnforceIdentities) { if (enable) { tbl.DropIdentity(columnName); } else { if (tableName == "def_FormResults") { int min = getMin(columnName, tableName); int next = min - 1; tbl.CreateIdentity(columnName, next.ToString(), "-1"); } else { int max = getMax(columnName, tableName); int next = max + 1; tbl.CreateIdentity(columnName, next.ToString(), "1"); } } } else { if (!enable) { if (tableName == "def_FormResults") { int min = getMin(columnName, tableName); int next = min - 1; tbl.CreateIdentity(columnName, next.ToString(), "-1"); } else { int max = getMax(columnName, tableName); int next = max + 1; tbl.CreateIdentity(columnName, next.ToString(), "1"); } } } // tbl.CreateIndex("Primary", "ID", true, true); // tbl.CreateIndex("idxDate", "COLDATETIME", false, false); tbl.Close(); tbl.Dispose(); tbl = null; }
/// <summary> /// Returns a page of errors from the databse in descending order /// of logged time. /// </summary> public override int GetErrors(int pageIndex, int pageSize, IList errorEntryList) { if (pageIndex < 0) { throw new ArgumentOutOfRangeException("pageIndex", pageIndex, null); } if (pageSize < 0) { throw new ArgumentOutOfRangeException("pageSize", pageSize, null); } VistaDBConnectionStringBuilder builder = new VistaDBConnectionStringBuilder(_connectionString); // Use the VistaDB Direct Data Access objects IVistaDBDDA ddaObjects = VistaDBEngine.Connections.OpenDDA(); // Create a connection object to a VistaDB database IVistaDBDatabase vistaDB = ddaObjects.OpenDatabase(_databasePath, builder.OpenMode, builder.Password); // Open the table IVistaDBTable elmahTable = vistaDB.OpenTable("ELMAH_Error", false, true); elmahTable.ActiveIndex = "IX_ELMAH_Error_App_Time_Id"; if (errorEntryList != null) { if (!elmahTable.EndOfTable) { // move to the correct record elmahTable.First(); elmahTable.MoveBy(pageIndex * pageSize); int rowsProcessed = 0; // Traverse the table to get the records we want while (!elmahTable.EndOfTable && rowsProcessed < pageSize) { rowsProcessed++; string id = Convert.ToString(elmahTable.Get("ErrorId").Value, CultureInfo.InvariantCulture); Error error = new Error(); error.ApplicationName = (string)elmahTable.Get("Application").Value; error.HostName = (string)elmahTable.Get("Host").Value; error.Type = (string)elmahTable.Get("Type").Value; error.Source = (string)elmahTable.Get("Source").Value; error.Message = (string)elmahTable.Get("Message").Value; error.User = (string)elmahTable.Get("User").Value; error.StatusCode = (int)elmahTable.Get("StatusCode").Value; error.Time = ((DateTime)elmahTable.Get("TimeUtc").Value).ToLocalTime(); errorEntryList.Add(new ErrorLogEntry(this, id, error)); // move to the next record elmahTable.Next(); } } } return(Convert.ToInt32(elmahTable.RowCount)); }