public string Save(BotTemplateRelation item)
{
string query = "";
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append(@"UPDATE [dbo].[robottemplaterelation] SET [templateid] = @templateID,[itemscoresum] = @itemscoresum,[raceid] = @raceid,
[missionlevel] = @missionlevel,[missionleveloverride] = @levelOverride,[killep] = @killep ,[note] = @note WHERE [definition] = @definitionID;");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue("@definitionID", item.definition);
command.Parameters.AddWithValue("@templateID", item.templateid);
command.Parameters.AddWithValue("@itemscoresum", item.itemscoresum);
command.Parameters.AddWithValue("@raceid", item.raceid);
command.Parameters.AddWithValue("@missionlevel", Utilities.getNullableInt(item.missionlevel));
command.Parameters.AddWithValue("@levelOverride", Utilities.getNullableInt(item.missionleveloverride));
command.Parameters.AddWithValue("@killep", Utilities.getNullableInt(item.killep));
command.Parameters.AddWithValue("@note", item.note);
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 == "@definitionID" || p.ParameterName == "@templateID")
{
continue;
}
else if (SqlDbType.NVarChar.Equals(p.SqlDbType) || SqlDbType.VarChar.Equals(p.SqlDbType))
{
query = query.Replace(p.ParameterName, "'" + p.Value.ToString() + "'");
}
else
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
}
}
return(query);
}
public string Insert(BotTemplateRelation item)
{
string query = "";
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append(@"INSERT INTO [dbo].[robottemplaterelation] ([definition],[templateid],[itemscoresum],[raceid],[missionlevel],[missionleveloverride],[killep],[note])
VALUES (@definitionID,@templateID,@itemscoresum,@raceid,@missionlevel,@levelOverride,@killep,@note);");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue("@definitionID", item.definition);
command.Parameters.AddWithValue("@templateID", item.templateid);
command.Parameters.AddWithValue("@itemscoresum", item.itemscoresum);
command.Parameters.AddWithValue("@raceid", item.raceid);
command.Parameters.AddWithValue("@missionlevel", Utilities.getNullableInt(item.missionlevel));
command.Parameters.AddWithValue("@levelOverride", Utilities.getNullableInt(item.missionleveloverride));
command.Parameters.AddWithValue("@killep", Utilities.getNullableInt(item.killep));
command.Parameters.AddWithValue("@note", item.note);
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 != "@definitionID" && p.ParameterName != "@templateID")
{
if (SqlDbType.NVarChar.Equals(p.SqlDbType) || SqlDbType.VarChar.Equals(p.SqlDbType))
{
query = query.Replace(p.ParameterName, "'" + p.Value.ToString() + "'");
}
else
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
}
}
}
return(query);
}
public BotTemplateRelation GetById(int id)
{
BotTemplateRelation temprelation = new BotTemplateRelation();
using (SqlConnection conn = new SqlConnection(this.ConnString))
{
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append(@"SELECT robottemplaterelation.definition,entitydefaults.definitionname,templateid
,robottemplates.name,itemscoresum,raceid,missionlevel,missionleveloverride,killep,robottemplaterelation.note
FROM perpetuumsa.dbo.robottemplaterelation
join robottemplates on robottemplates.id=robottemplaterelation.templateid
join entitydefaults on entitydefaults.definition=robottemplaterelation.definition
WHERE robottemplaterelation.definition=@id;");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue("@id", id);
command.Connection = conn;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
temprelation.definition = Convert.ToInt32(reader["definition"]);
temprelation.definitionname = Convert.ToString(reader["definitionname"]);
temprelation.templateid = Convert.ToInt32(reader["templateid"]);
temprelation.templatename = Convert.ToString(reader["name"]);
temprelation.itemscoresum = Convert.ToInt32(reader["itemscoresum"]);
temprelation.raceid = Convert.ToInt32(reader["raceid"]);
temprelation.missionlevel = Utilities.handleNullableInt(reader["missionlevel"]);
temprelation.missionleveloverride = Utilities.handleNullableInt(reader["missionleveloverride"]);
temprelation.killep = Utilities.handleNullableInt(reader["killep"]);
temprelation.note = Convert.ToString(reader["note"]);
}
}
}
}
return(temprelation);
}