//Insert method to insert new house object into tbl_HouseInfo
public static String Insert( BrowseHouse newHouse )
{
//Edit(newHouse);
String newHouseID="H0";
Int32 hId=0;
String datePostedString;
String seller = "NULL";
DataTable nHouse = GetHouseIDString();
if (nHouse.Rows.Count != 0)
newHouseID = nHouse.Rows[0].Field<String>("HouseID").ToString();
hId = Convert.ToInt32(newHouseID.Substring(1));
hId++;
newHouseID = "H" + hId.ToString();
if(newHouse.DatePosted != null)
datePostedString = "'" + Convert.ToDateTime(newHouse.DatePosted).ToString("MM/dd/yyyy") + "'";
else
datePostedString="NULL";
if (newHouse.SellerID == 0)
seller = "NULL";
else
seller = newHouse.SellerID.ToString();
string sqlInsert = string.Format(
"INSERT INTO tbl_HouseInfo(HouseID,BldNumber,StreetName,StreetDirection,UnitNumber,City,PostalCode, " +
"Province,Tax,Price,Bedroom,Bathroom,HouseType,HouseStyle,Description," +
"Parking,Garage,Basement,Refrigerator,Stove,DishWasher,WasherDryer," +
"Watersoftener,WaterHeater,CentralAC,CentralVaccum,FirePlace,HeatSystem," +
"SwimmingPool, Deck, LotSize, Area, RoofCover, Exterior, Ownership, Status, DatePosted, " +
"SellerID, YearBuilt) " +
"VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}', "+
"'{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}'," +
"'{32}', '{33}', '{34}', '{35}', {36}, {37}, '{38}')",
newHouseID, newHouse.BldNumber, newHouse.StreetName, newHouse.StreetDirection, newHouse.UnitNumber, newHouse.City,
newHouse.PostalCode,newHouse.Province,newHouse.Tax, Convert.ToDecimal(newHouse.Price.ToString("#.##")), newHouse.BedRoom, newHouse.BathRoom, newHouse.houseType,
newHouse.HouseStyle,newHouse.Description, newHouse.Parking, newHouse.Garage, newHouse.Basement, newHouse.Refrigerator,
newHouse.Stove, newHouse.DishWasher, newHouse.washerDryer, newHouse.Watersoftener, newHouse.WaterHeater,
newHouse.CentralAC, newHouse.centralVaccum, newHouse.FirePlace, newHouse.HeatSystem, newHouse.SwimmingPool,
newHouse.Deck, newHouse.LotSize, newHouse.Area, newHouse.RoofCover, newHouse.Exterior, newHouse.Ownership,
newHouse.Status, datePostedString, seller, newHouse.Yearbuilt);
SqlCommand houseCommand = new SqlCommand(sqlInsert, sqlCon);
try
{
sqlCon.Open();
houseCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCon.Close();
}
return newHouse.HouseID;
}
///// <summary>
/// update a house record using the given object
/// </summary>
/// <param name="newHouse"></param>
public static void Update(BrowseHouse newHouse)
{
string sqlUpdate = string.Format(
"UPDATE tbl_HouseInfo SET BldNumber='{0}',StreetName='{1}',StreetDirection='{2}',UnitNumber='{3}',City='{4}',PostalCode='{5}', " +
"Province='{6}',Tax='{7}',Price='{8}',Bedroom='{9}',Bathroom='{10}',HouseType='{11}',HouseStyle='{12}',Description='{13}'," +
"Parking='{14}',Garage='{15}',Basement='{16}',Refrigerator='{17}',Stove='{18}',DishWasher='{19}',WasherDryer='{20}'," +
"Watersoftener='{21}',WaterHeater='{22}',CentralAC='{23}',CentralVaccum='{24}',FirePlace='{25}',HeatSystem='{26}'," +
"SwimmingPool='{27}', Deck='{28}', LotSize='{29}', Area='{30}', RoofCover='{31}', Exterior='{32}', Ownership='{33}', Status='{34}', " +
"SellerID='{35}', YearBuilt='{36}' WHERE HouseID='{37}'",
newHouse.BldNumber, newHouse.StreetName, newHouse.StreetDirection, newHouse.UnitNumber, newHouse.City,
newHouse.PostalCode, newHouse.Province, newHouse.Tax, Convert.ToDecimal(newHouse.Price.ToString("#.##")), newHouse.BedRoom, newHouse.BathRoom, newHouse.houseType,
newHouse.HouseStyle, newHouse.Description, newHouse.Parking, newHouse.Garage, newHouse.Basement, newHouse.Refrigerator,
newHouse.Stove, newHouse.DishWasher, newHouse.washerDryer, newHouse.Watersoftener, newHouse.WaterHeater,
newHouse.CentralAC, newHouse.centralVaccum, newHouse.FirePlace, newHouse.HeatSystem, newHouse.SwimmingPool,
newHouse.Deck, newHouse.LotSize, newHouse.Area, newHouse.RoofCover, newHouse.Exterior, newHouse.Ownership,
newHouse.Status, newHouse.SellerID, newHouse.Yearbuilt, newHouse.HouseID);
SqlCommand houseUpdateCommand = new SqlCommand(sqlUpdate, sqlCon);
try
{
sqlCon.Open();
houseUpdateCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCon.Close();
}
}
public static void Delete(BrowseHouse newHouse)
{
string sqlDelete = string.Format(
"DELETE FROM tbl_HouseInfo WHERE HouseID='{0}'", newHouse.houseID);
SqlCommand houseCommand = new SqlCommand(sqlDelete, sqlCon);
try
{
sqlCon.Open();
houseCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCon.Close();
}
}
