private MySqlCommand BuildUpdateCommand(string tableName, DataTable attributes) { if (attributes == null || attributes.Rows.Count <= 0) { return null; } DataRow row = attributes.Rows[0]; DataTable tableSchema = this.GetSchema(tableName); MySqlCommand cmd = new MySqlCommand(); MySqlCommandBuilder bld = new MySqlCommandBuilder(); int modifiedColumns = 0; int whereColumns = 0; string tableNameEscaped = bld.QuoteIdentifier(tableName); string sqlBody = ""; string sqlWhere = "WHERE"; sqlBody = string.Format("UPDATE {0} SET", tableNameEscaped); foreach (DataRow schemaColumn in tableSchema.Rows) { string columnKey = (string)schemaColumn["COLUMN_KEY"]; string columnName = (string)schemaColumn["COLUMN_NAME"]; string dataType = (string)schemaColumn["DATA_TYPE"]; string columnNameEscaped = bld.QuoteIdentifier(columnName); if (columnKey.Length > 0) { if (columnKey == "PRI") { // Add WHERE clause to satisfy PRImary key if (attributes.Columns.Contains(columnName)) { object originalValue = row[columnName, DataRowVersion.Original]; string paramName = "@w" + modifiedColumns.ToString(); sqlWhere += string.Format("{2}{0} = {1}", columnNameEscaped, paramName, (whereColumns++ > 0 ? " AND " : " ")); cmd.Parameters.AddWithValue(paramName, originalValue); } } // Never update any UNIque, PRImary or MULty-key columns continue; } if (Array.IndexOf(stringTypes, dataType.ToUpper()) < 0) { // For now, ignore non-string parameters continue; } if (attributes.Columns.Contains(columnName)) { object orignalObject = row[columnName, DataRowVersion.Original]; object currentObject = row[columnName, DataRowVersion.Current]; string originalValue = Convert.IsDBNull(orignalObject) ? "" : (string)orignalObject; string currentValue = Convert.IsDBNull(currentObject) ? "" : (string)currentObject; if (originalValue != currentValue) { // Add SET expression string paramName = "@p" + modifiedColumns.ToString(); sqlBody += string.Format("{2}{0} = {1}", columnNameEscaped, paramName, (modifiedColumns++ > 0 ? ", " : " ")); cmd.Parameters.AddWithValue(paramName, currentValue); } } } cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = string.Format("{0} {1}", sqlBody, sqlWhere); if (modifiedColumns > 0) { return cmd; } else { return null; } }
public void RepairMissingAttributes() { this.GetPartTypes(delegate(List<PartType> partTypes) { foreach (PartType partType in partTypes) { MySqlCommand cmd = new MySqlCommand(); MySqlCommandBuilder bld = new MySqlCommandBuilder(); Connect(); cmd.Connection = _con; string typeAttributesTable = string.Format("{0}_attributes", partType.name); string partNumColumnEscaped = bld.QuoteIdentifier("Part_num"); string partsTableEscaped = bld.QuoteIdentifier("Parts"); string partTypesTableEscaped = bld.QuoteIdentifier("Part_types"); string partTypeIdColumnEscaped = bld.QuoteIdentifier("Part_type_id"); string typeAttributesTableEscaped = bld.QuoteIdentifier(typeAttributesTable); cmd.CommandText = string.Format("SELECT {0} FROM {1} AS P NATURAL JOIN {2} WHERE P.{3} = @partType AND P.{0} NOT IN ( SELECT {0} FROM {4} )", partNumColumnEscaped, partsTableEscaped, partTypesTableEscaped, partTypeIdColumnEscaped, typeAttributesTableEscaped); cmd.Parameters.AddWithValue("@partType", partType.typeId); #if DEBUG Console.WriteLine(Util.SqlCommandToString(cmd)); #endif MySqlDataReader typeReader = cmd.ExecuteReader(); List<MySqlCommand> insertCommands = new List<MySqlCommand>(); try { while (typeReader.Read()) { string partNum = (string)typeReader["Part_num"]; MySqlCommand insertCmd = new MySqlCommand(); insertCmd.Connection = _con; insertCmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES (@partNum)", typeAttributesTableEscaped, partNumColumnEscaped); insertCmd.Parameters.AddWithValue("@partNum", partNum); insertCommands.Add(insertCmd); } } finally { typeReader.Close(); } foreach (MySqlCommand insertCmd in insertCommands) { int rowsAffected = insertCmd.ExecuteNonQuery(); if (rowsAffected < 1) { Console.WriteLine("Warning: Failed to add {0} to {1} table", (string)insertCmd.Parameters["@partNum"].Value, typeAttributesTable); } } } }, null); }