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