/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
{
SharpMap.Geometries.Geometry geom = null;
using (PgConnection conn = new PgConnection(_ConnectionString))
{
String strSql = String.Format("SELECT AsBinary({0}) As Geom FROM {1} WHERE {2} = '{3}'",
this.GeometryColumn,
this.Table,
this.ObjectIdColumn,
oid);
conn.Open();
using (PgCommand command = new PgCommand(strSql, conn))
{
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
object obj = dr[0];
if (typeof(PgPoint) == obj.GetType())
{
geom = new SharpMap.Geometries.Point(((PgPoint)obj).X, ((PgPoint)obj).Y);
}
else if (obj != DBNull.Value)
{
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
}
conn.Close();
}
return(geom);
}
///<sumary>Obtener modelos por marca</sumary>
///<param name="brandId">Id de la marca</param>
///<returns>Lista de modelos por marca</returns>
///<exception cref="Throw BrandNotFoundException"> Si la marca no existe </exception>
///<exception cref=" WithoutExistenceOfModelsException">
/// Si no existen modelos para dicha marca
///</exception>
public List <Model> GetModelsByBrand(int brandId)
{
List <Model> models = new List <Model>();
try{
PostgresBrandDAO brandDAO = new PostgresBrandDAO();
brandDAO.GetBrandById(brandId);//Throw BrandNotFoundException
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("GetModelsByBrand(@brandId)", brandId);
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dataRow in dataTable.Rows)
{
Model model = new Model(
Convert.ToInt32(dataRow[0]),
Convert.ToInt32(dataRow[1]),
dataRow[2].ToString(),
Convert.ToInt32(dataRow[3]),
dataRow[4].ToString()
);
models.Add(model);
}
}
else
{
throw new WithoutExistenceOfModelsException("No se encontraron Modelos para dicha Marca");
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(models);
}
///<sumary>Obtener todos los modelos</sumary>
///<returns>Lista de modelos</returns>
///<exception cref="WithoutExistenceOfModelsException">
/// Si no hay existencia de modelos esta exepción es lanzada
///</exception>
public List <Model> GetModels()
{
List <Model> models = new List <Model>();
try{
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("GetModels()");
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dataRow in dataTable.Rows)
{
Model model = new Model(
Convert.ToInt32(dataRow[0]),
Convert.ToInt32(dataRow[1]),
dataRow[2].ToString(),
Convert.ToInt32(dataRow[3]),
dataRow[4].ToString()
);
models.Add(model);
}
}
else
{
throw new WithoutExistenceOfModelsException("Sin existencia de Modelos");
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(models);
}
///<sumary>Creación de un modelo</sumary>
///<param name="model">Instancia de Model</param>
///<returns>Id del modelo</returns>
///<exception cref="UniqueAttributeException">
/// Es excepción es lanzada cuando el nombre del modelo ya existe
///</exception>
public int AddModel(Model model)
{
int id = 0;
try {
PostgresBrandDAO brandDAO = new PostgresBrandDAO();
brandDAO.GetBrandById(model.ModelBrandId);//Throw BrandNotFoundException
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction(
"ModelUniqueness(@modelName)", model.ModelName);
if (Convert.ToBoolean(dataTable.Rows[0][0]))
{
throw new UniqueAttributeException("El modelo " + model.ModelName + " ya existe");
}
else
{
dataTable = pgConnection.ExecuteFunction(
"AddModel(@brandId, @modelName, @capacity, @picture)",
model.ModelBrandId, model.ModelName, model.Capacity, model.Picture);
id = Convert.ToInt32(dataTable.Rows[0][0]);
}
} catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
} catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(id);
}
public SharpMap.Data.FeatureDataTable QueryFeatures(SharpMap.Geometries.Geometry geom, double distance)
{
Collection <Geometries.Geometry> features = new Collection <SharpMap.Geometries.Geometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
{
strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
}
string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += this.GeometryColumn + " && " + "buffer(" + strGeom + "," + distance.ToString(Map.numberFormat_EnUS) + ")";
strSQL += " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<" + distance.ToString(Map.numberFormat_EnUS);
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
System.Data.DataSet ds = new System.Data.DataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
return(fdt);
}
else
{
return(null);
}
}
}
}
public int addcomment(Comment comment)
{
int id = 0;
try{
if (string.IsNullOrEmpty(comment.description) ||
comment.datetime == DateTime.MinValue ||
string.IsNullOrEmpty(comment.idforanea.ToString()))
{
throw new RequiredAttributeException("Falta información importante para poder crear el viaje");
}
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction(
"addcomment( @commentdescription, @tcommentdatetime, @commentidforanea)", comment.description, comment.datetime.ToString("yyyy-MM-dd"), comment.idforanea);
id = Convert.ToInt32(dataTable.Rows[0][0]);
}
catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(id);
}
public List <Brand> GetBrands()
{
List <Brand> brands = new List <Brand>();
try{
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("GetBrands()");
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dataRow in dataTable.Rows)
{
Brand brand = new Brand(
Convert.ToInt32(dataRow[0]),
dataRow[1].ToString()
);
brands.Add(brand);
}
}
else
{
// Throw Exception
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(brands);
}
///<sumary>Modificar un vehiculo</sumary>
///<param name="vehicle">Instancia de Vehicle</param>
///<returns>True si actualizo correctamente</returns>
///<exception cref="VehicleNotFoundException"> Si el vehiculo no existe </exception>
///<exception cref="ModelNotFoundException"> Si el modelo no existe </exception>
///<exception cref="LocationNotFoundException"> Si la ciudad no existe </exception>
public bool UpdateVehicle(Vehicle vehicle)
{
bool updated = false;
try{
GetVehicleById(vehicle.Id); // Throw VehicleNotFoundException
PostgresModelDAO modelDAO = new PostgresModelDAO();
modelDAO.GetModelById(vehicle.VehicleModelId); //Throw ModelNotFoundException
PostgresLocationDAO locationDAO = new PostgresLocationDAO();
locationDAO.GetLocationById(vehicle.VehicleLocationId); // Throw LocationNotFoundException
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction(
"UpdateVehicle(@vehid, @vehmodel, @vehlocation, @vehlicense, @vehprice, @vehstatus)",
vehicle.Id, vehicle.VehicleModelId, vehicle.VehicleLocationId, vehicle.License, vehicle.Price, vehicle.Status);
if (Convert.ToBoolean(dataTable.Rows[0][0]))
{
updated = true;
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(updated);
}
///<sumary>Obtener marcas por Id</sumary>
///<param name="brandId">Id de la marca</param>
///<returns>Instancia de la marca</returns>
///<exception cref="BrandNotFoundException"> Si la marca no existe </exception>
public Brand GetBrandById(int brandId)
{
Brand brand = null;
try{
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("GetBrandById(@brandId)", brandId);
if (dataTable.Rows.Count == 0)
{
throw new BrandNotFoundException(brandId, "No se ha encontrado una Marca con Id ");
}
else
{
brand = new Brand(
Convert.ToInt32(dataTable.Rows[0][0]),
dataTable.Rows[0][1].ToString()
);
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(brand);
}
private void setLeggendColorDescriptions(string viewName)
{
string pgConnectionString = ConfigurationManager.AppSettings[PgConnectionKey];
PgConnection pgCon = new PgConnection(pgConnectionString);
DataTable table = pgCon.RunSql(string.Format(GetMinValueSql, viewName));
int min = 0, max = 0;
if (table.Rows.Count > 0)
{
min = Convert.ToInt32(table.Rows[0][0].ToString());
}
table = pgCon.RunSql(string.Format(GetMaxValueSql, viewName));
if (table.Rows.Count > 0)
{
max = Convert.ToInt32(table.Rows[0][0].ToString());
}
int q1 = (max - min) / 3 + min;
int q2 = max - (max - min) / 3;
string criticalRange = string.Concat(max, RangeIndicator, q2);
string highRange = string.Concat(q2 > 0?q2 - 1:q2, RangeIndicator, q1);
string moderateRange = string.Concat(q1 > 0?q1 - 1:q1, RangeIndicator, min);
CriticalLegendItem.Description = criticalRange;
HighLegendItem.Description = highRange;
ModerateLegendItem.Description = moderateRange;
}
protected void Page_Load(object sender, EventArgs e)
{
Image1.Attributes.Add(Constants.UseMapKey, Constants.UseMapDistricSecretaryValue);
if (!IsPostBack)
{
string pgConnectionString = ConfigurationManager.AppSettings[PgConnectionKey];
PgConnection pgCon = new PgConnection(pgConnectionString);
DataTable table = pgCon.RunSql(GetYearsSql);
DropDownList2.DataSource = table;
DropDownList2.DataTextField = table.Columns[0].ToString();
DropDownList2.DataValueField = table.Columns[0].ToString();
DropDownList2.DataBind();
string sqlConnectionString = ConfigurationManager.AppSettings[SqlConnectionKey];
SqlServerConnection sqlCon = new SqlServerConnection(sqlConnectionString);
table = sqlCon.RunSql(GetDieseasesSql);
DropDownList1.DataSource = table;
DropDownList1.DataTextField = table.Columns[1].ToString();
DropDownList1.DataValueField = table.Columns[0].ToString();
DropDownList1.DataBind();
selectMostSuitableDiesease();
}
}
///<sumary>
/// Consulta de viejes por usuario
///</sumary>
///<param name="userId">Id del Usuario</param>
///<returns>
/// Lista de vieajes del usuario
///</returns>
///<exception cref="WithoutExistenceOfTravelsException">
/// La excepción es lanzada cuando el usuario no pesee ningún viaje
///</exception>
///<exception cref="UserNotFoundException">
/// La excepción es lanzada si el usuario no existe
///</exception>
public static List <Travel> GetTravels(int userId)
{
List <Travel> listOfTravels = new List <Travel>();
try{
User user = (User)UserRepository.GetUserById(userId); //Throw Exception if it doesn'n exist
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("GetTravels(@userId)", userId);
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dataRow in dataTable.Rows)
{
Travel travel = new Travel(
Convert.ToInt32(dataRow[0]),
dataRow[1].ToString(),
Convert.ToDateTime(dataRow[2]),
Convert.ToDateTime(dataRow[3]),
dataRow[4].ToString(),
Convert.ToInt32(dataRow[5])
);
listOfTravels.Add(travel);
}
}
else
{
throw new WithoutExistenceOfTravelsException(userId, "Animate, planifica un viaje");
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(listOfTravels);
}
///<sumary>
/// Crear una planificación de viaje
///</sumary>
///<param name="travel">Instancia de Travel, contiene todos los atributos necesarios
/// para realizar la creación en la base de datos.</param>
///<returns>
/// El id de Travel que fue creado
///</returns>
///<exception cref="RequiredAttributeException">
/// Es excepción es lanzada cuando falta algun campo importante para crear el viaje
///</exception>
///<exception cref="UserNotFoundException">
/// Es excepción es lanzada cuando el usuario no existe
///</exception>
public static int AddTravel(Travel travel)
{
int id = 0;
try{
if (string.IsNullOrEmpty(travel.Name) ||
string.IsNullOrEmpty(travel.Description) ||
travel.Init == DateTime.MinValue ||
travel.End == DateTime.MinValue)
{
throw new RequiredAttributeException("Falta información importante para poder crear el viaje");
}
User user = (User)UserRepository.GetUserById(travel.UserId); //Throw Exception if it doesn'n exist
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction(
"AddTravel(@travelName, @travelInit, @travelEnd, @travelDescription, @userId)",
travel.Name, travel.Init.ToString("yyyy-MM-dd"), travel.End.ToString("yyyy-MM-dd"), travel.Description, travel.UserId);
id = Convert.ToInt32(dataTable.Rows[0][0]);
travel.Id = id;
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(id);
}
///<sumary>
/// Consulta de reservas de un viaje por ciudad y tipo.
///</sumary>
///<param name="travelId">Id del viaje en la que se basará la consulta</param>
///<param name="locationId">Id de la ciudad en la que se basará la consulta</param>
///<param name="type">Tipo de reserva en la que se basará la consulta, los tipos son:
/// RESTAURANT, HOTEL, FLIGHT, CAR -> Todos en Uppercase, basado en alguno de ellos se obtendrán
/// las reservas de dicho tipo para determinado viaje con determinada ciudad.
///</param>
///<returns>
/// Las reservas por tipo, viaje y ciudad.
///</returns>
///<exception cref="InvalidReservationType">
/// Cuando el cliente envía un tipo de reserva no permitido es lanzada esta excepción
///</exception>
///<exception cref="WithoutTravelReservationsException">
/// Si el usuario no tiene reservaciones en el viaje para esa ciudad retorna esta excepción
/// no hay restricciones de si realmente la ciudad pertenece al viaje.
///</exception>
/* public static List<T> GetReservationsByTravelAndLocation<T>(int travelId, int locationId, string type){
* List<T> reservations = new List<T>();
* try{
* PgConnection pgConnection = PgConnection.Instance;
* if(type.Equals("HOTEL")){
* DataTable dataTable = pgConnection.ExecuteFunction(
* "GetReservationsOfHotelByTravelAndLocation(@travelId, @locationId)", travelId, locationId);
* if( dataTable.Rows.Count > 0 ){
* List<ReservationRoom> reservationsOfRoom = new List<ReservationRoom>();
* foreach (DataRow dataRow in dataTable.Rows){
* ReservationRoom reservationRoom = new ReservationRoom(
* Convert.ToInt32(dataRow[0]),
* DateTime.Parse(dataRow[1].ToString()),
* DateTime.Parse(dataRow[2].ToString()), Convert.ToInt32(dataRow[5]), Convert.ToInt32(dataRow[4]));
* reservationsOfRoom.Add(reservationRoom);
* }
* reservations = reservationsOfRoom.Cast<T>().ToList();
* }else{
* throw new WithoutTravelReservationsException(
* travelId, locationId, "No posee reservaciones de " + type.ToLower() + " en dicha ciudad");
* }
* }else if(type.Equals("RESTAURANT")){
* DataTable dataTable = pgConnection.ExecuteFunction(
* "GetReservationsOfRestaurantByTravelAndLocation(@travelId, @locationId)", travelId, locationId);
* if( dataTable.Rows.Count > 0 ){
* List<Restaurant_res> reservationsOfRest = new List<Restaurant_res>();
*
* foreach (DataRow dataRow in dataTable.Rows){
* Restaurant_res axu =new Restaurant_res();
* Restaurant_res reservationRest = new Restaurant_res(
* Convert.ToInt32(dataRow[0]),
* DateTime.Parse(dataRow[1].ToString()),
* Convert.ToInt32(dataRow[2].ToString()),
* Convert.ToInt32(dataRow[5]), Convert.ToInt32(dataRow[4]));
* reservationsOfRest.Add(reservationRest);
* }
* reservations = reservationsOfRest.Cast<T>().ToList();
* }else{
* throw new WithoutTravelReservationsException(
* travelId, locationId, "No posee reservaciones de " + type.ToLower() + " en dicha ciudad");
* }
* }else if(type.Equals("FLIGHT")){
*
* }else if(type.Equals("CAR")){
* DataTable dataTable = pgConnection.ExecuteFunction(
* "GetReservationsOfCarssByTravelAndLocation(@travelId, @locationId)", travelId, locationId);
* if( dataTable.Rows.Count > 0 ){
* List<ReservationVehicle> reservationsOfAuto = new List<ReservationVehicle>();
* foreach (DataRow dataRow in dataTable.Rows){
* ReservationVehicle reservationAuto = new ReservationVehicle(
* Convert.ToInt32(dataRow[0]),
* DateTime.Parse(dataRow[1].ToString()),
* DateTime.Parse(dataRow[2].ToString()), Convert.ToInt32(dataRow[4]), Convert.ToInt32(dataRow[3]));
* reservationsOfAuto.Add(reservationAuto);
* }
* reservations = reservationsOfAuto.Cast<T>().ToList();
* }else{
* throw new WithoutTravelReservationsException(
* travelId, locationId, "No posee reservaciones de " + type.ToLower() + " en dicha ciudad");
* }
* }else{
* throw new InvalidReservationTypeException(type,"Tipo de reserva invalido : " + type);
* }
* }catch(DatabaseException ex){
* throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
* }catch(InvalidStoredProcedureSignatureException ex){
* throw new InternalServerErrorException("Error en el servidor", ex);
* }
* return reservations;
* }*/
///<sumary>
/// Consulta las ciudades relacionadas con un viaje
///</sumary>
///<param name="travelId">Id del viaje con el que se basará la consulta</param>
///<returns>
/// Ciudades o Locations que estan asociadas a un viaje
///</returns>
///<exception cref="WithoutTravelLocationsException">
/// Se instancia cuando el viaje no tiene ciudades asociadas
///</exception>
public static List <Location> GetLocationsByTravel(int travelId)
{
List <Location> locationsByTravel = new List <Location>();
try{
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("GetLocationsByTravel(@travelId)", travelId);
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dataRow in dataTable.Rows)
{
Location location = new Location();
location.Id = Convert.ToInt32(dataRow[0]);
location.City = dataRow[1].ToString();
location.Country = dataRow[2].ToString();
locationsByTravel.Add(location);
}
}
else
{
throw new WithoutTravelLocationsException("El viaje aun no tiene ciudades");
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(locationsByTravel);
}
private void selectMostSuitableDiesease()
{
string pgConnectionString = ConfigurationManager.AppSettings[PgConnectionKey];
PgConnection pgCon = new PgConnection(pgConnectionString);
string year = DateTime.Now.Year.ToString();
DataTable table = pgCon.RunSql(string.Format(GetMostSuitableDieseaseCodeSql, year));
string dieseaseCode = table.Rows[0][0].ToString();
DropDownList1.ClearSelection();
DropDownList1.Items.FindByValue(dieseaseCode).Selected = true;
NameLabel.Text = string.Concat(year, YearDieseaseSeperator, DropDownList1.SelectedItem.Text);
DropDownList2.ClearSelection();
DropDownList2.Items.FindByValue(year).Selected = true;
HyperLink1.NavigateUrl = string.Format(ExportToExcelLinkFormat, DropDownList1.SelectedItem.Text.Trim(), DropDownList1.SelectedValue, year);
string viewName = string.Concat(dieseaseCode.ToLower(), year);
setMapOptions(dieseaseCode, year, false);
setLeggendColorDescriptions(viewName);
getDetailedLegend(viewName);
}
/// <summary>
/// Returns geometry object ids whose bounding box intersects
/// <paramref name="boundingBox"/>.
/// </summary>
/// <param name="boundingBox">The bounding box used to intersect.</param>
/// <returns></returns>
public IEnumerable <uint> GetIntersectingObjectIds(BoundingBox boundingBox)
{
using (PgConnection conn = new PgConnection(_connectionString))
{
string boundingBoxClause = getBoundingBoxSql(boundingBox, Srid);
String sql = String.Format("SELECT {0} FROM {1} WHERE ", ObjectIdColumn, Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += DefinitionQuery + " AND ";
}
sql += GeometryColumn + " && " + boundingBoxClause;
using (PgCommand command = new PgCommand(sql, conn))
{
conn.Open();
using (PgDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
yield return((uint)(int)reader[0]);
}
}
}
conn.Close();
}
}
}
///<sumary>Creación de un nuevo vehiculo</sumary>
///<param name="vehicle">Instancia de Vehicle</param>
///<returns>Id del vehiculo</returns>
///<exception cref="ModelNotFoundException"> Si no existe el modelo </exception>
///<exception cref="LocationNotFoundException"> Si no existe la ciudad </exception>
///<exception cref="UniqueAttributeException">
/// Es excepción es lanzada cuando ya existe un carro con dicha matricula
///</exception>
public int AddVehicle(Vehicle vehicle)
{
int id = 0;
try {
PostgresModelDAO modelDAO = new PostgresModelDAO();
modelDAO.GetModelById(vehicle.VehicleModelId); //Throw ModelNotFoundException
PostgresLocationDAO locationDAO = new PostgresLocationDAO();
locationDAO.GetLocationById(vehicle.VehicleLocationId); // Throw LocationNotFoundException
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction(
"LicenseUniqueness(@modelName)", vehicle.License);
if (Convert.ToBoolean(dataTable.Rows[0][0]))
{
throw new UniqueAttributeException("La matricula " + vehicle.License + " ya existe");
}
else
{
dataTable = pgConnection.ExecuteFunction(
"AddVehicle(@modelId, @locationId, @license, @price, @status)",
vehicle.VehicleModelId, vehicle.VehicleLocationId, vehicle.License, vehicle.Price, vehicle.Status);
id = Convert.ToInt32(dataTable.Rows[0][0]);
}
} catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
} catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(id);
}
///<sumary>Modificar una marca</sumary>
///<param name="marca">Instancia de una marca</param>
///<returns>True si actualizo correctamente</returns>
///<exception cref="BrandNotFoundException"> Si la marca no existe </exception>
///<exception cref="UniqueAttributeException">
///Si ya existe una marca con el mismo nombre
///</exception>
public bool UpdateBrand(Brand brand)
{
bool updated = false;
try{
GetBrandById(brand.Id);//Throw BrandNotFoundException
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction(
"BrandUniqueness(@brandName)", brand.BrandName);
if (Convert.ToBoolean(dataTable.Rows[0][0]))
{
throw new UniqueAttributeException("La marca " + brand.BrandName + " ya existe");
}
else
{
dataTable = pgConnection.ExecuteFunction("UpdateBrand(@vbid, @vbname)", brand.Id, brand.BrandName);
if (Convert.ToBoolean(dataTable.Rows[0][0]))
{
updated = true;
}
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(updated);
}
///<sumary>Creación de una marca de carro</sumary>
///<param name="brand">Instancia de Brand</param>
///<returns>Id de marca</returns>
///<exception cref="UniqueAttributeException">
/// Es excepción es lanzada cuando el nombre de la marca ya existe
///</exception>
public int AddBrand(Brand brand)
{
int id = 0;
try {
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction(
"BrandUniqueness(@brandName)", brand.BrandName);
if (Convert.ToBoolean(dataTable.Rows[0][0]))
{
throw new UniqueAttributeException("La marca " + brand.BrandName + " ya existe");
}
else
{
dataTable = pgConnection.ExecuteFunction(
"AddBrand(@brandName)", brand.BrandName);
id = Convert.ToInt32(dataTable.Rows[0][0]);
}
} catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
} catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(id);
}
public List <Comment> Getcomment(int id)
{/*esta funcion se encarga de traer desde la BD toda la informacion de un comentario
* hasta el front, mediante el id especificado */
List <Comment> Listcomment = new List <Comment>();
try{
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("Getcommentid (@id)", id);
if (dataTable.Rows.Count > 0)
{
Console.WriteLine(dataTable.Rows.Count);
foreach (DataRow dataRow in dataTable.Rows)
{
Comment comments = new Comment(
Convert.ToInt32(dataRow[0]),
dataRow[1].ToString(),
Convert.ToDateTime(dataRow[2]),
Convert.ToInt32(dataRow[3])
);
Listcomment.Add(comments);
}
}
else
{
throw new WithoutExistenceOfTravelsException(id, "no se encontro ningun comentario con este id ");
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(Listcomment);
}
///<sumary>Obtener modelos por Id</sumary>
///<param name="modelId">Id del modelo</param>
///<returns>Instancia del Modelo</returns>
///<exception cref="ModelNotFoundException"> Si no existe el modelo </exception>
public Model GetModelById(int modelId)
{
Model model = null;
try{
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("GetModelById(@modelId)", modelId);
if (dataTable.Rows.Count == 0)
{
throw new ModelNotFoundException(modelId, "No se ha encontrado un Modelo con Id ");
}
else
{
model = new Model(
Convert.ToInt32(dataTable.Rows[0][0]),
Convert.ToInt32(dataTable.Rows[0][1]),
dataTable.Rows[0][2].ToString(),
Convert.ToInt32(dataTable.Rows[0][3]),
dataTable.Rows[0][4].ToString()
);
model.ModelBrand = new PostgresBrandDAO().GetBrandById(model.ModelBrandId);
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(model);
}
///<sumary>Obtener vehiculos por su Id</sumary>
///<param name="vehicleId">Id del vehiculo</param>
///<returns>Instancia del Vehiculo</returns>
///<exception cref="VehicleNotFoundException"> Si no existe el vehiculo </exception>
public Vehicle GetVehicleById(int vehicleId)
{
Vehicle vehicle = null;
try{
PgConnection pgConnection = PgConnection.Instance;
DataTable dataTable = pgConnection.ExecuteFunction("GetVehicleById(@vehicleId)", vehicleId);
if (dataTable.Rows.Count == 0)
{
throw new VehicleNotFoundException(vehicleId, "No se ha encontrado un vehiculo con Id ");
}
else
{
vehicle = new Vehicle(
Convert.ToInt32(dataTable.Rows[0][0]),
Convert.ToInt32(dataTable.Rows[0][1]),
Convert.ToInt32(dataTable.Rows[0][2]),
dataTable.Rows[0][3].ToString(),
Convert.ToDouble(dataTable.Rows[0][4]),
true
);
}
}catch (DatabaseException ex) {
throw new InternalServerErrorException("Error en el servidor : Conexion a base de datos", ex);
}catch (InvalidStoredProcedureSignatureException ex) {
throw new InternalServerErrorException("Error en el servidor", ex);
}
return(vehicle);
}
static async Task TestQuery()
{
PgConnection cnn = new PgConnection(await "127.0.0.1:5432".ConnectAsync());
await cnn.LoginAsync();
while (true)
{
Console.Write("#> ");
string sql = Console.ReadLine();
sql = sql.Trim();
if (sql == "exit")
{
break;
}
try
{
var res = await cnn.QueryAsync(sql);
Console.WriteLine(res.CmdTag);
if (res.RowDesc != null)
{
Console.WriteLine(res.ColNames.ToString2());
foreach (var row in res)
{
Console.WriteLine(row);
}
}
}
catch (PgErrorException ex)
{
Console.WriteLine("PgErrorException: {0}", ex.Message);
}
}
cnn.Close();
}
public int Deletetravel(int travelid)
{
int i = 0;
PgConnection pgConnection = PgConnection.Instance;
pgConnection.ExecuteFunction("deletetravel (@travelid)", travelid);
return(i);
}
/// <summary>
/// Returns all features with the given view bounds.
/// </summary>
/// <param name="bounds">
/// The bounds of the view to query for intersection.
/// </param>
/// <param name="ds">
/// FeatureDataSet to fill data into.
/// </param>
public void ExecuteIntersectionQuery(BoundingBox bounds, FeatureDataSet ds)
{
using (PgConnection conn = new PgConnection(_connectionString))
{
string strBbox = getBoundingBoxSql(bounds, Srid);
string sql = String.Format("SELECT *, AsBinary({0}) AS {1} FROM {2} WHERE ",
GeometryColumn, RetrievedGeometryColumnName, Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += DefinitionQuery + " AND ";
}
sql += GeometryColumn + " && " + strBbox;
using (PgDataAdapter adapter = new PgDataAdapter(sql, conn))
{
conn.Open();
DataSet ds2 = new DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != RetrievedGeometryColumnName)
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (DataRow dr in ds2.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != RetrievedGeometryColumnName)
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWkb.Parse((byte[])dr[RetrievedGeometryColumnName]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(Geometry geom, FeatureDataSet ds)
{
//List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
using (PgConnection conn = new PgConnection(_connectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (Srid > 0)
{
strGeom = "setSrid(" + strGeom + "," + Srid + ")";
}
string sql = "SELECT * , AsBinary(" + GeometryColumn + ") As " + RetrievedGeometryColumnName + " FROM " +
Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += DefinitionQuery + " AND ";
}
sql += GeometryColumn + " && " + strGeom + " AND distance(" + GeometryColumn + ", " + strGeom + ") < 0";
using (PgDataAdapter adapter = new PgDataAdapter(sql, conn))
{
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != RetrievedGeometryColumnName)
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != RetrievedGeometryColumnName)
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWkb.Parse((byte[])dr[RetrievedGeometryColumnName]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns a datarow based on an object id.
/// </summary>
/// <param name="oid">The id of the feature to retrieve.</param>
/// <returns>
/// A FeatureDataRow which has the feature geometry and attributes.
/// </returns>
public FeatureDataRow <uint> GetFeature(uint oid)
{
string sql = String.Format("SELECT *, AsBinary({0}) as {1} FROM {2} WHERE {3} = '{4}'",
GeometryColumn, RetrievedGeometryColumnName, Table, ObjectIdColumn, oid);
using (PgConnection conn = new PgConnection(_connectionString))
{
using (PgCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
conn.Open();
using (PgDataReader reader = cmd.ExecuteReader())
{
FeatureDataTable <uint> fdt = new FeatureDataTable <uint>(Table, ObjectIdColumn);
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
string columnName = row["ColumnName"] as string;
if (String.Compare(columnName, GeometryColumn, StringComparison.CurrentCultureIgnoreCase) ==
0 &&
columnName != RetrievedGeometryColumnName)
{
fdt.Columns.Add(columnName, row["DataType"] as Type);
}
}
while (reader.Read())
{
FeatureDataRow <uint> fdr = fdt.NewRow((uint)reader[ObjectIdColumn]);
foreach (DataColumn col in fdt.Columns)
{
if (
String.Compare(col.ColumnName, GeometryColumn,
StringComparison.CurrentCultureIgnoreCase) == 0 &&
col.ColumnName != RetrievedGeometryColumnName)
{
fdr[col.ColumnName] = reader[col.ColumnName];
}
}
fdr.Geometry = GeometryFromWkb.Parse((byte[])reader[RetrievedGeometryColumnName]);
return(fdr);
}
}
}
}
return(null);
}
protected void Page_Load(object sender, EventArgs e)
{
string year = Request.Params[YearParameKey];
string diesease = Request.Params[DieseaseParameKey];
string code = Request.Params[DieseaseCodeParameKey];
string sql = null;
if (!string.IsNullOrEmpty(year))
{
sql = string.Format(GetDataSql, year, code);
}
string pgConnectionString = ConfigurationManager.AppSettings[PgConnectionKey];
PgConnection pgCon = new PgConnection(pgConnectionString);
DataTable table = null;
if (!string.IsNullOrEmpty(sql))
{
table = pgCon.RunSql(sql);
}
if (table != null)
{
DataGrid grid = new DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = table;
grid.DataBind();
// render the DataGrid control to a file
StringBuilder builder = new StringBuilder();
using (StringWriter writer = new StringWriter(builder))
{
using (HtmlTextWriter hw = new HtmlTextWriter(writer))
{
grid.RenderControl(hw);
}
}
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", string.Concat(diesease, year, FileExtension)));
Response.ContentEncoding = Encoding.UTF8;
Response.Cache.SetCacheability(HttpCacheability.Private);
Response.ContentType = "application/vnd.ms-excel";
Response.Write(builder.ToString());
Response.Flush();
Response.Close();
}
}
private void SetDatabase(string connectionString)
{
IConnection <NpgsqlConnection> connection = new PgConnection(connectionString);
connection = new LoggedConnection <NpgsqlConnection>(connection, logger);
database = new PgDatabase(connection);
view.Database = database;
columnsSelect.Database = database;
joinTable.Database = database;
pageSelector.Database = database;
tableSelect.Database = database;
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
string strSQL = String.Format("SELECT *, AsBinary({0}) AS sharpmap_tempgeometry FROM {1} WHERE ",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += this.GeometryColumn + " && " + strBbox;
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
public FeatureDataTable QueryFeatures(Geometry geom, double distance)
{
Collection<Geometry> features = new Collection<Geometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (SRID > 0)
strGeom = "setSRID(" + strGeom + "," + SRID.ToString() + ")";
string strSQL = "SELECT * , AsBinary(" + GeometryColumn + ") As sharpmap_tempgeometry FROM " + Table +
" WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += DefinitionQuery + " AND ";
strSQL += GeometryColumn + " && " + "buffer(" + strGeom + "," + distance.ToString(Map.NumberFormatEnUs) +
")";
strSQL += " AND distance(" + GeometryColumn + ", " + strGeom + ")<" +
distance.ToString(Map.NumberFormatEnUs);
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
DataSet ds = new DataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (DataRow dr in ds.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = GeometryFromWKB.Parse((byte[]) dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
return fdt;
}
else return null;
}
}
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public BoundingBox GetExtents()
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL = String.Format("SELECT EXTENT({0}) FROM {1}",
GeometryColumn,
Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + DefinitionQuery;
strSQL += ";";
using (PgCommand command = new PgCommand(strSQL, conn))
{
conn.Open();
BoundingBox bbox = null;
try
{
PgBox2D result = (PgBox2D) command.ExecuteScalar();
bbox = new BoundingBox(result.LowerLeft.X, result.LowerLeft.Y, result.UpperRight.X,
result.UpperRight.Y);
}
catch (Exception ex)
{
throw new Exception("Box2d couldn't fetched from table. " + ex.Message);
}
finally
{
conn.Close();
}
return bbox;
}
}
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public IGeometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (PgConnection conn = new PgConnection(_ConnectionString))
{
String strSql = String.Format("SELECT AsBinary({0}) As Geom FROM {1} WHERE {2} = '{3}'",
this.GeometryColumn,
this.Table,
this.ObjectIdColumn,
oid);
conn.Open();
using (PgCommand command = new PgCommand(strSql, conn))
{
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
object obj = dr[0];
if (typeof(PgPoint) == obj.GetType())
geom = SharpMap.Converters.Geometries.GeometryFactory.CreatePoint(((PgPoint)obj).X, ((PgPoint)obj).Y);
else if (obj != DBNull.Value)
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
conn.Close();
}
return geom;
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection<IGeometry> GetGeometriesInView(IEnvelope bbox)
{
Collection<IGeometry> features = new Collection<IGeometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
String strSql = String.Format("SELECT AsBinary({0}) as geom FROM {1} WHERE ",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSql += this.DefinitionQuery + " AND ";
strSql += String.Format("{0} && {1}", this.GeometryColumn, strBbox);
using (PgCommand command = new PgCommand(strSql, conn))
{
conn.Open();
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
//object obj = dr[0];
IGeometry geom = null;
// if ( typeof(PgPoint) == obj.GetType() )
// geom = new SharpMap.Geometries.Point( ((PgPoint)obj).X, ((PgPoint)obj).Y );
// else
if (dr[0] != DBNull.Value)
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
if (geom != null)
features.Add(geom);
}
}
conn.Close();
}
}
return features;
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public IEnvelope GetExtents()
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL = String.Format("SELECT EXTENT({0}) FROM {1}",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
strSQL += ";";
using (PgCommand command = new PgCommand(strSQL, conn))
{
conn.Open();
IEnvelope bbox = null;
try
{
PostgreSql.Data.PgTypes.PgBox2D result = (PostgreSql.Data.PgTypes.PgBox2D)command.ExecuteScalar();
bbox = SharpMap.Converters.Geometries.GeometryFactory.CreateEnvelope(result.LowerLeft.X, result.UpperRight.X, result.LowerLeft.Y, result.UpperRight.Y);
}
catch (System.Exception ex)
{
throw new Exception("Box2d couldn't fetched from table. " + ex.Message);
}
finally
{
conn.Close();
}
return bbox;
}
}
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(IEnvelope bbox, SharpMap.Data.FeatureDataSet ds)
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
string strSQL = String.Format("SELECT *, AsBinary({0}) AS sharpmap_tempgeometry FROM {1} WHERE ",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="RowID"></param>
/// <returns>datarow</returns>
public SharpMap.Data.FeatureDataRow GetFeature(uint RowID)
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL = String.Format("select * , AsBinary({0}) As sharpmap_tempgeometry from {1} WHERE {2} = '{3}'",
this.GeometryColumn, this.Table, this.ObjectIdColumn, RowID);
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
FeatureDataSet ds = new FeatureDataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
if (ds.Tables[0].Rows.Count > 0)
{
System.Data.DataRow dr = ds.Tables[0].Rows[0];
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
return fdr;
}
else
return null;
}
else
return null;
}
}
}
/// <summary>
/// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
/// </summary>
/// <remarks></remarks>
/// <returns>Name of column containing geometry</returns>
private string GetGeometryColumn()
{
string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name = @Table'";
using (PgConnection conn = new PgConnection(_ConnectionString))
using (PgCommand command = new PgCommand(strSQL, conn))
{
conn.Open();
command.Parameters.Add(new PgParameter("@Table", PgDbType.VarChar));
command.Parameters[0].Value = this._Table;
object columnname = command.ExecuteScalar();
conn.Close();
if (columnname == System.DBNull.Value)
throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
return (string)columnname;
}
}
/// <summary>
/// Returns the number of features in the dataset
/// </summary>
/// <returns>number of features</returns>
public int GetFeatureCount()
{
int count = 0;
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL = "SELECT COUNT(*) FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (PgCommand command = new PgCommand(strSQL, conn))
{
conn.Open();
count = (int)command.ExecuteScalar();
conn.Close();
}
}
return count;
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(IGeometry geom, FeatureDataSet ds)
{
//List<IGeometry> features = new List<IGeometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strGeom + " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<0";
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns geometry Object IDs whose bounding box intersects 'bbox'
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection<uint> GetObjectIDsInView(IEnvelope bbox)
{
Collection<uint> objectlist = new Collection<uint>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
String strSql = String.Format("SELECT {0} FROM {1} WHERE ", this.ObjectIdColumn, this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSql += this.DefinitionQuery + " AND ";
strSql += this.GeometryColumn + " && " + strBbox;
using (PgCommand command = new PgCommand(strSql, conn))
{
conn.Open();
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = (uint)(int)dr[0];
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return objectlist;
}