87 lines
3.2 KiB
C#
87 lines
3.2 KiB
C#
using BookingAbstractions.Models;
|
|
using BookingAbstractions.WorkAbstractions;
|
|
using Npgsql;
|
|
|
|
namespace BookingPostgresImplementation.WorkImplementation
|
|
{
|
|
public class ClientWork : IClientWork
|
|
{
|
|
public List<Client> GetAll()
|
|
{
|
|
var clients = new List<Client>();
|
|
using var con = SqlConnection.GetConnection();
|
|
con.Open();
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM client order by id", con);
|
|
using var reader = cmd.ExecuteReader();
|
|
while (reader.Read())
|
|
{
|
|
clients.Add(new Client
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
LastName = reader.GetString(2),
|
|
Address = reader.GetString(3),
|
|
Email = reader.GetString(4),
|
|
PhoneNumber = reader.GetString(5),
|
|
});
|
|
}
|
|
return clients;
|
|
}
|
|
|
|
public Client? Get(int id)
|
|
{
|
|
using var con = SqlConnection.GetConnection();
|
|
con.Open();
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM client WHERE id = {id}", con);
|
|
using var reader = cmd.ExecuteReader();
|
|
if (reader.Read())
|
|
{
|
|
return new Client
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
LastName = reader.GetString(2),
|
|
Address = reader.GetString(3),
|
|
Email = reader.GetString(4),
|
|
PhoneNumber = reader.GetString(5),
|
|
};
|
|
}
|
|
return null;
|
|
}
|
|
|
|
public Client? Create(Client client)
|
|
{
|
|
using var con = SqlConnection.GetConnection();
|
|
con.Open();
|
|
using var cmd = new NpgsqlCommand("INSERT INTO client (name, lastname, adress, email, phonenumber) VALUES (@Name, @LastName, @Address, @Email, @PhoneNumber)", con);
|
|
cmd.Parameters.AddWithValue("@Name", client.Name);
|
|
cmd.Parameters.AddWithValue("@LastName", client.LastName);
|
|
cmd.Parameters.AddWithValue("@Address", client.Address);
|
|
cmd.Parameters.AddWithValue("@Email", client.Email);
|
|
cmd.Parameters.AddWithValue("@PhoneNumber", client.PhoneNumber);
|
|
cmd.ExecuteNonQuery();
|
|
return client;
|
|
}
|
|
|
|
public Client? Update(Client client)
|
|
{
|
|
using var con = SqlConnection.GetConnection();
|
|
con.Open();
|
|
using var cmd = new NpgsqlCommand($"UPDATE client SET name = '{client.Name}', lastname = '{client.LastName}', adress = '{client.Address}', email = '{client.Email}', phonenumber = {client.PhoneNumber} WHERE id = {client.Id}", con);
|
|
cmd.ExecuteNonQuery();
|
|
var element = Get(client.Id);
|
|
return element;
|
|
}
|
|
|
|
public Client? Delete(int id)
|
|
{
|
|
var element = Get(id);
|
|
using var con = SqlConnection.GetConnection();
|
|
con.Open();
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM client WHERE id = {id}", con);
|
|
cmd.ExecuteNonQuery();
|
|
return element;
|
|
}
|
|
}
|
|
}
|