public string BuildUpdateQuery(MySqlCommand cmd)
{
StringBuilder UpdateBuilder = new StringBuilder("update `gaminginv`.`items` set ");
List <int> PlaceHolder = new List <int>();
if (!TypeValue.Equals(string.Empty))
{
PlaceHolder.Add(1);
}
if (!PlatformValue.Equals(string.Empty))
{
PlaceHolder.Add(2);
}
if (!SerialValue.Equals(string.Empty))
{
PlaceHolder.Add(3);
}
if (!DescriptionValue.Equals(string.Empty))
{
PlaceHolder.Add(4);
}
for (int i = 0; i < PlaceHolder.Count - 1; i++)
{
switch (PlaceHolder.ElementAt(i))
{
case 1:
UpdateBuilder.Append("`Type` = @TypeParam" + ',');
cmd.Parameters.AddWithValue("@TypeParam", TypeValue);
break;
case 2:
UpdateBuilder.Append("`Platform` = @PlatformParam" + ',');
cmd.Parameters.AddWithValue("@PlatformParam", PlatformValue);
break;
case 3:
UpdateBuilder.Append("`Serial` = @SerialParam" + ',');
cmd.Parameters.AddWithValue("@SerialParam", SerialValue);
break;
case 4:
UpdateBuilder.Append("`Description` = @DescriptionParam" + ',');
cmd.Parameters.AddWithValue("@DescriptionParam", DescriptionValue);
break;
}
}
switch (PlaceHolder.Last())
{
case 1:
UpdateBuilder.Append("`Type` = @TypeParam");
cmd.Parameters.AddWithValue("@TypeParam", TypeValue);
break;
case 2:
UpdateBuilder.Append("`Platform` = @PlatformParam");
cmd.Parameters.AddWithValue("@PlatformParam", PlatformValue);
break;
case 3:
UpdateBuilder.Append("`Serial` = @SerialParam");
cmd.Parameters.AddWithValue("@SerialParam", SerialValue);
break;
case 4:
UpdateBuilder.Append("`Description` = @DescriptionParam");
cmd.Parameters.AddWithValue("@DescriptionParam", DescriptionValue);
break;
}
UpdateBuilder.AppendFormat(" where `ID` = @IDParam" + ';');
cmd.Parameters.AddWithValue("@IDParam", IDValue);
return(UpdateBuilder.ToString());
}
public string BuildSelectQuery(MySqlCommand cmd, bool includeID, bool includeOwner, bool includePlatform, bool includeSerial, bool includeType, bool includeDescription)
{
StringBuilder SelectBuilder = new StringBuilder();
SelectBuilder.Append("select * from `gaminginv`.`items` where ");
List <int> PlaceHolder = new List <int>();
if (includeID && IDValue > 0)
{
PlaceHolder.Add(1);
}
if (includeOwner && !OwnerValue.Equals(string.Empty))
{
PlaceHolder.Add(2);
}
if (includePlatform && !PlatformValue.Equals(string.Empty))
{
PlaceHolder.Add(3);
}
if (includeSerial && !SerialValue.Equals(string.Empty))
{
PlaceHolder.Add(4);
}
if (includeType && !TypeValue.Equals(string.Empty))
{
PlaceHolder.Add(5);
}
if (includeDescription && !DescriptionValue.Equals(string.Empty))
{
PlaceHolder.Add(6);
}
for (int i = 0; i < PlaceHolder.Count - 1; i++)
{
switch (PlaceHolder.ElementAt(i))
{
case 1:
SelectBuilder.Append("`ID` = @IDParam and ");
cmd.Parameters.AddWithValue("@IDParam", IDValue);
break;
case 2:
SelectBuilder.Append("`Owner` = @OwnerParam and ");
cmd.Parameters.AddWithValue("@OwnerParam", OwnerValue.ToLower());
break;
case 3:
SelectBuilder.Append("`Platform` = @PlatformParam and ");
cmd.Parameters.AddWithValue("@PlatformParam", PlatformValue);
break;
case 4:
SelectBuilder.Append("`Serial` like @SerialParam and ");
cmd.Parameters.AddWithValue("@SerialParam", '%' + SerialValue.ToLower() + '%');
break;
case 5:
SelectBuilder.Append("`Type` = @TypeParam and ");
cmd.Parameters.AddWithValue("@TypeParam", TypeValue);
break;
case 6:
SelectBuilder.Append("`Description` like @DescriptionParam and ");
cmd.Parameters.AddWithValue("@DescriptionParam", '%' + DescriptionValue.ToLower() + '%');
break;
}
}
if (PlaceHolder.Count > 0)
{
switch (PlaceHolder.Last())
{
case 1:
SelectBuilder.Append("`ID` = @IDParam");
cmd.Parameters.AddWithValue("@IDParam", IDValue);
break;
case 2:
SelectBuilder.Append("`Owner` = @OwnerParam");
cmd.Parameters.AddWithValue("@OwnerParam", OwnerValue.ToLower());
break;
case 3:
SelectBuilder.Append("`Platform` = @PlatformParam");
cmd.Parameters.AddWithValue("@PlatformParam", PlatformValue);
break;
case 4:
SelectBuilder.Append("`Serial` like @SerialParam");
cmd.Parameters.AddWithValue("@SerialParam", '%' + SerialValue.ToLower() + '%');
break;
case 5:
SelectBuilder.Append("`Type` = @TypeParam");
cmd.Parameters.AddWithValue("@TypeParam", TypeValue);
break;
case 6:
SelectBuilder.AppendFormat("`Description` like @DescriptionParam");
cmd.Parameters.AddWithValue("@DescriptionParam", '%' + DescriptionValue.ToLower() + '%');
break;
}
SelectBuilder.Append(" and `LogisticState` = 'Arrived';");
return(SelectBuilder.ToString());
}
else
{
return(string.Empty);
}
}