public string BuildSelectQuery(MySqlCommand cmd) { StringBuilder SelectBuilder = new StringBuilder("select * from `gaminginv`.`owner` where "); List <int> PlaceHolder = new List <int>(); if (!IDName.Equals(string.Empty)) { PlaceHolder.Add(1); } if (!PhoneNumber.Equals(string.Empty)) { PlaceHolder.Add(2); } if (!EmailAddress.Equals(string.Empty)) { PlaceHolder.Add(3); } if (!BadgeName.Equals(string.Empty)) { PlaceHolder.Add(4); } if (OwnerID > 0) { PlaceHolder.Add(5); } for (int i = 0; i < PlaceHolder.Count - 1; i++) { switch (PlaceHolder.ElementAt(i)) { case 1: SelectBuilder.Append("`Name` like %" + "@NameParam" + "% + and "); cmd.Parameters.AddWithValue("@NameParam", IDName.ToLower()); break; case 2: SelectBuilder.Append("`Phone` = @PhoneParam and "); cmd.Parameters.AddWithValue("@PhoneParam", PhoneNumber); break; case 3: SelectBuilder.Append("`Email` like %" + "@EmailParam" + "% and "); cmd.Parameters.AddWithValue("@EmailParam", EmailAddress.ToLower()); break; case 4: SelectBuilder.Append("`BadgeName` like %" + "@BadgeParam" + "% and "); cmd.Parameters.AddWithValue("@BadgeParam", BadgeName.ToLower()); break; case 5: SelectBuilder.Append("`ID` = @IDParam and "); cmd.Parameters.AddWithValue("@IDParam", OwnerID); break; } } switch (PlaceHolder.Last()) { case 1: SelectBuilder.Append("`Name` like %" + "@NameParam" + '%'); cmd.Parameters.AddWithValue("@NameParam", IDName.ToLower()); break; case 2: SelectBuilder.Append("`Phone` = @PhoneParam"); cmd.Parameters.AddWithValue("@PhoneParam", PhoneNumber); break; case 3: SelectBuilder.Append("`Email` like %" + "@EmailParam" + '%'); cmd.Parameters.AddWithValue("@EmailParam", EmailAddress.ToLower()); break; case 4: SelectBuilder.Append("`BadgeName` = @BadgeParam"); cmd.Parameters.AddWithValue("@BadgeParam", BadgeName.ToLower()); break; case 5: SelectBuilder.Append("`ID` = @IDParam"); cmd.Parameters.AddWithValue("@IDParam", OwnerID); break; } SelectBuilder.Append(';'); return(SelectBuilder.ToString()); }
public string BuildUpdateQuery(MySqlCommand cmd) { StringBuilder UpdateBuilder = new StringBuilder("update `gaminginv`.`owner` set "); List <int> PlaceHolder = new List <int>(); if (!IDName.Equals(string.Empty)) { PlaceHolder.Add(1); } if (!PhoneNumber.Equals(string.Empty)) { PlaceHolder.Add(2); } if (!EmailAddress.Equals(string.Empty)) { PlaceHolder.Add(3); } if (!BadgeName.Equals(string.Empty)) { PlaceHolder.Add(4); } for (int i = 0; i < PlaceHolder.Count - 1; i++) { switch (PlaceHolder.ElementAt(i)) { case 1: UpdateBuilder.Append("`Name` = @NameParam" + ','); cmd.Parameters.AddWithValue("@NameParam", IDName); break; case 2: UpdateBuilder.Append("`Phone` = @PhoneParam" + ','); cmd.Parameters.AddWithValue("@PhoneParam", PhoneNumber); break; case 3: UpdateBuilder.Append("`Email` = @EmailParam" + ','); cmd.Parameters.AddWithValue("@EmailParam", EmailAddress); break; case 4: UpdateBuilder.Append("`BadgeName` = @BadgeParam" + ','); cmd.Parameters.AddWithValue("@BadgeParam", BadgeName); break; } } switch (PlaceHolder.Last()) { case 1: UpdateBuilder.Append("`Name` = @NameParam"); cmd.Parameters.AddWithValue("@NameParam", IDName); break; case 2: UpdateBuilder.Append("`Phone` = @PhoneParam"); cmd.Parameters.AddWithValue("@PhoneParam", PhoneNumber); break; case 3: UpdateBuilder.Append("`Email` = @EmailParam"); cmd.Parameters.AddWithValue("@EmailParam", EmailAddress); break; case 4: UpdateBuilder.Append("`BadgeName` = @BadgeParam"); cmd.Parameters.AddWithValue("@BadgeParam", BadgeName); break; } //UpdateBuilder.AppendFormat(" where `ID` = {0} or `Name` = \'{1}\' or `Phone` = \'{2}\' or `Email` = \'{3}\' or `BadgeName` = \'{4}\';", OwnerID, NameStr, PhoneNum, EmailAddr, Badge); UpdateBuilder.Append(" where `ID` = @FinalIDParam" + ';'); cmd.Parameters.AddWithValue("@FinalIDParam", OwnerID); return(UpdateBuilder.ToString()); }