/// <summary> /// Get Metadata information about the tables in a schema in the current database /// </summary> /// <param name="schema">Name of the schema in the database.</param> /// <returns></returns> public override SchemaTablesMetaData QuerySchemaDefinition(string schema) { SchemaTablesMetaData result = new SchemaTablesMetaData(); result.schemaName = schema; try { using (OleDbConnection connector = new OleDbConnection(connectionString)) { connector.Open(); using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" })) { foreach (DataRow row in dt.Rows) { TableMetaData table = new TableMetaData(); table.tableName = row[2].ToString(); result.AddTable(table); } } // get all views fo ms access db DataTable dt2 = connector.GetSchema("Views"); foreach (DataRow row in dt2.Rows) { string queryText = (string)row["VIEW_DEFINITION"]; queryText = queryText.Replace(System.Environment.NewLine, " "); string queryName = (string)row["TABLE_NAME"]; Console.WriteLine(queryName); ViewMetaData vm = new ViewMetaData(); vm.ViewName = queryName; vm.ViewQuery = queryText; result.views.Add(vm); } connector.Close(); } } catch (OleDbException ex) { Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message); } return(result); }
private bool RecoverRightJoins(ViewMetaData vm) { string keyword = "right"; if (vm.ViewQuery.IndexOf(keyword, StringComparison.OrdinalIgnoreCase) == 0) { return(false); } string newQuery = Utils.SwapTablesOfRightJoin(vm.ViewQuery); try { InsertViewInDB(vm.ViewName, newQuery); return(true); } catch (SQLiteException ex) { Console.Out.WriteLine("SQLite Exception creating view {0} : {1} : {2}", vm.ViewName, ex.Message, ex.ErrorCode); //try to recover from RIGHT JOIN view statements. return(false); } }
/// <summary> /// Get Metadata information about the tables in a schema in the current database /// </summary> /// <param name="schema">Name of the schema in the database.</param> /// <returns></returns> public override SchemaTablesMetaData QuerySchemaDefinition(string schema) { SchemaTablesMetaData result = new SchemaTablesMetaData(); result.schemaName = schema; try { using (OleDbConnection connector = new OleDbConnection(connectionString)) { connector.Open(); using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" })) { foreach (DataRow row in dt.Rows) { TableMetaData table = new TableMetaData(); table.tableName = row[2].ToString(); result.AddTable(table); } } // get all views fo ms access db DataTable dt2 = connector.GetSchema("Views"); foreach (DataRow row in dt2.Rows) { string queryText = (string)row["VIEW_DEFINITION"]; queryText = queryText.Replace(System.Environment.NewLine," "); string queryName = (string)row["TABLE_NAME"]; Console.WriteLine(queryName); ViewMetaData vm = new ViewMetaData(); vm.ViewName = queryName; vm.ViewQuery = queryText; result.views.Add(vm); } connector.Close(); } } catch (OleDbException ex) { Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message); } return result; }
private bool RecoverRightJoins(ViewMetaData vm) { string keyword = "right"; if (vm.ViewQuery.IndexOf(keyword, StringComparison.OrdinalIgnoreCase) == 0) return false; string newQuery = Utils.SwapTablesOfRightJoin(vm.ViewQuery); try { InsertViewInDB(vm.ViewName, newQuery); return true; } catch (SQLiteException ex) { Console.Out.WriteLine("SQLite Exception creating view {0} : {1} : {2}", vm.ViewName, ex.Message, ex.ErrorCode); //try to recover from RIGHT JOIN view statements. return false; } }
private void AddViews(List <ViewMetaData> views) { List <string> names = new List <string>(); List <string> addedViews = new List <string>(); List <string> processedViews = new List <string>(); Console.WriteLine("It contains {0} rows", views.Count); foreach (ViewMetaData vm in views) { names.Add(vm.ViewName); //Console.WriteLine(vm.ViewName); } while (views.Count > 0) { ViewMetaData toBeDeleted = null; foreach (ViewMetaData vm in views) { //Console.WriteLine(vm.ViewName); bool containsName = false; bool cannotBeAdded = false; foreach (string name in names) { if (vm.ViewQuery.Contains(name) && !addedViews.Contains(name)) { containsName = true; Console.WriteLine(name); if (processedViews.Contains(name)) { cannotBeAdded = true; } break; } } if (cannotBeAdded) { toBeDeleted = vm; break; } if (!containsName) { //View can be added as it does not contain other VIEWS on query statement try { InsertViewInDB(vm.ViewName, vm.ViewQuery); addedViews.Add(vm.ViewName); } catch (SQLiteException ex) { Console.Out.WriteLine("SQLite Exception creating view {0} : {1} : {2}", vm.ViewName, ex.Message, ex.ErrorCode); //try to recover from RIGHT JOIN view statements. if (RecoverRightJoins(vm)) { addedViews.Add(vm.ViewName); } } finally { processedViews.Add(vm.ViewName); toBeDeleted = vm; } break; } } views.Remove(toBeDeleted); } }