public List <Sublet> GetSubletSearchWithProp(string SubletQuery, string PropQuery)
{
SqlConnection con = null;
List <Sublet> sl = new List <Sublet>();
try
{
con = connect("ConnectionStringName"); // create a connection to the database using the connection String defined in the web config file
String selectSTR = "SELECT * FROM PropTbl p inner join SubletTbl s on p.SubPropID = s.SubletID WHERE " + SubletQuery + " AND " + PropQuery + ";";
SqlCommand cmd = new SqlCommand(selectSTR, con);
// get a reader
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // CommandBehavior.CloseConnection: the connection will be closed after reading has reached the end
while (dr.Read())
{ // Read till the end of the data into a row
Sublet s = new Sublet();
s.SubletID = Convert.ToInt32(dr["SubletID"]);
s.CheckIn = (DateTime)dr["CheckIn"];
s.CheckOut = (DateTime)dr["CheckOut"];
s.Price = Convert.ToInt32(dr["Price"]);
s.NomOfRooms = Convert.ToInt32(dr["NumOfRooms"]);
s.SqMtr = Convert.ToInt32(dr["SquareMeter"]);
s.FloorNo = Convert.ToInt32(dr["FloorNu"]);
s.Description = (string)dr["SubDescription"];
s.isFacebook = (bool)dr["isFacebook"];
s.Roommates = Convert.ToInt32(dr["Roommates"]);
sl.Add(s);
}
return(sl);
}
catch (Exception ex)
{
// write to log
throw (ex);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
// GET api/<controller>/5
public List <Sublet> GetSubletSearchWithProp(string SubletQuery, string PropQuery)
{
Sublet s = new Sublet();
return(s.GetSubletSearchWithProp(SubletQuery, PropQuery));
}
// GET api/<controller>/5
public List <Sublet> GetLikedSublets(string UserFBID)
{
Sublet s = new Sublet();
return(s.GetLikedSublets(UserFBID));
}
//GET api/<controller>
public List <Sublet> GetSearchSublet(string City, string EnterDate, string ExitDate, string Type, int Rooms, int MinBudjet, int MaxBudjet)
{
Sublet s = new Sublet();
return(s.SearchSublets(City, EnterDate, ExitDate, Type, Rooms, MinBudjet, MaxBudjet));
}
public List <Sublet> SearchSublets(string City, string EnterDate, string ExitDate, string Type, int Rooms, int MinBudjet, int MaxBudjet)
{
SqlConnection con = null;
SqlConnection con2 = null;
List <Location> ll = new List <Location>();
List <Sublet> sl = new List <Sublet>();
try
{
con = connect("ConnectionStringName"); // create a connection to the database using the connection String defined in the web config file
String selectSTR = "SELECT * FROM LocationTbl WHERE City LIKE '" + City + "'";
SqlCommand cmd = new SqlCommand(selectSTR, con);
// get a reader
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // CommandBehavior.CloseConnection: the connection will be closed after reading has reached the end
while (dr.Read())
{ // Find all sublets in requested city
Location l = new Location();
l.SubletID = Convert.ToInt32(dr["SubletID"]);
l.City = (string)dr["City"];
l.Rout = (string)dr["Rout"];
l.StreetAddress = (string)dr["StreetAddress"];
l.Lat = (string)dr["Lat"];
l.Lng = (string)dr["Lng"];
l.PlaceID = (string)dr["PlaceID"];
ll.Add(l);
}
if (ll.Count == 0)
{
throw new Exception("NO RESULTS FOUND IN REQUESTED CITY!");
}
else
{ //Get all sublets from DB
string SubStr = "SELECT * from SubletTbl WHERE Price BETWEEN " + MinBudjet + " AND " + MaxBudjet;
con2 = connect("ConnectionStringName");
SqlCommand cmd2 = new SqlCommand(SubStr, con2);
SqlDataReader dr2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
while (dr2.Read())
{
Sublet s = new Sublet();
s.SubletID = Convert.ToInt32(dr2["SubletID"]);
s.CheckIn = (DateTime)dr2["CheckIn"];
s.CheckOut = (DateTime)dr2["CheckOut"];
s.Price = Convert.ToInt32(dr2["Price"]);
s.NomOfRooms = Convert.ToInt32(dr2["NumOfRooms"]);
s.SqMtr = Convert.ToInt32(dr2["SquareMeter"]);
s.FloorNo = Convert.ToInt32(dr2["FloorNu"]);
s.Description = (string)dr2["SubDescription"];
s.isFacebook = (bool)dr2["isFacebook"];
s.Roommates = Convert.ToInt32(dr2["Roommates"]);
sl.Add(s);
}
}
return(sl);
}
catch (Exception ex)
{
// write to log
throw (ex);
}
finally
{
if (con != null)
{
con.Close();
con2.Close();
}
}
}
