public string getUpdateSqlCode(int row, database.Preferences.MySQLRow mysqlRow, MySqlConnection connection) { setDefaultItemValues(row); ItemTemplates.ItemTemplateRow tempRow = (ItemTemplates.ItemTemplateRow) this.ItemTemplate.Rows[row]; System.Data.DataTable columnsTable = new System.Data.DataTable(); //automatic detection if (mysqlRow.cellDetection == true && connection != null) { try { connection.Open(); string SQL = "SHOW COLUMNS FROM " + mysqlRow.ItemTemplateTable; MySqlCommand cmd = new MySqlCommand(SQL, connection); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { columnsTable.Columns.Add(reader.GetString(0)); } } catch (MySqlException ex) { System.Windows.Forms.MessageBox.Show(ex.Message); columnsTable = this.ItemTemplate; } finally { connection.Close(); } } else { columnsTable = this.ItemTemplate.Clone(); if (mysqlRow.ArtiID == false) { columnsTable.Columns.Remove("ArtiID"); } } string dataSQL = ""; bool insertedData = false; string sqlCodeRaw = ""; int i = 0; foreach (System.Data.DataColumn column in columnsTable.Columns) { if (this.ItemTemplate.Columns.Contains(column.ColumnName)) { if (sqlCodeRaw != "") { sqlCodeRaw += ", "; } sqlCodeRaw += "`" + column.ColumnName + "` = @value" + i; } i++; } string sqlCode = "UPDATE " + mysqlRow.ItemTemplateTable + " SET " + sqlCodeRaw; MySqlCommand c = new MySqlCommand(sqlCode); i = 0; foreach (System.Data.DataColumn column in columnsTable.Columns) { if (this.ItemTemplate.Columns.Contains(column.ColumnName)) { object value = tempRow[column.ColumnName]; c.Parameters.AddWithValue("value" + i, value); } i++; } foreach (System.Data.DataColumn column in columnsTable.Columns) { if (this.ItemTemplate.Columns.Contains(column.ColumnName)) { if (column.Unique == false && column.ColumnName != "ItemTemplate_ID") { if (tempRow.IsNull(column.ColumnName)) { if (insertedData != false) { dataSQL += ", "; } else { insertedData = true; } dataSQL += "`" + column.ColumnName + "` = NULL"; break; } else { object value = tempRow[column.ColumnName]; //if (value.GetType() != System.DBNull.Value.GetType()) //{ //Alle Spalten die einen Wert enthalten werdne in der SQL aufgelistet, um NULL-Werte automatisch setzen zu können //columnsSQL switch (value.GetType().ToString()) { case "System.String": { if (insertedData != false) { dataSQL += ", "; } else { insertedData = true; } dataSQL += "`" + column.ColumnName + "` = '" + this.escapeString(value.ToString()) + "'"; break; } case "System.Boolean": { if (insertedData != false) { dataSQL += ", "; } else { insertedData = true; } if ((bool)value == true) { dataSQL += "`" + column.ColumnName + "` = 1"; } else { dataSQL += "`" + column.ColumnName + "` = 0"; } break; } case "System.DBNull": { if (insertedData != false) { dataSQL += ", "; } else { insertedData = true; } dataSQL += "`" + column.ColumnName + "` = NULL"; break; } default: { if (insertedData != false) { dataSQL += ", "; } else { insertedData = true; } dataSQL += "`" + column.ColumnName + "` = " + value.ToString(); break; } } } } } } string code; code = "UPDATE " + mysqlRow.ItemTemplateTable + " SET " + dataSQL + " WHERE `Id_nb` = '" + tempRow.Id_nb + "' LIMIT 1"; return(code); }
public string getInsertSqlCode(int row, database.Preferences.MySQLRow mysqlRow, MySqlConnection connection) { setDefaultItemValues(row); ItemTemplates.ItemTemplateRow tempRow = (ItemTemplates.ItemTemplateRow) this.ItemTemplate.Rows[row]; System.Data.DataTable columnsTable = new System.Data.DataTable(); //automatic detection if (mysqlRow.cellDetection == true && connection != null) { try { connection.Open(); string SQL = "SHOW COLUMNS FROM " + mysqlRow.ItemTemplateTable; MySqlCommand cmd = new MySqlCommand(SQL, connection); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { columnsTable.Columns.Add(reader.GetString(0)); } } catch (MySqlException ex) { System.Windows.Forms.MessageBox.Show(ex.Message); columnsTable = this.ItemTemplate; } finally { connection.Close(); } } else { columnsTable = this.ItemTemplate.Clone(); if (mysqlRow.ArtiID == false) { columnsTable.Columns.Remove("ArtiID"); } } string columnsSQL = ""; string dataSQL = ""; bool insertedColumn = false; bool insertedData = false; foreach (System.Data.DataColumn column in columnsTable.Columns) { if (this.ItemTemplate.Columns.Contains(column.ColumnName)) { if (!tempRow.IsNull(column.ColumnName)) { object value = tempRow[column.ColumnName]; switch (value.GetType().ToString()) { case "System.String": { if ((string)value != "") { if (insertedColumn == false) { columnsSQL += "`" + column.ColumnName + "`"; insertedColumn = true; } else { columnsSQL += ", `" + column.ColumnName + "`"; } if (insertedData != false) { dataSQL += ", "; } else { insertedData = true; } dataSQL += "'" + this.escapeString(value.ToString()) + "'"; } break; } case "System.Boolean": { if (insertedColumn == false) { columnsSQL += "`" + column.ColumnName + "`"; insertedColumn = true; } else { columnsSQL += ", `" + column.ColumnName + "`"; } if (insertedData != false) { dataSQL += ", "; } else { insertedData = true; } if ((bool)value == true) { dataSQL += "1"; } else { dataSQL += "0"; } break; } default: { if (System.Convert.ToInt32(value) != 0) { if (insertedColumn == false) { columnsSQL += "`" + column.ColumnName + "`"; insertedColumn = true; } else { columnsSQL += ", `" + column.ColumnName + "`"; } if (insertedData != false) { dataSQL += ", "; } else { insertedData = true; } dataSQL += value.ToString(); } break; } } } } } string code; code = "REPLACE INTO " + mysqlRow.ItemTemplateTable + " (" + columnsSQL + ") VALUES (" + dataSQL + ")"; return(code); }