private void btnStart_Click(object sender, EventArgs e)
{
if (!EnsureSaveLocationExists())
{
MessageBox.Show("Specified save location is in a directory that does not exist!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
ConversionConfiguration config = _manager.CurrentConfiguration;
string sqlConnString = config.ConnectionString;
Cursor = Cursors.WaitCursor;
SqlConversionProgressReportingHandler progressReportingHandler = OnSqlConversionProgressReportingHandler;
SqlTableSelectionHandler selectionHandlerDefinition = OnSqlTableDefinitionSelectionHandler;
SqlTableSelectionHandler selectionHandlerRecords = OnSqlTableRecordSelectionHandler;
FailedViewDefinitionHandler viewFailureHandler = OnFailedViewDefinitionHandler;
var filePathWithReplacedEnvironmentValues = Environment.ExpandEnvironmentVariables(config.SqLiteDatabaseFilePath);
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, filePathWithReplacedEnvironmentValues, config.EncryptionPassword, progressReportingHandler, selectionHandlerDefinition, selectionHandlerRecords, viewFailureHandler, config.CreateTriggersEnforcingForeignKeys, config.TryToCreateViews);
}
/// <summary>
/// This method takes as input the connection string to an SQL Server database
/// and creates a corresponding SQLite database file with a schema as retrieved from
/// the SQL Server database.
/// </summary>
/// <param name="sqlServerConnString">The connection string to the SQL Server database.</param>
/// <param name="sqlitePath">The path to the SQLite database file that needs to get created.</param>
/// <param name="password">The password to use or NULL if no password should be used to encrypt the DB</param>
/// <param name="progressReportingHandler">A handler delegate for progress notifications.</param>
/// <param name="selectionHandlerDefinition">The selection handler that allows the user to select which tables to include in the converted SQLite database.</param>
/// /// <param name="selectionHandlerRecord">The selection handler that allows the user to select which tables to include the data of in the converted SQLite database.</param>
/// <remarks>The method continues asynchronously in the background and the caller returns immediately.</remarks>
public static Task ConvertSqlServerToSQLiteDatabase(string sqlServerConnString, string sqlitePath, string password, SqlConversionProgressReportingHandler progressReportingHandler, SqlTableSelectionHandler selectionHandlerDefinition, SqlTableSelectionHandler selectionHandlerRecord, FailedViewDefinitionHandler viewFailureHandler, Boolean createTriggers, Boolean createViews)
{
// Clear cancelled flag
_cancelled = false;
var task = Task.Factory.StartNew(() =>
{
try
{
_isActive = true;
String sqlitePathResolved = TemplateToFilename(sqlitePath);
ConvertSqlServerDatabaseToSQLiteFile(sqlServerConnString, sqlitePathResolved, password, progressReportingHandler, selectionHandlerDefinition, selectionHandlerRecord, viewFailureHandler, createTriggers, createViews);
_isActive = false;
progressReportingHandler(true, true, 100, "Finished converting database");
}
catch (Exception ex)
{
_log.Error("Failed to convert SQL Server database to SQLite database", ex);
_isActive = false;
progressReportingHandler(true, false, 100, ex.ToString());
}
});
return task;
}
/// <summary>
/// Do the entire process of first reading the SQL Server schema, creating a corresponding
/// SQLite schema, and copying all rows from the SQL Server database to the SQLite database.
/// </summary>
/// <param name="sqlConnString">The SQL Server connection string</param>
/// <param name="sqlitePath">The path to the generated SQLite database file</param>
/// <param name="password">The password to use or NULL if no password should be used to encrypt the DB</param>
/// <param name="progressReportingHandler">A handler to handle progress notifications.</param>
/// <param name="selectionHandler">The selection handler which allows the user to select which tables to convert.</param>
private static void ConvertSqlServerDatabaseToSQLiteFile(String sqlConnString, String sqlitePath, String password, SqlConversionProgressReportingHandler progressReportingHandler, SqlTableSelectionHandler selectionHandlerDefinition, SqlTableSelectionHandler selectionHandlerRecord, FailedViewDefinitionHandler viewFailureHandler, Boolean createTriggers, Boolean createViews)
{
// Delete the destination file (only if it exists)
if (DeleteFile(sqlitePath))
{
throw new Exception("File could not be deleted!");
}
SqlServerSchemaReader schemaReader = new SqlServerSchemaReader(sqlConnString, Log);
schemaReader.TableSchemaReaderProgressChanged += (sender, args) =>
{
int total = args.TablesProcessed + args.TablesRemaining;
int percentage = (int) ((args.TablesProcessed/(Double) total)*100);
String msg = String.Format("Parsed table {0}", args.LastProcessedTable.TableName);
progressReportingHandler(false, false, percentage, msg);
};
schemaReader.ViewSchemaReaderProgressChanged += (sender, args) =>
{
int total = args.ViewsProcessed + args.ViewsRemaining;
int percentage = (int) ((args.ViewsProcessed/(Double) total)*100);
String msg = String.Format("Parsed view {0}", args.LastProcessedView.ViewName);
progressReportingHandler(false, false, percentage, msg);
};
schemaReader.PopulateTableSchema();
schemaReader.PopulateViewSchema();
var includeSchema = selectionHandlerDefinition(schemaReader.Tables);
schemaReader.TablesIncludeSchema = includeSchema;
var includeData = selectionHandlerRecord(includeSchema);
schemaReader.TablesIncludeData = includeData;
// Read the schema of the SQL Server database into a memory structure
DatabaseSchema ds = schemaReader.GetDatabaseSchema();
// Create the SQLite database and apply the schema
CreateSQLiteDatabase(sqlitePath, ds, password, progressReportingHandler, viewFailureHandler, createViews);
// Copy all rows from SQL Server tables to the newly created SQLite database
var tablesToCopy = ds.Tables.Where(obj => includeData.Any(include => include.TableName == obj.TableName)).ToList();
CopySqlServerRowsToSQLiteDB(sqlConnString, sqlitePath, tablesToCopy, password, progressReportingHandler);
// Add triggers based on foreign key constraints
if (createTriggers)
{
AddTriggersForForeignKeys(sqlitePath, ds.Tables, password, progressReportingHandler);
}
}
private static void AddTriggersForForeignKeys(string sqlitePath, IEnumerable<TableSchema> schema, string password, SqlConversionProgressReportingHandler progressReportingHandler)
{
// Connect to the newly created database
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
foreach (TableSchema dt in schema)
{
try
{
AddTableTriggers(conn, dt);
}
catch (Exception ex)
{
_log.Error("AddTableTriggers failed", ex);
throw;
}
}
}
_log.Debug("finished adding triggers to schema");
}
/// <summary>
/// Creates the SQLite database from the schema read from the SQL Server.
/// </summary>
/// <param name="sqlitePath">The path to the generated DB file.</param>
/// <param name="schema">The schema of the SQL server database.</param>
/// <param name="password">The password to use for encrypting the DB or null if non is needed.</param>
/// <param name="progressReportingHandler">A handle for progress notifications.</param>
private static void CreateSQLiteDatabase(string sqlitePath, DatabaseSchema schema, string password, SqlConversionProgressReportingHandler progressReportingHandler, FailedViewDefinitionHandler viewFailureHandler, bool createViews)
{
_log.Debug("Creating SQLite database...");
// Create the SQLite database file
SQLiteConnection.CreateFile(sqlitePath);
_log.Debug("SQLite file was created successfully at [" + sqlitePath + "]");
// Connect to the newly created database
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
// Create all tables in the new database
Object stateLocker = new Object();
int tableCount = 0;
var orderedTables = schema.Tables.OrderBy(obj => obj.TableName).ToList();
foreach (var dt in orderedTables)
{
using (var conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
try
{
AddSQLiteTable(conn, dt);
}
catch (Exception ex)
{
_log.Error("AddSQLiteTable failed", ex);
throw;
}
lock (stateLocker)
{
tableCount++;
}
CheckCancelled();
progressReportingHandler(false, true, (int)(tableCount * 50.0 / schema.Tables.Count), String.Format("Added table [{0}] to SQLite", dt.TableName));
_log.Debug("added schema for SQLite table [" + dt.TableName + "]");
}
}
// Create all views in the new database
int viewCount = 0;
if (createViews)
{
var orderedViews = schema.Views.OrderBy(obj => obj.ViewName).ToList();
foreach (var vs in orderedViews)
{
using (var conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
try
{
AddSQLiteView(conn, vs, viewFailureHandler);
}
catch (Exception ex)
{
_log.Error("AddSQLiteView failed", ex);
throw;
}
}
viewCount++;
CheckCancelled();
progressReportingHandler(false, true, 50 + (int)(viewCount * 50.0 / schema.Views.Count), String.Format("Added view [{0}] to SQLite", vs.ViewName));
_log.Debug("added schema for SQLite view [" + vs.ViewName + "]");
}
}
_log.Debug("finished adding all table/view schemas for SQLite database");
}
/// <summary>
/// Copies table rows from the SQL Server database to the SQLite database.
/// </summary>
/// <param name="sqlConnString">The SQL Server connection string</param>
/// <param name="sqlitePath">The path to the SQLite database file.</param>
/// <param name="schema">The schema of the SQL Server database.</param>
/// <param name="password">The password to use for encrypting the file</param>
/// <param name="progressReportingHandler">A handler to handle progress notifications.</param>
private static void CopySqlServerRowsToSQLiteDB(String sqlConnString, String sqlitePath, List<TableSchema> schema, String password, SqlConversionProgressReportingHandler progressReportingHandler)
{
CheckCancelled();
progressReportingHandler(false, true, 0, "Preparing to insert tables...");
_log.Debug("preparing to insert tables ...");
// Connect to the SQL Server database
using (var sqlConnection = new SqlConnection(sqlConnString))
{
sqlConnection.Open();
// Connect to the SQLite database next
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
using (var sqliteConnection = new SQLiteConnection(sqliteConnString))
{
sqliteConnection.Open();
// Go over all tables in the schema and copy their rows
for (int i = 0; i < schema.Count; i++)
{
SQLiteTransaction tx = sqliteConnection.BeginTransaction();
try
{
String tableQuery = BuildSqlServerTableQuery(schema[i]);
var query = new SqlCommand(tableQuery, sqlConnection);
using (SqlDataReader reader = query.ExecuteReader())
{
SQLiteCommand insert = BuildSQLiteInsert(schema[i]);
int counter = 0;
while (reader.Read())
{
insert.Connection = sqliteConnection;
insert.Transaction = tx;
var pnames = new List<String>();
for (int j = 0; j < schema[i].Columns.Count; j++)
{
String pname = "@" + GetNormalizedName(schema[i].Columns[j].ColumnName, pnames);
insert.Parameters[pname].Value = CastValueForColumn(reader[j], schema[i].Columns[j]);
pnames.Add(pname);
}
insert.ExecuteNonQuery();
counter++;
if (counter % 1000 == 0)
{
CheckCancelled();
tx.Commit();
progressReportingHandler(false, true, (int)(100.0 * i / schema.Count), String.Format("Added {0} rows to [{1}]", counter, schema[i].TableName));
tx = sqliteConnection.BeginTransaction();
}
}
}
CheckCancelled();
tx.Commit();
progressReportingHandler(false, true, (int)(100.0 * i / schema.Count), String.Format("Finished inserting rows into [{0}]", schema[i].TableName));
_log.Debug("finished inserting all rows for table [" + schema[i].TableName + "]");
}
catch (Exception ex)
{
_log.Error("unexpected exception", ex);
tx.Rollback();
throw;
}
}
}
}
}
/// <summary>
/// This method takes as input the connection string to an SQL Server database
/// and creates a corresponding SQLite database file with a schema as retrieved from
/// the SQL Server database.
/// </summary>
/// <param name="sqlServerConnString">The connection string to the SQL Server database.</param>
/// <param name="sqlitePath">The path to the SQLite database file that needs to get created.</param>
/// <param name="password">The password to use or NULL if no password should be used to encrypt the DB</param>
/// <param name="progressReportingHandler">A handler delegate for progress notifications.</param>
/// <param name="selectionHandlerDefinition">The selection handler that allows the user to select which tables to include in the converted SQLite database.</param>
/// /// <param name="selectionHandlerRecord">The selection handler that allows the user to select which tables to include the data of in the converted SQLite database.</param>
/// <remarks>The method continues asynchronously in the background and the caller returns immediately.</remarks>
public static Task ConvertSqlServerToSQLiteDatabase(string sqlServerConnString, string sqlitePath, string password, SqlConversionProgressReportingHandler progressReportingHandler, SqlTableSelectionHandler selectionHandlerDefinition, SqlTableSelectionHandler selectionHandlerRecord, FailedViewDefinitionHandler viewFailureHandler, Boolean createTriggers, Boolean createViews)
{
// Clear cancelled flag
_cancelled = false;
var task = Task.Factory.StartNew(() =>
{
try
{
_isActive = true;
String sqlitePathResolved = TemplateToFilename(sqlitePath);
ConvertSqlServerDatabaseToSQLiteFile(sqlServerConnString, sqlitePathResolved, password, progressReportingHandler, selectionHandlerDefinition, selectionHandlerRecord, viewFailureHandler, createTriggers, createViews);
_isActive = false;
progressReportingHandler(true, true, 100, "Finished converting database");
}
catch (Exception ex)
{
_log.Error("Failed to convert SQL Server database to SQLite database", ex);
_isActive = false;
progressReportingHandler(true, false, 100, ex.ToString());
}
});
return(task);
}
/// <summary>
/// Creates the SQLite database from the schema read from the SQL Server.
/// </summary>
/// <param name="sqlitePath">The path to the generated DB file.</param>
/// <param name="schema">The schema of the SQL server database.</param>
/// <param name="password">The password to use for encrypting the DB or null if non is needed.</param>
/// <param name="progressReportingHandler">A handle for progress notifications.</param>
private static void CreateSQLiteDatabase(string sqlitePath, DatabaseSchema schema, string password, SqlConversionProgressReportingHandler progressReportingHandler, FailedViewDefinitionHandler viewFailureHandler, bool createViews)
{
_log.Debug("Creating SQLite database...");
// Create the SQLite database file
SQLiteConnection.CreateFile(sqlitePath);
_log.Debug("SQLite file was created successfully at [" + sqlitePath + "]");
// Connect to the newly created database
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
// Create all tables in the new database
Object stateLocker = new Object();
int tableCount = 0;
var orderedTables = schema.Tables.OrderBy(obj => obj.TableName).ToList();
foreach (var dt in orderedTables)
{
using (var conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
try
{
AddSQLiteTable(conn, dt);
}
catch (Exception ex)
{
_log.Error("AddSQLiteTable failed", ex);
throw;
}
lock (stateLocker)
{
tableCount++;
}
CheckCancelled();
progressReportingHandler(false, true, (int)(tableCount * 50.0 / schema.Tables.Count), String.Format("Added table [{0}] to SQLite", dt.TableName));
_log.Debug("added schema for SQLite table [" + dt.TableName + "]");
}
}
// Create all views in the new database
int viewCount = 0;
if (createViews)
{
var orderedViews = schema.Views.OrderBy(obj => obj.ViewName).ToList();
foreach (var vs in orderedViews)
{
using (var conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
try
{
AddSQLiteView(conn, vs, viewFailureHandler);
}
catch (Exception ex)
{
_log.Error("AddSQLiteView failed", ex);
throw;
}
}
viewCount++;
CheckCancelled();
progressReportingHandler(false, true, 50 + (int)(viewCount * 50.0 / schema.Views.Count), String.Format("Added view [{0}] to SQLite", vs.ViewName));
_log.Debug("added schema for SQLite view [" + vs.ViewName + "]");
}
}
_log.Debug("finished adding all table/view schemas for SQLite database");
}
/// <summary>
/// Copies table rows from the SQL Server database to the SQLite database.
/// </summary>
/// <param name="sqlConnString">The SQL Server connection string</param>
/// <param name="sqlitePath">The path to the SQLite database file.</param>
/// <param name="schema">The schema of the SQL Server database.</param>
/// <param name="password">The password to use for encrypting the file</param>
/// <param name="progressReportingHandler">A handler to handle progress notifications.</param>
private static void CopySqlServerRowsToSQLiteDB(String sqlConnString, String sqlitePath, List <TableSchema> schema, String password, SqlConversionProgressReportingHandler progressReportingHandler)
{
CheckCancelled();
progressReportingHandler(false, true, 0, "Preparing to insert tables...");
_log.Debug("preparing to insert tables ...");
// Connect to the SQL Server database
using (var sqlConnection = new SqlConnection(sqlConnString))
{
sqlConnection.Open();
// Connect to the SQLite database next
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
using (var sqliteConnection = new SQLiteConnection(sqliteConnString))
{
sqliteConnection.Open();
// Go over all tables in the schema and copy their rows
for (int i = 0; i < schema.Count; i++)
{
SQLiteTransaction tx = sqliteConnection.BeginTransaction();
try
{
String tableQuery = BuildSqlServerTableQuery(schema[i]);
var query = new SqlCommand(tableQuery, sqlConnection);
using (SqlDataReader reader = query.ExecuteReader())
{
SQLiteCommand insert = BuildSQLiteInsert(schema[i]);
int counter = 0;
while (reader.Read())
{
insert.Connection = sqliteConnection;
insert.Transaction = tx;
var pnames = new List <String>();
for (int j = 0; j < schema[i].Columns.Count; j++)
{
String pname = "@" + GetNormalizedName(schema[i].Columns[j].ColumnName, pnames);
insert.Parameters[pname].Value = CastValueForColumn(reader[j], schema[i].Columns[j]);
pnames.Add(pname);
}
insert.ExecuteNonQuery();
counter++;
if (counter % 1000 == 0)
{
CheckCancelled();
tx.Commit();
progressReportingHandler(false, true, (int)(100.0 * i / schema.Count), String.Format("Added {0} rows to [{1}]", counter, schema[i].TableName));
tx = sqliteConnection.BeginTransaction();
}
}
}
CheckCancelled();
tx.Commit();
progressReportingHandler(false, true, (int)(100.0 * i / schema.Count), String.Format("Finished inserting rows into [{0}]", schema[i].TableName));
_log.Debug("finished inserting all rows for table [" + schema[i].TableName + "]");
}
catch (Exception ex)
{
_log.Error("unexpected exception", ex);
tx.Rollback();
}
}
}
}
}
/// <summary>
/// Do the entire process of first reading the SQL Server schema, creating a corresponding
/// SQLite schema, and copying all rows from the SQL Server database to the SQLite database.
/// </summary>
/// <param name="sqlConnString">The SQL Server connection string</param>
/// <param name="sqlitePath">The path to the generated SQLite database file</param>
/// <param name="password">The password to use or NULL if no password should be used to encrypt the DB</param>
/// <param name="progressReportingHandler">A handler to handle progress notifications.</param>
/// <param name="selectionHandlerDefinition">The selection handler which allows the user to select which tables to convert.</param>
/// <param name="selectionHandlerRecord">The selection handler which allows the user to select which tables to convert.</param>
/// <param name="viewFailureHandler">The selection handler which allows the user to select which views to convert.</param>
/// <param name="createTriggers">Whether or not triggers should be converted</param>
/// <param name="createViews">Whether or not views should be converted</param>
private static void ConvertSqlServerDatabaseToSQLiteFile(String sqlConnString, String sqlitePath, String password, SqlConversionProgressReportingHandler progressReportingHandler, SqlTableSelectionHandler selectionHandlerDefinition, SqlTableSelectionHandler selectionHandlerRecord, FailedViewDefinitionHandler viewFailureHandler, Boolean createTriggers, Boolean createViews)
{
// Delete the destination file (only if it exists)
if (DeleteFile(sqlitePath))
{
throw new Exception("File could not be deleted!");
}
SqlServerSchemaReader schemaReader = new SqlServerSchemaReader(sqlConnString, Log);
schemaReader.TableSchemaReaderProgressChanged += (sender, args) =>
{
int total = args.TablesProcessed + args.TablesRemaining;
int percentage = (int)((args.TablesProcessed / (Double)total) * 100);
String msg = String.Format("Parsed table {0}", args.LastProcessedTable.TableName);
progressReportingHandler(false, false, percentage, msg);
};
schemaReader.ViewSchemaReaderProgressChanged += (sender, args) =>
{
int total = args.ViewsProcessed + args.ViewsRemaining;
int percentage = (int)((args.ViewsProcessed / (Double)total) * 100);
String msg = String.Format("Parsed view {0}", args.LastProcessedView.ViewName);
progressReportingHandler(false, false, percentage, msg);
};
schemaReader.PopulateTableSchema();
schemaReader.PopulateViewSchema();
var includeSchema = selectionHandlerDefinition(schemaReader.Tables);
schemaReader.TablesIncludeSchema = includeSchema;
var includeData = selectionHandlerRecord(includeSchema);
schemaReader.TablesIncludeData = includeData;
// Read the schema of the SQL Server database into a memory structure
DatabaseSchema ds = schemaReader.GetDatabaseSchema();
// Create the SQLite database and apply the schema
CreateSQLiteDatabase(sqlitePath, ds, password, progressReportingHandler, viewFailureHandler, createViews);
// Copy all rows from SQL Server tables to the newly created SQLite database
var tablesToCopy = ds.Tables.Where(obj => includeData.Any(include => include.TableName == obj.TableName)).ToList();
CopySqlServerRowsToSQLiteDB(sqlConnString, sqlitePath, tablesToCopy, password, progressReportingHandler);
// Add triggers based on foreign key constraints
if (createTriggers)
{
AddTriggersForForeignKeys(sqlitePath, ds.Tables, password, progressReportingHandler);
}
}
private static void AddTriggersForForeignKeys(string sqlitePath, IEnumerable <TableSchema> schema, string password, SqlConversionProgressReportingHandler progressReportingHandler)
{
// Connect to the newly created database
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
foreach (TableSchema dt in schema)
{
try
{
AddTableTriggers(conn, dt);
}
catch (Exception ex)
{
_log.Error("AddTableTriggers failed", ex);
throw;
}
}
}
_log.Debug("finished adding triggers to schema");
}
private List<ViewSchema> GetViews(SqlConversionProgressReportingHandler progressReportingHandler = null)
{
var views = new List<ViewSchema>();
Regex removedbo = new Regex(@"dbo\.", RegexOptions.Compiled | RegexOptions.IgnoreCase);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var vs = new ViewSchema();
if (reader["TABLE_NAME"] == DBNull.Value) { continue; }
if (reader["VIEW_DEFINITION"] == DBNull.Value) { continue; }
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);
}
}
}
Object stateLocker = new Object();
int count = 0;
int totalViews = views.Count;
var parallelResult = Parallel.For(0, totalViews, i =>
{
var vs = views[i];
count++;
if (progressReportingHandler != null)
{
progressReportingHandler(false, true, 50 + (int)(count * 50.0 / totalViews), "Parsed view " + vs.ViewName);
}
int viewsProcessed;
lock (stateLocker)
{
count++;
viewsProcessed = count; // Copy the current number of processed views to a local for future usage.
}
SqlServerToSQLite.CheckCancelled();
if (progressReportingHandler != null)
{
progressReportingHandler(false, true, (int)(count * 50.0 / totalViews), "Parsed table " + vs.ViewName);
}
_log.Debug("parsed view schema for [" + vs.ViewName + "]");
int remaining = totalViews - viewsProcessed;
OnViewSchemaReaderProgressChanged(vs, viewsProcessed, remaining);
});
while (!parallelResult.IsCompleted)
{
Thread.Sleep(1000);
}
_log.Debug("finished parsing all views in SQL Server schema");
return views;
}
/// <summary>
/// Gets a list of table schemas from the specified connection string.
/// </summary>
private List<TableSchema> GetTableSchemas(SqlConversionProgressReportingHandler progressReportingHandler = null)
{
List<Tuple<String, String>> tableNamesAndSchemas = new List<Tuple<String, String>>();
// First step is to read the names of all tables in the database
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
// This command will read the names of all tables in the database
var cmd = new SqlCommand(@"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (reader["TABLE_SCHEMA"] == DBNull.Value) { continue; }
if (reader["TABLE_NAME"] == DBNull.Value) { continue; }
var tableSchema = (String)reader["TABLE_SCHEMA"];
var tableName = (String) reader["TABLE_NAME"];
tableNamesAndSchemas.Add(new Tuple<String, String>(tableSchema, tableName));
}
}
}
tableNamesAndSchemas = tableNamesAndSchemas.OrderBy(obj => obj.Item1).ThenBy(obj => obj.Item2).ToList();
// Next step is to use ADO APIs to query the schema of each table.
List<TableSchema> tables = new List<TableSchema>();
Object stateLocker = new Object();
int count = 0;
int totalTables = tableNamesAndSchemas.Count;
Parallel.ForEach(tableNamesAndSchemas, tableTuple =>
{
String tableSchema = tableTuple.Item1;
String tableName = tableTuple.Item2;
TableSchema ts = CreateTableSchema(tableName, tableSchema);
CreateForeignKeySchema(ts);
int tablesProcessed;
lock (stateLocker)
{
tables.Add(ts);
count++;
tablesProcessed = count; // Copy the current number of processed tables to a local for future usage.
}
SqlServerToSQLite.CheckCancelled();
if (progressReportingHandler != null)
{
progressReportingHandler(false, true, (int)(count * 50.0 / totalTables), "Parsed table " + tableName);
}
_log.Debug("parsed table schema for [" + tableName + "]");
int remaining = totalTables - tablesProcessed;
OnTableSchemaReaderProgressChanged(ts, tablesProcessed, remaining);
});
_log.Debug("finished parsing all tables in SQL Server schema");
// Sort the resulting list of TableSchema objects by the underlying table's name.
tables = tables.OrderBy(obj => obj.TableName).ToList();
_log.Debug("finished sorting all tables in SQL Server schema");
return tables;
}
private List <ViewSchema> GetViews(SqlConversionProgressReportingHandler progressReportingHandler = null)
{
var views = new List <ViewSchema>();
Regex removedbo = new Regex(@"dbo\.", RegexOptions.Compiled | RegexOptions.IgnoreCase);
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
var cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var vs = new ViewSchema();
if (reader["TABLE_NAME"] == DBNull.Value)
{
continue;
}
if (reader["VIEW_DEFINITION"] == DBNull.Value)
{
continue;
}
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);
}
}
}
Object stateLocker = new Object();
int count = 0;
int totalViews = views.Count;
var parallelResult = Parallel.For(0, totalViews, i =>
{
var vs = views[i];
count++;
if (progressReportingHandler != null)
{
progressReportingHandler(false, true, 50 + (int)(count * 50.0 / totalViews), "Parsed view " + vs.ViewName);
}
int viewsProcessed;
lock (stateLocker)
{
//count++;
viewsProcessed = count; // Copy the current number of processed views to a local for future usage.
}
SqlServerToSQLite.CheckCancelled();
if (progressReportingHandler != null)
{
progressReportingHandler(false, true, (int)(count * 50.0 / totalViews), "Parsed table " + vs.ViewName);
}
_log.Debug("parsed view schema for [" + vs.ViewName + "]");
int remaining = totalViews - viewsProcessed;
OnViewSchemaReaderProgressChanged(vs, viewsProcessed, remaining);
});
while (!parallelResult.IsCompleted)
{
Thread.Sleep(1000);
}
_log.Debug("finished parsing all views in SQL Server schema");
return(views);
}
/// <summary>
/// Gets a list of table schemas from the specified connection string.
/// </summary>
private List <TableSchema> GetTableSchemas(SqlConversionProgressReportingHandler progressReportingHandler = null)
{
List <Tuple <String, String> > tableNamesAndSchemas = new List <Tuple <String, String> >();
// First step is to read the names of all tables in the database
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
// This command will read the names of all tables in the database
var cmd = new SqlCommand(@"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (reader["TABLE_SCHEMA"] == DBNull.Value)
{
continue;
}
if (reader["TABLE_NAME"] == DBNull.Value)
{
continue;
}
var tableSchema = (String)reader["TABLE_SCHEMA"];
var tableName = (String)reader["TABLE_NAME"];
tableNamesAndSchemas.Add(new Tuple <String, String>(tableSchema, tableName));
}
}
}
tableNamesAndSchemas = tableNamesAndSchemas.OrderBy(obj => obj.Item1).ThenBy(obj => obj.Item2).ToList();
// Next step is to use ADO APIs to query the schema of each table.
List <TableSchema> tables = new List <TableSchema>();
Object stateLocker = new Object();
int count = 0;
int totalTables = tableNamesAndSchemas.Count;
Parallel.ForEach(tableNamesAndSchemas, tableTuple =>
{
String tableSchema = tableTuple.Item1;
String tableName = tableTuple.Item2;
TableSchema ts = CreateTableSchema(tableName, tableSchema);
CreateForeignKeySchema(ts);
int tablesProcessed;
lock (stateLocker)
{
tables.Add(ts);
count++;
tablesProcessed = count; // Copy the current number of processed tables to a local for future usage.
}
SqlServerToSQLite.CheckCancelled();
if (progressReportingHandler != null)
{
progressReportingHandler(false, true, (int)(count * 50.0 / totalTables), "Parsed table " + tableName);
}
_log.Debug("parsed table schema for [" + tableName + "]");
int remaining = totalTables - tablesProcessed;
OnTableSchemaReaderProgressChanged(ts, tablesProcessed, remaining);
});
_log.Debug("finished parsing all tables in SQL Server schema");
// Sort the resulting list of TableSchema objects by the underlying table's name.
tables = tables.OrderBy(obj => obj.TableName).ToList();
_log.Debug("finished sorting all tables in SQL Server schema");
return(tables);
}
static void Main(String[] args)
{
_options = new Options();
var result = CommandLine.Parser.Default.ParseArguments(args, _options);
if (!result)
{
AddMessage("Invalid Arguments");
return;
}
String logFilePath = _options.LogFile;
if (!String.IsNullOrWhiteSpace(logFilePath))
{
if (File.Exists(logFilePath))
{
File.Delete(logFilePath);
}
_logFileStream = new StreamWriter(File.OpenWrite(logFilePath));
}
String filename = _options.ConfigFile;
Boolean success = SerializationHelper.TryXmlDeserialize(filename, out _config);
if (!success)
{
AddMessage("The selected file was not a valid configuration file for this application.");
return;
}
if (!String.IsNullOrWhiteSpace(_options.DatabaseName))
{
// Allow user to override database name.
AddMessage(String.Format("A database name was supplied as an argument. Configured database will not be used."));
_config.DatabaseName = _options.DatabaseName;
}
AddMessage(String.Format("Converting database: {0}", _config.DatabaseName));
String sqlConnString = _config.ConnectionString;
SqlConversionProgressReportingHandler progressReportingHandler = OnSqlConversionProgressReportingHandler;
SqlTableSelectionHandler selectionHandlerDefinition = OnSqlTableDefinitionSelectionHandler;
SqlTableSelectionHandler selectionHandlerRecords = OnSqlTableRecordSelectionHandler;
FailedViewDefinitionHandler viewFailureHandler = OnFailedViewDefinitionHandler;
var filePathWithReplacedEnvironmentValues = Environment.ExpandEnvironmentVariables(_config.SqLiteDatabaseFilePath);
var task = SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, filePathWithReplacedEnvironmentValues, _config.EncryptionPassword, progressReportingHandler, selectionHandlerDefinition, selectionHandlerRecords, viewFailureHandler, _config.CreateTriggersEnforcingForeignKeys, _config.TryToCreateViews);
task.Wait();
if (task.Exception != null)
{
AddMessage("An error has occurred. Details:");
var exception = task.Exception;
AddMessage(exception.ToString(), false);
foreach (var innerException in exception.InnerExceptions)
{
AddMessage(innerException.ToString(), false);
}
}
if (_logFileStream != null)
{
_logFileStream.Dispose();
}
}
