public async Task <IList <Brand> > GetAllAsync()
{
using var connection = new NpgsqlConnection(_dbConnection.ToString());
await connection.OpenAsync().ConfigureAwait(false);
using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM brands;";
var brands = new List <Brand>();
using NpgsqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
brands.Add(new Brand
{
Id = new Guid(reader.GetValue(0).ToString()),
Name = reader.GetValue(1).ToString()
});
}
}
await reader.CloseAsync().ConfigureAwait(false);
await connection.CloseAsync().ConfigureAwait(false);
return(brands);
}
public async Task <Guid> Create(Car car)
{
using var connection = new NpgsqlConnection(_dbConnection.ToString());
await connection.OpenAsync().ConfigureAwait(false);
car.Id = Guid.NewGuid();
string base64 = null;
if (car.PhotoBase64 != null)
{
base64 = car.PhotoBase64[(car.PhotoBase64.IndexOf("base64,") + 7)..];
public async Task BeginInitAsync()
{
if (_firstStart.InitDataBase)
{
using (var connection = new NpgsqlConnection(_dbConnection.ToString()))
{
await connection.OpenAsync().ConfigureAwait(false);
using var command = connection.CreateCommand();
command.CommandText = @"
DROP TABLE IF EXISTS cars;
DROP TABLE IF EXISTS brands;
DROP TABLE IF EXISTS body_types;
CREATE EXTENSION IF NOT EXISTS " + "\"uuid-ossp\"" + @";
CREATE TABLE brands(
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
Name VARCHAR NOT NULL
);
INSERT INTO brands(name) VALUES('Audi');
INSERT INTO brands(name) VALUES('Ford');
INSERT INTO brands(name) VALUES('Jeep');
INSERT INTO brands(name) VALUES('Nissan');
INSERT INTO brands(name) VALUES('Toyota');
CREATE TABLE body_types(
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
Name VARCHAR NOT NULL
);
INSERT INTO body_types(name) VALUES('Седан');
INSERT INTO body_types(name) VALUES('Хэтчбек');
INSERT INTO body_types(name) VALUES('Универсал');
INSERT INTO body_types(name) VALUES('Минивэн');
INSERT INTO body_types(name) VALUES('Внедорожник');
INSERT INTO body_types(name) VALUES('Купе');
CREATE TABLE cars(
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
brand_id UUID,
FOREIGN KEY(brand_id) REFERENCES brands(id),
model VARCHAR(1000) NOT NULL,
creation_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp,
body_type_id UUID,
FOREIGN KEY(body_type_id) REFERENCES body_types(id),
photo BYTEA,
seats_count int NOT NULL CHECK(seats_count >= 1 AND seats_count <= 12),
url VARCHAR(1000)
); ";
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
await connection.CloseAsync().ConfigureAwait(false);
}
await _firstStart.UpdateAppsettingsAsync();
_firstStart.InitDataBase = false;
}
}