} //end method private void LoadSQLServerCE35SchemaTree() { queryBuilder.MetadataContainer.BeginUpdate(); PFDataAccessObjects.PFDatabase db = null; string sql = string.Empty; try { queryBuilder.MetadataContainer.Items.Clear(); //get list of tables and add them to the schema tree db = new PFDataAccessObjects.PFDatabase(PFDataAccessObjects.DatabasePlatform.SQLServerCE35); db.ConnectionString = this.ConnectionString; db.OpenConnection(); sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES"; DataTable datatableList = db.RunQueryDataTable(sql, CommandType.Text); foreach (DataRow row in datatableList.Rows) { string tableName = row["TABLE_NAME"].ToString(); MetadataObject table = queryBuilder.MetadataContainer.AddTable(tableName); sql = "select * from " + tableName + " where 0=1"; Console.WriteLine(sql); DataTable columnList = db.RunQueryDataTable(sql, CommandType.Text); foreach (DataColumn col in columnList.Columns) { MetadataField metadataField = table.AddField(col.ColumnName); // setup field metadataField.FieldType = TypeToDbType(col.DataType); metadataField.Nullable = col.AllowDBNull; metadataField.ReadOnly = col.ReadOnly; if (col.MaxLength != -1) { metadataField.Size = col.MaxLength; } // detect the field is primary key foreach (DataColumn pkColumn in columnList.PrimaryKey) { if (col == pkColumn) { metadataField.PrimaryKey = true; } } } //close and reopen connection to free up space used by temp tables in the .NET provider db.CloseConnection(); db.OpenConnection(); } } catch (System.Exception ex) { _msg.Length = 0; _msg.Append("Attempt to load SQLCE 3.5 Schema Tree failed."); _msg.Append(Environment.NewLine); _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); throw new System.Exception(_msg.ToString()); } finally { if (db != null) { db.Connection.Close(); if (db.IsConnected) { db.CloseConnection(); } } db = null; queryBuilder.MetadataContainer.EndUpdate(); } queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0; queryBuilder.InitializeDatabaseSchemaTree(); }
} //end method private void LoadUltraLiteSchemaTree() { queryBuilder.MetadataContainer.BeginUpdate(); MetadataItem schemaRoot = null; PFDataAccessObjects.PFDatabase db = null; try { queryBuilder.MetadataContainer.Items.Clear(); queryBuilder.InitializeDatabaseSchemaTree(); if (queryBuilder.MetadataContainer.Items.Count == 1) { schemaRoot = queryBuilder.MetadataContainer.Items[0]; } else { schemaRoot = queryBuilder.MetadataContainer.AddDatabase("UltraLiteDb"); } //MetadataObject table = queryBuilder.MetadataContainer.AddTable("TestTable"); //MetadataObject table = schemaRoot.AddTable("TestTable"); //get list of tables and add them to the schema tree db = new PFDataAccessObjects.PFDatabase(PFDataAccessObjects.DatabasePlatform.SQLAnywhereUltraLite); db.ConnectionString = this.ConnectionString; db.OpenConnection(); DataTable datatableList = db.Connection.GetSchema(ULMetaDataCollectionNames.Tables); foreach (DataRow row in datatableList.Rows) { string tableName = row["table_name"].ToString(); MetadataObject table = schemaRoot.AddTable(tableName); string sql = "select * from " + tableName + " where 0=1"; Console.WriteLine(sql); DataTable columnList = db.RunQueryDataTable(sql, CommandType.Text); foreach (DataColumn col in columnList.Columns) { MetadataField metadataField = table.AddField(col.ColumnName); // setup field metadataField.FieldType = TypeToDbType(col.DataType); metadataField.Nullable = col.AllowDBNull; metadataField.ReadOnly = col.ReadOnly; if (col.MaxLength != -1) { metadataField.Size = col.MaxLength; } // detect the field is primary key foreach (DataColumn pkColumn in columnList.PrimaryKey) { if (col == pkColumn) { metadataField.PrimaryKey = true; } } } //close and reopen connection to free up space used by temp tables in the UL .NET provider db.CloseConnection(); db.OpenConnection(); } //MetadataField field = table.AddField("Fld1"); //field.FieldTypeName = "nvarchar"; //field.Size = 30; //field = table.AddField("Fld2"); //field.FieldTypeName = "int"; } catch (System.Exception ex) { _msg.Length = 0; _msg.Append("Attempt to load UltraLite Schema Tree failed."); _msg.Append(Environment.NewLine); _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); throw new System.Exception(_msg.ToString()); } finally { if (db != null) { db.Connection.Close(); if (db.IsConnected) { db.CloseConnection(); } } db = null; queryBuilder.MetadataContainer.EndUpdate(); } queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 1; queryBuilder.InitializeDatabaseSchemaTree(); }