public string Delete(BotBonusObj b)
{
string query = "";
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append(@"DELETE [dbo].[chassisbonus] WHERE id=@chassisbonusID;");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue("@chassisbonusID", b.id);
SqlConnection conn = new SqlConnection(this.ConnString);
conn.Open();
command.Connection = conn;
command.ExecuteNonQuery();
conn.Close();
query = command.CommandText;
foreach (SqlParameter p in command.Parameters)
{
if (p.ParameterName == "@chassisbonusID")
{
continue;
}
query = query.Replace(p.ParameterName, p.Value.ToString());
}
}
return(query);
}
public ObservableCollection <BotBonusObj> getByEntity(int definitionID)
{
ObservableCollection <BotBonusObj> list = new ObservableCollection <BotBonusObj>();
using (SqlConnection conn = new SqlConnection(this.ConnString))
{
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append(@"SELECT chassisbonus.id,chassisbonus.definition,chassisbonus.extension,chassisbonus.note,chassisbonus.targetpropertyID,chassisbonus.bonus,
chassisbonus.effectenhancer,entitydefaults.definitionname,extensions.extensionname,aggregatefields.name
FROM chassisbonus JOIN entitydefaults on chassisbonus.definition=entitydefaults.definition JOIN extensions on extensions.extensionid=chassisbonus.extension
JOIN aggregatefields on aggregatefields.id=chassisbonus.targetpropertyID WHERE entitydefaults.definition=@id;");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue("@id", definitionID);
command.Connection = conn;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
BotBonusObj tmp = new BotBonusObj();
tmp.id = Convert.ToInt32(reader["id"]);
tmp.definition = Convert.ToInt32(reader["definition"]);
tmp.extension = Convert.ToInt32(reader["extension"]);
tmp.bonus = Convert.ToDecimal(reader["bonus"]);
tmp.note = Convert.ToString(reader["note"]);
tmp.targetpropertyID = Convert.ToInt32(reader["targetpropertyID"]);
tmp.effectenhancer = Convert.ToInt32(reader["effectenhancer"]);
tmp.definitionName = Convert.ToString(reader["definitionname"]);
tmp.extensionName = Convert.ToString(reader["extensionname"]);
tmp.aggFieldName = Convert.ToString(reader["name"]);
list.Add(tmp);
}
}
}
}
return(list);
}
public string Save(BotBonusObj bonusChanges)
{
string query = "";
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE chassisbonus SET effectenhancer=@effectenhancer, bonus=@bonus WHERE id = " + BotBonusObj.IDkey + ";");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue(BotBonusObj.IDkey, bonusChanges.id);
command.Parameters.AddWithValue("@bonus", bonusChanges.bonus);
command.Parameters.AddWithValue("@effectenhancer", bonusChanges.effectenhancer);
SqlConnection conn = new SqlConnection(this.ConnString);
conn.Open();
command.Connection = conn;
command.ExecuteNonQuery();
conn.Close();
query = Utilities.parseCommandString(command, new List <string>(new string[] { BotBonusObj.IDkey }));
}
return(query);
}
public string Insert(BotBonusObj b) //TODO bug here? Requires unique triplet of keys...
{
string query = "";
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append(@"INSERT INTO [dbo].[chassisbonus] ([definition],[extension],[bonus],[note],[targetpropertyID],[effectenhancer])
VALUES (" + EntityDefaults.IDkey + ", " + Extensions.IDkey + ", @bonus, @note, " + AggregateFields.IDkey + ", @effectenhancer);");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue(EntityDefaults.IDkey, b.definition);
command.Parameters.AddWithValue(Extensions.IDkey, b.extension);
command.Parameters.AddWithValue("@bonus", b.bonus);
command.Parameters.AddWithValue(AggregateFields.IDkey, b.targetpropertyID);
command.Parameters.AddWithValue("@effectenhancer", b.effectenhancer);
if (b.note == null)
{
command.Parameters.AddWithValue("@note", string.Empty);
}
else
{
command.Parameters.AddWithValue("@note", b.note);
}
SqlConnection conn = new SqlConnection(this.ConnString);
conn.Open();
command.Connection = conn;
command.ExecuteNonQuery();
conn.Close();
query = Utilities.parseCommandString(command, new List <string>(new string[] { EntityDefaults.IDkey, Extensions.IDkey, AggregateFields.IDkey }));
}
return(query);
}