/// <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 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 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 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)); }
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); }
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); }
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(); } }
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 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 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); }
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); }
public Int64 getCopyStatements(MysqlHandler connection, string sourceTable, Int64 start, MysqlHandler targetConnection, List <string> queryList, bool asFullInsert) { 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; bool startRow = true; string fullInsert = "INSERT INTO " + sourceTable; string valuesExport = ""; string valuesExportAdd = ""; string fields = ""; string fieldsAdd = "("; string valuesSeperator = ""; while (Reader.Read()) { count++; refresher++; string insertSql = "INSERT INTO " + sourceTable + " SET "; string add = ""; valuesExportAdd = valuesSeperator + "("; for (int i = 0; i < Reader.FieldCount; i++) { if (refresher > 1000) { if (showProgress && msg.Visible) { msg.msg.Text = "get copy statement for 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 (asFullInsert) { if (startRow) { fields += fieldsAdd + "`" + Struct[i].name + "`"; fieldsAdd = ","; } } 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; if (asFullInsert) { valuesExport += valuesExportAdd + val; } break; case "Single": case "Float": float fVal = Reader.GetFloat(i); insertSql += add + addToField + Struct[i].name + " = '" + fVal.ToString().Replace(',', '.') + "'"; if (asFullInsert) { valuesExport += valuesExportAdd + "'" + fVal.ToString().Replace(',', '.') + "'"; } break; case "Double": double dVal = Reader.GetDouble(i); insertSql += add + addToField + Struct[i].name + " = '" + dVal.ToString().Replace(',', '.') + "'"; if (asFullInsert) { valuesExport += valuesExportAdd + "'" + 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 + "'"; if (asFullInsert) { valuesExport += valuesExportAdd + "'" + 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"; } if (asFullInsert) { if (bval) { valuesExport += valuesExportAdd + "1"; } else { valuesExport += valuesExportAdd + "0"; } } break; default: string stringVal = Reader.GetString(i); /* * if (stringVal.Contains(@"\")) * { * stringVal.Replace(@"\", @"\\"); * } */ string sval = stringVal.Replace("'", @"\'"); sval = sval.Replace("‘", "?"); insertSql += add + addToField + Struct[i].name + " = '" + sval + "'"; if (asFullInsert) { valuesExport += valuesExportAdd + "'" + sval + "'"; } break; } add = ","; valuesExportAdd = ","; } else { valuesExport += valuesExportAdd + "NULL"; } } else { valuesExport += valuesExportAdd + "NULL"; } valuesSeperator = ","; } valuesExport += ")"; startRow = false; if (!asFullInsert) { queryList.Add(insertSql); } } if (asFullInsert && count > 0) { string exportSql = fullInsert + fields + ") VALUES " + valuesExport + ";"; queryList.Add(exportSql); } 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(); } }