//Products
public static void AlterProduct(int ProductID, string Productname, float price, string Colour, string Brand, string FrameType, string Glasstype, int?RightLensID, int?LeftLensID, string Productdescription = "")
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "AlterProduct";
com.Parameters.Add("@ProductID", SqlDbType.Int).Value = ProductID;
com.Parameters.Add("@Productname", SqlDbType.VarChar).Value = Productname;
com.Parameters.Add("@Price", SqlDbType.Real).Value = price;
com.Parameters.Add("@Colour", SqlDbType.VarChar).Value = Colour;
com.Parameters.Add("@Brand", SqlDbType.VarChar).Value = Brand;
com.Parameters.Add("@FrameType", SqlDbType.VarChar).Value = FrameType;
com.Parameters.Add("@Glasstype", SqlDbType.VarChar).Value = Glasstype;
com.Parameters.Add("@RightLensID", SqlDbType.Int).Value = RightLensID;
com.Parameters.Add("@LeftLensID", SqlDbType.Int).Value = LeftLensID;
com.Parameters.Add("@Productdescription", SqlDbType.VarChar).Value = Productdescription;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
}
}
public static DataTable GetProductsSpecificDataTable(string Brand, string Colour, string FrameType, string Glasstype, int Price)
{
var ProductSpeceficTable = new DataTable();
using (SqlConnection conn = new SqlConnection(SQLConnecter.Connect()))
{
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "SelectSpecificProducts";
com.Parameters.Add("@Brand", SqlDbType.VarChar).Value = Brand;
com.Parameters.Add("@Colour", SqlDbType.VarChar).Value = Colour;
com.Parameters.Add("@FrameType", SqlDbType.VarChar).Value = FrameType;
com.Parameters.Add("@Glasstype", SqlDbType.VarChar).Value = Glasstype;
com.Parameters.Add("@Price", SqlDbType.Real).Value = Price;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader reader = com.ExecuteReader();
ProductSpeceficTable.Load(reader);
}
}
return(ProductSpeceficTable);
}
//Customer
public static string AlterCustomer(string forename, string lastname, string adress, int phoneNumber, string email, float strengthRight, float strengthLeft, string notes, DateTime signupDate, int CustomerID)
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "AlterCustomer";
com.Parameters.Add("@CustomerID", SqlDbType.Int).Value = CustomerID;
com.Parameters.Add("@Forename", SqlDbType.VarChar).Value = forename;
com.Parameters.Add("@Lastname", SqlDbType.VarChar).Value = lastname;
com.Parameters.Add("@adress", SqlDbType.VarChar).Value = adress;
com.Parameters.Add("@PhoneNumber", SqlDbType.Int).Value = phoneNumber;
com.Parameters.Add("@Email", SqlDbType.VarChar).Value = email;
com.Parameters.Add("@StrengthRight", SqlDbType.Float).Value = strengthRight;
com.Parameters.Add("@StrengthLeft", SqlDbType.Float).Value = strengthLeft;
com.Parameters.Add("@notes", SqlDbType.VarChar).Value = notes;
com.Parameters.Add("@SignupDate", SqlDbType.Date).Value = signupDate;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
return("Customer Created!");
}
}
//Customer
public static string AddCustomer(string Forename, string Lastname, string adress, int PhoneNumber, string Email, double StrengthRight, double StrengthLeft, DateTime SignupDate, string notes = "")
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "AddCustomer";
com.Parameters.Add("@Forename", SqlDbType.VarChar).Value = Forename;
com.Parameters.Add("@Lastname", SqlDbType.VarChar).Value = Lastname;
com.Parameters.Add("@adress", SqlDbType.VarChar).Value = adress;
com.Parameters.Add("@PhoneNumber", SqlDbType.Int).Value = PhoneNumber;
com.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email;
com.Parameters.Add("@StrengthRight", SqlDbType.Float).Value = StrengthRight;
com.Parameters.Add("@StrengthLeft", SqlDbType.Float).Value = StrengthLeft;
com.Parameters.Add("@notes", SqlDbType.VarChar).Value = notes;
com.Parameters.Add("@SignupDate", SqlDbType.Date).Value = SignupDate;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
return("Customer Created!");
}
}
//Products
public static void DeleteProduct(int ProductID)
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "DeleteProduct";
com.Parameters.Add("@ProductID", SqlDbType.Int).Value = ProductID;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
}
}
public static string DeleteOrder(int OrderID)
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "DeleteOrder";
com.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
return("Order Deleted");
}
}
//Order
public static void AddOrder(int OrderID, int customerID, DateTime Date)
{
SqlConnection conn = new SqlConnection(SQLConnecter.Connect());
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "AddOrder";
com.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderID;
com.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerID;
com.Parameters.Add("@Date", SqlDbType.Date).Value = Date;
com.CommandType = CommandType.StoredProcedure;
conn.Open();
com.ExecuteScalar();
conn.Close();
}
}
//Products
public static DataTable GetProductsDataTable()
{
var ProductTable = new DataTable();
using (SqlConnection conn = new SqlConnection(SQLConnecter.Connect()))
{
using (SqlCommand com = new SqlCommand())
{
com.Connection = conn;
com.CommandText = "SelectAllProducts";
com.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader reader = com.ExecuteReader();
ProductTable.Load(reader);
}
}
return(ProductTable);
}