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);
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public bool CreateSQLiteTable(string tableName, KaJourDAL.SQL sqlCe, KaJourDAL.SQL sqLITE)
{
bool ret = false;
var tableExists = sqLITE.TableExists(tableName);
ret = tableExists;
if (!tableExists)
{
// SELECT * FROM INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME, ORDINAL_POSITION
// SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='adatcjou' order by TABLE_NAME, ORDINAL_POSITION
var sel = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "' order by TABLE_NAME, ORDINAL_POSITION";
var colList = sqlCe.Execute("SELECT", sel);
string sqlColList = "";
for (int i = 0; i < colList.Rows.Count; i++)
{
var colName = colList.Rows[i]["COLUMN_NAME"].ToString();
var colType = colList.Rows[i]["DATA_TYPE"].ToString();
var colCollate = "";
// Lite
// [C001] integer NOT NULL PRIMARY KEY AUTOINCREMENT,
// Ce
// [C001] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
// AUTOINC_MIN AUTOINC_MAX AUTOINC_NEXT AUTOINC_SEED AUTOINC_INCREMENT
// -2147483648 2147483647 2 1 1
var colIdent = "";
var ColPK = "";
if (colList.Rows[i]["AUTOINC_SEED"] != System.DBNull.Value)
{
//var colAutoSeed = colList.Rows[i]["AUTOINC_SEED"];
//var colAutoInc = colList.Rows[i]["AUTOINC_INCREMENT"];
colIdent = " PRIMARY KEY AUTOINCREMENT ";
if (colType == "int")
{
colType = "integer";
}
}
//if (colList.Rows[i]["AUTOINC_INCREMENT"] != System.DBNull.Value)
//{
// var colAutoInc = colList.Rows[i]["AUTOINC_INCREMENT"];
//}
if (colType == "nvarchar" || colType == "nchar")
{
// [CC1] nvarchar(4) NULL COLLATE NOCASE
//, [CCHINWEIS] nvarchar(30) NULL COLLATE NOCASE
// [CNChar-10] nchar(10),
var colMaxLen = colList.Rows[i]["CHARACTER_MAXIMUM_LENGTH"];
colType += "(" + colMaxLen.ToString() + ")";
colCollate = " COLLATE NOCASE ";
}
if (colType == "binary")
{
// [CBin-50] binary(50),
var colMaxLen = colList.Rows[i]["CHARACTER_MAXIMUM_LENGTH"];
colType += "(" + colMaxLen.ToString() + ")";
}
if (colType == "binary")
{
// [CVarBin-50] varbinary(50)
var colMaxLen = colList.Rows[i]["CHARACTER_MAXIMUM_LENGTH"];
colType += "(" + colMaxLen.ToString() + ")";
}
if (colType == "numeric")
{
// , [LFIX] numeric(15,5) NULL
// , [JBETRAG] numeric(18,2) NULL
var colNumPrec = colList.Rows[i]["NUMERIC_PRECISION"]; // NUMERIC_PRECISION 18
var colNumScale = colList.Rows[i]["NUMERIC_SCALE"]; // NUMERIC_SCALE 2
colType += "(" + colNumPrec.ToString() + "," + colNumScale.ToString() + ")";
}
var colIsNull = colList.Rows[i]["IS_NULLABLE"].ToString(); // "YES", "NO"
//
if (sqlColList != "")
{
sqlColList += " , ";
}
sqlColList += " [" + colName + "] " + colType + " ";
// [CRowVersion] rowversion NOT NULL,
if (colIsNull == "YES")
{
sqlColList += " NULL ";
}
if (colIsNull == "NO")
{
sqlColList += " NOT NULL ";
}
sqlColList += colIdent;
sqlColList += ColPK;
sqlColList += colCollate;
}
// Create Table
string crTab = "CREATE TABLE [" + tableName + "] ( " + sqlColList + " ) ";
var create = sqLITE.ExecuteNonQuery("CREATE TABLE", crTab);
ret = true;
}
return(ret);
}
private bool DispStatus()
{
bool ret = false;
this.textBoxAction.Text = "";
this.toolStripProgressBarTable.Value = 0;
// Grid-Clear
this.dataGridView1.Rows.Clear();
Application.DoEvents();
int countTables = 0;
int countRows = 0;
StringBuilder sb = new StringBuilder();
List <GridRecord> gridRecordList = new List <GridRecord>();
// 1. Check Databases
// 2. Check Tables in SQLite
// 3. Delete Records in SQLite
// 4. Select Records from SQLCe and insert to SQLite
// 5. Check RecordCount from SQLCe and SQLite
//
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 + "'";
// ##############################################
sb.AppendLine(KaJourDAL.KaJour_Global_CE.SQLProvider + ":");
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();
countRows += tableRec1;
this.toolStripProgressBarTable.Value = ((iTable + 1) * 100) / tablesCE.Rows.Count;
Application.DoEvents();
sb.AppendLine(" " + tableName + " Rec:" + tableRec1.ToString());
// Grid-Data
GridRecord gridRecord = new GridRecord();
gridRecord.Row = iTable + 1;
gridRecord.Name1 = tableName;
gridRecord.Rec1 = tableRec1;
gridRecordList.Add(gridRecord);
}
}
sb.AppendLine(" Count: Tables: " + countTables.ToString() + ", Rows: " + countRows.ToString());
countTables = 0;
countRows = 0;
// ##############################################
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++;
var tableName = tablesLITE.Rows[iTable][0].ToString();
sqLITE.Connect();
var tableRec1 = sqLITE.GetTableRecCount(tableName);
sqLITE.DisConnect();
countRows += tableRec1;
this.toolStripProgressBarTable.Value = ((iTable + 1) * 100) / tablesLITE.Rows.Count;
Application.DoEvents();
sb.AppendLine(" " + tableName + " Rec:" + tableRec1.ToString());
// Grid-Data
// find name1
for (int i = 0; i < gridRecordList.Count; i++)
{
string name1 = gridRecordList[i].Name1;
if (tableName == name1)
{
gridRecordList[i].Name2 = tableName;
gridRecordList[i].Rec2 = tableRec1;
break; // ==================>
}
}
}
}
sb.AppendLine(" Count: Tables: " + countTables.ToString() + ", Rows: " + countRows.ToString());
this.textBoxAction.Text = sb.ToString();
this.toolStripProgressBarTable.Value = 100;
// Grid-Data
for (int i = 0; i < gridRecordList.Count; i++)
{
int newRow = this.dataGridView1.Rows.Add();
this.dataGridView1.Rows[newRow].Cells["Row"].Value = gridRecordList[i].Row;
this.dataGridView1.Rows[newRow].Cells["Table1"].Value = gridRecordList[i].Name1;
this.dataGridView1.Rows[newRow].Cells["Rec1"].Value = gridRecordList[i].Rec1;
this.dataGridView1.Rows[newRow].Cells["Table2"].Value = gridRecordList[i].Name2;
this.dataGridView1.Rows[newRow].Cells["Rec2"].Value = gridRecordList[i].Rec2;
bool toggle = (i % 2) == 0;
// Zeilenfinder
if (toggle)
{
this.dataGridView1.Rows[newRow].DefaultCellStyle.BackColor = Color.LightCyan;
}
}
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);
}
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);
}
