public List <PgModel> get_list_pg_on_nearbyarea(string cityname, string nearbyarea)
{
int cityid = getCityid(cityname);
con.Open();
SqlCommand cmd_pg = new SqlCommand("Select * from pgtable where pgcity=@cityid and pgnearbyarea=@nearby", con);
cmd_pg.Parameters.AddWithValue("@cityid", cityid);
cmd_pg.Parameters.AddWithValue("@nearby", nearbyarea);
SqlDataReader dr_pg = cmd_pg.ExecuteReader();
List <PgModel> list = new List <PgModel>();
while (dr_pg.Read())
{
PgModel obj = new PgModel();
obj.pgID = dr_pg.GetInt32(0);
obj.OBuserID = dr_pg.GetString(1);
obj.pgName = dr_pg.GetString(2);
obj.pgaddress = dr_pg.GetString(3);
obj.nearbyarea = dr_pg.GetString(4);
obj.pgcityid = dr_pg.GetInt32(5).ToString();
obj.pgImagesAddress = dr_pg.GetString(6);
obj.amenities = dr_pg.GetString(7);
obj.pgDescription = dr_pg.GetString(8);
list.Add(obj);
}
con.Close();
return(list);
}
public List <PgModel> getPgofowner(string ownerid)
{
con.Open();
SqlCommand cmd_get_pg_of_owner = new SqlCommand("Select * from pgtable where obuserid=@obuserid", con);
cmd_get_pg_of_owner.Parameters.AddWithValue("@obuserid", ownerid);
SqlDataReader dr_pgs_of_owner = cmd_get_pg_of_owner.ExecuteReader();
List <PgModel> ls = new List <PgModel>();
while (dr_pgs_of_owner.Read())
{
PgModel obj = new PgModel();
obj.pgID = dr_pgs_of_owner.GetInt32(0);
obj.OBuserID = dr_pgs_of_owner.GetString(1);
obj.pgName = dr_pgs_of_owner.GetString(2);
obj.pgaddress = dr_pgs_of_owner.GetString(3);
obj.nearbyarea = dr_pgs_of_owner.GetString(4);
obj.pgcityid = dr_pgs_of_owner.GetInt32(5).ToString();
obj.pgImagesAddress = dr_pgs_of_owner.GetString(6);
obj.amenities = dr_pgs_of_owner.GetString(7);
obj.pgDescription = dr_pgs_of_owner.GetString(8);
ls.Add(obj);
}
return(ls);
}
public bool addPg(PgModel model)
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd_insert_pg = new SqlCommand("insert pgtable values(@obuserid,@pgname,@pgaddress,@pgnearbyarea,@pgcity,null,@pgAmenities,@pgdescription)", con);
cmd_insert_pg.Parameters.AddWithValue("@pgname", model.pgName);
cmd_insert_pg.Parameters.AddWithValue("@obuserid", model.OBuserID);
cmd_insert_pg.Parameters.AddWithValue("@pgaddress", model.pgaddress);
cmd_insert_pg.Parameters.AddWithValue("@pgnearbyarea", model.nearbyarea);
cmd_insert_pg.Parameters.AddWithValue("@pgcity", Convert.ToInt32(model.pgcityid));
cmd_insert_pg.Parameters.AddWithValue("@pgAmenities", model.amenities);
cmd_insert_pg.Parameters.AddWithValue("@pgdescription", model.pgDescription);
cmd_insert_pg.Transaction = tran;
cmd_insert_pg.ExecuteNonQuery();
SqlCommand cmd_pgid = new SqlCommand("select @@identity", con);
cmd_pgid.Transaction = tran;
model.pgID = Convert.ToInt32(cmd_pgid.ExecuteScalar());
model.pgImagesAddress = "/PGImages/" + model.pgID + ".jpg";
SqlCommand cmd_update_img = new SqlCommand(@"update pgtable set pgImageaddress=@pgimg where pgid=@pgid", con);
cmd_update_img.Parameters.AddWithValue("@pgimg", model.pgImagesAddress);
cmd_update_img.Parameters.AddWithValue("@pgid", model.pgID);
cmd_update_img.Transaction = tran;
cmd_update_img.ExecuteNonQuery();
if (model.pgID != 0)
{
tran.Commit();
con.Close();
return(true);
}
else
{
tran.Rollback();
con.Close();
return(false);
}
}
public PgModel getPgDetails(int id)
{
con.Open();
SqlCommand cmd_get_pg = new SqlCommand("Select * from pgtable where pgID=@pgID", con);
cmd_get_pg.Parameters.AddWithValue("@pgID", id);
SqlDataReader dr_pg = cmd_get_pg.ExecuteReader();
PgModel model = new PgModel();
if (dr_pg.Read())
{
model.pgID = dr_pg.GetInt32(0);
model.pgName = dr_pg.GetString(2);
model.pgaddress = dr_pg.GetString(3);
model.nearbyarea = dr_pg.GetString(4);
model.pgcityid = dr_pg.GetString(5);
model.pgImagesAddress = dr_pg.GetString(6);
model.amenities = dr_pg.GetString(7);
}
return(model);
}
public List <PgModel> get_list_pg_on_cities(string cityname)
{
con.Open();
int cityid = getCityid(cityname);
SqlCommand cmd_get_pg_on_cities = new SqlCommand("Select * from pgtable where pgcity=@cityid", con);
cmd_get_pg_on_cities.Parameters.AddWithValue("@cityid", cityid);
SqlDataReader dr_pgs = cmd_get_pg_on_cities.ExecuteReader();
List <PgModel> ls = new List <PgModel>();
while (dr_pgs.Read())
{
PgModel pg = new PgModel();
pg.pgID = dr_pgs.GetInt32(0);
pg.pgName = dr_pgs.GetString(2);
pg.pgaddress = dr_pgs.GetString(3);
pg.nearbyarea = dr_pgs.GetString(4);
pg.pgImagesAddress = dr_pgs.GetString(6);
pg.amenities = dr_pgs.GetString(7);
ls.Add(pg);
}
con.Close();
return(ls);
}