/// <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);
}
}
