public List <Employee> GetEmployeesByShopId(int shopId)
{
//---method to get employee from the SQL database by it shopId---
using (var _sqlConnection = _databaseConnectionProvider.GetOpenConnection())
{
var sqlCommand = new SqlCommand(StoredProcedures.GetEmployeesByShopId, _sqlConnection);
sqlCommand.Parameters.Add(new SqlParameter("@shopId", shopId));
sqlCommand.CommandType = CommandType.StoredProcedure;
using (var reader = sqlCommand.ExecuteReader())
{
var employees = new List <Employee>();
while (reader.Read())
{
employees.Add(new Employee
{
Id = Convert.ToInt32(reader["id"].ToString()),
Name = Convert.ToString(reader["name"]),
Surname = Convert.ToString(reader["surname"]),
PositionId = Convert.ToInt32(reader["positionID"].ToString()),
ShopId = Convert.ToInt32(reader["shopID"].ToString())
});
}
return(employees);
}
}
}
public async Task <Product> GetProduct(int id)
{
using var connection = await _databaseConnectionProvider.GetOpenConnection();
return(await connection.QueryFirstOrDefaultAsync <Product>(
"SELECT Id, Name, Description FROM Product WHERE Id=@id", new { id }));
}
public List <Position> GetPositionsAll()
{
//---method to get positions names from the SQL database---
using (var _sqlConnection = _databaseConnectionProvider.GetOpenConnection())
{
var sqlCommand = new SqlCommand(StoredProcedures.GetPositionsAll, _sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
using (var reader = sqlCommand.ExecuteReader())
{
var positions = new List <Position>();
while (reader.Read())
{
positions.Add(new Position
{
Id = Convert.ToInt32(reader["id"].ToString()),
Name = Convert.ToString(reader["position"]),
});
}
return(positions);
}
}
}
public List <Shop> GetShopsAll()
{
//---method to get shops names from the SQL database---
using (var _sqlConnection = _databaseConnectionProvider.GetOpenConnection())
{
var sqlCommand = new SqlCommand(StoredProcedures.GetShopsAll, _sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
using (var reader = sqlCommand.ExecuteReader())
{
var shops = new List <Shop>();
while (reader.Read())
{
shops.Add(new Shop
{
Id = Convert.ToInt32(reader["id"].ToString()),
Name = Convert.ToString(reader["name"]),
City = Convert.ToString(reader["city"]),
});
}
return(shops);
}
}
}