public int Update(CalendarModels item) { int rs = 0; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("UPDATE "); sqlBuilder.Append("gov_work_schedule "); sqlBuilder.Append("SET "); sqlBuilder.Append("date = @1, "); sqlBuilder.Append("time = @2, "); sqlBuilder.Append("description = @3, "); sqlBuilder.Append("location = @4, "); sqlBuilder.Append("person_execute = @5, "); sqlBuilder.Append("update_username = @6, "); sqlBuilder.Append("update_datetime = @7 "); sqlBuilder.Append("WHERE "); sqlBuilder.Append("id = @8"); this.Sql = sqlBuilder.ToString(); MySqlCommand objCmd = new MySqlCommand(Sql, getConnection()); objCmd.Parameters.AddWithValue("@1", item.Date); objCmd.Parameters.AddWithValue("@2", item.Time); objCmd.Parameters.AddWithValue("@3", item.Description); objCmd.Parameters.AddWithValue("@4", item.Location); objCmd.Parameters.AddWithValue("@5", item.PersonExecute); objCmd.Parameters.AddWithValue("@6", item.UpdateUsername); objCmd.Parameters.AddWithValue("@7", item.UpdateDatetime); objCmd.Parameters.AddWithValue("@8", item.Id); rs = objCmd.ExecuteNonQuery(); return rs; }
public int insert(CalendarModels item) { int rs = 0; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("INSERT "); sqlBuilder.Append("INTO "); sqlBuilder.Append("gov_work_schedule "); sqlBuilder.Append("( "); sqlBuilder.Append("date, "); sqlBuilder.Append("time, "); sqlBuilder.Append("description, "); sqlBuilder.Append("location, "); sqlBuilder.Append("person_execute, "); sqlBuilder.Append("update_username, "); sqlBuilder.Append("update_datetime"); sqlBuilder.Append(") "); sqlBuilder.Append("values("); sqlBuilder.Append("@1, @2, @3, @4, @5, @6, @7"); sqlBuilder.Append(")"); this.Sql = sqlBuilder.ToString(); MySqlCommand objCmd = new MySqlCommand(Sql, getConnection()); objCmd.Parameters.AddWithValue("@1", item.Date); objCmd.Parameters.AddWithValue("@2", item.Time); objCmd.Parameters.AddWithValue("@3", item.Description); objCmd.Parameters.AddWithValue("@4", item.Location); objCmd.Parameters.AddWithValue("@5", item.PersonExecute); objCmd.Parameters.AddWithValue("@6", item.UpdateUsername); objCmd.Parameters.AddWithValue("@7", item.UpdateDatetime); rs = objCmd.ExecuteNonQuery(); return rs; }
public List<CalendarModels> select(int page, int limit) { int offset = (page - 1) * limit; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT "); sqlBuilder.Append("gws.*, gt.name "); sqlBuilder.Append("FROM "); sqlBuilder.Append("gov_work_schedule gws "); sqlBuilder.Append("INNER JOIN "); sqlBuilder.Append("gov_work_time gt "); sqlBuilder.Append("ON "); sqlBuilder.Append("gws.time = gt.id "); sqlBuilder.Append(getBaseSQL()); if (page > 0 && limit > 0) { sqlBuilder.Append(" LIMIT @limit "); sqlBuilder.Append(" OFFSET @offset "); } this.Sql = sqlBuilder.ToString(); MySqlCommand objCmd = new MySqlCommand(Sql, getConnection()); if (page > 0 && limit > 0) { objCmd.Parameters.AddWithValue("@limit", limit); objCmd.Parameters.AddWithValue("@offset", offset); } MySqlDataReader dataReader = objCmd.ExecuteReader(); List<CalendarModels> lstCalendar= new List<CalendarModels>(); CalendarModels item; while (dataReader.Read()) { item = new CalendarModels(); item.Id = Convert.ToInt32(dataReader["id"]); item.Date = Convert.ToDateTime(dataReader["date"]); item.Time = dataReader["name"].ToString(); item.TimeRf = Convert.ToInt32(dataReader["time"]); item.Description = dataReader["description"].ToString(); item.Location = dataReader["location"].ToString(); item.PersonExecute = dataReader["person_execute"].ToString(); item.UpdateUsername = dataReader["update_username"].ToString(); item.UpdateDatetime = Convert.ToDateTime(dataReader["update_datetime"]); lstCalendar.Add(item); } getConnection().Close(); return lstCalendar; }