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);
}
