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); }