public String InsertTrail(TrailViewModel trail)
{
using (MySqlConnection conn = GetConnection())
{
try
{
conn.Open();
string Query
= "INSERT INTO TRAIL (name, description, colorCode, photoName, distance, totalTime)" +
" VALUES ('" + trail.name + "','" + trail.description + "','" + trail.colorCode + "','"
+ trail.photoName + "','" + trail.distance + "','" + trail.totalTime + "')";
MySqlCommand cmd = new MySqlCommand(Query, conn);
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
// Check Error
if (result < 0)
{
return("Fail");
}
else
{
return("Insert");
}
}
catch (MySqlException ex)
{
Console.WriteLine("Can not open connection ! : " + ex.Message);
return("Fail");
}
}
}
public TrailViewModel getColorCodeList(TrailViewModel trail)
{
List <SelectListItem> list = trail.colorCodeSelectList;
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("Select DISTINCT(ColorCode) FROM Trail", conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SelectListItem item = trail.colorCodeSelectList.Where(x => x.Value == reader["colorCode"].ToString()).Single();
if (trail.colorCode != reader["colorCode"].ToString())
{
list = list.Where(c => c.Value != reader["colorCode"].ToString()).ToList();
}
}
}
cmd.Dispose();
}
trail.colorCodeSelectList = list;
return(trail);
}
public void UpdateTrailById(int?id, TrailViewModel trail)
{
using (MySqlConnection conn = GetConnection())
{
conn.Open();
string Query = "UPDATE TRAIL SET " +
"name='" + trail.name + "', " +
"description='" + trail.description + "', " +
"colorCode='" + trail.colorCode + "', " +
"photoName='" + trail.photoName + "', " +
"distance='" + trail.distance + "', " +
"totalTime='" + trail.totalTime + "' " +
"WHERE id=" + trail.id;
MySqlCommand cmd = new MySqlCommand(Query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
/// <summary>
/// To get data to edit
/// </summary>
/// <param name = "id" ></ param > it can be null
/// <returns></returns>
public TrailViewModel GetTrailById(int?id, TrailViewModel trail)
{
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from Trail where id=" + id, conn);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
trail.id = Convert.ToInt32(reader["id"]);
trail.name = reader["name"].ToString();
trail.description = reader["description"].ToString();
trail.colorCode = reader["colorCode"].ToString();
trail.photoName = reader["photoName"].ToString();
trail.distance = reader["distance"].ToString();
trail.totalTime = reader["totalTime"].ToString();
}
}
cmd.Dispose();
}
return(trail);
}
