public static int InsertPackagesProductSuppliers(PackagesProductSuppliers newPackagesProductSuppliers)
{
int yay = 0;
//using package db to initialize connection
DBCall.InitSQL();
using (SqlConnection conn = new SqlConnection(DBCall.builder.ConnectionString))
{
string insertStatement = "INSERT INTO Packages_Products_Suppliers(PackageId, ProductSupplierId) VALUES(@PackageId, @ProductSupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, conn);
//Takes values from the main page and puts into database
cmd.Parameters.AddWithValue("@PackageId", newPackagesProductSuppliers.PackageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", newPackagesProductSuppliers.ProductSupplierId);
try
{
conn.Open();
yay = cmd.ExecuteNonQuery(); //run DML statements
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
return(yay);
}
//Maryam
//Update statement for Packages
public static bool UpdatePackage(Package newPkg, Package oldPkg)
{
bool Pkg = false;
DBCall.InitSQL();
using (SqlConnection conn = new SqlConnection(DBCall.builder.ConnectionString))
{
string updateStatement = "UPDATE Packages " +
"SET PkgName = @NewPkgName, " +
" PkgStartDate = @NewPkgStartDate, " +
" PkgEndDate = @NewPkgEndDate, " +
" PkgDesc = @NewPkgDesc, " +
" PkgBasePrice = @NewPkgBasePrice, " +
" PkgAgencyCommission = @NewPkgAgencyCommission " +
"WHERE PackageId = @OldPackageId " +
"AND PkgName = @OldPkgName " +
"AND PkgStartDate = @OldPkgStartDate " +
"AND PkgEndDate = @OldPkgEndDate " +
"AND PkgDesc = @OldPkgDesc " +
"AND PkgBasePrice = @OldPkgBasePrice " +
"AND PkgAgencyCommission = @OldPkgAgencyCommission";
SqlCommand cmd = new SqlCommand(updateStatement, conn);
cmd.Parameters.AddWithValue("@NewPkgName", newPkg.PkgName);
cmd.Parameters.AddWithValue("@NewPkgStartDate", newPkg.PkgStartDate);
cmd.Parameters.AddWithValue("@NewPkgEndDate", newPkg.PkgEndDate);
cmd.Parameters.AddWithValue("@NewPkgDesc", newPkg.PkgDesc);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPkg.PkgBasePrice);
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", newPkg.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@OldPackageId", oldPkg.PackageId);
cmd.Parameters.AddWithValue("@OldPkgName", oldPkg.PkgName);
cmd.Parameters.AddWithValue("@OldPkgStartDate", oldPkg.PkgStartDate);
cmd.Parameters.AddWithValue("@OldPkgEndDate", oldPkg.PkgEndDate);
cmd.Parameters.AddWithValue("@OldPkgDesc", oldPkg.PkgDesc);
cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPkg.PkgBasePrice);
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", oldPkg.PkgAgencyCommission);
try
{
conn.Open();
Pkg = Convert.ToBoolean(cmd.ExecuteNonQuery());
if (Pkg)
{
Pkg = true;
}
else
{
Pkg = false;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
return(Pkg);
}
//Helen Lin
//Adding a new product to database
public static void AddNewProduct(Product newProduct)
//returns the auto-generated ID of the new Package
{
DBCall.InitSQL(); //use the database connection from PackageDB
using (SqlConnection conn = new SqlConnection(DBCall.builder.ConnectionString))
{
string insertStatement = "INSERT INTO Products(ProdName)" + "VALUES(@ProdName)";
SqlCommand cmd = new SqlCommand(insertStatement, conn);
cmd.Parameters.AddWithValue("@ProdName", newProduct.ProdName);
try
{
conn.Open();
cmd.ExecuteNonQuery(); //run DML statements
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
private void BLogin_Click(object sender, EventArgs e)
{
//check login type
if (RBLocal.Checked && Err.IsNonNullString(TBName.Text))
{
LName = TBName.Text;
Local = true;
DBCall.InitSQL();
if (DBCall.CSucces)
{
Close();
}
}
if (RBRemote.Checked && Err.IsNonNullString(TBName.Text))
{
Local = false;
LName = TBName.Text;
User = TBUser.Text;
Pass = TBPass.Text;
DBCall.InitSQL();
if (DBCall.CSucces)
{
Close();
}
}
}
//Helen
//this will allow user to add a new Supplier to list
public static void AddNewSupplier(Supplier newSupplier)
//returns the auto-generated ID of the new Package
{
//using package db to initialize connection
DBCall.InitSQL();
using (SqlConnection conn = new SqlConnection(DBCall.builder.ConnectionString))
{
string insertStatement = "INSERT INTO Suppliers(SupplierId, SupName) VALUES(@SupplierId, @SupName)";
SqlCommand cmd = new SqlCommand(insertStatement, conn);
cmd.Parameters.AddWithValue("@SupplierId", newSupplier.SupplierId);
cmd.Parameters.AddWithValue("@SupName", newSupplier.SupName);
try
{
conn.Open();
cmd.ExecuteNonQuery(); //run DML statements
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
//this will allow user to add a new package to list
public static int AddNewPackage(Package newPackage)
//returns the auto-generated ID of the new Package
{
//PackageId will be auto incremented once created
int PackageId;
DBCall.InitSQL();
using (SqlConnection conn = new SqlConnection(DBCall.builder.ConnectionString))
{
string insertStatement = "INSERT INTO Packages(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, ProdName)" + "VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission, @ProdName)";
//selects all the columns needed to insert the new product into package_products_suppliers
SqlCommand cmd = new SqlCommand(insertStatement, conn);
cmd.Parameters.AddWithValue("@PkgName", newPackage.PkgName);
cmd.Parameters.AddWithValue("@PkgStartDate", newPackage.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", newPackage.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", newPackage.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", newPackage.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", newPackage.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", newPackage.PkgAgencyCommission);
try
{
conn.Open();
cmd.ExecuteNonQuery(); //run DML statements
//retrieve generated ID
//runs first command which inserts
string selectQuery = "SELECT IDENT_CURRENT('Packages') FROM Packages"; //gets current ID
SqlCommand selectCommand = new SqlCommand(selectQuery, conn);
selectCommand.ExecuteScalar(); //selects one value
PackageId = Convert.ToInt32(selectCommand.ExecuteScalar()); //selects one value Note that typecasting Int does not work!
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(PackageId);
}
}
// creating list of instances of the Package class
public static List <Package> GetPackages()
{
// create new list of Packages
List <Package> Packagess = new List <Package>(); // make an empty list
Package pkg; // reference to new Package object
DBCall.InitSQL();
using (SqlConnection connection = new SqlConnection(DBCall.builder.ConnectionString))
{
// create select command
string selectString = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, " +
"PkgDesc, PkgBasePrice, PkgAgencyCommission FROM Packages";
// selects records from data source based on connection and string
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
// open connection
connection.Open();
// run the select command and process the results adding Packages to the list
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
// create Package objects to populate list
pkg = new Package();
pkg.PackageId = Convert.ToInt32(reader["PackageId"]);
pkg.PkgName = reader["PkgName"].ToString();
pkg.PkgStartDate = reader["PkgStartDate"].ToString();
pkg.PkgEndDate = reader["PkgEndDate"].ToString();
pkg.PkgDesc = reader["PkgDesc"].ToString();
pkg.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]);
pkg.PkgAgencyCommission = Convert.ToDecimal(reader["PkgAgencyCommission"]);
Packagess.Add(pkg);
}
reader.Close();
}
// general exception
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
// return list of Packages
return(Packagess);
}
}
public static ProdSupplier GetProdSuppliersOBJECT(int yolo)
{
// create new list of ProdSuppliers
//List<ProdSupplier> ProdSuppliers = new List<ProdSupplier>(); // make an empty list
ProdSupplier prodSup = null; // reference to new ProdSupplier object
//
DBCall.InitSQL();
using (SqlConnection connection = new SqlConnection(DBCall.builder.ConnectionString))
{
// create select command
string selectString = "SELECT ProductSupplierId, ps.ProductId, ps.SupplierId, ProdName, SupName " +
"FROM Products_Suppliers ps " +
"join Products p on ps.ProductId = p.ProductId " +
"join Suppliers s on ps.SupplierId = s.SupplierId " +
"where ProductSupplierId = @ProductSupplierId";
// selects records from data source based on connection and string
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("ProductSupplierId", yolo);
try
{
// open connection
connection.Open();
// run the select command and process the results adding ProdSuppliers to the list
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
// create ProdSupplier objects to populate list
prodSup = new ProdSupplier();
prodSup.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
prodSup.ProductId = Convert.ToInt32(reader["ProductId"]);
prodSup.SupplierId = Convert.ToInt32(reader["SupplierId"]);
prodSup.ProdName = (reader["ProdName"]).ToString();
prodSup.SupName = (reader["SupName"]).ToString();
}
reader.Close();
}
// general exception
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
}
// return list of ProdSuppliers
return(prodSup);
}
private void btnPrdctSupps_Click_1(object sender, EventArgs e)
{
dGView.DataSource = null;
dGView.DataSource = DBCall.GetPSuppliers();
txtStartDate.Visible = true;
txtEndDate.Visible = false;
txtDesc.Visible = false;
txtBasePrice.Visible = false;
txtAgencyComm.Visible = false;
ClearTxtBoxes();
lblDetails.Text = "Product Supplier Details";
lblSearch.Text = "Search Product Suppliers:";
PopulateList();
}
public Main()
{
InitializeComponent();
SQLCon();
if (!ConnectionPage.Term && DBCall.CSucces)
{
dGView.DataSource = DBCall.GetPackages();
PopulateList();
dDLSelected = ddl[0].ToString();
}
else if (ConnectionPage.Term || !DBCall.CSucces)
{
Environment.Exit(1);
}
}
//Helen
//Here we will get a ProductSupplier Id and a SupId
//which will be used in the supplier combo box within add packages and then insert method later in same
public static List <ProdSupplier> GetProdSuppliersBasedOnProductId(ProdSupplier newProductSupplier)
{
List <ProdSupplier> SupplierListBasedOnProd = new List <ProdSupplier>(); // make an empty list
DBCall.InitSQL();
ProdSupplier prodSupListObj;// reference to new ProdSupplier object
using (SqlConnection connection = new SqlConnection(DBCall.builder.ConnectionString))
{
// create select command
string selectString = "select ps.SupplierId, ps.ProductSupplierId from Products_Suppliers ps " +
"join Suppliers s on ps.SupplierId = s.SupplierId " +
"where ProductId = @ProductId";
// selects records from data source based on connection and string
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("@ProductId", newProductSupplier.ProductId);
try
{
// open connection
connection.Open();
// run the select command and process the results
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
// create Prod Supplier objects to populate list
prodSupListObj = new ProdSupplier();
prodSupListObj.SupplierId = Convert.ToInt32(reader["SupplierId"]);
prodSupListObj.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
SupplierListBasedOnProd.Add(prodSupListObj);
}
reader.Close();
}
// general exception
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
//return the list
return(SupplierListBasedOnProd);
}
}
// creating list of instances of the ProdSuppliers class
public static List <Product> GetProducts()
{
// create new list of Products
List <Product> Products = new List <Product>(); // make an empty list
Product prod; // reference to new Product object
//
DBCall.InitSQL();
using (SqlConnection connection = new SqlConnection(DBCall.builder.ConnectionString))
{
// create select command
string selectString = "SELECT ProductId, ProdName FROM Products";
// selects records from data source based on connection and string
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
// open connection
connection.Open();
// run the select command and process the results adding Products to the list
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
// create Product objects to populate list
prod = new Product();
prod.ProductId = Convert.ToInt32(reader["ProductId"]);
prod.ProdName = reader["ProdName"].ToString();
Products.Add(prod);
}
reader.Close();
}
// general exception
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
// return list of Products
return(Products);
}
}
// creating list of instances of the Suppliers class
public static List <Supplier> GetSuppliers()
{
// create new list of Suppliers
List <Supplier> Suppliers = new List <Supplier>(); // make an empty list
Supplier sup; // reference to new Supplier object
DBCall.InitSQL();
using (SqlConnection connection = new SqlConnection(DBCall.builder.ConnectionString))
{
// create select command
string selectString = "SELECT SupplierId, SupName FROM Suppliers";
// selects records from data source based on connection and string
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
// open connection
connection.Open();
// run the select command and process the results adding Suppliers to the list
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
// create Supplier objects to populate list
sup = new Supplier();
sup.SupplierId = Convert.ToInt32(reader["SupplierId"]);
sup.SupName = reader["SupName"].ToString();
Suppliers.Add(sup);
}
reader.Close();
}
// general exception
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
// return list of Suppliers
return(Suppliers);
}
}