public SqlDataReader showUserComments(UserMasterPage masterPage, int hoodId) { newConnection.Open(); string query = string.Format( @"SELECT n.name, comment, n.neighbourhoodID, Account.firstName, Account.lastName, CrimeRate, TransportationRate, HousingRate, EntertainmentRate, SchoolRate, ShoppingRate, ((crimeRate*2+transportationRate+housingRate*2+entertainmentRate+schoolRate*2+shoppingRate)/9) as overalRate " + "from Review " + "Inner join Account on Account.accountID = Review.accountID " + "Inner join Neighbourhood n on n.neighbourhoodID = Review.neighbourhoodID " + "WHERE n.neighbourhoodID = " + hoodId + "and CrimeRate >= '" + masterPage.SliderCrime + "'" + "and TransportationRate >= '" + masterPage.SliderTransportation + "'" + "and HousingRate >= '" + masterPage.SliderHousing + "'" + "and EntertainmentRate >= '" + masterPage.SliderEntertainment + "'" + "and ShoppingRate >= '" + masterPage.SliderShopping + "'" + "and SchoolRate >= '" + masterPage.SliderSchools + "' "); command = new SqlCommand(query, newConnection); return(command.ExecuteReader()); }
protected void Page_Load(object sender, EventArgs e) { List <string> result = new List <string>(); if (Session["USER"] != null) { lbl_welcome.Text = $"Welcome, {Session["USER"].ToString()} !!"; } nc = new Connection(); try { UserMasterPage masterPage = Master as UserMasterPage; result = nc.showTop3Ranking(masterPage); nc.CloseConnection(); } finally { nc.CloseConnection(); } //rank1 Latitude1 = Convert.ToDouble(result[9]); Lngitude1 = Convert.ToDouble(result[10]); lbl_Title1.Text = result[1]; all_rank1.Text = result[8]; crime1.Text = result[2]; trans1.Text = result[3]; house1.Text = result[4]; entertainment1.Text = result[5]; school1.Text = result[6]; shopping1.Text = result[7]; disc_rank1.Text = result[30]; //rank2 Latitude2 = Convert.ToDouble(result[20]); Lngitude2 = Convert.ToDouble(result[21]); lbl_Title2.Text = result[12]; all_rank2.Text = result[19]; crime2.Text = result[13]; trans2.Text = result[14]; house2.Text = result[15]; entertainment2.Text = result[16]; school2.Text = result[17]; shopping2.Text = result[18]; disc_rank2.Text = result[30]; //rank3 Latitude3 = Convert.ToDouble(result[31]); Lngitude3 = Convert.ToDouble(result[32]); lbl_Title3.Text = result[23]; all_rank3.Text = result[30]; crime3.Text = result[24]; trans3.Text = result[25]; house3.Text = result[26]; entertainment3.Text = result[27]; school3.Text = result[28]; shopping3.Text = result[29]; disc_rank3.Text = result[30]; }
protected void Page_Load(object sender, EventArgs e) { Latitude = Convert.ToDouble(Session["Lat"]); Lngitude = Convert.ToDouble(Session["Lng"]); if (Session["hoodId"] != null) { Latitude = Convert.ToDouble(Session["Lat"]); Lngitude = Convert.ToDouble(Session["Lng"]); newConnection = new Connection(); int hoodIdInt = Convert.ToInt16(Session["hoodId"]); hood = newConnection.getNeighbourhood2(hoodIdInt); //DetailsView1.DataSource = newConnection.getNeighbourhood(hoodIdInt); //DetailsView1.DataBind(); //newConnection.CloseConnection(); //hood = new Neighbourhood(); try later move entity objects using Session //lbl_Title.Text = Session["hoodIdReview"].ToString(); lbl_Title.Text = hood.Name.ToString(); lblCrime.Text = hood.OveralCrimeRate.ToString(); lbl_rate.Text = hood.OveralRate.ToString(); lblHouse.Text = hood.OveralHousingRate.ToString(); lblTrans.Text = hood.OveralTransportationRate.ToString(); lblEnter.Text = hood.OveralEntertainmentRate.ToString(); lblShopping.Text = hood.OveralShoppingRate.ToString(); lblSchool.Text = hood.OveralSchoolRate.ToString(); lbl_description.Text = hood.Description.ToString(); Session["hoodName"] = lbl_Title.Text; //adding user comments try { UserMasterPage masterPage = Master as UserMasterPage; rptRate.DataSource = newConnection.showUserComments(masterPage, hoodIdInt); rptRate.DataBind(); newConnection.CloseConnection(); } finally { newConnection.CloseConnection(); } } }
protected void Page_Load(object sender, EventArgs e) { newConnection = new Connection(); try { UserMasterPage masterPage = Master as UserMasterPage; userListRepeaterAdminReviewPage.DataSource = newConnection.showReviewList(); userListRepeaterAdminReviewPage.DataBind(); } catch (Exception sqlException) { throw new Exception(sqlException.ToString()); } finally { newConnection.CloseConnection(); } }
//public SqlDataReader ShowRanking() //{ // newConnection.Open(); // string query = string.Format(@"SELECT name, convert(decimal(5,2),overallCrimeRate) as CrimeRate, convert(decimal(5,2),overallTransportationRate) as TransportationRate," + // "convert(decimal(5,2),overallHousingRate) as HousingRate,convert(decimal(5,2),overallEntertainmentRate) as EntertainmentRate,convert(decimal(5,2),overallSchoolRate) as SchoolRate," + // "convert(decimal(5,2),overallShoppingRate) AS ShoppingRate," + // "convert(decimal(5,2),overallRate) as overallRate FROM NEIGHBOURHOOD ORDER BY OVERALLRATE DESC"); // command = new SqlCommand(query, newConnection); // return command.ExecuteReader(); //} public SqlDataReader showRanking2(UserMasterPage masterPage) { newConnection.Open(); string query = string.Format(@"SELECT neighbourhoodId, name, convert(decimal(5,2),overallCrimeRate) as CrimeRate, convert(decimal(5,2),overallTransportationRate) as TransportationRate," + "convert(decimal(5,2),overallHousingRate) as HousingRate,convert(decimal(5,2),overallEntertainmentRate) as EntertainmentRate,convert(decimal(5,2),overallSchoolRate) as SchoolRate," + "convert(decimal(5,2),overallShoppingRate) as ShoppingRate," + "convert(decimal(5,2),overallRate) as overallRate, latitude, longitude FROM HOODCOORD " + "WHERE overallCrimeRate >= '" + masterPage.SliderCrime + "'" + "and overallTransportationRate >= '" + masterPage.SliderTransportation + "'" + "and overallHousingRate >= '" + masterPage.SliderHousing + "'" + "and overallEntertainmentRate >= '" + masterPage.SliderEntertainment + "'" + "and overallShoppingRate >= '" + masterPage.SliderShopping + "'" + "and overallSchoolRate >= '" + masterPage.SliderSchools + "' " + "ORDER BY OVERALLRATE DESC"); command = new SqlCommand(query, newConnection); return(command.ExecuteReader()); }
protected void Page_Load(object sender, EventArgs e) { Lat = 43.65F; Lng = -79.38F; Session["hoodId"] = null; nc = new Connection(); try { UserMasterPage masterPage = Master as UserMasterPage; neighborhoodRepeater.DataSource = nc.showRanking2(masterPage); neighborhoodRepeater.DataBind(); nc.CloseConnection(); } finally { nc.CloseConnection(); } }
//SqlConnection connection = new SqlConnection("Data Source=LAOGUNAGXPS\\LAOGUANGSQL;Initial Catalog=HoodTo;Integrated Security=True"); protected void Page_Load(object sender, EventArgs e) { Session["PAGETOGO"] = null; Session["PAGE"] = "UserProfile"; //Session["USER"] = "******"; if (Session["USER"] == null) { ClientScript.RegisterStartupScript(this.GetType(), "Check if user login", "alert('Please LOG IN');window.location ='LoginPage.aspx';", true); } else { lblWelcome.Text = $"Welcome, {Session["USER"].ToString()} "; } try { UserMasterPage masterPage = Master as UserMasterPage; query = string.Format( @"SELECT n.name, comment, n.neighbourhoodID, CrimeRate, TransportationRate, HousingRate, EntertainmentRate, SchoolRate, ShoppingRate " + "from Review " + "Inner join Account on Account.accountID = Review.accountID " + "Inner join Neighbourhood n on n.neighbourhoodID = Review.neighbourhoodID " + "WHERE CrimeRate > '" + masterPage.SliderCrime + "'" + "and TransportationRate > '" + masterPage.SliderTransportation + "'" + "and HousingRate > '" + masterPage.SliderHousing + "'" + "and EntertainmentRate > '" + masterPage.SliderEntertainment + "'" + "and ShoppingRate > '" + masterPage.SliderShopping + "'" + "and SchoolRate > '" + masterPage.SliderSchools + "'" + "and Account.email = '" + Session["USER"].ToString() + "'"); connection.Open(); command = new SqlCommand(query, connection); rptRate.DataSource = command.ExecuteReader(); rptRate.DataBind(); connection.Close(); query = string.Format(@"SELECT admin from account where email = '" + Session["USER"].ToString() + "'"); command = new SqlCommand(query, connection); connection.Open(); string isAdmin = (string)command.ExecuteScalar(); command.ExecuteReader().Close(); connection.Close(); try { if (isAdmin.ToLower() == "y") { tbl1.Enabled = true; tbl1.Visible = true; } } catch (Exception) { var message = new JavaScriptSerializer().Serialize("Cannot find the user"); var script = string.Format("alert({0});", message); ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", script, true); } } catch (Exception ex) { Response.Write(ex.Message); connection.Close(); } finally { connection.Close(); } }
public List <string> showTop3Ranking(UserMasterPage masterPage) { List <string> rankingInfo = new List <string>(); newConnection.Open(); string query = string.Format(@"SELECT TOP 3 neighbourhoodId, name, convert(decimal(5,2),overallCrimeRate) as CrimeRate, convert(decimal(5,2),overallTransportationRate) as TransportationRate," + "convert(decimal(5,2),overallHousingRate) as HousingRate,convert(decimal(5,2),overallEntertainmentRate) as EntertainmentRate,convert(decimal(5,2),overallSchoolRate) as SchoolRate," + "convert(decimal(5,2),overallShoppingRate) as ShoppingRate," + "convert(decimal(5,2),overallRate) as overallRate, latitude, longitude FROM HOODCOORD " + "WHERE overallCrimeRate > '" + masterPage.SliderCrime + "'" + "and overallTransportationRate > '" + masterPage.SliderTransportation + "'" + "and overallHousingRate > '" + masterPage.SliderHousing + "'" + "and overallEntertainmentRate > '" + masterPage.SliderEntertainment + "'" + "and overallShoppingRate > '" + masterPage.SliderShopping + "'" + "and overallSchoolRate > '" + masterPage.SliderSchools + "' " + "ORDER BY OVERALLRATE DESC"); command = new SqlCommand(query, newConnection); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { rankingInfo.Add(reader.GetValue(0).ToString()); rankingInfo.Add(reader.GetValue(1).ToString()); rankingInfo.Add(reader.GetValue(2).ToString()); rankingInfo.Add(reader.GetValue(3).ToString()); rankingInfo.Add(reader.GetValue(4).ToString()); rankingInfo.Add(reader.GetValue(5).ToString()); rankingInfo.Add(reader.GetValue(6).ToString()); rankingInfo.Add(reader.GetValue(7).ToString()); rankingInfo.Add(reader.GetValue(8).ToString()); rankingInfo.Add(reader.GetValue(9).ToString()); rankingInfo.Add(reader.GetValue(10).ToString()); } reader.Close(); newConnection.Close(); } else { throw new Exception(); } //newConnection.Open(); //query = $"Select description from Neighbourhood where neighbourhoodID = '{rankingInfo[0]}' or neighbourhoodID = '{rankingInfo[11]}' or neighbourhoodID = '{rankingInfo[22]}'"; //command = new SqlCommand(query, newConnection); //reader = command.ExecuteReader(); //if (reader.HasRows) //{ // while (reader.Read()) // { // rankingInfo.Add(reader.GetValue(0).ToString()); // } //} //else //{ // throw new Exception(); //} return(rankingInfo); }