private static void AddSQLiteView(SQLiteConnection conn, ViewSchema vs, FailedViewDefinitionHandler handler)
{
// Prepare a CREATE VIEW DDL statement
string stmt = vs.ViewSQL;
_log.Info("\n\n" + stmt + "\n\n");
// Execute the query in order to actually create the view.
SQLiteTransaction tx = conn.BeginTransaction();
try
{
SQLiteCommand cmd = new SQLiteCommand(stmt, conn, tx);
cmd.ExecuteNonQuery();
tx.Commit();
}
catch (SQLiteException ex)
{
tx.Rollback();
if (handler != null)
{
ViewSchema updated = new ViewSchema();
updated.ViewName = vs.ViewName;
updated.ViewSQL = vs.ViewSQL;
// Ask the user to supply the new view definition SQL statement
string sql = handler(updated);
if (sql == null)
return; // Discard the view
else
{
// Try to re-create the view with the user-supplied view definition SQL
updated.ViewSQL = sql;
AddSQLiteView(conn, updated, handler);
}
}
else
throw;
} // catch
}
/// <summary>
/// Reads the entire SQL Server DB schema using the specified connection string.
/// </summary>
/// <param name="connString">The connection string used for reading SQL Server schema.</param>
/// <param name="handler">A handler for progress notifications.</param>
/// <param name="selectionHandler">The selection handler which allows the user to select
/// which tables to convert.</param>
/// <returns>database schema objects for every table/view in the SQL Server database.</returns>
private static DatabaseSchema ReadSqlServerSchema(string connString, SqlConversionHandler handler,
SqlTableSelectionHandler selectionHandler)
{
// First step is to read the names of all tables in the database
List<TableSchema> tables = new List<TableSchema>();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
List<string> tableNames = new List<string>();
List<string> tblschema = new List<string>();
// This command will read the names of all tables in the database
SqlCommand cmd = new SqlCommand(@"select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableNames.Add((string)reader["TABLE_NAME"]);
tblschema.Add((string)reader["TABLE_SCHEMA"]);
} // while
} // using
// Next step is to use ADO APIs to query the schema of each table.
int count = 0;
for (int i=0; i<tableNames.Count; i++)
{
string tname = tableNames[i];
string tschma = tblschema[i];
TableSchema ts = CreateTableSchema(conn, tname, tschma);
CreateForeignKeySchema(conn, ts);
tables.Add(ts);
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / tableNames.Count), "Parsed table " + tname);
_log.Debug("parsed table schema for [" + tname + "]");
} // foreach
} // using
_log.Debug("finished parsing all tables in SQL Server schema");
// Allow the user a chance to select which tables to convert
if (selectionHandler != null)
{
List<TableSchema> updated = selectionHandler(tables);
if (updated != null)
tables = updated;
} // if
Regex removedbo = new Regex(@"dbo\.", RegexOptions.Compiled | RegexOptions.IgnoreCase);
// Continue and read all of the views in the database
List<ViewSchema> views = new List<ViewSchema>();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read())
{
ViewSchema vs = new ViewSchema();
vs.ViewName = (string)reader["TABLE_NAME"];
vs.ViewSQL = (string)reader["VIEW_DEFINITION"];
// Remove all ".dbo" strings from the view definition
vs.ViewSQL = removedbo.Replace(vs.ViewSQL, string.Empty);
views.Add(vs);
count++;
CheckCancelled();
handler(false, true, 50+(int)(count * 50.0 / views.Count), "Parsed view " + vs.ViewName);
_log.Debug("parsed view schema for [" + vs.ViewName + "]");
} // while
} // using
} // using
DatabaseSchema ds = new DatabaseSchema();
ds.Tables = tables;
ds.Views = views;
return ds;
}
private string OnFailedViewDefinitionHandler(ViewSchema vs)
{
string updated = null;
Invoke(new MethodInvoker(() =>
{
var dlg = new ViewFailureDialog();
dlg.View = vs;
DialogResult res = dlg.ShowDialog(this);
if (res == DialogResult.OK)
{
updated = dlg.ViewSQL;
}
else
{
updated = null;
}
}));
return updated;
}
