public IClient GetClientById(Guid id)
{
IClient client = null;
using (IDbConnection connection = _DbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand("SELECT * FROM Client WHERE Id = @id");
command.Parameters.Add(command.CreateParameter("@id", id));
using (IDataReader dataReader = command.ExecuteReader(CommandBehavior.SingleRow))
{
if (dataReader.Read())
{
client = MapClient(dataReader);
}
else
{
throw new KeyNotFoundException("Client not found");
}
}
}
}
return(client);
}
public IEnumerable <ICountry> GetCountries()
{
List <ICountry> countryList = new List <ICountry>()
{
};
using (IDbConnection connection = _DbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand("SELECT * FROM Country");
using (IDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
countryList.Add(new Country(dataReader.GetSafeGuid(0), dataReader.GetSafeString(1)));
}
}
}
}
return(countryList);
}
private static void ExecuteQuery(string sql)
{
using (IDbConnection connection = new DBService(new DbConnectionService("Connection")).CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand(sql);
command.ExecuteNonQuery();
}
}
}
private void ExecuteQuery(string sql)
{
using (IDbConnection connection = _dbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand(sql);
command.ExecuteNonQuery();
}
}
}
public bool RemoveClientById(Guid id)
{
using (IDbConnection connection = _DbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand("DELETE FROM Client WHERE Id=@id;");
command.Parameters.Add(command.CreateParameter("@id", id));
return(command.ExecuteNonQuery() > 0);
}
}
}
public void UpdateClientById(IClient client)
{
using (IDbConnection connection = _DbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand("Update Client SET Name=@name, Address=@address, City=@city, ZipCode=@zipCode, CountryId=@countryId WHERE id=@id");
AddParameters(command, client);
if (command.ExecuteNonQuery() == 0)
{
throw new KeyNotFoundException("Client not found");
}
}
}
}
public void AddClient(IClient newClient)
{
using (IDbConnection connection = _DbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand("INSERT INTO Client (Id, Name, Address, City, ZipCode, CountryId) VALUES (@id, @name, @address, @city, @zipCode, @countryId)");
AddParameters(command, newClient);
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new ConstraintException(ex.Message);
}
}
}
}
public IEnumerable <IClient> FilterClientsByName(string clientName)
{
List <IClient> clientList = new List <IClient>();
using (IDbConnection connection = _DbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand("SELECT * FROM Client WHERE lower(Name) LIKE @name;");
command.Parameters.Add(command.CreateParameter("@name", $"%{clientName.ToLower()}%"));
using (IDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
clientList.Add(MapClient(dataReader));
}
}
}
}
return(clientList);
}
public IEnumerable <IClient> GetClients()
{
List <IClient> clientList = new List <IClient>()
{
};
using (IDbConnection connection = _DbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand("SELECT * FROM Client");
using (IDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
clientList.Add(MapClient(dataReader));
}
}
}
}
return(clientList);
}
public IEnumerable <IClient> GetClientsByPaging(int offset, int rowsCount)
{
List <IClient> clientList = new List <IClient>();
using (IDbConnection connection = _DbService.CreateDbConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.AddCommand("SELECT * FROM Client ORDER BY Name OFFSET @offset ROWS FETCH NEXT @rowsCount ROWS ONLY;");
command.Parameters.Add(command.CreateParameter("@offset", offset));
command.Parameters.Add(command.CreateParameter("@rowsCount", rowsCount));
using (IDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
clientList.Add(MapClient(dataReader));
}
}
}
}
return(clientList);
}
