public static List <Products_Supplier> GetProducts_Suppliers()
{
List <Products_Supplier> products_Suppliers = new List <Products_Supplier>(); // empty list object
Products_Supplier ps; // for reading
using (SqlConnection connection = TravelExpertConnection.GetConnection())
{
string selectQuery = "SELECT ProductSupplierId, ProductId, ProdName, SupplierId, SupName" +
" FROM Products_Suppliers" +
" order BY SupplierId";
using (SqlCommand cmd = new SqlCommand(selectQuery, connection))
{
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read()) // while there is data
{
ps = new Products_Supplier(); // object with no data initialized
ps.ProductSupplierId = (int)dr["ProductSupplierId"];
ps.ProductId = (int)dr["ProductId"];
ps.SupplierId = (int)dr["SupplierId"];
ps.ProdName = (string)dr["ProdName"];
ps.SupName = (string)dr["SupName"];
products_Suppliers.Add(ps);
}
} // dr gets closed and recycled
} // cmd gets recycled
} // connection gets closed, and recycled
return(products_Suppliers);
}
public static Products_Supplier GetProductSupplierByID(int productID)
{
List <Products_Supplier> products_Supplier = new List <Products_Supplier>(); // empty list object
//Products_Supplier products_Supplier = new Products_Supplier(); // empty list object
Products_Supplier psi = null;
using (SqlConnection connection = TravelExpertConnection.GetConnection())
{
string query = "SELECT ProductId, ProductSupplierId, SupplierId FROM Products_Suppliers " +
//"ORDER BY ProductSupplierId " +
" Where ProductId = @ProductId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
cmd.Parameters.AddWithValue("@ProductId", productID);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read()) // while there is data
{
psi = new Products_Supplier();
psi.ProductSupplierId = (int)dr["ProductSupplierId"];
psi.ProductId = (int)dr["ProductId"];
psi.SupplierId = (int)dr["SupplierId"];
products_Supplier.Add(psi);
}
}
}
}
return(psi);
}
public static List <int> GetSupplierIDs()
{
List <int> supplierIDs = new List <int>();
// make empty list of IDs
int id;
//int id = -1;
using (SqlConnection con = TravelExpertConnection.GetConnection())
{
string query = "SELECT SupplierId FROM Suppliers";
using (SqlCommand cmd = new SqlCommand(query, con))
{
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
id = (int)reader["SuppplierId"];
supplierIDs.Add(id);
}
}
}
}
return(supplierIDs);
}
} // end of AddSuplier
public static Suppliers GetSupplier(int supplierId)
{
Suppliers supplier = null;
using (SqlConnection con = TravelExpertConnection.GetConnection())
{
string query = "SELECT SupplierId, SupName " //SupplierId,
+ "FROM Suppliers "
+ "WHERE SupplierId = @SupplierId";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@SupplierId", supplierId);
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader
(CommandBehavior.SingleRow))
{
if (reader.Read()) // filling up the suppliers
{
supplier = new Suppliers();
supplier.SupplierId = (int)reader["SupplierId"];
supplier.SupName = reader["SupName"].ToString();
} // end of if object
} // end of 3rd using
} // end of 2nd using
} // end of 1st using
return(supplier);
} // end of GetSupplier
} // end of query search
public static List <Suppliers> GetAllSuppliers()
{
List <Suppliers> suppliers = new List <Suppliers>(); // empty list object
Suppliers ps; // for reading
using (SqlConnection connection = TravelExpertConnection.GetConnection())
{
string selectQuery = "SELECT SupplierId, SupName FROM Suppliers " +
"ORDER BY SupName";
using (SqlCommand cmd = new SqlCommand(selectQuery, connection))
{
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read()) // while there is data
{
ps = new Suppliers(); // object with no data initialized
ps.SupplierId = (int)dr["SupplierID"];
ps.SupName = (string)dr["SupName"];
suppliers.Add(ps);
}
} // dr gets closed and recycled
} // cmd gets recycled
} // connection gets closed, and recycled
return(suppliers);
}
public static List <Packages_Products_Supplier> GetPackages_Products_SupplierByID(int productSupplierId)
{
List <Packages_Products_Supplier> packages_Products_Supplier = new List <Packages_Products_Supplier>(); // empty list object
Packages_Products_Supplier pps; // for reading
using (SqlConnection connection = TravelExpertConnection.GetConnection())
{
string selectQuery = "SELECT PackageId, ProductSupplierId " +
"FROM [Packages_Products_Supplier]" +
"WHERE ProductSupplierId = @ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(selectQuery, connection)) // build an object an pass it a
{
connection.Open();
cmd.Parameters.AddWithValue("@ProductSupplierId", productSupplierId);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read()) // while there is data
{
pps = new Packages_Products_Supplier(); // object with no data initialized
pps.PackageId = (int)dr["PackageId"];
pps.ProductSupplierId = (int)dr["ProductSupplierId"];
packages_Products_Supplier.Add(pps);
}
}
}
}
return(packages_Products_Supplier);
}
} //End of Update
//Add package
public static int AddPackage(Packages pack)
{
int pkId = 0;
using (SqlConnection con = TravelExpertConnection.GetConnection())
{
string insertStatement = "INSERT INTO Packages " +
"(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, Product) " +
"OUTPUT inserted.PackageId " + // output auto-generated ID
"VALUES DISTINCT (@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission, @Product) ";
using (SqlCommand cmd = new SqlCommand(insertStatement, con))
{
cmd.Parameters.AddWithValue("@PkgName", pack.PkgName);
cmd.Parameters.AddWithValue("@PkgDesc", pack.PkgDesc);
//Start date
if (pack.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", pack.PkgStartDate);
}
//End date
if (pack.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", pack.PkgEndDate);
}
//Product
if (pack.Product == null)
{
cmd.Parameters.AddWithValue("@Product", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Product", pack.Product);
}
cmd.Parameters.AddWithValue("@PkgBasePrice", pack.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", pack.PkgAgencyCommission);
con.Open();
pkId = (int)cmd.ExecuteScalar();
} //2nd using
} //1st using
return(pkId);
} // end AddPackage
// Add Product
public static int AddProduct(Products prod)
{
int prId = 0;
using (SqlConnection con = TravelExpertConnection.GetConnection())
{
string insertStatement = "INSERT INTO Products " +
"(ProdName) " +
"OUTPUT inserted.ProductId " + // output auto-generated ID
"VALUES(@ProdName)";
using (SqlCommand cmd = new SqlCommand(insertStatement, con))
{
cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);
con.Open();
prId = (int)cmd.ExecuteScalar();
}
}
return(prId);
}
} // end of GetSupplier
public static bool UpdateSuppliers(Suppliers oldSup, Suppliers newSup)
{
bool success = false; // if no new supplier
using (SqlConnection con = TravelExpertConnection.GetConnection())
{
string UpdateStatement = "UPDATE SUPPLIERS SET " +
" SupName = @NewSupName " +
" WHERE Suppliersid = @OldSuppliersid " +
" And (SupName = @OldSupName OR " +
" SupName IS NULL AND OldSupName IS NULL)";
using (SqlCommand cmd = new SqlCommand(UpdateStatement, con))
{
if (newSup.SupName == null)
{
cmd.Parameters.AddWithValue("@NewSupName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewSupName", (string)oldSup.SupName);
cmd.Parameters.AddWithValue("@NewSuuplierID", oldSup.SupplierId);
}
if (oldSup.SupName == null)
{
cmd.Parameters.AddWithValue("@OldSupName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldSupName", (string)oldSup.SupName);
}
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true;
}
} // end of object using object
return(success);
} // end of connection update Supplier
} // end of query search
}//End of GetAllProducts
//Update Product
public static bool UpdateProduct(Products oldProd, Products newProd)
{
using (SqlConnection con = TravelExpertConnection.GetConnection())
{
string updateStatement = "UPDATE Products " +
"SET ProdName = @NewProdName " +
"WHERE ProductId = @OldProductId " +
"AND ProdName = @OldProdName ";
//"ADD CONSTRAINT ProdNam UNIQUE (ProductId, ProdName)";
using (SqlCommand cmd = new SqlCommand(updateStatement, con))
{
//******NEW Products data*********
cmd.Parameters.AddWithValue("@NewProdName", newProd.ProdName);
//******OLD Products data*********
cmd.Parameters.AddWithValue("@OldProductId", oldProd.ProductId);
cmd.Parameters.AddWithValue("@OldProdName", oldProd.ProdName);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}
}
//Getting all products
public static List <Products> GetAllProducts()
{
List <Products> products = new List <Products>();
Products pr = null;
SqlConnection con = TravelExpertConnection.GetConnection();
string selectStatement = "SELECT * " +
"FROM Products " +
"ORDER BY ProductId";
SqlCommand cmd = new SqlCommand(selectStatement, con);
//con.Open();
//SqlDataReader reader = cmd.ExecuteReader();
//if (!reader.HasRows)
//{
// MessageBox.Show("Product Name has to be unique")
//}
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // while there is another record
{
pr = new Products();
pr.ProductId = (int)reader["ProductId"];
pr.ProdName = reader["ProdName"].ToString();
products.Add(pr);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(products);
}//End of GetAllProducts
public static int AddSupplier(Suppliers supplier)
{
int supID = -1; // -1
using (SqlConnection con = TravelExpertConnection.GetConnection())
{
string insertStatement = "INSERT INTO Suppliers (SupName, ) "
+ "OUTPUT inserted.SupplierId " +
"Values (@SupplierName)";
using (SqlCommand cmd = new SqlCommand(insertStatement, con))
{
// add more codes
cmd.CommandText = "spInsertSupplier"; //procedure for handle non-auto generate pk
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SupplierName", supplier.SupName);
con.Open();
supID = (int)cmd.ExecuteScalar();
}
}
return(supID);
} // end of AddSuplier
public static List <int> GetProductSupplierIDs()
{
List <int> productSupplierIDs = new List <int>(); // empty list of IDs
int id; // for reading
using (SqlConnection connection = TravelExpertConnection.GetConnection())
{
string query = "SELECT ProductSupplierId FROM Products_Suppliers";
// any exception not handled here is automatically thrown to the form
// where the method was called
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// close connection as soon as done with reading
while (reader.Read())
{
id = (int)reader["productSupplierId"];
productSupplierIDs.Add(id);
}
} // command object recycled
} // connection object recycled
return(productSupplierIDs);
}
}//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
