public static List <Products> GetProducts()
{
SqlConnection connection = TravelExpertsDB.GetConnection();
List <Products> results = new List <Products>();
try
{
string sql = "SELECT ProductId, ProdName FROM Products ";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Products d = new Products();
d.ProductId = Convert.ToInt32(reader["ProductId"]);
d.ProdName = reader["ProdName"].ToString();
results.Add(d);
}
}
catch
{
}
finally
{
connection.Close();
}
return(results);
}
public static int GetPSID_By_P_SID(int pid, int sid)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
int result = 0;
try
{
string sql = "SELECT ProductSupplierId " +
" FROM Products_Suppliers " +
" WHERE ProductId=" + pid + " AND SupplierId=" + sid;
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
reader.Read();
result = Convert.ToInt32(reader["ProductSupplierId"]);
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
return(result);
}
public static List <Supplier> GetSuppliersByProductID(int pid)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
List <Supplier> results = new List <Supplier>();
try
{
string sql = "SELECT SupplierId, SupName FROM Suppliers " +
" WHERE SupplierId = ANY (SELECT SupplierId " +
" FROM Products_Suppliers " +
" WHERE ProductId =" + pid + ")";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Supplier s = new Supplier();
s.SupplierId = Convert.ToInt32(reader["SupplierId"]);
s.SupName = reader["SupName"].ToString();
results.Add(s);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
return(results);
}
public static List <Product_Supplier> GetProducts_Suppliers()
{
SqlConnection connection = TravelExpertsDB.GetConnection();
List <Product_Supplier> results = new List <Product_Supplier>();
try
{
string sql = "SELECT ProductSupplierID, ProductId, " +
" SupplierId FROM Products_Suppliers";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Product_Supplier s = new Product_Supplier();
s.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
s.ProductId = Convert.ToInt32(reader["ProductId"]);
s.SupplierId = Convert.ToInt32(reader["SupplierId"]);
results.Add(s);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
return(results);
}
public static bool AddPS(int pid, int sid)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
Product_Supplier result = new Product_Supplier();
int rowAffected = 0;
try
{
string sql = "INSERT INTO Products_Suppliers (ProductId, " +
" SupplierId) " +
" VALUES (@pid, @sid);";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@pid", pid);
command.Parameters.AddWithValue("@sid", sid);
rowAffected = command.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
if (rowAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public static void AddPackage(string PkgName, string PkgStartDate, string PkgEndDate, string PkgDesc, string PkgBasePrice, string PkgAgencyCommission)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
try
{
string sql = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) VALUES (@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@PkgName", PkgName);
command.Parameters.AddWithValue("@PkgStartDate", PkgStartDate);
command.Parameters.AddWithValue("@PkgEndDate", PkgEndDate);
command.Parameters.AddWithValue("@PkgDesc", PkgDesc);
command.Parameters.AddWithValue("@PkgBasePrice", PkgBasePrice);
command.Parameters.AddWithValue("@PkgAgencyCommission", PkgAgencyCommission);
command.ExecuteNonQuery();
}
catch
{
}
finally
{
connection.Close();
}
}
public static bool UpdatePS(int psid, int pid, int sid)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
Product_Supplier result = new Product_Supplier();
int rowAffected = 0;
try
{
string sql = "UPDATE Products_Suppliers SET ProductId=@pid," +
" SupplierId=@sid " +
" WHERE ProductSupplierId=@psid";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@psid", psid);
command.Parameters.AddWithValue("@pid", pid);
command.Parameters.AddWithValue("@sid", sid);
rowAffected = command.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
if (rowAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public int SavePackage(Package package)
{
//Create the SQL Query for inserting a package
string createQuery = String.Format("Insert into Packages (PkgName, PkgStartDate , PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) Values('{0}', '{1}', '{2}', '{3}', '{4}', {5});"
+ "Select @@Identity", package.PkgName, package.PkgStartDate, package.PkgEndDate, package.PkgDesc, package.PkgBasePrice, package.PkgAgencyCommission);
//Create the SQL Query for updating a package
string updateQuery = String.Format("Update Packages SET PkgName='{0}', PkgStartDate = '{1}', PkgEndDate ='{2}', PkgDesc = '{3}', PkgBasePrice = '{4}', PkgAgencyCommission = {5} Where PackageID = {6};",
package.PkgName, package.PkgStartDate, package.PkgEndDate, package.PkgDesc, package.PkgBasePrice, package.PkgAgencyCommission, package.PackageID);
//Create and open a connection to SQL Server
SqlConnection connection = TravelExpertsDB.GetConnection();
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
//Create a Command object
SqlCommand command = null;
if (package.PackageID != 0)
{
command = new SqlCommand(updateQuery, connection);
}
else
{
command = new SqlCommand(createQuery, connection);
}
int savedPackageID = 0;
try
{
//Execute the command to SQL Server and return the newly created ID
var commandResult = command.ExecuteScalar();
if (commandResult != null)
{
savedPackageID = Convert.ToInt32(commandResult);
}
else
{
//the update SQL query will not return the primary key but if doesn't throw exception
//then we will take it from the already provided data
savedPackageID = package.PackageID;
}
}
catch (Exception ex)
{
//there was a problem executing the script
}
//Close and dispose
command.Dispose();
connection.Close();
connection.Dispose();
return(savedPackageID);
}
// Modified By Yue Yang 20190602 Add AddPackageForForm2
public static int AddPackageForForm2(string pkgname, DateTime startdate,
DateTime enddate, string desc, decimal baseprice, decimal comm)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
int rowAffected = 0;
int pId = 0;
try
{
string sql =
"INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
" VALUES (@pkgname, @startdate, @enddate, @desc, @baseprice, @comm);";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@pkgname", pkgname);
command.Parameters.AddWithValue("@startdate", startdate);
command.Parameters.AddWithValue("@enddate", enddate);
command.Parameters.AddWithValue("@desc", desc);
command.Parameters.AddWithValue("@baseprice", baseprice);
command.Parameters.AddWithValue("@comm", comm);
rowAffected = command.ExecuteNonQuery();
string sql1 = "SELECT PackageId FROM Packages " +
" WHERE PkgName=" + "'" + pkgname + "'";
SqlCommand command1 = new SqlCommand(sql1, connection);
SqlDataReader reader =
command1.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
reader.Read();
pId = Convert.ToInt32(reader["PackageId"]);
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
if (rowAffected > 0)
{
return(pId);
}
else
{
return(-1);
}
}
public List <Package> GetPackage()
{
List <Package> result = new List <Package>();
//Create the SQL Query for returning all the packages
string sqlQuery = String.Format("select * from Packages");
//Create and open a connection to SQL Server
SqlConnection connection = TravelExpertsDB.GetConnection();
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
SqlCommand command = new SqlCommand(sqlQuery, connection);
//Create DataReader for storing the returning table into server memory
SqlDataReader dataReader = command.ExecuteReader();
Package package = null;
//load into the result object the returned row from the database
if (dataReader.HasRows)
{
while (dataReader.Read())
{
package = new Package();
package.PackageID = Convert.ToInt32(dataReader["PackageID"]);
package.PkgName = dataReader["PkgName"].ToString();
package.PkgStartDate = Convert.ToDateTime(dataReader["PkgStartDate"]);
package.PkgEndDate = Convert.ToDateTime(dataReader["PkgEndDate"]);
package.PkgDesc = dataReader["PkgDesc"].ToString();
package.PkgBasePrice = Convert.ToInt32(dataReader["PkgBasePrice"]);
package.PkgAgencyCommission = Convert.ToInt32(dataReader["PkgAgencyCommission"]);
result.Add(package);
}
}
return(result);
}
public static void AddProducts(string ProdName)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
try
{
string sql = "INSERT INTO Products (ProdName) VALUES (@ProdName)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@ProdName", ProdName);
command.ExecuteNonQuery();
}
catch
{
}
finally
{
connection.Close();
}
}
public static List <Package> GetPackages()
{
SqlConnection connection = TravelExpertsDB.GetConnection();
List <Package> results = new List <Package>();
try
{
string sql = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission FROM Packages";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Package s = new Package();
s.PackageId = Convert.ToInt32(reader["PackageId"]);
s.PkgName = reader["PkgName"].ToString();
s.PkgStartDate = reader["PkgStartDate"].ToString();
s.PkgEndDate = reader["PkgEndDate"].ToString();
s.PkgDesc = reader["PkgDesc"].ToString();
s.PkgBasePrice = reader["PkgBasePrice"].ToString();
s.PkgAgencyCommission = reader["PkgAgencyCommission"].ToString();
results.Add(s);
}
}
catch
{
}
finally
{
connection.Close();
}
return(results);
}
// ------------Added by Wei Guang Yan----------------
// Method to get maximum id from table "Suppliers"
public static int GetMaxId()
{
SqlConnection connection = TravelExpertsDB.GetConnection();
int result = 0;
try
{
string sql = "SELECT MAX(SupplierId) AS MaxId FROM Suppliers";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
reader.Read();
result = Convert.ToInt32(reader["MaxId"]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(result);
}
public static void AddPackage(string SupName)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
try
{
string sql = "INSERT INTO Suppliers (SupName) VALUES (@SupName)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@SupName", SupName);
command.ExecuteNonQuery();
}
catch
{
}
finally
{
connection.Close();
}
}
public Package GetPackageById(int packageId)
{
Package result = new Package();
//Create the SQL Query for returning a package based on its primary key
string sqlQuery = String.Format("select * from Packages where PackageId={0}", packageId);
//Create and open a connection to SQL Server
SqlConnection connection = TravelExpertsDB.GetConnection();
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
SqlCommand command = new SqlCommand(sqlQuery, connection);
SqlDataReader dataReader = command.ExecuteReader();
//load into the result object the returned row from the database
if (dataReader.HasRows)
{
while (dataReader.Read())
{
result.PackageID = Convert.ToInt32(dataReader["PackageID"]);
result.PkgName = dataReader["PkgName"].ToString();
result.PkgStartDate = Convert.ToDateTime(dataReader["PkgStartDate"]);
result.PkgEndDate = Convert.ToDateTime(dataReader["PkgEndDate"]);
result.PkgDesc = dataReader["PkgDesc"].ToString();
result.PkgBasePrice = Convert.ToInt32(dataReader["PkgBasePrice"]);
result.PkgAgencyCommission = Convert.ToInt32(dataReader["PkgAgencyCommission"]);
}
}
return(result);
}
