/// <summary>
/// This method takes as input the connection string to an SQL Server database
/// and creates a corresponding SQLite database file with a schema derived 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="handler">A handler delegate for progress notifications.</param>
/// <param name="selectionHandler">The selection handler that allows the user to select which
/// tables to convert</param>
/// <remarks>The method continues asynchronously in the background and the caller returned
/// immediatly.</remarks>
public static void ConvertSqlServerToSQLiteDatabase(string sqlServerConnString,
string sqlitePath, string password, SqlConversionHandler handler,
SqlTableSelectionHandler selectionHandler,
FailedViewDefinitionHandler viewFailureHandler,
bool createTriggers)
{
// Clear cancelled flag
_cancelled = false;
WaitCallback wc = new WaitCallback(delegate(object state)
{
try
{
_isActive = true;
ConvertSqlServerDatabaseToSQLiteFile(sqlServerConnString, sqlitePath, password, handler, selectionHandler, viewFailureHandler, createTriggers);
_isActive = false;
handler(true, true, 100, "Finished converting database");
}
catch (Exception ex)
{
_log.Error("Failed to convert SQL Server database to SQLite database", ex);
_isActive = false;
handler(true, false, 100, ex.Message);
} // catch
});
ThreadPool.QueueUserWorkItem(wc);
}
public override void ConvertToDatabase(string sqlServerConnectionString, SQLiteConnection sqliteConnection, SqlConversionHandler sqlConversionHandler, SqlTableSelectionHandler sqlTableSelectionHandler, FailedViewDefinitionHandler failedViewDefinitionHandler, bool createTriggers)
{
using (SqlConnection sqlServerConnection = new SqlConnection(sqlServerConnectionString))
{
ConvertToDatabase(sqlServerConnection, sqliteConnection, sqlConversionHandler, sqlTableSelectionHandler, failedViewDefinitionHandler, createTriggers);
}
}
private static void ConvertToSQLite()
{
SqlServerToSQLiteConverter sqlConv = new SqlServerToSQLiteConverter(Settings.TablesToLoad);
using (SQLiteConnection sqliteConn = sqlConv.InitializeSQLiteConnection(Settings.SQLiteConnectionString))
{
sqlConv.InitializeSQLiteDatabase(sqliteConn, Settings.PragmaParameters);
bool loadSuccess = false;
SqlConversionHandler sqlConversionHandler = new SqlConversionHandler(
delegate(bool done, bool success, int percent, string msg)
{
if (done)
{
if (success)
{
// log success
//Logging.Log(LogLevel.Debug, string.Format("SQLite DB load succeeded: {0}", msg));
loadSuccess = true;
}
else
{
// log failure
//Logging.Log(LogLevel.Error, string.Format("SQLite DB load failed: {0}", msg));
loadSuccess = false;
}
}
});
sqlConv.ConvertToDatabase(Settings.SQLConnectionString, sqliteConn, sqlConversionHandler, null, null, false);
sqliteConn.Close();
Console.WriteLine(loadSuccess);
}
}
private static void ConvertToSQLite()
{
SqlServerToSQLiteConverter sqlConv = new SqlServerToSQLiteConverter(Settings.TablesToLoad);
using (SQLiteConnection sqliteConn = sqlConv.InitializeSQLiteConnection(Settings.SQLiteConnectionString))
{
sqlConv.InitializeSQLiteDatabase(sqliteConn, Settings.PragmaParameters);
bool loadSuccess = false;
SqlConversionHandler sqlConversionHandler = new SqlConversionHandler(
delegate(bool done, bool success, int percent, string msg)
{
if (done)
{
if (success)
{
// log success
//Logging.Log(LogLevel.Debug, string.Format("SQLite DB load succeeded: {0}", msg));
loadSuccess = true;
}
else
{
// log failure
//Logging.Log(LogLevel.Error, string.Format("SQLite DB load failed: {0}", msg));
loadSuccess = false;
}
}
});
sqlConv.ConvertToDatabase(Settings.SQLConnectionString, sqliteConn, sqlConversionHandler, null, null, false);
sqliteConn.Close();
Console.WriteLine(loadSuccess);
}
}
private void btnDownloadData_Click(object sender, EventArgs e)
{
if (tbnSQLitePath.Text == "")
{
CommonHandler.ShowMessage(MessageType.Information, "请选择\"数据路径\"");
tbnSQLitePath.Focus();
return;
}
string sqlConnString = GetSqlServerConnectionString("123.57.229.128", "Toyota", "sa", "mxT1@mfb");
string sqlitePath = Path.Combine(tbnSQLitePath.Text.Trim(), "readonly.db");
this.Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = new SqlConversionHandler(delegate(bool done,
bool success, int percent, string msg)
{
Invoke(new MethodInvoker(delegate()
{
pbrProgress.Value = percent;
if (done)
{
this.Cursor = Cursors.Default;
if (success)
{
File.Copy(sqlitePath, Path.Combine(Path.GetDirectoryName(sqlitePath), "writeable.db"), true);
CommonHandler.ShowMessage(MessageType.Information, "下载成功");
pbrProgress.Value = 0;
}
else
{
CommonHandler.ShowMessage(MessageType.Information, "下载失败\r\n" + msg);
pbrProgress.Value = 0;
}
}
}));
});
SqlTableSelectionHandler selectionHandler = new SqlTableSelectionHandler(delegate(List <TableSchema> schema)
{
return(schema);
});
FailedViewDefinitionHandler viewFailureHandler = new FailedViewDefinitionHandler(delegate(ViewSchema vs)
{
return(null);
});
string password = null;
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, sqlitePath, password, handler,
selectionHandler, viewFailureHandler, false, false);
}
private void btnStart_Click(object sender, EventArgs e)
{
ConversionConfiguration config = _manager.CurrentConfiguration;
string sqlConnString = config.ConnectionString;
this.Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = this.OnSqlConversionHandler;
SqlTableSelectionHandler selectionHandler = this.OnSqlTableSelectionHandler;
FailedViewDefinitionHandler viewFailureHandler = this.OnFailedViewDefinitionHandler;
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, config.SqLiteDatabaseFilePath, config.EncryptionPassword, handler, selectionHandler, viewFailureHandler, config.CreateTriggersEnforcingForeignKeys, config.TryToCreateViews);
}
private void btnDownloadDataForUpdate_Click(object sender, EventArgs e)
{
if (tbnSQLitePathForUpdate.Text == "")
{
CommonHandler.ShowMessage(MessageType.Information, "请选择\"数据路径\"");
tbnSQLitePathForUpdate.Focus();
return;
}
string sqlConnString = GetSqlServerConnectionString("192.168.1.99", "XinHuaXin_YQ", "DSAT", "DSAT");
string sqlitePath = Path.Combine(tbnSQLitePathForUpdate.Text.Trim(), "readonly.db");
this.Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = new SqlConversionHandler(delegate(bool done,
bool success, int percent, string msg)
{
Invoke(new MethodInvoker(delegate()
{
pbrProgressForUpdate.Value = percent;
if (done)
{
this.Cursor = Cursors.Default;
if (success)
{
CommonHandler.ShowMessage(MessageType.Information, "下载成功");
pbrProgressForUpdate.Value = 0;
}
else
{
CommonHandler.ShowMessage(MessageType.Information, "下载失败\r\n" + msg);
pbrProgressForUpdate.Value = 0;
}
}
}));
});
SqlTableSelectionHandler selectionHandler = new SqlTableSelectionHandler(delegate(List <TableSchema> schema)
{
return(schema);
});
FailedViewDefinitionHandler viewFailureHandler = new FailedViewDefinitionHandler(delegate(ViewSchema vs)
{
return(null);
});
string password = null;
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, sqlitePath, password, handler,
selectionHandler, viewFailureHandler, false, false);
}
private static void RunConversion(string sqlConnString)
{
if (string.IsNullOrEmpty(sqlConnString))
{
sqlConnString = Configuration.SqlServer;
}
var sqlitePath = Configuration.Sqlite;
var password = Configuration.Password;
var generateTriggers = Configuration.ExportTriggers;
var tableRegex = new Regex(Configuration.Tables, RegexOptions.IgnoreCase);
SqlTableSelectionHandler selectionHandler = allTables =>
{
var tables = new List <TableSchema>();
foreach (var table in allTables)
{
if (tableRegex.IsMatch(table.TableName))
{
Console.WriteLine("Exporting table " + table.TableName);
tables.Add(table);
}
}
return(tables);
};
SqlConversionHandler handler = (done, success, percent, msg) =>
{
Console.WriteLine(percent + "% " + msg + (success ? "" : " - ERROR"));
if (done)
{
Console.WriteLine("Conversion done");
}
};
FailedViewDefinitionHandler viewFailureHandler = vs =>
{
Console.WriteLine("Error on view " + vs.ViewName);
return(null);
};
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, sqlitePath, password, handler,
selectionHandler, viewFailureHandler, generateTriggers, false);
}
private void dbConvert_Load(object sender, EventArgs e)
{
string text = "";
string appSettings = ConfigOperation.GetAppSettings("OLD_POS_DATABASE_NAME");
text = ((!bool.Parse(ConfigOperation.GetAppSettings("OLD_POS_DATABASE_SSPI"))) ? string.Format("Data Source=(local)\\SQLExpress;Initial Catalog={0};User ID=sa;Password=1031", appSettings) : string.Format("Data Source=(local)\\SQLExpress;Initial Catalog={0};Integrated Security=SSPI;", appSettings));
string sqlitePath = Program.DataPath + "\\Old_db.db3";
Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = new SqlConversionHandler(_003CdbConvert_Load_003Eb__1_0);
SqlTableSelectionHandler selectionHandler = null;
FailedViewDefinitionHandler viewFailureHandler = new FailedViewDefinitionHandler(_003CdbConvert_Load_003Eb__1_2);
string password = "******";
bool createViews = false;
bool createTriggers = false;
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(text, sqlitePath, password, handler, selectionHandler, viewFailureHandler, createTriggers, createViews);
}
public override void ConvertToDatabase(SqlConnection sqlServerConnection, SQLiteConnection sqliteConnection, SqlConversionHandler sqlConversionHandler, SqlTableSelectionHandler sqlTableSelecttionHandler, FailedViewDefinitionHandler failedViewDefinitionHandler, bool createTriggers)
{
// Clear cancelled flag
_cancelled = false;
//WaitCallback wc = new WaitCallback(delegate(object state)
//{
try
{
_isActive = true;
ConvertSourceDatabaseToDestination(sqlServerConnection, sqliteConnection, sqlConversionHandler, sqlTableSelecttionHandler, failedViewDefinitionHandler, createTriggers);
_isActive = false;
sqlConversionHandler(true, true, 100, "Finished converting database");
}
catch (Exception ex)
{
//Logging.Log(LogLevel.Error, string.Format("Failed to convert SQL Server database to SQLite database: {0}", FileLogger.GetInnerException(ex).Message));
//Logging.HandleException(ex);
_isActive = false;
sqlConversionHandler(true, false, 100, ex.Message);
}
//});
//ThreadPool.QueueUserWorkItem(wc);
}
private void btnStart_Click(object sender, EventArgs e)
{
string sqlConnString;
if (cbxIntegrated.Checked) {
sqlConnString = GetSqlServerConnectionString(txtSqlAddress.Text, (string)cboDatabases.SelectedItem);
} else {
sqlConnString = GetSqlServerConnectionString(txtSqlAddress.Text, (string)cboDatabases.SelectedItem, txtUserDB.Text, txtPassDB.Text);
}
bool createViews = cbxCreateViews.Checked;
string sqlitePath = txtSQLitePath.Text.Trim();
this.Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = new SqlConversionHandler(delegate(bool done,
bool success, int percent, string msg) {
Invoke(new MethodInvoker(delegate() {
UpdateSensitivity();
lblMessage.Text = msg;
pbrProgress.Value = percent;
if (done)
{
btnStart.Enabled = true;
this.Cursor = Cursors.Default;
UpdateSensitivity();
if (success)
{
MessageBox.Show(this,
msg,
"Conversion Finished",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
{
if (!_shouldExit)
{
MessageBox.Show(this,
msg,
"Conversion Failed",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
Application.Exit();
}
}
}));
});
SqlTableSelectionHandler selectionHandler = new SqlTableSelectionHandler(delegate(List<TableSchema> schema)
{
List<TableSchema> updated = null;
Invoke(new MethodInvoker(delegate
{
// Allow the user to select which tables to include by showing him the
// table selection dialog.
TableSelectionDialog dlg = new TableSelectionDialog();
DialogResult res = dlg.ShowTables(schema, this);
if (res == DialogResult.OK)
updated = dlg.IncludedTables;
}));
return updated;
});
FailedViewDefinitionHandler viewFailureHandler = new FailedViewDefinitionHandler(delegate(ViewSchema vs)
{
string updated = null;
Invoke(new MethodInvoker(delegate
{
ViewFailureDialog dlg = new ViewFailureDialog();
dlg.View = vs;
DialogResult res = dlg.ShowDialog(this);
if (res == DialogResult.OK)
updated = dlg.ViewSQL;
else
updated = null;
}));
return updated;
});
string password = txtPassword.Text.Trim();
if (!cbxEncrypt.Checked)
password = null;
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, sqlitePath, password, handler,
selectionHandler, viewFailureHandler, cbxTriggers.Checked, createViews);
}
/// <summary>
/// This method takes as input the connection string to an SQL Server database
/// and creates a corresponding SQLite database file with a schema derived from
/// the SQL Server database.
/// </summary>
/// <param name="sqlServerConnectionString">The connection string to the SQL Server database.</param>
/// <param name="sqliteConnectionString">The connection string to the SQLite database.</param>
/// <param name="sqlConversionHandler">The SQL conversion handler.</param>
/// <param name="sqlTableSelecttionHandler">The SQL table selection handler.</param>
/// <param name="failedViewDefinitionHandler">The failed view definition handler.</param>
/// <param name="createTriggers">if set to <c>true</c> [create triggers].</param>
/// <remarks>The method continues asynchronously in the background and the caller returned
/// immediatly.</remarks>
public abstract void ConvertToDatabase(string sqlServerConnectionString, string sqliteConnectionString,
SqlConversionHandler sqlConversionHandler,
SqlTableSelectionHandler sqlTableSelectionHandler,
FailedViewDefinitionHandler failedViewDefinitionHandler, bool createTriggers);
protected override void ConvertSourceDatabaseToDestination(SqlConnection sqlConnection,
SQLiteConnection sqliteConnection, SqlConversionHandler sqlConversionHandler,
SqlTableSelectionHandler sqlTableSelectionHandler, FailedViewDefinitionHandler failedViewDefinitionHandler,
bool createTriggers)
{
// Read the schema of the SQL Server database into a memory structure
DatabaseSchema ds = ReadSourceSchema(sqlConnection, sqlConversionHandler, sqlTableSelectionHandler);
// Create the SQLite database and apply the schema
CreateSQLiteDatabase(sqliteConnection, ds, sqlConversionHandler, failedViewDefinitionHandler);
// Copy all rows from SQL Server tables to the newly created SQLite database
CopySourceDatabaseRowsToDestination(sqlConnection, sqliteConnection, ds.Tables, sqlConversionHandler);
// Add triggers based on foreign key constraints
if (createTriggers)
{
AddTriggersForForeignKeys(sqliteConnection, ds.Tables, sqlConversionHandler);
}
}
public override void ConvertToDatabase(string sqlServerConnectionString, SQLiteConnection sqliteConnection, SqlConversionHandler sqlConversionHandler, SqlTableSelectionHandler sqlTableSelectionHandler, FailedViewDefinitionHandler failedViewDefinitionHandler, bool createTriggers)
{
using (SqlConnection sqlServerConnection = new SqlConnection(sqlServerConnectionString))
{
ConvertToDatabase(sqlServerConnection, sqliteConnection, sqlConversionHandler, sqlTableSelectionHandler, failedViewDefinitionHandler, createTriggers);
}
}
/// <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="handler">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, SqlConversionHandler handler,
SqlTableSelectionHandler selectionHandler,
FailedViewDefinitionHandler viewFailureHandler,
bool createTriggers)
{
// Delete the target file if it exists already.
if (File.Exists(sqlitePath))
File.Delete(sqlitePath);
// Read the schema of the SQL Server database into a memory structure
DatabaseSchema ds = ReadSqlServerSchema(sqlConnString, handler, selectionHandler);
// Create the SQLite database and apply the schema
CreateSQLiteDatabase(sqlitePath, ds, password, handler, viewFailureHandler);
// Copy all rows from SQL Server tables to the newly created SQLite database
CopySqlServerRowsToSQLiteDB(sqlConnString, sqlitePath, ds.Tables, password, handler);
// Add triggers based on foreign key constraints
if (createTriggers)
AddTriggersForForeignKeys(sqlitePath, ds.Tables, password, handler);
}
/// <summary>
/// This method takes as input the connection to an SQL Server database
/// and creates a corresponding SQLite database file with a schema derived from
/// the SQL Server database.
/// </summary>
/// <param name="sqlConnection">The SQL connection.</param>
/// <param name="sqliteConnection">The SQLite connection.</param>
/// <param name="sqlConversionHandler">The SQL conversion handler.</param>
/// <param name="sqlTableSelectionHandler">The SQL table selection handler.</param>
/// <param name="failedViewDefinitionHandler">The failed view definition handler.</param>
/// <param name="createTriggers">if set to <c>true</c> [create triggers].</param>
/// <remarks>The method continues asynchronously in the background and the caller returned
/// immediatly.</remarks>
public abstract void ConvertToDatabase(SqlConnection sqlConnection,
SQLiteConnection sqliteConnection, SqlConversionHandler sqlConversionHandler,
SqlTableSelectionHandler sqlTableSelectionHandler,
FailedViewDefinitionHandler failedViewDefinitionHandler,
bool createTriggers);
private static void AddTriggersForForeignKeys(string sqlitePath, IEnumerable<TableSchema> schema,
string password, SqlConversionHandler handler)
{
// Connect to the newly created database
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
// foreach
foreach (TableSchema dt in schema)
{
try
{
AddTableTriggers(conn, dt);
}
catch (Exception ex)
{
_log.Error("AddTableTriggers failed", ex);
throw;
}
}
} // using
_log.Debug("finished adding triggers to schema");
}
/// <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 ReadOracleSchema(string connString, SqlConversionHandler handler,List<String> includedTables, List<String> existedTables)
{
// First step is to read the names of all tables in the database
List<TableSchema> tables = new List<TableSchema>();
using (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();
// Next step is to use ADO APIs to query the schema of each table.
int count = 0;
for (int i = 0; i < includedTables.Count; i++)
{
string tname = includedTables[i];
TableSchema ts = CreateTableSchema(conn, tname, ""); //TODO Schema
ts.Existed = existedTables.Contains(tname);
//CreateForeignKeySchema(conn, ts);
tables.Add(ts);
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / includedTables.Count), "Parsed table " + tname);
_log.Debug("parsed table schema for [" + tname + "]");
} // foreach
} // using
_log.Debug("finished parsing all tables in SQL Server schema");
// 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 (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", conn);
using (OracleDataReader reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read())
{
ViewSchema 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);
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 void btnSQLiteSqlServer_Click(object sender, EventArgs e)
{
string tempFilePath = string.Empty;
string SqlServerPath = string.Empty;
string sqlConnString;
string dbname;
string tempDirPath = Path.GetTempPath() + @"\SqlConverter";
if (Directory.Exists(tempDirPath))
{
Directory.Delete(tempDirPath, true);
}
System.IO.Directory.CreateDirectory(tempDirPath);
DirectoryInfo tempDirInfo = new DirectoryInfo(tempDirPath);
DirectorySecurity tempDirSecurity = tempDirInfo.GetAccessControl();
tempDirSecurity.AddAccessRule(new FileSystemAccessRule("everyone", FileSystemRights.FullControl, AccessControlType.Allow));
tempDirInfo.SetAccessControl(tempDirSecurity);
string SQLitePath = Path.GetFullPath(txtSQLitePath.Text);
if (!File.Exists(SQLitePath))
{
MessageBox.Show("Input file " + SQLitePath + " not found.", "File not found", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (txtSqlServerPath.Text != string.Empty)
{
tempFilePath = Path.GetFullPath(tempDirPath + @"\" + Path.GetFileName(txtSqlServerPath.Text));
SqlServerPath = Path.GetFullPath(txtSqlServerPath.Text);
if (cboWhatToCopy.SelectedIndex == 2) // ie if we are copying into an existing database
{
if (!File.Exists(SqlServerPath))
{
MessageBox.Show("Output file '" + SqlServerPath + "' not found.", "File not found", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
System.IO.File.Copy(SqlServerPath, tempFilePath);
}
else
{
if (File.Exists(SqlServerPath))
{
DialogResult result = MessageBox.Show("Replace existing file '" + SqlServerPath + "'?", "Confirm replace file", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result != DialogResult.OK)
{
return;
}
}
}
string constr;
if (cbxIntegrated.Checked)
{
constr = GetSqlServerConnectionString(txtSqlAddress.Text, "master");
}
else
{
constr = GetSqlServerConnectionString(txtSqlAddress.Text, "master", txtUserDB.Text, txtPassDB.Text);
}
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
string queryString = "CREATE DATABASE SqlConverter on (NAME=N'" + Path.GetFileNameWithoutExtension(txtSqlServerPath.Text) + "',FILENAME=N'" + tempFilePath + "')";
if (cboWhatToCopy.SelectedIndex == 2) // ie if we are copying into an existing database
{
queryString += " FOR ATTACH";
}
SqlCommand query = new SqlCommand(queryString, conn);
query.ExecuteNonQuery();
dbname = "SqlConverter";
}
}
else
{
dbname = (string)cboDatabases.SelectedItem;
}
if (cbxIntegrated.Checked)
{
sqlConnString = GetSqlServerConnectionString(txtSqlAddress.Text, dbname);
}
else
{
sqlConnString = GetSqlServerConnectionString(txtSqlAddress.Text, dbname, txtUserDB.Text, txtPassDB.Text);
}
this.Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = new SqlConversionHandler(delegate(bool done,
bool success, int percent, string msg)
{
Invoke(new MethodInvoker(delegate()
{
UpdateSensitivity();
lblMessage.Text = msg;
pbrProgress.Value = percent;
if (done)
{
if (txtSqlServerPath.Text != string.Empty)
{
dropSqlConverterDatabase();
if (success)
{
System.IO.File.Copy(tempFilePath, SqlServerPath, true);
}
Directory.Delete(tempDirPath, true);
}
if (success)
{
MessageBox.Show(this,
msg,
"Conversion Finished",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
{
if (!_shouldExit)
{
MessageBox.Show(this,
msg,
"Conversion Failed",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
{
Application.Exit();
}
}
btnSQLiteSqlServer.Enabled = true;
this.Cursor = Cursors.Default;
UpdateSensitivity();
}
}));
});
SqlTableSelectionHandler selectionHandler = new SqlTableSelectionHandler(delegate(List <TableSchema> schema)
{
List <TableSchema> updated = null;
Invoke(new MethodInvoker(delegate
{
// Allow the user to select which tables to include by showing him the
// table selection dialog.
TableSelectionDialog dlg = new TableSelectionDialog();
DialogResult res = dlg.ShowTables(schema, this);
if (res == DialogResult.OK)
{
updated = dlg.IncludedTables;
}
}));
return(updated);
});
FailedViewDefinitionHandler viewFailureHandler = new FailedViewDefinitionHandler(delegate(ViewSchema vs)
{
string updated = null;
Invoke(new MethodInvoker(delegate
{
ViewFailureDialog dlg = new ViewFailureDialog();
dlg.View = vs;
DialogResult res = dlg.ShowDialog(this);
if (res == DialogResult.OK)
{
updated = dlg.ViewSQL;
}
else
{
updated = null;
}
}));
return(updated);
});
string password = txtPassword.Text.Trim();
if (!cbxEncrypt.Checked)
{
password = null;
}
bool copyStructure = (cboWhatToCopy.SelectedIndex != 2);
bool copyData = (cboWhatToCopy.SelectedIndex != 1);
SQLiteToSqlServer.ConvertSQLiteToSqlServerDatabase(sqlConnString, SQLitePath, password, handler,
selectionHandler, viewFailureHandler, copyStructure, copyData);
}
/// <summary>
/// Creates the SQLite database.
/// </summary>
/// <param name="sqliteConnection">The SQLite connection.</param>
/// <param name="schema">The schema.</param>
/// <param name="handler">The handler.</param>
/// <param name="viewFailureHandler">The view failure handler.</param>
protected static void CreateSqlServerDatabase(SQLiteConnection sqliteConnection, DatabaseSchema schema,
SqlConversionHandler handler, FailedViewDefinitionHandler viewFailureHandler)
{
//Logging.Log(LogLevel.Debug, "Creating SQLite database...");
// Create all tables in the new database
int count = 0;
foreach (TableSchema dt in schema.Tables) {
try {
AddSQLiteTable(sqliteConnection, dt);
}
catch (Exception ex) {
//Logging.Log(LogLevel.Error, string.Format("AddSQLiteTable failed: {0}", FileLogger.GetInnerException(ex).Message));
//Logging.HandleException(ex);
throw;
}
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / schema.Tables.Count), "Added table " + dt.TableName + " to the SQLite database");
//Logging.Log(LogLevel.Debug, "added schema for SQLite table [" + dt.TableName + "]");
}
// Create all views in the new database
count = 0;
foreach (ViewSchema vs in schema.Views) {
try {
AddSQLiteView(sqliteConnection, vs, viewFailureHandler);
}
catch (Exception ex) {
//Logging.Log(LogLevel.Error, string.Format("AddSQLiteView failed: {0}", FileLogger.GetInnerException(ex).Message));
//Logging.HandleException(ex);
throw;
}
count++;
CheckCancelled();
handler(false, true, 50 + (int)(count * 50.0 / schema.Views.Count), "Added view " + vs.ViewName + " to the SQLite database");
//Logging.Log(LogLevel.Debug, "added schema for SQLite view [" + vs.ViewName + "]");
}
//Logging.Log(LogLevel.Debug, "finished adding all table/view schemas for SQLite database");
}
private static void CopySqlServerRowsToSQLiteDB(string sqlConnString, string sqlitePath, List <TableSchema> schema, string password, SqlConversionHandler handler)
{
CheckCancelled();
handler(done: false, success: true, 0, "準備複製資料表...");
_log.Debug("preparing to insert tables ...");
using (SqlConnection sqlConnection = new SqlConnection(sqlConnString))
{
sqlConnection.Open();
using (SQLiteConnection sQLiteConnection = new SQLiteConnection(CreateSQLiteConnectionString(sqlitePath, password)))
{
sQLiteConnection.Open();
for (int i = 0; i < schema.Count; i++)
{
SQLiteTransaction sQLiteTransaction = sQLiteConnection.BeginTransaction();
try
{
using (SqlDataReader sqlDataReader = new SqlCommand(BuildSqlServerTableQuery(schema[i]), sqlConnection).ExecuteReader())
{
SQLiteCommand sQLiteCommand = BuildSQLiteInsert(schema[i]);
int num = 0;
while (sqlDataReader.Read())
{
sQLiteCommand.Connection = sQLiteConnection;
sQLiteCommand.Transaction = sQLiteTransaction;
List <string> list = new List <string>();
for (int j = 0; j < schema[i].Columns.Count; j++)
{
string text = "@" + GetNormalizedName(schema[i].Columns[j].ColumnName, list);
sQLiteCommand.Parameters[text].Value = CastValueForColumn(sqlDataReader[j], schema[i].Columns[j]);
list.Add(text);
}
sQLiteCommand.ExecuteNonQuery();
num++;
if (num % 1000 == 0)
{
CheckCancelled();
sQLiteTransaction.Commit();
handler(done: false, success: true, (int)(100.0 * (double)i / (double)schema.Count), "已複製 " + num + " 行: 資料表 " + schema[i].TableName);
sQLiteTransaction = sQLiteConnection.BeginTransaction();
}
}
}
CheckCancelled();
sQLiteTransaction.Commit();
handler(done: false, success: true, (int)(100.0 * (double)i / (double)schema.Count), "複製資料表: " + schema[i].TableName);
_log.Debug("finished inserting all rows for table [" + schema[i].TableName + "]");
}
catch (Exception exception)
{
_log.Error("預期外的錯誤", exception);
sQLiteTransaction.Rollback();
throw;
}
}
}
}
}
private static DatabaseSchema ReadSqlServerSchema(string connString, SqlConversionHandler handler, SqlTableSelectionHandler selectionHandler)
{
List <TableSchema> list = new List <TableSchema>();
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
sqlConnection.Open();
List <string> list2 = new List <string>();
List <string> list3 = new List <string>();
using (SqlDataReader sqlDataReader = new SqlCommand("select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'", sqlConnection).ExecuteReader())
{
while (sqlDataReader.Read())
{
if (sqlDataReader["TABLE_NAME"] != DBNull.Value && sqlDataReader["TABLE_SCHEMA"] != DBNull.Value)
{
list2.Add((string)sqlDataReader["TABLE_NAME"]);
list3.Add((string)sqlDataReader["TABLE_SCHEMA"]);
}
}
}
int num = 0;
for (int i = 0; i < list2.Count; i++)
{
string text = list2[i];
string tschma = list3[i];
TableSchema tableSchema = CreateTableSchema(sqlConnection, text, tschma);
CreateForeignKeySchema(sqlConnection, tableSchema);
list.Add(tableSchema);
num++;
CheckCancelled();
handler(done: false, success: true, (int)((double)num * 50.0 / (double)list2.Count), "解析資料表: " + text);
_log.Debug("parsed table schema for [" + text + "]");
}
}
_log.Debug("finished parsing all tables in SQL Server schema");
if (selectionHandler != null)
{
List <TableSchema> list4 = selectionHandler(list);
if (list4 != null)
{
list = list4;
}
}
Regex regex = new Regex("dbo\\.", RegexOptions.IgnoreCase | RegexOptions.Compiled);
List <ViewSchema> list5 = new List <ViewSchema>();
using (SqlConnection sqlConnection2 = new SqlConnection(connString))
{
sqlConnection2.Open();
using (SqlDataReader sqlDataReader2 = new SqlCommand("SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", sqlConnection2).ExecuteReader())
{
int num2 = 0;
while (sqlDataReader2.Read())
{
ViewSchema viewSchema = new ViewSchema();
if (sqlDataReader2["TABLE_NAME"] != DBNull.Value && sqlDataReader2["VIEW_DEFINITION"] != DBNull.Value)
{
viewSchema.ViewName = (string)sqlDataReader2["TABLE_NAME"];
viewSchema.ViewSQL = (string)sqlDataReader2["VIEW_DEFINITION"];
viewSchema.ViewSQL = regex.Replace(viewSchema.ViewSQL, string.Empty);
list5.Add(viewSchema);
num2++;
CheckCancelled();
handler(done: false, success: true, 50 + (int)((double)num2 * 50.0 / (double)list5.Count), "解析View: " + viewSchema.ViewName);
_log.Debug("parsed view schema for [" + viewSchema.ViewName + "]");
}
}
}
}
return(new DatabaseSchema
{
Tables = list,
Views = list5
});
}
private static void ConvertSqlServerDatabaseToSQLiteFile(string sqlConnString, string sqlitePath, string password, SqlConversionHandler handler, SqlTableSelectionHandler selectionHandler, FailedViewDefinitionHandler viewFailureHandler, bool createTriggers, bool createViews)
{
if (File.Exists(sqlitePath))
{
File.Delete(sqlitePath);
}
DatabaseSchema databaseSchema = ReadSqlServerSchema(sqlConnString, handler, selectionHandler);
CreateSQLiteDatabase(sqlitePath, databaseSchema, password, handler, viewFailureHandler, createViews);
CopySqlServerRowsToSQLiteDB(sqlConnString, sqlitePath, databaseSchema.Tables, password, handler);
if (createTriggers)
{
AddTriggersForForeignKeys(sqlitePath, databaseSchema.Tables, password, handler);
}
}
private static void CreateSQLiteDatabase(string sqlitePath, DatabaseSchema schema, string password, SqlConversionHandler handler, FailedViewDefinitionHandler viewFailureHandler, bool createViews)
{
_log.Debug("Creating SQLite database...");
SQLiteConnection.CreateFile(sqlitePath);
_log.Debug("SQLite file was created successfully at [" + sqlitePath + "]");
using (SQLiteConnection sQLiteConnection = new SQLiteConnection(CreateSQLiteConnectionString(sqlitePath, password)))
{
sQLiteConnection.Open();
int num = 0;
foreach (TableSchema table in schema.Tables)
{
try
{
AddSQLiteTable(sQLiteConnection, table);
}
catch (Exception exception)
{
_log.Error("AddSQLiteTable failed", exception);
throw;
}
num++;
CheckCancelled();
handler(done: false, success: true, (int)((double)num * 50.0 / (double)schema.Tables.Count), "產生資料表: " + table.TableName);
_log.Debug("added schema for SQLite table [" + table.TableName + "]");
}
num = 0;
if (createViews)
{
foreach (ViewSchema view in schema.Views)
{
try
{
AddSQLiteView(sQLiteConnection, view, viewFailureHandler);
}
catch (Exception exception2)
{
_log.Error("AddSQLiteView failed", exception2);
throw;
}
num++;
CheckCancelled();
handler(done: false, success: true, 50 + (int)((double)num * 50.0 / (double)schema.Views.Count), "產生view: " + view.ViewName);
_log.Debug("added schema for SQLite view [" + view.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="sqlConnection">The SQL connection.</param>
/// <param name="sqliteConnection">The SQLite connection.</param>
/// <param name="schema">The schema of the SQL Server database.</param>
/// <param name="handler">A handler to handle progress notifications.</param>
protected abstract void CopySourceDatabaseRowsToDestination(
SqlConnection sqlConnection, SQLiteConnection sqliteConnection, List<TableSchema> schema,
SqlConversionHandler handler);
public static void ConvertSqlServerToSQLiteDatabase(string sqlServerConnString, string sqlitePath, string password, SqlConversionHandler handler, SqlTableSelectionHandler selectionHandler, FailedViewDefinitionHandler viewFailureHandler, bool createTriggers, bool createViews)
{
_cancelled = false;
ThreadPool.QueueUserWorkItem(delegate
{
try
{
_isActive = true;
ConvertSqlServerDatabaseToSQLiteFile(sqlServerConnString, sqlitePath, password, handler, selectionHandler, viewFailureHandler, createTriggers, createViews);
_isActive = false;
handler(done: true, success: true, 100, "資料移轉成功");
}
catch (SqlException exception)
{
_log.Error("資料移轉失敗", exception);
_isActive = false;
handler(done: true, success: false, 100, "查無原先POS系統資料庫，請洽客服人員");
}
catch (Exception ex)
{
_log.Error("資料移轉失敗", ex);
_isActive = false;
handler(done: true, success: false, 100, ex.Message);
}
});
}
/// <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="handler">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 ConvertOracleDatabaseToSQLiteFile(
string sqlConnString, string sqlitePath, string password, SqlConversionHandler handler,
List<String> includedTables, List<String> existedTables,
FailedViewDefinitionHandler viewFailureHandler,
bool createTriggers, bool createViews)
{
// Read the schema of the SQL Server database into a memory structure
DatabaseSchema ds = ReadOracleSchema(sqlConnString, handler, includedTables, existedTables);
// Create the SQLite database and apply the schema
CreateSQLiteDatabase(sqlitePath, ds, password, handler, viewFailureHandler, createViews);
// Copy all rows from SQL Server tables to the newly created SQLite database
CopyOracleRowsToSQLiteDB(sqlConnString, sqlitePath, ds.Tables, password, handler);
// Add triggers based on foreign key constraints
if (createTriggers)
AddTriggersForForeignKeys(sqlitePath, ds.Tables, password, handler);
}
/// <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="handler">A handle for progress notifications.</param>
private static void CreateSQLiteDatabase(string sqlitePath, DatabaseSchema schema, string password,
SqlConversionHandler handler,
FailedViewDefinitionHandler viewFailureHandler)
{
_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);
using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
// Create all tables in the new database
int count = 0;
foreach (TableSchema dt in schema.Tables)
{
try
{
AddSQLiteTable(conn, dt);
}
catch (Exception ex)
{
_log.Error("AddSQLiteTable failed", ex);
throw;
}
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / schema.Tables.Count), "Added table " + dt.TableName + " to the SQLite database");
_log.Debug("added schema for SQLite table [" + dt.TableName + "]");
} // foreach
// Create all views in the new database
count = 0;
foreach (ViewSchema vs in schema.Views)
{
try
{
AddSQLiteView(conn, vs, viewFailureHandler);
}
catch (Exception ex)
{
_log.Error("AddSQLiteView failed", ex);
throw;
} // catch
count++;
CheckCancelled();
handler(false, true, 50+(int)(count * 50.0 / schema.Views.Count), "Added view " + vs.ViewName + " to the SQLite database");
_log.Debug("added schema for SQLite view [" + vs.ViewName + "]");
} // foreach
} // using
_log.Debug("finished adding all table/view schemas for SQLite database");
}
private void Iniciar()
{
string sqlConnString;
sqlConnString = this.txtSqlServer.Text;
bool createViews = cbxCreateViews.Checked;
string sqlitePath = txtSQLite.Text.Trim();
this.Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = new SqlConversionHandler(delegate(bool done,
bool success, int percent, string msg)
{
Invoke(new MethodInvoker(delegate()
{
UpdateSensitivity();
lblMessage.Text = msg;
pbrProgress.Value = percent;
if (done)
{
btnStart.Enabled = true;
this.Cursor = Cursors.Default;
UpdateSensitivity();
if (success)
{
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
Application.Exit();
}
else
{
if (!_shouldExit)
{
MessageBox.Show(this,
msg,
"Conversion Failed",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
Application.Exit();
}
}
}));
});
SqlTableSelectionHandler selectionHandler = new SqlTableSelectionHandler(delegate(List<TableSchema> schema)
{
List<TableSchema> updated = null;
Invoke(new MethodInvoker(delegate
{
updated = schema;
}));
return updated;
});
FailedViewDefinitionHandler viewFailureHandler = new FailedViewDefinitionHandler(delegate(ViewSchema vs)
{
string updated = null;
Invoke(new MethodInvoker(delegate
{
ViewFailureDialog dlg = new ViewFailureDialog();
dlg.View = vs;
DialogResult res = dlg.ShowDialog(this);
if (res == DialogResult.OK)
updated = dlg.ViewSQL;
else
updated = null;
}));
return updated;
});
string password = txtPassword.Text.Trim();
if (!cbxEncrypt.Checked)
password = null;
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, sqlitePath, password, handler,
selectionHandler, viewFailureHandler, cbxTriggers.Checked, createViews);
}
/// <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;
}
protected static void AddTriggersForForeignKeys(SQLiteConnection sqliteConnection, IEnumerable<TableSchema> schema,
SqlConversionHandler handler)
{
// foreach
foreach (TableSchema dt in schema) {
try {
AddTableTriggers(sqliteConnection, dt);
}
catch (Exception ex) {
//Logging.Log(LogLevel.Error, string.Format("AddTableTriggers failed: {0}", FileLogger.GetInnerException(ex).Message));
throw;
}
}
//Logging.Log(LogLevel.Debug, "finished adding triggers to schema");
}
protected override DatabaseSchema ReadSourceSchema(SqlConnection sqlServerConnection, SqlConversionHandler handler,
SqlTableSelectionHandler selectionHandler)
{
// First step is to read the names of all tables in the database
List <TableSchema> tables = new List <TableSchema>();
sqlServerConnection.Open();
//Logging.Log(LogLevel.Debug, "SQL Server Connection initialized");
List <string> tableNames = new List <string>();
List <string> tblschema = new List <string>();
// Initialize in clause for schemas and tables.
//Logging.Log(LogLevel.Debug, "Intializing schemas and tables to load where clauses");
string schemasToLoad = Utilities.ContainsSchemaInfo(TablesToLoad) ? Utilities.ConvertTableToLoadToInClause(TablesToLoad, true, "TABLE_SCHEMA").ToUpper() : string.Empty;
//if (schemasToLoad != string.Empty) //Logging.Log(LogLevel.Debug, string.Format("Schemas IN clause: {0}", schemasToLoad));
string tablesToLoad = TablesToLoad.Count > 0 ? Utilities.ConvertTableToLoadToInClause(TablesToLoad, false, "TABLE_NAME").ToUpper() : string.Empty;
//if (tablesToLoad != string.Empty) //Logging.Log(LogLevel.Debug, string.Format("Tables IN clause: {0}", tablesToLoad));
string whereClause = string.Empty;
if (schemasToLoad != string.Empty && tablesToLoad != string.Empty)
{
whereClause = string.Format(" AND {0} AND {1} ", schemasToLoad, tablesToLoad);
}
else if (schemasToLoad != string.Empty && tablesToLoad == string.Empty)
{
whereClause = string.Format(" AND {0} ", schemasToLoad);
}
else if (schemasToLoad == string.Empty && tablesToLoad != string.Empty)
{
whereClause = string.Format(" AND {0} ", tablesToLoad);
}
//Logging.Log(LogLevel.Debug, "Intializing SQL statement");
// This command will read the names of all tables in the database
string sqlQuery = string.Format(@"select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'{0}", whereClause);
//Logging.Log(LogLevel.Debug, string.Format("Sql Server INFORMATION_SCHEMA.TABLES query: \n\n{0}\n\n", sqlQuery));
SqlCommand cmd = new SqlCommand(sqlQuery, sqlServerConnection);
//Logging.Log(LogLevel.Debug, "SqlCommand initialized");
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableNames.Add((string)reader["TABLE_NAME"]);
tblschema.Add((string)reader["TABLE_SCHEMA"]);
}
}
// 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];
string fullName = tschma + "." + tname;
// Load only the tables in TablesToLoad.
if (TablesToLoad.Count > 0 && !TablesToLoad.Exists(t => t.SqlServerFullName.ToLower() == fullName.ToLower()))
{
continue;
}
TableSchema ts = CreateTableSchema(sqlServerConnection, tname, tschma);
CreateForeignKeySchema(sqlServerConnection, ts);
tables.Add(ts);
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / tableNames.Count), "Parsed table " + tname);
//Logging.Log(LogLevel.Debug, "parsed table schema for [" + tname + "]");
}
sqlServerConnection.Close();
//Logging.Log(LogLevel.Debug, "finished parsing all tables in SQL Server schema");
// Allow the user a chance to select which tables to convert, only if the TablesToLoad list isn't defined.
if (selectionHandler != null && TablesToLoad.Count > 0)
{
List <TableSchema> updated = selectionHandler(tables);
if (updated != null)
{
tables = updated;
}
}
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>();
sqlServerConnection.Open();
cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", sqlServerConnection);
using (SqlDataReader reader = cmd.ExecuteReader())
{
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);
//Logging.Log(LogLevel.Debug, "parsed view schema for [" + vs.ViewName + "]");
}
}
sqlServerConnection.Close();
DatabaseSchema ds = new DatabaseSchema();
ds.Tables = tables;
ds.Views = views;
return(ds);
}
private void btnStart_Click(object sender, EventArgs e)
{
string sqlConnString;
if (cbxIntegrated.Checked)
{
sqlConnString = GetSqlServerConnectionString(txtSqlAddress.Text, (string)cboDatabases.SelectedItem);
}
else
{
sqlConnString = GetSqlServerConnectionString(txtSqlAddress.Text, (string)cboDatabases.SelectedItem, txtUserDB.Text, txtPassDB.Text);
}
bool createViews = cbxCreateViews.Checked;
string sqlitePath = txtSQLitePath.Text.Trim();
this.Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = new SqlConversionHandler(delegate(bool done,
bool success, int percent, string msg) {
Invoke(new MethodInvoker(delegate() {
UpdateSensitivity();
lblMessage.Text = msg;
pbrProgress.Value = percent;
if (done)
{
btnStart.Enabled = true;
this.Cursor = Cursors.Default;
UpdateSensitivity();
if (success)
{
MessageBox.Show(this,
msg,
"Conversion Finished",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
{
if (!_shouldExit)
{
MessageBox.Show(this,
msg,
"Conversion Failed",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
{
Application.Exit();
}
}
}
}));
});
SqlTableSelectionHandler selectionHandler = new SqlTableSelectionHandler(delegate(List <TableSchema> schema)
{
List <TableSchema> updated = null;
Invoke(new MethodInvoker(delegate
{
// Allow the user to select which tables to include by showing him the
// table selection dialog.
TableSelectionDialog dlg = new TableSelectionDialog();
DialogResult res = dlg.ShowTables(schema, this);
if (res == DialogResult.OK)
{
updated = dlg.IncludedTables;
}
}));
return(updated);
});
FailedViewDefinitionHandler viewFailureHandler = new FailedViewDefinitionHandler(delegate(ViewSchema vs)
{
string updated = null;
Invoke(new MethodInvoker(delegate
{
ViewFailureDialog dlg = new ViewFailureDialog();
dlg.View = vs;
DialogResult res = dlg.ShowDialog(this);
if (res == DialogResult.OK)
{
updated = dlg.ViewSQL;
}
else
{
updated = null;
}
}));
return(updated);
});
string password = txtPassword.Text.Trim();
if (!cbxEncrypt.Checked)
{
password = null;
}
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(sqlConnString, sqlitePath, password, handler,
selectionHandler, viewFailureHandler, cbxTriggers.Checked, createViews);
}
public override void ConvertToDatabase(SqlConnection sqlServerConnection, SQLiteConnection sqliteConnection, SqlConversionHandler sqlConversionHandler, SqlTableSelectionHandler sqlTableSelecttionHandler, FailedViewDefinitionHandler failedViewDefinitionHandler, bool createTriggers)
{
// Clear cancelled flag
_cancelled = false;
//WaitCallback wc = new WaitCallback(delegate(object state)
//{
try
{
_isActive = true;
ConvertSourceDatabaseToDestination(sqlServerConnection, sqliteConnection, sqlConversionHandler, sqlTableSelecttionHandler, failedViewDefinitionHandler, createTriggers);
_isActive = false;
sqlConversionHandler(true, true, 100, "Finished converting database");
}
catch (Exception ex)
{
//Logging.Log(LogLevel.Error, string.Format("Failed to convert SQL Server database to SQLite database: {0}", FileLogger.GetInnerException(ex).Message));
//Logging.HandleException(ex);
_isActive = false;
sqlConversionHandler(true, false, 100, ex.Message);
}
//});
//ThreadPool.QueueUserWorkItem(wc);
}
/// <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="sqlConnection">The SQL connection.</param>
/// <param name="sqliteConnection">The SQLite connection.</param>
/// <param name="sqlConversionHandler">The SQL conversion handler.</param>
/// <param name="sqlTableSelectionHandler">The SQL table selection handler.</param>
/// <param name="failedViewDefinitionHandler">The failed view definition handler.</param>
/// <param name="createTriggers">if set to <c>true</c> [create triggers].</param>
protected abstract void ConvertSourceDatabaseToDestination(SqlConnection sqlConnection,
SQLiteConnection sqliteConnection, SqlConversionHandler sqlConversionHandler,
SqlTableSelectionHandler sqlTableSelectionHandler,
FailedViewDefinitionHandler failedViewDefinitionHandler,
bool createTriggers);
protected override void CopySourceDatabaseRowsToDestination(
SqlConnection sqlConnection, SQLiteConnection sqliteConnection, List <TableSchema> schema,
SqlConversionHandler handler)
{
CheckCancelled();
handler(false, true, 0, "Preparing to insert tables...");
//Logging.Log(LogLevel.Debug, "preparing to insert tables ...");
// Connect to the SQL Server database
sqlConnection.Open();
// Go over all tables in the schema and copy their rows
for (int i = 0; i < schema.Count; i++)
{
if (schema[i].DataAction == DataActionEnum.Ignore)
{
continue;
}
SQLiteTransaction tx = sqliteConnection.BeginTransaction();
try
{
string tableQuery = BuildSourceTableQuery(schema[i]);
SqlCommand query = new SqlCommand(tableQuery, sqlConnection);
using (SqlDataReader reader = query.ExecuteReader())
{
SQLiteCommand sqlCommand = null;
switch (schema[i].DataAction)
{
case DataActionEnum.Insert:
case DataActionEnum.Truncate:
sqlCommand = BuildSQLiteInsert(schema[i]);
break;
case DataActionEnum.Update:
sqlCommand = BuildSQLiteUpdate(schema[i]);
break;
}
int counter = 0;
while (reader.Read())
{
sqlCommand.Connection = sqliteConnection;
sqlCommand.Transaction = tx;
List <string> pnames = new List <string>();
for (int j = 0; j < schema[i].Columns.Count; j++)
{
string pname = "@" + GetNormalizedName(schema[i].Columns[j].ColumnName, pnames);
sqlCommand.Parameters[pname].Value = CastValueForColumn(reader[j], schema[i].Columns[j]);
pnames.Add(pname);
}
sqlCommand.ExecuteNonQuery();
counter++;
if (counter % 1000 == 0)
{
CheckCancelled();
tx.Commit();
handler(false, true, (int)(100.0 * i / schema.Count),
"Added " + counter + " rows to table " + schema[i].TableName + " so far");
tx = sqliteConnection.BeginTransaction();
}
}
}
CheckCancelled();
tx.Commit();
handler(false, true, (int)(100.0 * i / schema.Count), "Finished inserting rows for table " + schema[i].TableName);
//Logging.Log(LogLevel.Debug, "finished inserting all rows for table [" + schema[i].TableName + "]");
}
catch (Exception ex)
{
//Logging.Log(LogLevel.Error, string.Format("unexpected exception: {0}", FileLogger.GetInnerException(ex).Message));
//Logging.HandleException(ex);
tx.Rollback();
throw;
}
}
}
protected static void AddTriggersForForeignKeys(string sqlitePath, IEnumerable<TableSchema> schema,
string password, SqlConversionHandler handler)
{
// Connect to the newly created database
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString)) {
conn.Open();
// foreach
foreach (TableSchema dt in schema) {
try {
AddTableTriggers(conn, dt);
}
catch (Exception ex) {
//Logging.Log(LogLevel.Error, string.Format("AddTableTriggers failed: {0}", FileLogger.GetInnerException(ex).Message));
throw;
}
}
}
//Logging.Log(LogLevel.Debug, "finished adding triggers to schema");
}
private void dbConvert_Load(object sender, EventArgs e)
{
string text = "";
string appSettings = ConfigOperation.GetAppSettings("OLD_POS_DATABASE_NAME");
text = ((!bool.Parse(ConfigOperation.GetAppSettings("OLD_POS_DATABASE_SSPI"))) ? $"Data Source=(local)\\SQLExpress;Initial Catalog={appSettings};User ID=sa;Password=1031" : $"Data Source=(local)\\SQLExpress;Initial Catalog={appSettings};Integrated Security=SSPI;");
string sqlitePath = Program.DataPath + "\\Old_db.db3";
Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = delegate(bool done, bool success, int percent, string msg)
{
dbConvert dbConvert = this;
Invoke((MethodInvoker) delegate
{
dbConvert.lblMessage.Text = msg;
dbConvert.pbrProgress.Value = percent;
if (done)
{
dbConvert.Cursor = Cursors.Default;
if (success)
{
MessageBox.Show(dbConvert, msg, "資料移轉成功", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
dbConvert.Close();
}
else
{
MessageBox.Show(dbConvert, msg, "資料移轉失敗", MessageBoxButtons.OK, MessageBoxIcon.Hand);
dbConvert.pbrProgress.Value = 0;
dbConvert.lblMessage.Text = string.Empty;
Application.Exit();
}
}
});
};
SqlTableSelectionHandler selectionHandler = null;
FailedViewDefinitionHandler viewFailureHandler = delegate(ViewSchema vs)
{
dbConvert owner = this;
string updated = null;
Invoke((MethodInvoker) delegate
{
ViewFailureDialog viewFailureDialog = new ViewFailureDialog
{
View = vs
};
if (viewFailureDialog.ShowDialog(owner) == DialogResult.OK)
{
updated = viewFailureDialog.ViewSQL;
}
else
{
updated = null;
}
});
return(updated);
};
string password = "******";
bool createViews = false;
bool createTriggers = false;
SqlServerToSQLite.ConvertSqlServerToSQLiteDatabase(text, sqlitePath, password, handler, selectionHandler, viewFailureHandler, createTriggers, createViews);
}
protected override void ConvertSourceDatabaseToDestination(SqlConnection sqlConnection,
SQLiteConnection sqliteConnection, SqlConversionHandler sqlConversionHandler,
SqlTableSelectionHandler sqlTableSelectionHandler, FailedViewDefinitionHandler failedViewDefinitionHandler,
bool createTriggers)
{
// Read the schema of the SQL Server database into a memory structure
DatabaseSchema ds = ReadSourceSchema(sqlConnection, sqlConversionHandler, sqlTableSelectionHandler);
// Create the SQLite database and apply the schema
CreateSQLiteDatabase(sqliteConnection, ds, sqlConversionHandler, failedViewDefinitionHandler);
// Copy all rows from SQL Server tables to the newly created SQLite database
CopySourceDatabaseRowsToDestination(sqlConnection, sqliteConnection, ds.Tables, sqlConversionHandler);
// Add triggers based on foreign key constraints
if (createTriggers)
AddTriggersForForeignKeys(sqliteConnection, ds.Tables, sqlConversionHandler);
}
/// <summary>
/// Reads the entire SQL Server DB schema using the specified connection string.
/// </summary>
/// <param name="sqlConnection">The SQL connection.</param>
/// <param name="sqlConversionHandler">The SQL conversion handler.</param>
/// <param name="sqlTableSelectionHandler">The SQL table selection handler.</param>
/// <returns>
/// Database schema objects for every table/view in the SQL Server database.
/// </returns>
protected abstract DatabaseSchema ReadSourceSchema(SqlConnection sqlConnection, SqlConversionHandler sqlConversionHandler,
SqlTableSelectionHandler sqlTableSelectionHandler);
/// <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="handler">A handler to handle progress notifications.</param>
private static void CopySqlServerRowsToSQLiteDB(
string sqlConnString, string sqlitePath, List<TableSchema> schema,
string password, SqlConversionHandler handler)
{
CheckCancelled();
handler(false, true, 0, "Preparing to insert tables...");
_log.Debug("preparing to insert tables ...");
// Connect to the SQL Server database
using (SqlConnection ssconn = new SqlConnection(sqlConnString))
{
ssconn.Open();
// Connect to the SQLite database next
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
using (SQLiteConnection sqconn = new SQLiteConnection(sqliteConnString))
{
sqconn.Open();
// Go over all tables in the schema and copy their rows
for (int i = 0; i < schema.Count; i++)
{
SQLiteTransaction tx = sqconn.BeginTransaction();
try
{
string tableQuery = BuildSqlServerTableQuery(schema[i]);
SqlCommand query = new SqlCommand(tableQuery, ssconn);
using (SqlDataReader reader = query.ExecuteReader())
{
SQLiteCommand insert = BuildSQLiteInsert(schema[i]);
int counter = 0;
while (reader.Read())
{
insert.Connection = sqconn;
insert.Transaction = tx;
List<string> 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();
handler(false, true, (int)(100.0 * i / schema.Count),
"Added " + counter + " rows to table "+schema[i].TableName+" so far");
tx = sqconn.BeginTransaction();
}
} // while
} // using
CheckCancelled();
tx.Commit();
handler(false, true, (int)(100.0 * i / schema.Count), "Finished inserting rows for table " + 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;
} // catch
}
} // using
} // using
}
protected override void CopySourceDatabaseRowsToDestination(
SqlConnection sqlConnection, SQLiteConnection sqliteConnection, List<TableSchema> schema,
SqlConversionHandler handler)
{
CheckCancelled();
handler(false, true, 0, "Preparing to insert tables...");
//Logging.Log(LogLevel.Debug, "preparing to insert tables ...");
// Connect to the SQL Server database
sqlConnection.Open();
// Go over all tables in the schema and copy their rows
for (int i = 0; i < schema.Count; i++)
{
if (schema[i].DataAction == DataActionEnum.Ignore) continue;
SQLiteTransaction tx = sqliteConnection.BeginTransaction();
try
{
string tableQuery = BuildSourceTableQuery(schema[i]);
SqlCommand query = new SqlCommand(tableQuery, sqlConnection);
using (SqlDataReader reader = query.ExecuteReader())
{
SQLiteCommand sqlCommand = null;
switch (schema[i].DataAction)
{
case DataActionEnum.Insert:
case DataActionEnum.Truncate:
sqlCommand = BuildSQLiteInsert(schema[i]);
break;
case DataActionEnum.Update:
sqlCommand = BuildSQLiteUpdate(schema[i]);
break;
}
int counter = 0;
while (reader.Read())
{
sqlCommand.Connection = sqliteConnection;
sqlCommand.Transaction = tx;
List<string> pnames = new List<string>();
for (int j = 0; j < schema[i].Columns.Count; j++)
{
string pname = "@" + GetNormalizedName(schema[i].Columns[j].ColumnName, pnames);
sqlCommand.Parameters[pname].Value = CastValueForColumn(reader[j], schema[i].Columns[j]);
pnames.Add(pname);
}
sqlCommand.ExecuteNonQuery();
counter++;
if (counter % 1000 == 0)
{
CheckCancelled();
tx.Commit();
handler(false, true, (int)(100.0 * i / schema.Count),
"Added " + counter + " rows to table " + schema[i].TableName + " so far");
tx = sqliteConnection.BeginTransaction();
}
}
}
CheckCancelled();
tx.Commit();
handler(false, true, (int)(100.0 * i / schema.Count), "Finished inserting rows for table " + schema[i].TableName);
//Logging.Log(LogLevel.Debug, "finished inserting all rows for table [" + schema[i].TableName + "]");
}
catch (Exception ex)
{
//Logging.Log(LogLevel.Error, string.Format("unexpected exception: {0}", FileLogger.GetInnerException(ex).Message));
//Logging.HandleException(ex);
tx.Rollback();
throw;
}
}
}
private static void AddTriggersForForeignKeys(string sqlitePath, IEnumerable <TableSchema> schema, string password, SqlConversionHandler handler)
{
using (SQLiteConnection sQLiteConnection = new SQLiteConnection(CreateSQLiteConnectionString(sqlitePath, password)))
{
sQLiteConnection.Open();
foreach (TableSchema item in schema)
{
try
{
AddTableTriggers(sQLiteConnection, item);
}
catch (Exception exception)
{
_log.Error("AddTableTriggers failed", exception);
throw;
}
}
}
_log.Debug("finished adding triggers to schema");
}
protected override DatabaseSchema ReadSourceSchema(SqlConnection sqlServerConnection, SqlConversionHandler handler,
SqlTableSelectionHandler selectionHandler)
{
// First step is to read the names of all tables in the database
List<TableSchema> tables = new List<TableSchema>();
sqlServerConnection.Open();
//Logging.Log(LogLevel.Debug, "SQL Server Connection initialized");
List<string> tableNames = new List<string>();
List<string> tblschema = new List<string>();
// Initialize in clause for schemas and tables.
//Logging.Log(LogLevel.Debug, "Intializing schemas and tables to load where clauses");
string schemasToLoad = Utilities.ContainsSchemaInfo(TablesToLoad) ? Utilities.ConvertTableToLoadToInClause(TablesToLoad, true, "TABLE_SCHEMA").ToUpper() : string.Empty;
//if (schemasToLoad != string.Empty) //Logging.Log(LogLevel.Debug, string.Format("Schemas IN clause: {0}", schemasToLoad));
string tablesToLoad = TablesToLoad.Count > 0 ? Utilities.ConvertTableToLoadToInClause(TablesToLoad, false, "TABLE_NAME").ToUpper() : string.Empty;
//if (tablesToLoad != string.Empty) //Logging.Log(LogLevel.Debug, string.Format("Tables IN clause: {0}", tablesToLoad));
string whereClause = string.Empty;
if (schemasToLoad != string.Empty && tablesToLoad != string.Empty)
whereClause = string.Format(" AND {0} AND {1} ", schemasToLoad, tablesToLoad);
else if (schemasToLoad != string.Empty && tablesToLoad == string.Empty)
whereClause = string.Format(" AND {0} ", schemasToLoad);
else if (schemasToLoad == string.Empty && tablesToLoad != string.Empty)
whereClause = string.Format(" AND {0} ", tablesToLoad);
//Logging.Log(LogLevel.Debug, "Intializing SQL statement");
// This command will read the names of all tables in the database
string sqlQuery = string.Format(@"select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'{0}", whereClause);
//Logging.Log(LogLevel.Debug, string.Format("Sql Server INFORMATION_SCHEMA.TABLES query: \n\n{0}\n\n", sqlQuery));
SqlCommand cmd = new SqlCommand(sqlQuery, sqlServerConnection);
//Logging.Log(LogLevel.Debug, "SqlCommand initialized");
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableNames.Add((string)reader["TABLE_NAME"]);
tblschema.Add((string)reader["TABLE_SCHEMA"]);
}
}
// 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];
string fullName = tschma + "." + tname;
// Load only the tables in TablesToLoad.
if (TablesToLoad.Count > 0 && !TablesToLoad.Exists(t => t.SqlServerFullName.ToLower() == fullName.ToLower())) continue;
TableSchema ts = CreateTableSchema(sqlServerConnection, tname, tschma);
CreateForeignKeySchema(sqlServerConnection, ts);
tables.Add(ts);
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / tableNames.Count), "Parsed table " + tname);
//Logging.Log(LogLevel.Debug, "parsed table schema for [" + tname + "]");
}
sqlServerConnection.Close();
//Logging.Log(LogLevel.Debug, "finished parsing all tables in SQL Server schema");
// Allow the user a chance to select which tables to convert, only if the TablesToLoad list isn't defined.
if (selectionHandler != null && TablesToLoad.Count > 0)
{
List<TableSchema> updated = selectionHandler(tables);
if (updated != null)
tables = updated;
}
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>();
sqlServerConnection.Open();
cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", sqlServerConnection);
using (SqlDataReader reader = cmd.ExecuteReader())
{
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);
//Logging.Log(LogLevel.Debug, "parsed view schema for [" + vs.ViewName + "]");
}
}
sqlServerConnection.Close();
DatabaseSchema ds = new DatabaseSchema();
ds.Tables = tables;
ds.Views = views;
return ds;
}
private void btnStart_Click(object sender, EventArgs e)
{
string sqlConnString = GetSqlServerConnectionString();
bool createViews = cbxCreateViews.Checked;
string sqlitePath = txtSQLitePath.Text.Trim();
this.Cursor = Cursors.WaitCursor;
SqlConversionHandler handler = new SqlConversionHandler(delegate(bool done,
bool success, int percent, string msg) {
Invoke(new MethodInvoker(delegate() {
UpdateSensitivity();
lblMessage.Text = msg;
pbrProgress.Value = percent;
if (done)
{
btnStart.Enabled = true;
this.Cursor = Cursors.Default;
UpdateSensitivity();
if (success)
{
MessageBox.Show(this,
msg,
"Conversion Finished",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
{
if (!_shouldExit)
{
MessageBox.Show(this,
msg,
"Conversion Failed",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
pbrProgress.Value = 0;
lblMessage.Text = string.Empty;
}
else
Application.Exit();
}
}
}));
});
// SqlTableSelectionHandler selectionHandler = new SqlTableSelectionHandler(delegate(List<string> schema, List<int> numRows)
// {
// return IncludedTables;
// });
FailedViewDefinitionHandler viewFailureHandler = new FailedViewDefinitionHandler(delegate(ViewSchema vs)
{
string updated = null;
Invoke(new MethodInvoker(delegate
{
ViewFailureDialog dlg = new ViewFailureDialog();
dlg.View = vs;
DialogResult res = dlg.ShowDialog(this);
if (res == DialogResult.OK)
updated = dlg.ViewSQL;
else
updated = null;
}));
return updated;
});
string password = txtPassword.Text.Trim();
if (!cbxEncrypt.Checked) password = null;
List<string> includedTables = new List<string>();
List<string> existedTables = new List<string>();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
bool include = (bool)row.Cells[0].Value;
if (include)
{
includedTables.Add((string)row.Cells[1].Value);
if ((string)row.Cells[5].Value == "Existed") existedTables.Add((string)row.Cells[1].Value);
}
} // foreach
OracleToSQLite.ConvertOracleToSQLiteDatabase(sqlConnString, sqlitePath, password, handler,
includedTables, existedTables, viewFailureHandler, cbxTriggers.Checked, createViews);
}