476 lines
19 KiB
C#
476 lines
19 KiB
C#
using Npgsql;
|
|
using System.Data;
|
|
|
|
namespace database
|
|
{
|
|
public class Implementation : Abstractions
|
|
{
|
|
private NpgsqlConnection GetConnection()
|
|
{
|
|
return new NpgsqlConnection("Host=localhost;Username=postgres;Password=1234;Database=Computerfirmbd;");
|
|
}
|
|
|
|
// CDUD операции для каждой сущности
|
|
|
|
// Device
|
|
public override void AddDevice(Device device)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO device (typedevice, model, serialnumber, clientid, orderid) VALUES (@TypeDevice, @Model, @SerialNumber, @ClientId, @OrderId)", conn);
|
|
cmd.Parameters.AddWithValue("@TypeDevice", device.TypeDevice);
|
|
cmd.Parameters.AddWithValue("@Model", device.Model);
|
|
cmd.Parameters.AddWithValue("@SerialNumber", device.SerialNumber);
|
|
cmd.Parameters.AddWithValue("@ClientId", device.ClientId);
|
|
cmd.Parameters.AddWithValue("@OrderId", device.OrderId);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override List<Device> GetDevices()
|
|
{
|
|
var devices = new List<Device>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM device", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
devices.Add(new Device
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
TypeDevice = reader.GetString(1),
|
|
Model = reader.GetString(2),
|
|
SerialNumber = reader.GetString(3),
|
|
ClientId = reader.GetInt32(4),
|
|
OrderId = reader.GetInt32(5),
|
|
});
|
|
}
|
|
return devices;
|
|
}
|
|
|
|
public override Device GetDeviceById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM device WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Device
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
TypeDevice = reader.GetString(1),
|
|
Model = reader.GetString(2),
|
|
SerialNumber = reader.GetString(3),
|
|
ClientId = reader.GetInt32(4),
|
|
OrderId = reader.GetInt32(5),
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override void UpdateDevice(Device device)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("UPDATE device SET typedevice = @TypeDevice, model = @Model, serialnumber = @SerialNumber, clientid = @ClientId, orderid = @OrderId WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@TypeDevice", device.TypeDevice);
|
|
cmd.Parameters.AddWithValue("@Model", device.Model);
|
|
cmd.Parameters.AddWithValue("@SerialNumber", device.SerialNumber);
|
|
cmd.Parameters.AddWithValue("@ClientId", device.ClientId);
|
|
cmd.Parameters.AddWithValue("@OrderId", device.OrderId);
|
|
cmd.Parameters.AddWithValue("@Id", device.Id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteDevice(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM device WHERE id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
//Employee
|
|
|
|
public override void AddEmployee(Employee employee)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO employee (name, surname, position, timework, seniority, costperhour) VALUES (@Name, @Surname, @Position, @Timework, @Seniority, @CostPerHour)");
|
|
cmd.Parameters.AddWithValue("@Name", employee.Name);
|
|
cmd.Parameters.AddWithValue("@Surname", employee.Surname);
|
|
cmd.Parameters.AddWithValue("@Position", employee.Position);
|
|
cmd.Parameters.AddWithValue("@Timework", employee.Timework);
|
|
cmd.Parameters.AddWithValue("@Seniority", employee.Seniority);
|
|
cmd.Parameters.AddWithValue("@CostPerHour", employee.CostPerHour);
|
|
cmd.Connection = conn;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override List<Employee> GetEmployees()
|
|
{
|
|
var employee = new List<Employee>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM employee", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
employee.Add(new Employee()
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
Surname = reader.GetString(2),
|
|
Position = reader.GetString(3),
|
|
Timework = reader.GetString(4),
|
|
Seniority = reader.GetString(5),
|
|
CostPerHour = reader.GetDecimal(6)
|
|
});
|
|
}
|
|
return employee;
|
|
}
|
|
|
|
public override Employee GetEmployeeById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM employee WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Employee
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
Surname = reader.GetString(2),
|
|
Position = reader.GetString(3),
|
|
Timework = reader.GetString(4),
|
|
Seniority = reader.GetString(5),
|
|
CostPerHour = reader.GetDecimal(6)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override void UpdateEmployee(Employee employee)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open(); // добавил открытие соединения
|
|
using var cmd = new NpgsqlCommand("UPDATE employee SET name = @Name, surname = @Surname, position = @Position, timework = @Timework, seniority = @Seniority, costperhour = @CostPerHour WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Name", employee.Name);
|
|
cmd.Parameters.AddWithValue("@Surname", employee.Surname);
|
|
cmd.Parameters.AddWithValue("@Position", employee.Position);
|
|
cmd.Parameters.AddWithValue("@Timework", employee.Timework);
|
|
cmd.Parameters.AddWithValue("@Seniority", employee.Seniority);
|
|
cmd.Parameters.AddWithValue("@CostPerHour", employee.CostPerHour);
|
|
cmd.Parameters.AddWithValue("@Id", employee.Id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteEmployee(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM employee WHERE id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
// Client
|
|
public override void AddClient(Client client)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO client (name, surname, email, phone) VALUES (@Name, @Surname, @Email, @Phone)", conn);
|
|
cmd.Parameters.AddWithValue("@Name", client.Name);
|
|
cmd.Parameters.AddWithValue("@Surname", client.Surname);
|
|
cmd.Parameters.AddWithValue("@Email", client.Email);
|
|
cmd.Parameters.AddWithValue("@Phone", client.Phone);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override List<Client> GetClients()
|
|
{
|
|
var client = new List<Client>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM client", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
client.Add(new Client
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
Surname = reader.GetString(2),
|
|
Email = reader.GetString(3),
|
|
Phone = reader.GetString(4),
|
|
});
|
|
}
|
|
return client;
|
|
}
|
|
|
|
public override Client GetClientById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM client WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Client
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
Surname = reader.GetString(2),
|
|
Email = reader.GetString(3),
|
|
Phone = reader.GetString(4)
|
|
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override void UpdateClient(Client client)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"UPDATE client SET name = '{client.Name}', surname = '{client.Surname}', email = '{client.Email}', phone = '{client.Phone}' WHERE id = {client.Id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteClient(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM client WHERE id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
// Order
|
|
public override void AddOrder(Order order)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO orders (datestart, dateend, endcost, clientid, componentid, serviceid) VALUES (@DateStart, @DateEnd, @EndCost, @ClientId, @ComponentId, @ServiceId)", conn);
|
|
cmd.Parameters.AddWithValue("@DateStart", order.DateStart);
|
|
cmd.Parameters.AddWithValue("@DateEnd", order.DateEnd);
|
|
cmd.Parameters.AddWithValue("@EndCost", order.EndCost);
|
|
cmd.Parameters.AddWithValue("@ClientId", order.ClientId);
|
|
cmd.Parameters.AddWithValue("@ComponentId", order.ComponentId);
|
|
cmd.Parameters.AddWithValue("@ServiceId", order.ServiceId);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override List<Order> GetOrders()
|
|
{
|
|
var orders = new List<Order>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM orders", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
orders.Add(new Order
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
DateStart = reader.GetString(1),
|
|
DateEnd = reader.GetString(2),
|
|
EndCost = reader.GetDecimal(3),
|
|
ClientId = reader.GetInt32(4),
|
|
ComponentId = reader.GetInt32(5),
|
|
ServiceId = reader.GetInt32(6),
|
|
});
|
|
}
|
|
return orders;
|
|
}
|
|
|
|
public override Order GetOrderById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM orders WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Order
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
DateStart = reader.GetString(1),
|
|
DateEnd = reader.GetString(2),
|
|
EndCost = reader.GetDecimal(3),
|
|
ClientId = reader.GetInt32(4),
|
|
ComponentId = reader.GetInt32(5),
|
|
ServiceId = reader.GetInt32(6),
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override void UpdateOrder(Order order)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("UPDATE orders SET datestart = @DateStart, dateend = @DateEnd, endcost = @EndCost, clientid = @ClientId, componentid = @ComponentId, serviceid = @ServiceId WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@DateStart", order.DateStart);
|
|
cmd.Parameters.AddWithValue("@DateEnd", order.DateEnd);
|
|
cmd.Parameters.AddWithValue("@EndCost", order.EndCost);
|
|
cmd.Parameters.AddWithValue("@ClientId", order.ClientId);
|
|
cmd.Parameters.AddWithValue("@ComponentId", order.ComponentId);
|
|
cmd.Parameters.AddWithValue("@ServiceId", order.ServiceId);
|
|
cmd.Parameters.AddWithValue("@Id", order.Id);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteOrder(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM orders WHERE id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
// Service
|
|
public override void AddService(Service service)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO service (title, cost, employeesid) VALUES (@Title, @Cost, @EmployeesId)", conn);
|
|
cmd.Parameters.AddWithValue("@Title", service.Title);
|
|
cmd.Parameters.AddWithValue("@Cost", service.Cost);
|
|
cmd.Parameters.AddWithValue("@EmployeesId", service.EmployeesId);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override List<Service> GetServices()
|
|
{
|
|
var services = new List<Service>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM service", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
services.Add(new Service
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(1),
|
|
Cost = reader.GetDecimal(2),
|
|
EmployeesId = reader.GetInt32(3),
|
|
});
|
|
}
|
|
return services;
|
|
}
|
|
|
|
public override Service GetServiceById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM service WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Service
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(1),
|
|
Cost = reader.GetDecimal(2),
|
|
EmployeesId = reader.GetInt32(3),
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
public override void UpdateService(Service service)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("UPDATE service SET title = @Title, cost = @Cost, employeesid = @EmployeesId WHERE id = @Id", conn);
|
|
cmd.Parameters.AddWithValue("@Title", service.Title);
|
|
cmd.Parameters.AddWithValue("@Cost", service.Cost);
|
|
cmd.Parameters.AddWithValue("@EmployeesId", service.EmployeesId);
|
|
cmd.Parameters.AddWithValue("@Id", service.Id);
|
|
cmd.ExecuteNonQuery();
|
|
|
|
}
|
|
|
|
|
|
public override void DeleteService(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM service WHERE id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
// Component
|
|
public override void AddComponent(Component component)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO component (title, countsklad, manufacturer, price) VALUES (@Title, @CountSklad, @Manufacturer, @Price)", conn);
|
|
cmd.Parameters.AddWithValue("@Title", component.Title);
|
|
cmd.Parameters.AddWithValue("@CountSklad", component.CountSklad);
|
|
cmd.Parameters.AddWithValue("@Manufacturer", component.Manufacturer);
|
|
cmd.Parameters.AddWithValue("@Price", component.Price);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override List<Component> GetComponents()
|
|
{
|
|
var components = new List<Component>();
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM component", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
components.Add(new Component
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(1),
|
|
CountSklad = reader.GetString(2),
|
|
Manufacturer = reader.GetString(3),
|
|
Price = reader.GetDouble(4)
|
|
});
|
|
}
|
|
return components;
|
|
}
|
|
|
|
public override Component GetComponentById(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM component WHERE id = {id}", conn);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Component
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Title = reader.GetString(1),
|
|
CountSklad = reader.GetString(2),
|
|
Manufacturer = reader.GetString(3),
|
|
Price = reader.GetDouble(4)
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public override void UpdateComponent(Component component)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"UPDATE component SET title = '{component.Title}', countsklad = '{component.CountSklad}', manufacturer = '{component.Manufacturer}', price = '{component.Price}' WHERE id = {component.Id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
public override void DeleteComponent(int id)
|
|
{
|
|
using var conn = GetConnection();
|
|
conn.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM component WHERE id = {id}", conn);
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|