}//End of GetAllPackages
//Update Package
public static bool UpdatePackage(Packages oldPack, Packages newPack)
{
using (SqlConnection con = TravelExpertConnection.GetConnection())
{
string updateStatement = "UPDATE Packages " +
"SET PkgName = @NewPkgName, " +
" PkgDesc = @NewPkgDesc, " +
" PkgStartDate = @NewPkgStartDate, " +
" PkgEndDate = @NewPkgEndDate, " +
" PkgBasePrice = @NewPkgBasePrice, " +
" PkgAgencyCommission = @NewPkgAgencyCommission, " +
" Product = @NewProduct " +
"WHERE PackageId = @OldPackageId " +
"AND PkgName = @OldPkgName " +
"AND PkgDesc = @OldPkgDesc " +
"AND (PkgStartDate = @OldPkgStartDate OR " + //Nullable
" PkgStartDate IS NULL AND @OldPkgStartDate IS NULL) " +
"AND (PkgEndDate = @OldPkgEndDate OR " + //Nullable
" PkgStartDate IS NULL AND @OldPkgStartDate IS NULL) " +
"AND PkgBasePrice = @OldPkgBasePrice " +
"AND PkgAgencyCommission = @OldPkgAgencyCommission " +
"AND (Product = @OldProduct OR " + //Nullable
" Product IS NULL AND @OldProduct IS NULL)";
using (SqlCommand cmd = new SqlCommand(updateStatement, con))
{
//******NEW Packages data*********
cmd.Parameters.AddWithValue("@NewPkgName", newPack.PkgName);
cmd.Parameters.AddWithValue("@NewPkgDesc", newPack.PkgDesc);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPack.PkgBasePrice);
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", newPack.PkgAgencyCommission);
if (newPack.Product == null)
{
cmd.Parameters.AddWithValue("@NewProduct", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewProduct", newPack.Product);
}
//New Start date
if (newPack.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@NewPkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgStartDate", (DateTime)newPack.PkgStartDate);
}
//New End date
if (newPack.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@NewPkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgEndDate", (DateTime)newPack.PkgEndDate);
}
//******OLD Packages data*********
cmd.Parameters.AddWithValue("@OldPackageId", oldPack.PackgeId);
cmd.Parameters.AddWithValue("@OldPkgName", oldPack.PkgName);
cmd.Parameters.AddWithValue("@OldPkgDesc", oldPack.PkgDesc);
//old Start date
if (oldPack.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@OldPkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgStartDate", (DateTime)oldPack.PkgStartDate);
}
//old End date
if (oldPack.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@OldPkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgEndDate", (DateTime)oldPack.PkgEndDate);
}
cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPack.PkgBasePrice);
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", oldPack.PkgAgencyCommission);
if (oldPack.Product == null)
{
cmd.Parameters.AddWithValue("@OldProduct", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldProduct", oldPack.Product);
}
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
} //2nd using
} //1st "using"
} //End of Update
//Getting all packages
public static List <Packages> GetAllPackages()
{
List <Packages> packages = new List <Packages>();
Packages pk = null;
SqlConnection con = TravelExpertConnection.GetConnection();
string selectStatement = "SELECT * " +
"FROM Packages " +
"ORDER BY PackageId";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
pk = new Packages();
pk.PackgeId = (int)reader["PackageId"];
pk.PkgName = reader["PkgName"].ToString();
pk.PkgDesc = reader["PkgDesc"].ToString();
pk.PkgBasePrice = (decimal)reader["PkgBasePrice"];
pk.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
packages.Add(pk);
//for null values
//Added Product
int prod = reader.GetOrdinal("Product");
if (reader.IsDBNull(prod))
{
pk.Product = null;
}
else
{
pk.Product = reader["Product"].ToString();
}
//Dates
int dateStart = reader.GetOrdinal("PkgStartDate");
int dateEnd = reader.GetOrdinal("PkgEndDate");
if (reader.IsDBNull(dateStart))
{
pk.PkgStartDate = null;
}
else if (reader.IsDBNull(dateEnd))
{
pk.PkgEndDate = null;
}
else // it is not null
{
pk.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]);
pk.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(packages);
}//End of GetAllPackages