示例#1
0
        private bool CopyData()
        {
            bool ret = false;

            this.textBoxAction.Text = "";
            this.toolStripProgressBarTable.Value = 0;
            Application.DoEvents();

            DateTime dtStart = DateTime.Now;

            int countTables = 0;
            int countRows   = 0;

            StringBuilder sb = new StringBuilder();

            //
            KaJourDAL.KaJour_Global_CE.SQLProvider = "SQLCE";
            KaJourDAL.KaJour_Global_CE.SQLConnStr  = "Data Source='" + this.textBoxSqlCe.Text + "'";

            KaJourDAL.KaJour_Global_LITE.SQLProvider = "SQLITE";
            KaJourDAL.KaJour_Global_LITE.SQLConnStr  = "Data Source='" + this.textBoxSQLite.Text + "'";

            bool error = false;

            bool testNRecords     = this.checkBoxTestNRecords.Checked;
            int  testNRecordCount = -1;

            try
            {
                testNRecordCount = Convert.ToInt32(this.textBoxTestNRecords.Text);
            }
            catch (Exception)
            {
            }

            // ##############################################
            sb.AppendLine(KaJourDAL.KaJour_Global_CE.SQLProvider + ":");

            var sqLITE = new KaJourDAL.SQL(KaJourDAL.KaJour_Global_LITE.SQLProvider, KaJourDAL.KaJour_Global_LITE.SQLConnStr);

            try
            {
                sqLITE.Connect();
                sqLITE.DisConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
                return(ret);
            }

            var       sqlCe    = new KaJourDAL.SQL(KaJourDAL.KaJour_Global_CE.SQLProvider, KaJourDAL.KaJour_Global_CE.SQLConnStr);
            DataTable tablesCE = null;

            try
            {
                sqlCe.Connect();
                tablesCE = sqlCe.GetTableList("", false);
                sqlCe.DisConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
                return(ret);
            }
            if (tablesCE != null)
            {
                for (int iTable = 0; iTable < tablesCE.Rows.Count; iTable++)
                {
                    countTables++;

                    var tableName = tablesCE.Rows[iTable][0].ToString();
                    sqlCe.Connect();
                    var tableRec1 = sqlCe.GetTableRecCount(tableName);
                    sqlCe.DisConnect();

                    this.toolStripProgressBarTable.Value = ((iTable + 1) * 100) / tablesCE.Rows.Count;
                    this.toolStripStatusLabel2.Text      = " " + (iTable + 1).ToString() + "/" + tablesCE.Rows.Count.ToString() + " " + tableName + " 0/" + tableRec1.ToString() + " ";
                    Application.DoEvents();

                    sb.AppendLine("  " + tableName + "   Rec:" + tableRec1.ToString());

                    // Check if Table exists, Create Table
                    bool sqliteCheckCreateTable = this.CreateSQLiteTable(tableName, sqlCe, sqLITE);

                    // delete SQLite
                    var del    = sqLITE.DeleteBuilder(tableName);
                    var retDel = sqLITE.ExecuteNonQuery("DELETE", del);

                    bool   paraOk       = false;
                    var    par          = sqlCe.InitParameterList();
                    string sqlFieldList = "";
                    string sqlValueList = "";
                    string sqlIns       = sqLITE.InsertBuilder(tableName); // "insert into Table"

                    var tableSelect = sqlCe.Execute("SELECT", "SELECT * FROM " + tableName);
                    for (int iRow = 0; iRow < tableSelect.Rows.Count; iRow++)
                    {
                        countRows++;

                        // Parameter
                        //par = sqlCe.InitParameterList();
                        if (!paraOk)
                        {
                            // Parameter
                            sqlFieldList = "";
                            sqlValueList = "";
                            for (int iCol = 0; iCol < tableSelect.Columns.Count; iCol++)
                            {
                                var colVal  = tableSelect.Rows[iRow][iCol];
                                var colName = tableSelect.Columns[iCol].ColumnName;

                                par.Add(colName, colVal);

                                // (Fld1) values (@Fld1)
                                if (sqlFieldList != "")
                                {
                                    sqlFieldList += ",";
                                }
                                sqlFieldList += " [" + colName + "]";

                                if (sqlValueList != "")
                                {
                                    sqlValueList += ",";
                                }
                                sqlValueList += " @" + colName;
                            }

                            // insert into SQLite
                            //var sqLITE = new KaJourDAL.SQL(KaJourDAL.KaJour_Global_LITE.SQLProvider, KaJourDAL.KaJour_Global_LITE.SQLConnStr);
                            sqlIns += " (" + sqlFieldList + ") VALUES (" + sqlValueList + ")";  // (Fld1) values (@Fld1)

                            //...

                            paraOk = true;
                        }

                        for (int iCol = 0; iCol < tableSelect.Columns.Count; iCol++)
                        {
                            var colVal  = tableSelect.Rows[iRow][iCol];
                            var colName = tableSelect.Columns[iCol].ColumnName;
                            //par.Add(colName, colVal);

                            par[colName] = colVal;
                        }

                        this.toolStripStatusLabel2.Text = " " + (iTable + 1).ToString() + "/" + tablesCE.Rows.Count.ToString() + " " + tableName + " " + (iRow + 1).ToString() + "/" + tableRec1.ToString() + " ";
                        bool doEvents = UXHelper.CalcModulo(iRow);
                        if (doEvents)
                        {
                            Application.DoEvents();
                        }

                        // Test
                        if (testNRecords)
                        {
                            if (testNRecordCount > 0)
                            {
                                if (iRow >= testNRecordCount)
                                {
                                    break;  //=================>
                                }
                            }
                        }

                        // Insert
                        var retIns = sqLITE.ExecuteNonQuery("INSERT", sqlIns, par);
                        var exc    = sqLITE.GetException();
                        if (exc != null)
                        {
                            MessageBox.Show("Error:" + exc.Message);
                            sb.AppendLine("--------------------");
                            sb.AppendLine("Error:" + exc.Message);
                            sb.AppendLine("--------------------");

                            error = true;
                            break;  //=================>
                        }
                    }
                    if (error)
                    {
                        break;  //=================>
                    }

                    ret = true;
                }
            }

            this.toolStripProgressBarTable.Value = 100;

            DateTime dtEnde = DateTime.Now;

            TimeSpan ts        = dtEnde - dtStart;
            string   timings   = "Duration: " + dtStart.ToString("HH:mm:ss") + " - " + dtEnde.ToString("HH:mm:ss") + " -> " + ts.ToString();
            double   RecPerSec = countRows / ts.TotalSeconds;

            sb.AppendLine("Count: Tables: " + countTables.ToString() + ", Rows: " + countRows.ToString() + ", Rec/Sec: " + RecPerSec.ToString());
            sb.AppendLine("" + timings);
            this.textBoxAction.Text = sb.ToString();

            this.toolStripStatusLabel2.Text = " Fertig: " + timings;

            return(ret);
        }
        private bool DisplayData()
        {
            bool ret = false;

            this.toolStripStatusLabel1.Text  = "Load Data...";
            this.textBoxAction.Text          = "";
            this.toolStripProgressBar1.Value = 0;
            // Clear Grid
            this.dataGridView1.Rows.Clear();
            if (!_gridColOk)
            {
                this.dataGridView1.Columns.Clear();
            }
            Application.DoEvents();

            int maxRows = -1;

            if (this.checkBoxTop.Checked)
            {
                try
                {
                    maxRows = Convert.ToInt32(this.textBoxTop.Text);
                }
                catch (Exception)
                {
                }
            }

            int countRows = 0;

            StringBuilder sb = new StringBuilder();
            StringBuilder sl = new StringBuilder();

            KaJourDAL.KaJour_Global_LITE.SQLProvider = _SQLProvider;
            KaJourDAL.KaJour_Global_LITE.SQLConnStr  = "Data Source='" + _SQLDB + "'";

            // ##############################################
            sb.AppendLine(KaJourDAL.KaJour_Global_LITE.SQLProvider + ":");

            var       sqCEorLITE = new KaJourDAL.SQL(KaJourDAL.KaJour_Global_LITE.SQLProvider, KaJourDAL.KaJour_Global_LITE.SQLConnStr);
            DataTable tablesLITE = null;

            try
            {
                sqCEorLITE.Connect();
                tablesLITE = sqCEorLITE.GetTableList("", false);
                sqCEorLITE.DisConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
                return(ret);
            }

            var tableName = this.textBoxTableName.Text;

            sqCEorLITE.Connect();
            var tableRec1 = sqCEorLITE.GetTableRecCount(tableName);

            sqCEorLITE.DisConnect();

            sb.AppendLine("  " + tableName + "   Rec:" + tableRec1.ToString());

            // Display
            var sel = "SELECT * FROM " + tableName;

            if (this.checkBoxTop.Checked)
            {
                sel = sqCEorLITE.TopBuilder(tableName, "SELECT ", "* FROM {0}", maxRows);
            }
            DataTable tableSelect = sqCEorLITE.Execute("SELECT", sel);

            if (!_gridColOk)
            {
                // Init Grid
                var gr = CreateGraphics();
                _dgh = new DataGridHelper(this.dataGridView1, gr);
                _dgh.Init();
                //_dgh.ColumAdd("", "", "__", "", "");
                _dgh.ColumAdd("_Row_", "#", "#####_", "Alignment_MiddleRight", "#"); // 0
                for (int iCol = 0; iCol < tableSelect.Columns.Count; iCol++)
                {
                    var colName = tableSelect.Columns[iCol].ColumnName;
                    _dgh.ColumAdd(colName, colName, colName + "__", "", "");
                }

                _gridColOk = true;
            }

            for (int iRow = 0; iRow < tableSelect.Rows.Count; iRow++)
            {
                countRows++;

                this.toolStripProgressBar1.Value = ((iRow + 1) * 100) / tableRec1;
                this.toolStripStatusLabel1.Text  = "Load Data... " + (iRow + 1).ToString();
                var doEvents = UXHelper.CalcModulo(iRow);
                if (doEvents)
                {
                    Application.DoEvents();
                }

                sl.Length = 0;

                // Grid-Data
                int newRow = this.dataGridView1.Rows.Add();
                this.dataGridView1.Rows[newRow].Cells["_Row_"].Value = newRow + 1;
                for (int iCol = 0; iCol < tableSelect.Columns.Count; iCol++)
                {
                    var colVal = tableSelect.Rows[iRow][iCol];
                    if (sl.Length != 0)
                    {
                        sl.Append(", ");
                    }
                    sl.Append(colVal);

                    // Grid-Data
                    var colName = tableSelect.Columns[iCol].ColumnName;
                    this.dataGridView1.Rows[newRow].Cells[colName].Value = colVal;
                }
                sl.AppendLine("");
                sb.Append(sl);
            }

            this.toolStripStatusLabel1.Text  = "Load Data Ok.";
            this.textBoxAction.Text          = sb.ToString();
            this.toolStripProgressBar1.Value = 100;

            return(ret);
        }