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