internal static void AddParam(NpgsqlCommand cmd, string name, Point2 <double> location) { if (location != null) { NpgsqlTypes.PostgisPoint geom = Osrs.Numerics.Spatial.Postgres.NpgSpatialUtils.ToPGis(location); if (geom != null) { cmd.Parameters.AddWithValue(name, geom); } else { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam(name, NpgsqlTypes.NpgsqlDbType.Geometry)); } } else { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam(name, NpgsqlTypes.NpgsqlDbType.Geometry)); } }
// GET: Home/AddPoint /*public ActionResult AddPoint(PointPlusUser point) * { * List<string> addresslist = new List<string>(); * bool logged = false; * using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;" + * "User Id=postgres;Password=goralt;Database=postgis;")) * { * conn.Open(); * string loginQuery = "(SELECT \"user\", pass, latestactivity FROM users)"; * var command = new NpgsqlCommand(loginQuery, conn); * using (NpgsqlDataReader dr = command.ExecuteReader()) * { * while (dr.Read()) * { * * if (dr[0].ToString() == point.user && dr[1].ToString() == point.password && DateTime.Compare(((DateTime)dr[2]).Date, DateTime.Now.Date) < 0) * { * logged = true; * } * } * } * * } * if (logged) * { * string name = "unknown"; * Int32 saveResult; * * * using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;" + * "User Id=postgres;Password=goralt;Database=postgis;")) * { * * string punkt = "ST_Transform(ST_GeomFromText('POINT(" + * point.Lon.ToString(CultureInfo.InvariantCulture) + " " + point.Lat.ToString(CultureInfo.InvariantCulture) + ")',3857),2180)"; * * conn.Open(); * * string areaQuery = "(SELECT geom FROM warszawa WHERE ST_Contains(warszawa.geom," + punkt + " ))"; * string addressQuery = "SELECT adres FROM adresy WHERE ST_Contains(" + areaQuery + ",ST_Transform(adresy.geom,2180))"; * var command = new NpgsqlCommand(addressQuery, conn); * int i = 0; * * using (NpgsqlDataReader dr = command.ExecuteReader()) * { * while (dr.Read()) * { * addresslist.Add(dr.GetValue(0).ToString()); * } * } * * if (name != "undefined") * { * * //int w = 1; * //string Query = "(SELECT address FROM points WHERE address='" + name + "')"; * //var com = new NpgsqlCommand(Query, conn); * //string firstrecord=""; * //using (NpgsqlDataReader dr = com.ExecuteReader()) * //{ * // if(dr.Read()) * // { * // while(dr.GetValue(i++)!=null) * // string s; * // addresslist.Add(dr.GetValue(0).ToString()); * // } * //} * //if (firstrecord == "") * //{ * // string insertQuery = "insert into points(address,geom,p) values (\'" + name + "\'," + punkt + "," + w.ToString() + ")"; * // var insertComm = new NpgsqlCommand(insertQuery, conn); * // saveResult = insertComm.ExecuteNonQuery(); * //} * //else * //{ * // string insertQuery = "update points set p=p+" + w.ToString() + " where address='" + name + "'"; * // var insertComm = new NpgsqlCommand(insertQuery, conn); * // saveResult = insertComm.ExecuteNonQuery(); * //} * } * * * string loginUpdateQuery = "UPDATE users SET latestactivity = NOW() WHERE \"user\" ='" + point.user + "' AND pass='******'"; * var upcommand = new NpgsqlCommand(loginUpdateQuery, conn); * Int32 save = upcommand.ExecuteNonQuery(); * * } * * var commune = new * { * Name = name, * //Saved = saveResult * }; * } * return Json(addresslist); * }*/ public ActionResult AddAddress(_address name) { bool logged = false; using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;" + "User Id=postgres;Password=goralt;Database=postgis;")) { conn.Open(); string loginQuery = "(SELECT \"user\", pass, latestactivity FROM users)"; var command = new NpgsqlCommand(loginQuery, conn); using (NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { if (dr[0].ToString() == name.User && dr[1].ToString() == name.Password && DateTime.Compare(((DateTime)dr[2]).Date, DateTime.Now.Date) < 0) { logged = true; } } } } _address response = new _address { Address = "", Lat = 0, Lon = 0, Points = 0, User = "", Password = "" }; if (logged) { using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;" + "User Id=postgres;Password=goralt;Database=postgis;")) { if (name.Address != "") { conn.Open(); double w = name.Points; /*string areaQuery = "(SELECT geom FROM warszawa WHERE ST_Contains(warszawa.geom," + point + " ))"; * string addressQuery = "SELECT adres FROM adresy WHERE ST_Contains(" + areaQuery + ",ST_Transform(adresy.geom,2180))"; * string Query = "(SELECT address FROM points WHERE address='" + name.Address + "' and ST_Contains(" + areaQuery + ",points.geom))"; * string Querygeom = "(SELECT geom FROM adresy WHERE adres='" + name.Address + "' and ST_Contains(" + areaQuery + ",ST_Transform(adresy.geom,2180)))";*/ string Query = "SELECT address from points where address ='" + name.Address + "'"; var com = new NpgsqlCommand(Query, conn); string firstrecord = ""; NpgsqlTypes.PostgisPoint geometry = null; Int32 saveResult; using (NpgsqlDataReader dr = com.ExecuteReader()) { if (dr.Read()) { firstrecord = dr.GetValue(0).ToString(); } } /*com = new NpgsqlCommand(Querygeom, conn); * using (NpgsqlDataReader dr = com.ExecuteReader()) * { * if (dr.Read()) * { * geometry = (NpgsqlTypes.PostgisPoint)dr.GetValue(0); * } * }*/ if (firstrecord == "") { /*string punkt = "ST_Transform(ST_GeomFromText('POINT(" + * geometry.X.ToString(CultureInfo.InvariantCulture) + " " + geometry.Y.ToString(CultureInfo.InvariantCulture) + ")',2178),2180)";*/ string point = "ST_Transform(ST_GeomFromText('POINT(" + name.Lon.ToString(CultureInfo.InvariantCulture) + " " + name.Lat.ToString(CultureInfo.InvariantCulture) + ")',3857),2180)"; string insertQuery = "insert into points(address,geom,p,count) values ('" + name.Address + "'," + point + "," + w.ToString() + ",1)"; var insertComm = new NpgsqlCommand(insertQuery, conn); response.Address = name.Address; response.Lat = name.Lat; response.Lon = name.Lon; saveResult = insertComm.ExecuteNonQuery(); } else { string insertQuery = "update points set p=p+" + w.ToString() + ", count=count+1 where address='" + name.Address + "'"; var insertComm = new NpgsqlCommand(insertQuery, conn); saveResult = insertComm.ExecuteNonQuery(); response.Address = "Update"; } string loginUpdateQuery = "UPDATE users SET latestactivity = NOW() WHERE \"user\" ='" + name.User + "' AND pass='******'"; var upcommand = new NpgsqlCommand(loginUpdateQuery, conn); Int32 save = upcommand.ExecuteNonQuery(); } } } return(Json(response)); }
public override bool Update(Site site) { if (site != null && this.CanUpdate(site)) { try { NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString); cmd.CommandText = Db.UpdateSite; cmd.Parameters.AddWithValue("sid", site.Identity.DataStoreIdentity); cmd.Parameters.AddWithValue("id", site.Identity.Identity); cmd.Parameters.AddWithValue("osid", site.OwningOrganizationIdentity.DataStoreIdentity); cmd.Parameters.AddWithValue("oid", site.OwningOrganizationIdentity.Identity); cmd.Parameters.AddWithValue("name", site.Name); if (string.IsNullOrEmpty(site.Description)) { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("desc", NpgsqlTypes.NpgsqlDbType.Varchar)); } else { cmd.Parameters.AddWithValue("desc", site.Description); } if (site.Location != null) { NpgsqlTypes.PostgisGeometry geom = Osrs.Numerics.Spatial.Postgres.NpgSpatialUtils.ToPGis(site.Location); if (geom != null) { cmd.Parameters.AddWithValue("loc", geom); } else { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("loc", NpgsqlTypes.NpgsqlDbType.Geometry)); } } else { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("loc", NpgsqlTypes.NpgsqlDbType.Geometry)); } if (site.LocationMark != null) { NpgsqlTypes.PostgisPoint geom = Osrs.Numerics.Spatial.Postgres.NpgSpatialUtils.ToPGis(site.LocationMark); if (geom != null) { cmd.Parameters.AddWithValue("poi", geom); } else { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("poi", NpgsqlTypes.NpgsqlDbType.Geometry)); } } else { cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("poi", NpgsqlTypes.NpgsqlDbType.Geometry)); } cmd.Parameters.AddWithValue("start", DateTime.UtcNow); Db.ExecuteNonQuery(cmd); return(true); } catch { } } return(false); }