private bool CopyDataV2()
{
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: SQLITE: Connect, DisConnect - " + 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: SQLCE: GetTableList - " + 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);
var exc = sqLITE.GetException();
if (exc != null)
{
MessageBox.Show("Error: SQLITE: DELETE - " + exc.Message);
sb.AppendLine("--------------------");
sb.AppendLine("Error: SQLITE: DELETE - " + exc.Message);
sb.AppendLine("--------------------");
error = true;
break; //=================>
}
bool paraOk = false;
//var par = sqlCe.InitParameterList();
string sqlFieldList = "";
string sqlValueList = "";
string sqlIns = sqLITE.InsertBuilder(tableName); // "insert into Table"
// using (var transaction = connection.BeginTransaction())
// var command = connection.CreateCommand();
// command.CommandText = "INSERT INTO data VALUES ($value)"
// var parameter = command.CreateParameter();
// parameter.ParameterName = "$value";
// command.Parameters.Add(parameter);
// // Insert a lot of data
// parameter.Value = random.Next();
// command.ExecuteNonQuery();
// transaction.Commit();
sqLITE.BeginTransaction();
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
List <string> paramList = new List <string>();
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);
paramList.Add(colName);
// (Fld1) values (@Fld1)
if (sqlFieldList != "")
{
sqlFieldList += ",";
}
sqlFieldList += " " + colName;
if (sqlValueList != "")
{
sqlValueList += ",";
}
sqlValueList += " @" + colName;
}
// insert into SQLite
//sqlIns += " (" + sqlFieldList + ") VALUES (" + sqlValueList + ")"; // " (Fld1) values (@Fld1)"
sqlIns += " VALUES (" + sqlValueList + ")"; // " values (@Fld1)"
sqLITE.CreateCommand(sqlIns);
sqLITE.AddParameters(paramList);
paraOk = true;
}
List <object> paramValues = new List <object>();
for (int iCol = 0; iCol < tableSelect.Columns.Count; iCol++)
{
var colVal = tableSelect.Rows[iRow][iCol];
var colName = tableSelect.Columns[iCol].ColumnName;
//par[colName] = colVal;
paramValues.Add(colVal);
}
sqLITE.SetParameters(paramValues);
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");
exc = sqLITE.GetException();
if (exc != null)
{
MessageBox.Show("Error: SQLITE: INSERT - " + exc.Message);
sb.AppendLine("--------------------");
sb.AppendLine("Error: SQLITE: INSERT - " + exc.Message);
sb.AppendLine("--------------------");
error = true;
break; //=================>
}
}
if (error)
{
sqLITE.EndTransaction();
break; //=================>
}
sqLITE.EndTransaction();
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);
}