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