public List <Order> GetCustomerOrders(int customerId)
{
var orders = new List <Order>();
using (var connection = DataAccessObject.CreateConnection())
{
connection.Open();
string commandString = $"SELECT * FROM [Orders] WHERE CustomerId = {customerId}";
var command = DataAccessObject.CreateCommand(commandString, System.Data.CommandType.Text, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
var order = new Order
{
Amount = double.Parse(reader["Amount"].ToString()),
VAT = double.Parse(reader["VAT"].ToString()),
OrderId = int.Parse(reader["OrderId"].ToString())
};
orders.Add(order);
}
DataAccessObject.CloseConnection(connection);
}
return(orders);
}
public static Customer Load(int customerId)
{
Customer customer = new Customer();
using (var connection = DataAccessObject.CreateConnection())
{
connection.Open();
string commandString = $"SELECT* FROM Customer WHERE CustomerId = {customerId}";
var command = DataAccessObject.CreateCommand(commandString, System.Data.CommandType.Text, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
customer.CustomerId = int.Parse(reader["CustomerId"].ToString());
customer.Name = reader["Name"].ToString();
customer.DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
customer.Country = reader["Country"].ToString();
}
DataAccessObject.CloseConnection(connection);
}
return(customer);
}
public static void Save(Customer customer)
{
using (var connection = DataAccessObject.CreateConnection())
{
connection.Open();
string commandString = "INSERT INTO Customer VALUES (@Name, @DateOfBirth, @Country)";
var command = DataAccessObject.CreateCommand(commandString, System.Data.CommandType.Text, connection);
command.Parameters.Add(DataParameterHelper.CreateParameter(string.Empty, "@Name", 150, customer.Name, System.Data.DbType.String));
command.Parameters.Add(DataParameterHelper.CreateParameter(string.Empty, "@DateOfBirth", customer.DateOfBirth, System.Data.DbType.Date));
command.Parameters.Add(DataParameterHelper.CreateParameter(string.Empty, "@Country", 150, customer.Country, System.Data.DbType.String));
command.ExecuteNonQuery();
DataAccessObject.CloseConnection(connection);
}
}
public void Save(Order order, int customerId)
{
using (var connection = DataAccessObject.CreateConnection())
{
connection.Open();
string commandString = "INSERT INTO [Orders] VALUES (@CustomerId, @Amount, @VAT)";
var command = DataAccessObject.CreateCommand(commandString, System.Data.CommandType.Text, connection);
command.Parameters.Add(DataParameterHelper.CreateParameter(string.Empty, "@CustomerId", customerId, System.Data.DbType.Int32));
command.Parameters.Add(DataParameterHelper.CreateParameter(string.Empty, "@Amount", order.Amount, System.Data.DbType.Double));
command.Parameters.Add(DataParameterHelper.CreateParameter(string.Empty, "@VAT", order.VAT, System.Data.DbType.Double));
command.ExecuteNonQuery();
DataAccessObject.CloseConnection(connection);
}
}
public static List <Customer> LoadAllCustomers()
{
List <Customer> customers = new List <Customer>();
using (var connection = DataAccessObject.CreateConnection())
{
connection.Open();
string commandString = $"SELECT * FROM Customer";
var command = DataAccessObject.CreateCommand(commandString, System.Data.CommandType.Text, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
var customer = new Customer
{
CustomerId = int.Parse(reader["CustomerId"].ToString()),
Name = reader["Name"].ToString(),
DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString()),
Country = reader["Country"].ToString()
};
customers.Add(customer);
}
DataAccessObject.CloseConnection(connection);
}
foreach (var customer in customers)
{
LoadCustomerOrders(customer);
}
return(customers);
}