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 DelTarget()
{
bool ret = false;
this.textBoxAction.Text = "";
this.toolStripProgressBarTable.Value = 0;
Application.DoEvents();
int countTables = 0;
int countRows = 0;
StringBuilder sb = new StringBuilder();
KaJourDAL.KaJour_Global_LITE.SQLProvider = "SQLITE";
KaJourDAL.KaJour_Global_LITE.SQLConnStr = "Data Source='" + this.textBoxSQLite.Text + "'";
// ##############################################
sb.AppendLine(KaJourDAL.KaJour_Global_LITE.SQLProvider + ":");
var sqLITE = new KaJourDAL.SQL(KaJourDAL.KaJour_Global_LITE.SQLProvider, KaJourDAL.KaJour_Global_LITE.SQLConnStr);
DataTable tablesLITE = null;
try
{
sqLITE.Connect();
tablesLITE = sqLITE.GetTableList("", false);
sqLITE.DisConnect();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
return(ret);
}
if (tablesLITE != null)
{
for (int iTable = 0; iTable < tablesLITE.Rows.Count; iTable++)
{
countTables++;
this.toolStripProgressBarTable.Value = ((iTable + 1) * 100) / tablesLITE.Rows.Count;
Application.DoEvents();
var tableName = tablesLITE.Rows[iTable][0].ToString();
sqLITE.Connect();
var tableRec1 = sqLITE.GetTableRecCount(tableName);
sqLITE.DisConnect();
countRows += tableRec1;
// Delete
var del = sqLITE.DeleteBuilder(tableName);
var retDel = sqLITE.ExecuteNonQuery("DELETE", del);
sqLITE.Connect();
var tableRec2 = sqLITE.GetTableRecCount(tableName);
sqLITE.DisConnect();
sb.AppendLine(" " + tableName + " Rec:" + tableRec1.ToString() + " Del:" + retDel.ToString() + " Rec:" + tableRec2.ToString());
}
}
sb.AppendLine(" Count: Tables: " + countTables.ToString() + ", Rows: " + countRows.ToString());
this.textBoxAction.Text = sb.ToString();
this.toolStripProgressBarTable.Value = 100;
return(ret);
}
