public int Update(DistrictModels item) { int rs = 0; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("UPDATE "); sqlBuilder.Append("gov_district "); sqlBuilder.Append("SET "); sqlBuilder.Append("name = @1, "); sqlBuilder.Append("level = @2, "); sqlBuilder.Append("decription = @3, "); sqlBuilder.Append("link = @4, "); sqlBuilder.Append("update_user_id = @5, "); sqlBuilder.Append("update_datetime = @6"); sqlBuilder.Append("order_number = @7"); sqlBuilder.Append("WHERE "); sqlBuilder.Append("id = @8"); this.Sql = sqlBuilder.ToString(); MySqlCommand objCmd = new MySqlCommand(Sql, getConnection()); objCmd.Parameters.AddWithValue("@8", item.Id); objCmd.Parameters.AddWithValue("@1", item.Name); objCmd.Parameters.AddWithValue("@2", item.Level); objCmd.Parameters.AddWithValue("@3", item.Decription); objCmd.Parameters.AddWithValue("@4", item.Link); objCmd.Parameters.AddWithValue("@5", item.UpdateUserId); objCmd.Parameters.AddWithValue("@6", item.UpdateDatetime); objCmd.Parameters.AddWithValue("@7", item.OrderNumber); rs = objCmd.ExecuteNonQuery(); return rs; }
public int insert(DistrictModels item) { int rs = 0; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("INSERT "); sqlBuilder.Append("INTO "); sqlBuilder.Append("gov_district "); sqlBuilder.Append("( "); sqlBuilder.Append("name, "); sqlBuilder.Append("level, "); sqlBuilder.Append("decription, "); sqlBuilder.Append("link, "); sqlBuilder.Append("update_user_id, "); sqlBuilder.Append("update_datetime,"); sqlBuilder.Append("order_number"); sqlBuilder.Append(") "); sqlBuilder.Append("values("); sqlBuilder.Append("@2, @3, @4, @5, @6, @7, @8"); sqlBuilder.Append(")"); this.Sql = sqlBuilder.ToString(); MySqlCommand objCmd = new MySqlCommand(Sql, getConnection()); objCmd.Parameters.AddWithValue("@2", item.Name); objCmd.Parameters.AddWithValue("@3", item.Level); objCmd.Parameters.AddWithValue("@4", item.Decription); objCmd.Parameters.AddWithValue("@5", item.Link); objCmd.Parameters.AddWithValue("@6", item.UpdateUserId); objCmd.Parameters.AddWithValue("@7", item.UpdateDatetime); objCmd.Parameters.AddWithValue("@8", item.OrderNumber); rs = objCmd.ExecuteNonQuery(); return rs; }
public String GetJsonMap(int id) { String rs = "{\"type\": \"FeatureCollection\","; rs += "\"features\": ["; DistrictServices districtServices = new DistrictServices(); districtServices.ShowMap = Boolean.TrueString; districtServices.Id = id.ToString(); List<DistrictModels> lstDistrict = districtServices.select(-1, -1); DistrictModels districtInfo = new DistrictModels(); if(lstDistrict != null && lstDistrict.Count > 0){ districtInfo = lstDistrict.First(); } String coordinates = districtInfo.Coordinates; String[] lstCoordinates = coordinates.Split(','); for (int i = 0; i < lstCoordinates.Length; i++ ) { String[] coordinates1 = lstCoordinates[i].Trim().Split(' '); rs += "{ \"type\": \"Feature\", \"id\": \"" + (i + 1).ToString() +"\", \"geometry\": { \"type\": \"Point\", \"coordinates\": [" + coordinates1[0] + "," + coordinates1[1] + "] } }"; if(i != (lstCoordinates.Length - 1)) rs += ","; } rs += "]}"; return rs; }
public List<DistrictModels> select(int page, int limit) { int offset = (page - 1) * limit; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT "); sqlBuilder.Append("* "); sqlBuilder.Append("FROM "); sqlBuilder.Append("gov_district gdt "); 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<DistrictModels> lstMenu = new List<DistrictModels>(); DistrictModels item; while (dataReader.Read()) { item = new DistrictModels(); item.Id = Convert.ToInt32(dataReader["id"]); item.Name = dataReader["name"].ToString(); item.Level = Convert.ToInt32(dataReader["level"]); item.Decription = dataReader["decription"].ToString(); item.Link = dataReader["link"].ToString(); item.ShowMap = Convert.ToBoolean(dataReader["show_map"]); item.Coordinates = dataReader["coordinates"].ToString(); if (dataReader["order_number"] != null) { item.OrderNumber = Convert.ToInt32(dataReader["order_number"]); } //item.ActiveFlg = Convert.ToBoolean(dataReader["active_flg"]); item.UpdateDatetime = Convert.ToDateTime(dataReader["update_datetime"]); lstMenu.Add(item); } getConnection().Close(); return lstMenu; }