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); }