public static Boolean UpdateProduct(int id, Product product)
{
if (DatabaseOperation.ReturnItem(product) == null)
{
return(false);
}
SqlConnection connection = RetaloDB.GetConnection();
string updateStatement = "UPDATE Products SET "
+ "Name = @Name"
+ "Description = @Description"
+ "Cost = @Cost"
+ "\"Product Type\" = @ProductType"
+ "WHERE PerID = @ID;";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@Name", product.Name);
updateCommand.Parameters.AddWithValue("@Description", product.Description);
updateCommand.Parameters.AddWithValue("@Cost", product.Cost);
updateCommand.Parameters.AddWithValue("@ProductType", product.ProductType);
updateCommand.Parameters.AddWithValue("@ID", product.ID);
try{
connection.Open();
updateCommand.ExecuteNonQuery();
}
catch (Exception ex) {
throw ex;
}
finally{
connection.Close();
}
return(true);
}
public static Boolean DeleteItem(int id, String database)
{
//Delete Item Object Parser and check if item exists
SqlConnection connection = RetaloDB.GetConnection();
SqlCommand remove;
if (database == "Person")
{
Person person = new Person();
person.ID = id;
if (ReturnItem(person) == null)
{
connection.Close();
return(false);
}
else
{
string removestatement =
"DELETE FROM " + database + " WHERE PerID = @id";
remove = new SqlCommand(removestatement, connection);
remove.Parameters.AddWithValue("@id", id);
return(DeleteItemAction(remove, connection));
}
}
else
{
return(false);
}
}
public static Boolean AddProduct(Product product)
{
if (DatabaseOperation.ReturnItem(product) != null)
{
return(false);
}
SqlConnection connection = RetaloDB.GetConnection();
string addStatement = "INSERT INTO Products (Name, Description, Cost, \"Product Type\") "
+ "Values(@Name, @Description, @Cost, @ProductType); ";
SqlCommand addCommand = new SqlCommand(addStatement, connection);
addCommand.Parameters.AddWithValue("@Name", product.Name);
addCommand.Parameters.AddWithValue("@Description", product.Description);
addCommand.Parameters.AddWithValue("@Cost", product.Cost);
addCommand.Parameters.AddWithValue("@ProductType", product.ProductType);
try{
connection.Open();
addCommand.ExecuteNonQuery();
}
catch (Exception ex) {
throw ex;
}
finally{
connection.Close();
}
return(true);
}
public static Boolean UpdatePerson(int id, Person person)
{
if (DatabaseOperation.ReturnItem(person) == null)
{
return(false);
}
SqlConnection connection = RetaloDB.GetConnection();
string updateStatement = "UPDATE Person SET " +
"FName = @FName, " +
"LName = @LName, " +
"\"Phone Number\" = @Phone_Number, " +
"Email = @Email, " +
"\"Reward Points\" = @Reward_Points, " +
"password = @Password, " +
"Is_Teacher = @IsTeacher, " +
"Is_Admin = @IsAdmin, " +
"Is_Senior = @IsSenior, " +
"Is_Veteran = @IsVeteran " +
"WHERE PerID = @ID;";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@FName", person.FName);
updateCommand.Parameters.AddWithValue("@LName", person.LName);
updateCommand.Parameters.AddWithValue("@Phone_Number", person.Phone_Number);
updateCommand.Parameters.AddWithValue("@Email", person.Email);
updateCommand.Parameters.AddWithValue("@Reward_Points", person.Reward_Points);
updateCommand.Parameters.AddWithValue("@Password", person.Password);
updateCommand.Parameters.AddWithValue("@IsTeacher", person.IsTeacher);
updateCommand.Parameters.AddWithValue("@IsAdmin", person.IsAdmin);
updateCommand.Parameters.AddWithValue("@IsSenior", person.IsSenior);
updateCommand.Parameters.AddWithValue("@IsVeteran", person.IsVeteran);
updateCommand.Parameters.AddWithValue("@ID", person.ID);
try
{
connection.Open();
updateCommand.ExecuteNonQuery();
}catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(true);
}
public static Invoice ReturnItem(Invoice invoice)
{
try {
SqlConnection connect = RetaloDB.GetConnection();
string selectStatement =
"Select *"
+ "FROM Invoice"
+ " Where InvID = @id";
SqlCommand selectCommand = new SqlCommand(selectStatement, connect);
selectCommand.Parameters.AddWithValue("@id", invoice.ID);
Invoice invoice2 = InvoiceOperations.ReturnInvoice(selectCommand, connect);
return(invoice2);
}
catch (Exception ex) {
throw ex;
}
}
public static Product ReturnItem(Product product)
{
try {
SqlConnection connect = RetaloDB.GetConnection();
string selectStatement =
"Select *"
+ "FROM Products"
+ " Where ProdID = @id";
SqlCommand selectCommand = new SqlCommand(selectStatement, connect);
selectCommand.Parameters.AddWithValue("@id", product.ID);
Product product2 = ProductOperations.ReturnProduct(selectCommand, connect);
return(product2);
}
catch (Exception ex) {
throw ex;
}
}
public static Person ReturnItem(String email)
{
try {
SqlConnection connect = RetaloDB.GetConnection();
string selectStatement =
"Select *"
+ " FROM Person"
+ " Where Email = @email";
SqlCommand selectCommand = new SqlCommand(selectStatement, connect);
selectCommand.Parameters.AddWithValue("@email", email);
Person person = PersonOperations.ReturnPerson(selectCommand, connect);
return(person);
} catch (Exception ex) {
throw ex;
}
}
public static Boolean AddInvoice(Invoice invoice)
{
if (DatabaseOperation.ReturnItem(invoice) != null)
{
return(false);
}
else
{
SqlConnection connect = RetaloDB.GetConnection();
string invoiceadd = "INSERT INTO Invoice (PerID, \"Date Of Invoice\", \"Amount Of Product\", \"Is Paid For\", \"Total Cost\") " +
"Values(@PerID, @Date, @Amount, @Paid, @Cost);";
SqlCommand invoiceaddcommand = new SqlCommand(invoiceadd, connect);
invoiceaddcommand.Parameters.AddWithValue("@PerID", invoice.PerID);
invoiceaddcommand.Parameters.AddWithValue("@Date", invoice.DateOfInvoice);
invoiceaddcommand.Parameters.AddWithValue("@Amount", invoice.TotalProductInInvoiceQuantity);
invoiceaddcommand.Parameters.AddWithValue("@Paid", false);
invoiceaddcommand.Parameters.AddWithValue("@Cost", invoice.TotalCost);
try
{
connect.Open();
invoiceaddcommand.ExecuteNonQuery();
connect.Close();
AddInvoiceDetails(invoice, connect);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connect.Close();
}
return(true);
}
}
public static Boolean AddPerson(Person person)
{
if (DatabaseOperation.ReturnItem(person) != null)
{
return(false);
}
SqlConnection connection = RetaloDB.GetConnection();
string addStatement = "INSERT INTO Person (Fname, Lname, \"Phone Number\", Email, \"Reward Points\", password, Is_Teacher, Is_Admin, Is_Senior, Is_Veteran) "
+ "Values(@FName, @LName, @Phone_Number, @Email, "
+ "@Reward_Points, @Password, @IsTeacher, @IsAdmin, @IsSenior, @IsVeteran); ";
SqlCommand addCommand = new SqlCommand(addStatement, connection);
addCommand.Parameters.AddWithValue("@FName", person.FName);
addCommand.Parameters.AddWithValue("@LName", person.LName);
addCommand.Parameters.AddWithValue("@Phone_Number", person.Phone_Number);
addCommand.Parameters.AddWithValue("@Email", person.Email);
addCommand.Parameters.AddWithValue("@Reward_Points", person.Reward_Points);
addCommand.Parameters.AddWithValue("@Password", person.Password);
addCommand.Parameters.AddWithValue("@IsTeacher", person.IsTeacher);
addCommand.Parameters.AddWithValue("@IsAdmin", person.IsAdmin);
addCommand.Parameters.AddWithValue("@IsSenior", person.IsSenior);
addCommand.Parameters.AddWithValue("@IsVeteran", person.IsVeteran);
try{
connection.Open();
addCommand.ExecuteNonQuery();
}
catch (Exception ex) {
throw ex;
}
finally{
connection.Close();
}
return(true);
}
public static Person ReturnItem(Person person)
{
if (person.ID.ToString() == "")
{
person = null;
return(person);
}
try {
SqlConnection connect = RetaloDB.GetConnection();
string selectStatement =
"Select *"
+ "FROM Person"
+ " Where PerID = @id";
SqlCommand selectCommand = new SqlCommand(selectStatement, connect);
selectCommand.Parameters.AddWithValue("@id", person.ID);
Person person2 = PersonOperations.ReturnPerson(selectCommand, connect);
return(person2);
}
catch (Exception ex) {
throw ex;
}
}
