public static void AddCustomer(Customer customer)
{
var query = @"INSERT INTO CUSTOMERS
(Email
,AccountCode
,FirstName
,LastName
,FirstAddress
,City
,Country
,ZipCode
,Website
,Active
,Enrrolled
,PhoneNumber
,MobileNumber
,FaxNumber)
VALUES
(@Email
,@AccountCode
,@FirstName
,@LastName
,@FirstAddress
,@City
,@Country
,@ZipCode
,@Website
,@Active
,@Enrrolled
,@PhoneNumber
,@MobileNumber
,@FaxNumber)";
var parameters = new SqlParameter[]
{
new SqlParameter("@Email", customer.Email),
new SqlParameter("@AccountCode", customer.AccountCode),
new SqlParameter("@FirstName", customer.FirstName),
new SqlParameter("@LastName", customer.LastName),
new SqlParameter("@FirstAddress", customer.FirstAddress),
new SqlParameter("@City", customer.City),
new SqlParameter("@Country", customer.Country),
new SqlParameter("@ZipCode", customer.ZipCode),
new SqlParameter("@Website", customer.Website),
new SqlParameter("@Active", (bool)customer.Active),
new SqlParameter("@Enrrolled", (int)customer.Enrrolled),
new SqlParameter("@PhoneNumber", customer.PhoneNumber),
new SqlParameter("@MobileNumber", customer.MobileNumber),
new SqlParameter("@FaxNumber", customer.FaxNumber)
};
try
{
DataAccessHandler.ExecuteNonSelect(query, parameters);
}
catch (Exception e)
{
throw e;
}
}
public static void UpdateOrder(Order order)
{
var query = @"UPDATE ORDERS
SET Code = @Code
,Date = @Date
,Type = @Type
,ItemName = @ItemName
,Status = @Status
,Total = @Total
WHERE OrderId = @OrderId";
var parameters = new SqlParameter[]
{
new SqlParameter("@Code", order.Code),
new SqlParameter("@Date", order.Date),
new SqlParameter("@Type", order.Type),
new SqlParameter("@ItemName", order.ItemName),
new SqlParameter("@Status", order.Status),
new SqlParameter("@Total", order.Total),
new SqlParameter("@OrderId", order.OrderId)
};
try
{
DataAccessHandler.ExecuteNonSelect(query, parameters);
}
catch (Exception e)
{
throw e;
}
}
public static IEnumerable <Customer> GetCustomers()
{
var query = string.Format("SELECT * FROM CUSTOMERS");
try
{
var result = DataAccessHandler.ExecuteSelect(query);
return(GetMappedCustomers(result));
}
catch (Exception e)
{
throw e;
}
}
public static Customer GetDefaultCustomer()
{
var query = "SELECT TOP 1 * FROM CUSTOMERS";
try
{
var result = DataAccessHandler.ExecuteSelect(query);
return(GetMappedCustomer(result.Rows[0]));
}
catch (Exception e)
{
throw e;
}
}
public static IEnumerable <Order> GetOrdersRandomized()
{
var query = "SELECT TOP 5 * FROM ORDERS ORDER BY NEWID()";
try
{
var result = DataAccessHandler.ExecuteSelect(query);
return(GetMappedOrders(result));
}
catch (Exception e)
{
throw e;
}
}
public static IEnumerable <Order> GetOrders(int numberOfOrders = 5)
{
var query = string.Format("SELECT TOP {0} * FROM ORDERS", numberOfOrders);
try
{
var result = DataAccessHandler.ExecuteSelect(query);
return(GetMappedOrders(result));
}
catch (Exception e)
{
throw e;
}
}
public static void DeleteCustomer(int customerId)
{
var query = "DELETE FROM CUSTOMERS WHERE CustomerId = @CustomerId";
var param = new SqlParameter("@CustomerId", customerId);
try
{
DataAccessHandler.ExecuteNonSelect(query, new SqlParameter[] { param });
}
catch (Exception e)
{
throw e;
}
}
public static IEnumerable <Order> GetOrders(int customerId, int numberOfOrders = 5)
{
var query = "SELECT * FROM ORDERS WHERE CustomerId = @customerId";
SqlParameter param = new SqlParameter("@customerId", customerId);
try
{
var result = DataAccessHandler.ExecuteSelect(query, new SqlParameter[] { param });
return(GetMappedOrders(result));
}
catch (Exception e)
{
throw e;
}
}
public static void DeleteOrder(int orderId)
{
var query = "DELETE FROM ORDERS WHERE OrderId = @OrderId";
var param = new SqlParameter("@OrderId", orderId);
try
{
DataAccessHandler.ExecuteNonSelect(query, new SqlParameter[] { param });
}
catch (Exception e)
{
throw e;
}
}
public static void UpdateCustomer(Customer customer)
{
var query = @"UPDATE CUSTOMERS
SET Email = @Email
,AccountCode = @AccountCode
,FirstName = @FirstName
,LastName = @LastName
,FirstAddress = @FirstAddress
,City = @City
,Country = @Country
,ZipCode = @ZipCode
,Website = @Website
,Active = @Active
,Enrrolled = @Enrrolled
,PhoneNumber = @PhoneNumber
,MobileNumber = @MobileNumber
,FaxNumber = @FaxNumber
WHERE CustomerId = @CustomerId";
var parameters = new SqlParameter[]
{
new SqlParameter("@Email", customer.Email),
new SqlParameter("@AccountCode", customer.AccountCode),
new SqlParameter("@FirstName", customer.FirstName),
new SqlParameter("@LastName", customer.LastName),
new SqlParameter("@FirstAddress", customer.FirstAddress),
new SqlParameter("@City", customer.City),
new SqlParameter("@Country", customer.Country),
new SqlParameter("@ZipCode", customer.ZipCode),
new SqlParameter("@Website", customer.Website),
new SqlParameter("@Active", (bool)customer.Active),
new SqlParameter("@Enrrolled", (int)customer.Enrrolled),
new SqlParameter("@PhoneNumber", customer.PhoneNumber),
new SqlParameter("@MobileNumber", customer.MobileNumber),
new SqlParameter("@FaxNumber", customer.FaxNumber),
new SqlParameter("@CustomerId", customer.CustomerId)
};
try
{
DataAccessHandler.ExecuteNonSelect(query, parameters);
}
catch (Exception e)
{
throw e;
}
}
public static void ChangeEnrollmentStatus(Customer customer)
{
var query = "UPDATE CUSTOMERS SET ENRROLLED = @enrollmentStatus where CustomerId = @customerId";
var enrollmentParam = new SqlParameter("@enrollmentStatus", (int)customer.Enrrolled);
var customerIdParam = new SqlParameter("@customerId", customer.CustomerId);
var parameters = new SqlParameter[] { enrollmentParam, customerIdParam };
try
{
DataAccessHandler.ExecuteNonSelect(query, parameters);
}
catch (Exception e)
{
throw e;
}
}
public static Customer GetCustomerByEmailOrName(string emailOrName)
{
var query = "SELECT TOP 1 * FROM CUSTOMERS WHERE FirstName = @emailOrName OR Email = @emailOrName";
var param = new SqlParameter("@emailOrName", emailOrName);
try
{
var result = DataAccessHandler.ExecuteSelect(query, new SqlParameter[] { param });
if (result.Rows.Count > 0 && result.Rows[0] != null)
{
return(GetMappedCustomer(result.Rows[0]));
}
return(null);
}
catch (Exception e)
{
throw e;
}
}
public static Customer GetCustomerById(int customerId)
{
var query = "SELECT TOP 1 * FROM CUSTOMERS WHERE CustomerId = @customerId";
var param = new SqlParameter("@customerId", customerId);
try
{
var result = DataAccessHandler.ExecuteSelect(query, new SqlParameter[] { param });
if (result.Rows.Count > 0 && result.Rows[0] != null)
{
return(GetMappedCustomer(result.Rows[0]));
}
return(null);
}
catch (Exception e)
{
throw e;
}
}
public static void AddOrder(Order order)
{
var query = @"INSERT INTO ORDERS
([Code]
,[Date]
,[Type]
,[ItemName]
,[Status]
,[Total])
VALUES
(@Code
,@Date
,@Type
,@ItemName
,@Status
,@Total)";
var parameters = new SqlParameter[]
{
new SqlParameter("@Code", order.Code),
new SqlParameter("@Date", order.Date),
new SqlParameter("@Type", order.Type),
new SqlParameter("@ItemName", order.ItemName),
new SqlParameter("@Status", order.Status),
new SqlParameter("@Total", order.Total)
};
try
{
DataAccessHandler.ExecuteNonSelect(query, parameters);
}
catch (Exception e)
{
throw e;
}
}
