//****************** PRIVATE *******************
private void copyRows(MysqlHandler connection, string sourceTable, MysqlHandler targetConnection)
{
if (showProgress)
{
msg.msg.Text = "Start .... " + sourceTable;
msg.Show();
msg.Refresh();
}
Int64 writes = 0;
Int64 start = 0;
writes = copyValues(connection, sourceTable, start, targetConnection);
start = writes;
while (writes != 0)
{
if (showProgress)
{
msg.msg.Text = "Block ...." + start + " @ " + sourceTable;
msg.Refresh();
}
writes = copyValues(connection, sourceTable, start, targetConnection);
start += writes;
}
if (showProgress)
{
msg.Close();
}
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
Connection = new MysqlHandler(usedProfile);
Connection.connect();
progress.Maximum = queryList.SelectedItems.Count;
progress.Value = 0;
for (int i = queryList.SelectedItems.Count - 1; i >= 0; i--)
{
state.Text = queryList.SelectedItems[i].SubItems[1].Text;
Connection.sql_update(queryList.SelectedItems[i].SubItems[1].Text);
if (Connection.lastSqlErrorMessage != "")
{
if (MessageBox.Show(this, Connection.lastSqlErrorMessage + "\n Abort ? ", "SQL Error", MessageBoxButtons.YesNo, MessageBoxIcon.Hand) == DialogResult.Yes)
{
break;
}
}
queryList.SelectedItems[i].Remove();
progress.Value = progress.Maximum - i;
Application.DoEvents();
}
Connection.disConnect();
}
/// <summary>
/// gets the creation Query for the given
/// Stored procedure
/// </summary>
/// <param name="proc"></param>
/// <param name="database"></param>
private void getCreate(StoredProcedure proc, MysqlHandler database)
{
if (database.isConnected())
{
MySql.Data.MySqlClient.MySqlDataReader res = database.sql_select(MysqlProcedures.readProcStruct.Replace("[NAME]", proc.name));
if (res != null)
{
while (res.Read() && res.FieldCount > 0)
{
string getResult;
try
{
getResult = res.GetString(MysqlProcedures.createCodeIndex);
}
catch (System.Data.SqlTypes.SqlNullValueException nullEx)
{
getResult = null;
}
if (null != getResult)
{
proc.created = getResult;
res.Close();
}
return;
}
res.Close();
}
}
else
{
throw new Exception("Database must be connected");
}
}
public MysqlExplain(QueryComposer comp, MysqlHandler connection)
{
this.composer = comp;
this.db = connection;
this.explainSelect();
this.analyse();
}
public void dropTable(MysqlHandler connection, string tableName, bool backup)
{
bool disconnecting = false;
if (!connection.isConnected())
{
disconnecting = true;
connection.connect();
}
if (backup)
{
dropTable(connection, tableName + "_backup", false);
copyTable(connection, tableName, tableName + "_backup");
}
string sql = "DROP TABLE " + tableName;
connection.sql_update(sql);
if (disconnecting)
{
connection.disConnect();
}
}
public string getMysqlDump(MysqlHandler connection, string sourceTable)
{
bool disconnecting = false;
if (!connection.isConnected())
{
disconnecting = true;
connection.connect();
}
string ResultStr = connection.getTableCreationString(sourceTable);
ResultStr += "\n\n";
List <string> SqlArr = getSqlDumpRows(connection, sourceTable);
for (int i = 0; i < SqlArr.Count; i++)
{
ResultStr += SqlArr[i];
}
if (disconnecting)
{
connection.disConnect();
}
return(ResultStr);
}
public bool copyCreationTable(MysqlHandler connection, string sourceTable, MysqlHandler targetConnection)
{
bool disconnecting = false;
bool targetDisconnecting = false;
if (!connection.isConnected())
{
disconnecting = true;
connection.connect();
}
if (!targetConnection.isConnected())
{
targetDisconnecting = true;
targetConnection.connect();
}
string createSql = connection.getTableCreationString(sourceTable);
if (createSql != "")
{
targetConnection.sql_update(createSql);
}
if (disconnecting)
{
connection.disConnect();
}
if (targetDisconnecting)
{
targetConnection.disConnect();
}
targetConnection.resetTableList();
return(targetConnection.tableExists(sourceTable));
}
public MysqlExplain(String sqlStr, MysqlHandler connection)
{
this.composer = null;
this.sql = sqlStr;
this.db = connection;
this.explainSelect();
this.analyse();
}
public TableToForm(string tableName, MysqlHandler DataBase)
{
this.db = DataBase;
this.TableName = tableName;
if (!DataBase.isConnected())
{
throw new Exception("Database not connected. TableToForm needs an connected MysqlHandler");
}
maskQuery = new QueryComposer(this.TableName);
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
asyncSql bgWorkerObj = (asyncSql)e.Argument;
Profil dbProfil = bgWorkerObj.dbProfil;
string sql = bgWorkerObj.sql;
MysqlHandler database = new MysqlHandler(dbProfil);
database.connect();
if (database.isConnected())
{
//database.beginTransaction();
bgWorkerObj.status = 1;
//database.query(sql);
MySql.Data.MySqlClient.MySqlDataReader dbResult;
if (bgWorkerObj.fireSingleShots)
{
dbResult = null;
string[] singleShots = sql.Split(';');
for (int i = 0; i < singleShots.Length; i++)
{
dbResult = database.sql_select(singleShots[i]);
}
}
else
{
dbResult = database.sql_select(sql);
}
bgWorkerObj.result = dbResult;
bgWorkerObj.haseRows = (dbResult != null && dbResult.HasRows == true);
bgWorkerObj.lastError = database.lastSqlErrorMessage;
/*
* if (database.lastSqlErrorMessage != "")
* {
* // database.rollBack();
* }
* else
* {
* // database.commit();
* }
*/
bgWorkerObj.status = 2;
bgWorkerObj.resultListView = new ListView();
if (bgWorkerObj.haseRows)
{
database.sql_data2ListView(dbResult, bgWorkerObj.resultListView);
}
ThreadList[bgWorkerObj.workerID].ReportProgress(bgWorkerObj.workerID, bgWorkerObj);
}
database.disConnect();
}
public List <String> getCopyTableStatements(MysqlHandler connection, string sourceTable, MysqlHandler targetConnection)
{
bool disconnecting = false;
bool targetDisconnecting = false;
List <String> querys = new List <string>();
if (!connection.isConnected())
{
disconnecting = true;
connection.connect();
}
if (!targetConnection.isConnected())
{
targetDisconnecting = true;
targetConnection.connect();
}
targetConnection.lastSqlErrorMessage = "";
querys.Add("DROP TABLE IF EXISTS " + sourceTable);
querys.Add(connection.getTableCreationString(sourceTable));
copyRowLimit = 500;
Int64 writes = 0;
Int64 start = 0;
writes = getCopyStatements(connection, sourceTable, start, targetConnection, querys, useMassInsertQuerys);
start = writes;
while (writes != 0)
{
if (showProgress)
{
msg.msg.Text = "Block ...." + start + " @ " + sourceTable;
msg.Refresh();
}
writes = getCopyStatements(connection, sourceTable, start, targetConnection, querys, useMassInsertQuerys);
start += writes;
}
copyRowLimit = 10000;
if (disconnecting)
{
connection.disConnect();
}
if (targetDisconnecting)
{
targetConnection.disConnect();
}
return(querys);
}
private void getDatabases(Profil testProfil)
{
MysqlHandler TestConnect = new MysqlHandler(testProfil);
List <string> shematas = TestConnect.getDataBases();
tableListBox.Items.Clear();
if (shematas != null)
{
for (int i = 0; i < shematas.Count; i++)
{
tableListBox.Items.Add(shematas[i]);
}
tableListBox.Visible = true;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (lastSelectedId != "")
{
MysqlHandler mysql = new MysqlHandler(watchProfil);
mysql.connect();
string sql = "KILL " + lastSelectedId;
mysql.sql_update(sql);
mysql.disConnect();
kill_btn.Text = "KILL ";
}
}
private void init(MysqlHandler db, string source)
{
this.tableName = source;
if (!db.isConnected())
{
disConnect = true;
db.connect();
}
sourceStruct = db.getAllFieldsStruct(source);
isInit = true;
redrawComponents();
}
public void readKeys(MysqlHandler database)
{
bool closeConnect = false;
if (!database.isConnected())
{
closeConnect = true;
database.connect();
}
assignValues(database.selectAsHash("SHOW INDEX FROM " + this.tableName));
if (closeConnect)
{
database.disConnect();
}
}
public void copyTable(MysqlHandler connection, string sourceTable, MysqlHandler targetConnection)
{
bool disconnecting = false;
bool targetDisconnecting = false;
if (!connection.isConnected())
{
disconnecting = true;
connection.connect();
}
if (!targetConnection.isConnected())
{
targetDisconnecting = true;
targetConnection.connect();
}
targetConnection.lastSqlErrorMessage = "";
if (!targetConnection.tableExists(sourceTable))
{
string createSql = connection.getTableCreationString(sourceTable);
targetConnection.sql_update(createSql);
if (targetConnection.lastSqlErrorMessage.Length > 0)
{
MessageBox.Show(createSql + "\n\n" + targetConnection.lastSqlErrorMessage);
targetConnection.lastSqlErrorMessage = "";
}
else
{
copyRows(connection, sourceTable, targetConnection);
}
}
if (disconnecting)
{
connection.disConnect();
}
if (targetDisconnecting)
{
targetConnection.disConnect();
}
targetConnection.resetTableList();
}
public void copyTable(MysqlHandler connection, string sourceTable, string targetTable)
{
bool disconnecting = false;
string sql = "CREATE TABLE " + targetTable + " SELECT * FROM " + sourceTable;
if (!connection.isConnected())
{
disconnecting = true;
connection.connect();
}
connection.sql_update(sql);
if (disconnecting)
{
connection.disConnect();
}
}
private void mysql_watcher_DoWork(object sender, DoWorkEventArgs e)
{
ListView retObj = (ListView)e.Argument;
//ListView retObj = new ListView();
MysqlHandler mysql = new MysqlHandler(watchProfil);
mysql.connect();
string sql = "show full processlist";
MySql.Data.MySqlClient.MySqlDataReader rankings = mysql.sql_select(sql);
if (retObj != null)
{
mysql.sql_data2ListView(rankings, retObj);
}
mysql.disConnect();
}
public void getStartupData()
{
database = new MysqlHandler(sensorProfil);
database.connect();
if (database != null)
{
MySql.Data.MySqlClient.MySqlDataReader reader = database.sql_select("SHOW STATUS");
string name = "";
string value = "";
sensorListing.Items.Clear();
while (reader != null && reader.Read())
{
//userid = atAll.GetInt64(atAll.GetOrdinal("gladi_userid"));
//Data.Add();
name = reader.GetString(0);
value = reader.GetString(1);
sensorListing.Items.Add(name);
addToSensorTree(name);
PanelDrawing sensView = new PanelDrawing();
sensView.Name = name;
sensView.Width = flowLayout.Width - 6;
sensView.Height = 120;
sensView.BackColor = Color.FromArgb(0, 33, 0);
sensView.BorderStyle = BorderStyle.Fixed3D;
sensView.lineColor = Color.FromArgb(0, 254, 0);
sensView.positivColor = Color.FromArgb(0, 64, 0);
sensView.negativColor = Color.FromArgb(54, 0, 0);
sensView.Visible = false;
sensView.Dock = DockStyle.Top;
panelSensors.Add(sensView);
flowLayout.Controls.Add(panelSensors[panelSensors.Count - 1]);
}
if (reader != null)
{
reader.Close();
}
}
}
private List <string> getTableExport(MysqlHandler connection, string sourceTable, Int64 start)
{
List <string> Data = new List <string>();
if (connection.isConnected())
{
MySql.Data.MySqlClient.MySqlDataReader Reader =
connection.sql_select("SELECT * FROM " + sourceTable + " LIMIT " + start + "," + this.copyRowLimit);
if (Reader != null)
{
while (Reader.Read())
{
string result = getInsertSql(sourceTable, Reader);
if (result != "")
{
Data.Add(result + ";\n");
}
}
}
}
return(Data);
}
public String alterTableFromSource(MysqlHandler connection, string sourceTable, MysqlHandler targetConnection, Boolean addOnly)
{
bool disconnecting = false;
bool targetDisconnecting = false;
if (!connection.isConnected())
{
disconnecting = true;
connection.connect();
}
if (!targetConnection.isConnected())
{
targetDisconnecting = true;
targetConnection.connect();
}
string createSql = connection.getTableCreationString(sourceTable);
List <MysqlStruct> sourceStruct = connection.getAllFieldsStruct(sourceTable);
List <MysqlStruct> targetStruct = targetConnection.getAllFieldsStruct(sourceTable);
String alterSql = getAlterTableSql(sourceStruct, targetStruct, addOnly);
if (alterSql.Length > 0)
{
alterSql = "ALTER TABLE `" + sourceTable + "` " + alterSql;
}
if (disconnecting)
{
connection.disConnect();
}
if (targetDisconnecting)
{
targetConnection.disConnect();
}
targetConnection.resetTableList();
return(alterSql);
}
public static statusResult checkConnection(Profil testProfil)
{
statusResult result = new statusResult();
MysqlHandler TestConnect = new MysqlHandler(testProfil);
TestConnect.connect();
if (TestConnect.lastSqlErrorMessage.Length > 0)
{
result.message = TestConnect.lastSqlErrorMessage;
result.status = false;
result.StatusKey = 0;
return(result);
}
else
{
TestConnect.disConnect();
result.message = Projector.Properties.Resources.MysqlConnectionTestSucces;;
result.status = true;
result.StatusKey = 1;
return(result);
}
}
private void checkConnection(Profil testProfil)
{
MysqlHandler TestConnect = new MysqlHandler(testProfil);
try
{
TestConnect.connect();
}
catch (Exception)
{
//throw;
}
if (TestConnect.lastSqlErrorMessage.Length > 0)
{
errorLabel.Text = TestConnect.lastSqlErrorMessage;
}
else
{
errorLabel.Text = Projector.Properties.Resources.MysqlConnectionTestSucces;
TestConnect.disConnect();
}
}
private List <string> getSqlDumpRows(MysqlHandler connection, string sourceTable)
{
List <string> Res = new List <string>();
List <string> RTemp = new List <string>();
if (showProgress)
{
msg.msg.Text = "Start .... " + sourceTable;
msg.Show();
msg.Refresh();
}
Int64 writes = 0;
Int64 start = 0;
RTemp = getTableExport(connection, sourceTable, start);
Res.AddRange(RTemp);
start = writes;
while (writes != 0)
{
if (showProgress)
{
msg.msg.Text = "Read Block ...." + start + " @ " + sourceTable;
msg.Refresh();
}
RTemp = getTableExport(connection, sourceTable, start);
Res.AddRange(RTemp);
start += writes;
}
if (showProgress)
{
msg.Close();
}
return(Res);
}
private void fireSql(Profil dbProfil, string sql)
{
MysqlHandler database = new MysqlHandler(dbProfil);
database.connect();
if (database.isConnected())
{
ListViewWorker listTool = new ListViewWorker();
GroupBox instGroup = new GroupBox();
instGroup.Width = MainView.Width - 30;
instGroup.BackColor = SystemColors.Info;
//instGroup.Height = 350;
instGroup.Text = currentProfileName;
instGroup.AutoSize = true;
Label infoData = new Label();
infoData.AutoSize = true;
infoData.Text = dbProfil.getProperty("db_username") + "@" + dbProfil.getProperty("db_host") + "/" + dbProfil.getProperty("db_schema");
infoData.Top = 15;
infoData.Left = 15;
ListView resultView = new ListView();
resultView.View = View.Details;
resultView.Width = MainView.Width - 60;
resultView.Height = 300;
resultView.Left = 10;
resultView.Top = 30;
resultView.FullRowSelect = true;
resultView.GridLines = true;
MySql.Data.MySqlClient.MySqlDataReader dbResult = database.sql_select(sql);
if (database.lastSqlErrorMessage != "")
{
Label noData = new Label();
noData.Top = 30;
noData.Left = 15;
noData.Text = database.lastSqlErrorMessage;
noData.AutoSize = true;
noData.Font = new Font("Courier New", 10, FontStyle.Bold);
instGroup.Controls.Add(noData);
instGroup.Controls.Add(infoData);
instGroup.Height = 60;
instGroup.BackColor = Color.LightPink;
}
else
{
if (dbResult != null && dbResult.HasRows)
{
database.sql_data2ListView(dbResult, resultView);
for (int i = 0; i < resultView.Columns.Count; i++)
{
resultView.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
}
listTool.setRowColors(resultView, Color.LightBlue, Color.LightGreen);
resultView.Height = (resultView.Items.Count * 13) + 50;
if (resultView.Height > 700)
{
resultView.Height = 700;
}
listLabelExports.Add(instGroup.Text);
listViewExports.Add(resultView);
instGroup.Controls.Add(resultView);
instGroup.Controls.Add(infoData);
}
else
{
Label noData = new Label();
noData.Top = 30;
noData.Left = 15;
noData.Text = "No Result for this query ";
noData.AutoSize = true;
instGroup.Controls.Add(noData);
instGroup.Height = 60;
instGroup.BackColor = Color.LightYellow;
instGroup.Controls.Add(infoData);
}
}
MainView.Controls.Add(instGroup);
MainView.Refresh();
}
database.disConnect();
}
public Int64 copyValues(MysqlHandler connection, string sourceTable, Int64 start, MysqlHandler targetConnection)
{
Int64 count = 0;
Int64 refresher = 0;
List <MysqlStruct> Struct = connection.getAllFieldsStruct(sourceTable);
string addToField = sourceTable + ".";
if (showProgress && msg.Visible)
{
msg.progressBar.Maximum = (int)this.copyRowLimit + 1;
}
if (connection.isConnected())
{
MySql.Data.MySqlClient.MySqlDataReader Reader =
connection.sql_select("SELECT * FROM " + sourceTable + " LIMIT " + start + "," + this.copyRowLimit);
if (Reader != null)
{
//if (Reader.RecordsAffected==-1) return 0;
while (Reader.Read())
{
count++;
refresher++;
string insertSql = "INSERT INTO " + sourceTable + " SET ";
string add = "";
for (int i = 0; i < Reader.FieldCount; i++)
{
if (refresher > 1000)
{
if (showProgress && msg.Visible)
{
msg.msg.Text = "Copying Table " + sourceTable + " block: " + start + "/" + count;
msg.progressBar.Value = (int)count;
msg.progressBar.Refresh();
msg.Refresh();
msg.Focus();
}
refresher = 0;
}
System.Type fType = Reader.GetFieldType(i);
Object chk = null;
try
{
chk = Reader.GetValue(i);
}
catch (Exception)
{
//throw;
}
if (chk != null)
{
int hash = chk.GetHashCode();
string dd = chk.ToString();
if (chk != null && dd != "")
{
switch (fType.Name)
{
case "Int32":
case "UInt32":
case "Int64":
case "UInt64":
Int64 val = Reader.GetInt64(i);
insertSql += add + addToField + Struct[i].name + " = " + val;
break;
case "Single":
case "Float":
float fVal = Reader.GetFloat(i);
insertSql += add + addToField + Struct[i].name + " = '" + fVal.ToString().Replace(',', '.') + "'";
break;
case "Double":
double dVal = Reader.GetDouble(i);
insertSql += add + addToField + Struct[i].name + " = '" + dVal.ToString().Replace(',', '.') + "'";
break;
case "DateTime":
DateTime dt = Reader.GetDateTime(i);
String dtValue = zeroVal(dt.Year) + "-" + zeroVal(dt.Month) + "-" + zeroVal(dt.Day) + " " + dt.TimeOfDay;
insertSql += add + addToField + Struct[i].name + " = '" + dtValue + "'";
break;
case "Boolean":
bool bval = Reader.GetBoolean(i);
if (bval)
{
insertSql += add + addToField + Struct[i].name + " = 1";
}
else
{
insertSql += add + addToField + Struct[i].name + " = 0";
}
break;
default:
string sval = Reader.GetString(i).Replace("'", @"\'");
insertSql += add + addToField + Struct[i].name + " = '" + sval + "'";
break;
}
add = ",";
}
}
}
//returnValue += insertSql + ";\n";
targetConnection.sql_update(insertSql);
}
Reader.Close();
}
}
return(count);
}
/// <summary>
/// Reads all Procedures and updates
/// the content
/// </summary>
/// <param name="database">Used database Connection</param>
public void getProcedures(MysqlHandler database)
{
isError = false;
errorMessage = "";
bool closeConnection = false;
if (!database.isConnected())
{
closeConnection = true;
database.connect();
}
if (database.isConnected())
{
// MySql.Data.MySqlClient.MySqlDataReader data = database.sql_select(MysqlProcedures.readQuery);
List <Hashtable> data = database.selectAsHash(MysqlProcedures.readQuery);
if (database.lastSqlErrorMessage != "")
{
isError = true;
errorMessage = database.lastSqlErrorMessage;
return;
}
spList.Clear();
count = 0;
current = 0;
isLoaded = false;
for (int i = 0; i < data.Count; i++)
{
Hashtable tmpData = data[i];
StoredProcedure tmpStore = new StoredProcedure();
count++;
isLoaded = true;
if (tmpData["Db"] != null)
{
tmpStore.db = tmpData["Db"].ToString();
}
if (tmpData["Name"] != null)
{
tmpStore.name = tmpData["Name"].ToString();
}
if (tmpData["Type"] != null)
{
tmpStore.type = tmpData["Type"].ToString();
}
if (tmpData["Definer"] != null)
{
tmpStore.definer = tmpData["Definer"].ToString();
}
if (tmpData["Modified"] != null)
{
tmpStore.modified = tmpData["Modified"].ToString();
}
if (tmpData["Created"] != null)
{
tmpStore.created = tmpData["Created"].ToString();
}
if (tmpData["Security_type"] != null)
{
tmpStore.securityType = tmpData["Security_type"].ToString();
}
if (tmpData["Comment"] != null)
{
tmpStore.created = tmpData["Comment"].ToString();
}
getCreate(tmpStore, database);
spList.Add(tmpStore);
}
}
else
{
throw new Exception("MysqlProcedures: can not connect to Database");
}
if (closeConnection)
{
database.disConnect();
}
}
public String alterTableFromSource(MysqlHandler connection, string sourceTable, MysqlHandler targetConnection)
{
return(alterTableFromSource(connection, sourceTable, targetConnection, false));
}
public void init(string TableName, MysqlHandler database)
{
this.tableName = TableName;
this.readKeys(database);
this.initialized = true;
}
public void setTables(Profil useProfil, string source)
{
this.db = new MysqlHandler(useProfil);
init(db, source);
}
