private void GetGeoContent(HttpContext context)
{
GeoContent geoContent = new GeoContent();
using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["hadb5"].ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT GeoID, Title, Intro, Latitude, Longitude FROM Geo WHERE GeoID = " + routeData.Values["id"], conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
geoContent = new GeoContent()
{
id = (int)dr["GeoID"],
title = dr["Title"].ToString(),
lat = (decimal)dr["Latitude"],
lng = (decimal)dr["Longitude"],
intro = dr["Intro"].ToString()
};
using (SqlCommand cmdTags = new SqlCommand("SELECT TagID FROM Tag_Geo WHERE GeoID = " + geoContent.id, conn))
using (SqlDataReader drTags = cmdTags.ExecuteReader())
while (drTags.Read())
{
geoContent.tagids.Add((int)drTags["TagID"]);
}
using (SqlCommand cmdImgs = new SqlCommand("SELECT ImageID FROM Geo_Image WHERE GeoID = " + geoContent.id, conn))
using (SqlDataReader drImgs = cmdImgs.ExecuteReader())
while (drImgs.Read())
{
geoContent.imageids.Add((int)drImgs["ImageID"]);
}
//using (SqlCommand cmdVids = new SqlCommand("SELECT VideoID FROM Geo_Video WHERE GeoID = " + geoContent.id, conn))
//using (SqlDataReader drVids = cmdVids.ExecuteReader())
// while (drVids.Read())
// geoContent.videoids.Add((int)drVids["VideoID"]);
//using (SqlCommand cmdPDFs = new SqlCommand("SELECT PDFID FROM Geo_PDF WHERE GeoID = " + geoContent.id, conn))
//using (SqlDataReader drPDFs = cmdPDFs.ExecuteReader())
// while (drPDFs.Read())
// geoContent.pdfids.Add((int)drPDFs["PDFID"]);
//using (SqlCommand cmdExts = new SqlCommand("SELECT Source, Link FROM Geo_Ext WHERE GeoID = " + geoContent.id, conn))
//using (SqlDataReader drExts = cmdExts.ExecuteReader())
// while (drExts.Read())
// geoContent.exts.Add(new GeoContentExt() { source = (byte)drExts["Source"], link = drExts["Link"].ToString() }); //"http://www.youtube.com/watch?v="
using (SqlCommand cmdTexts = new SqlCommand("SELECT Content.Ordering, Headline FROM Content, Text WHERE Content.ContentID = Text.ContentID AND GeoID = " + geoContent.id + " ORDER BY Content.Ordering", conn))
using (SqlDataReader drTexts = cmdTexts.ExecuteReader())
while (drTexts.Read())
{
geoContent.texts.Add(new GeoContentText()
{
ordering = (int)((Int16)drTexts["Ordering"]), headline = drTexts["Headline"].ToString()
});
}
}
}
}
Common.SendStats(context, "geocontent5");
Common.WriteOutput(geoContent, context, routeData);
}
private void GetGeoContent(HttpContext context)
{
GeoContent geoContent = new GeoContent();
using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["hadb"].ConnectionString))
{
conn.Open();
//TODO: Should only do this, if its a non editor who is accessing the info
//new SqlCommand("UPDATE Geo SET Views = Views + 1 WHERE GeoID = " + routeData.Values["id"], conn).ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("SELECT GeoID, Title, Intro, GeoX, GeoY FROM Geo WHERE GeoID = " + routeData.Values["id"], conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
LatLng ll = LatLng.FromHACoord(new HACoord((int)dr["GeoX"], (int)dr["GeoY"]));
geoContent = new GeoContent()
{
id = (int)dr["GeoID"],
title = dr["Title"].ToString(),
lat = ll.latitude,
lng = ll.longitude,
intro = dr["Intro"].ToString()
};
using (SqlCommand cmdTags = new SqlCommand("SELECT TagID FROM Tag_Geo WHERE GeoID = " + geoContent.id, conn))
using (SqlDataReader drTags = cmdTags.ExecuteReader())
while (drTags.Read())
{
geoContent.tagids.Add((int)drTags["TagID"]);
}
using (SqlCommand cmdImgs = new SqlCommand("SELECT ImageID FROM Geo_Image WHERE GeoID = " + geoContent.id, conn))
using (SqlDataReader drImgs = cmdImgs.ExecuteReader())
while (drImgs.Read())
{
geoContent.imageids.Add((int)drImgs["ImageID"]);
}
using (SqlCommand cmdVids = new SqlCommand("SELECT VideoID FROM Geo_Video WHERE GeoID = " + geoContent.id, conn))
using (SqlDataReader drVids = cmdVids.ExecuteReader())
while (drVids.Read())
{
geoContent.videoids.Add((int)drVids["VideoID"]);
}
using (SqlCommand cmdPDFs = new SqlCommand("SELECT PDFID FROM Geo_PDF WHERE GeoID = " + geoContent.id, conn))
using (SqlDataReader drPDFs = cmdPDFs.ExecuteReader())
while (drPDFs.Read())
{
geoContent.pdfids.Add((int)drPDFs["PDFID"]);
}
using (SqlCommand cmdExts = new SqlCommand("SELECT Source, Link FROM Geo_Ext WHERE GeoID = " + geoContent.id, conn))
using (SqlDataReader drExts = cmdExts.ExecuteReader())
while (drExts.Read())
{
geoContent.exts.Add(new GeoContentExt()
{
source = (byte)drExts["Source"], link = drExts["Link"].ToString()
});
} //"http://www.youtube.com/watch?v="
using (SqlCommand cmdTexts = new SqlCommand("SELECT Ordering, Headline FROM Geo_Text WHERE GeoID = " + geoContent.id + " ORDER BY Ordering", conn))
using (SqlDataReader drTexts = cmdTexts.ExecuteReader())
while (drTexts.Read())
{
geoContent.texts.Add(new GeoContentText()
{
ordering = (int)drTexts["Ordering"], headline = drTexts["Headline"].ToString()
});
}
}
}
}
Common.SendStats(context, "geocontent");
Common.WriteOutput(geoContent, context, routeData);
}
