/// <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);
     }
 }
Beispiel #3
0
        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);
        }
Beispiel #6
0
        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);
        }
Beispiel #8
0
        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);
        }
Beispiel #9
0
        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);
        }
Beispiel #11
0
        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");
        }
Beispiel #18
0
        /// <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;
        }
Beispiel #19
0
        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");
        }
Beispiel #21
0
 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;
                 }
             }
         }
     }
 }
Beispiel #22
0
    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
        });
    }
Beispiel #23
0
    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);
        }
    }
Beispiel #24
0
 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);
Beispiel #26
0
 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);
         }
     });
 }
Beispiel #27
0
        /// <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);
        }
Beispiel #33
0
        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");
        }
Beispiel #38
0
    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;
                }
            }
        }
Beispiel #43
0
 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;
        }
Beispiel #45
0
        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);
        }