public void UpdateRoutes(RouteCl routeCl)//обновляем
{
routeList.Clear();
LoadData();
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
FillDGV();
}
public void AddRoutes(RouteCl routeCl)//добавляем новый
{
routeList.Clear();
routeList.Add(routeCl);
LoadData();
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
FillDGV();
}
void LoadData()//загрузили данные
{
using (var cmd = new NpgsqlCommand($"Select id_route, routes.id_hotel, name_route, hotels.name_hotel, countries.name_country, cities.name_city, " +
$"hotels_categories.name_hotel_category, typies_of_food.name_type_of_food, number_of_nights, price, " +
$"cities.id_city, cities.id_country, hotels.id_hotel_category, hotels.id_type_of_food " +
$"FROM routes, countries, cities, hotels, hotels_categories, typies_of_food WHERE routes.id_hotel = hotels.id_hotel " +
$"AND countries.id_country = cities.id_country " +
$"AND cities.id_city = hotels.id_city " +
$"AND routes.id_hotel = hotels.id_hotel " +
$"AND hotels.id_hotel_category = hotels_categories.id_hotel_category " +
$"AND hotels.id_type_of_food = typies_of_food.id_type_of_food", Connection))
{
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
var routeCl = new RouteCl();
var idR = reader["id_route"].ToString().Trim();
routeCl.idRoute = Int32.Parse(idR);
routeCl.nameRoute = reader["name_route"].ToString().Trim();
var idHot = reader["id_hotel"].ToString().Trim();
routeCl.idHotel = Int32.Parse(idHot);
routeCl.nameHotel = reader["name_hotel"].ToString().Trim();
var idCities = reader["id_city"].ToString().Trim();
routeCl.idCity = Int32.Parse(idCities);
routeCl.nameCity = reader["name_city"].ToString().Trim();
var idCountries = reader["id_country"].ToString().Trim();
routeCl.idCountry = Int32.Parse(idCountries);
routeCl.nameCountry = reader["name_country"].ToString().Trim();
var idCategHot = reader["id_hotel_category"].ToString().Trim();
routeCl.idCategoryHotel = Int32.Parse(idCategHot);
routeCl.nameCategoryHotel = reader["name_hotel_category"].ToString().Trim();
var idTypeF = reader["id_type_of_food"].ToString().Trim();
routeCl.idTypeFooHotel = Int32.Parse(idTypeF);
routeCl.nameTypeFoodHotel = reader["name_type_of_food"].ToString().Trim();
routeList.Add(routeCl);
var numNight = reader["number_of_nights"].ToString().Trim();
routeCl.numNights = Int32.Parse(numNight);
var priceR = reader["price"].ToString().Trim();
routeCl.price = float.Parse(priceR);
}
}
}
private void button_search_Click(object sender, EventArgs e)
{
if (textBox_search.Text.Length == 0)
{
MessageBox.Show("Данные введены некорректно!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
routeList.Clear();
using (var cmdCount = new NpgsqlCommand($"SELECT COUNT(*) FROM routes WHERE name_route LIKE '%" + textBox_search.Text.ToString().Trim() + "%'", Connection))
{
int countRows = Convert.ToInt32(cmdCount.ExecuteScalar());
if (countRows > 0)
{
using (var cmd = new NpgsqlCommand("Select id_route, routes.id_hotel, name_route, hotels.name_hotel, countries.name_country, cities.name_city, " +
$"hotels_categories.name_hotel_category, typies_of_food.name_type_of_food, number_of_nights, price, " +
$"cities.id_city, cities.id_country, hotels.id_hotel_category, hotels.id_type_of_food " +
$"FROM routes, countries, cities, hotels, hotels_categories, typies_of_food WHERE routes.id_hotel = hotels.id_hotel " +
$"AND countries.id_country = cities.id_country " +
$"AND cities.id_city = hotels.id_city " +
$"AND routes.id_hotel = hotels.id_hotel " +
$"AND hotels.id_hotel_category = hotels_categories.id_hotel_category " +
$"AND hotels.id_type_of_food = typies_of_food.id_type_of_food AND name_route LIKE '%" + textBox_search.Text.ToString().Trim() + "%'", Connection))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var routeCl = new RouteCl();
var idR = reader["id_route"].ToString().Trim();
routeCl.idRoute = Int32.Parse(idR);
routeCl.nameRoute = reader["name_route"].ToString().Trim();
var idHot = reader["id_hotel"].ToString().Trim();
routeCl.idHotel = Int32.Parse(idHot);
routeCl.nameHotel = reader["name_hotel"].ToString().Trim();
var idCities = reader["id_city"].ToString().Trim();
routeCl.idCity = Int32.Parse(idCities);
routeCl.nameCity = reader["name_city"].ToString().Trim();
var idCountries = reader["id_country"].ToString().Trim();
routeCl.idCountry = Int32.Parse(idCountries);
routeCl.nameCountry = reader["name_country"].ToString().Trim();
var idCategHot = reader["id_hotel_category"].ToString().Trim();
routeCl.idCategoryHotel = Int32.Parse(idCategHot);
routeCl.nameCategoryHotel = reader["name_hotel_category"].ToString().Trim();
var idTypeF = reader["id_type_of_food"].ToString().Trim();
routeCl.idTypeFooHotel = Int32.Parse(idTypeF);
routeCl.nameTypeFoodHotel = reader["name_type_of_food"].ToString().Trim();
var numNight = reader["number_of_nights"].ToString().Trim();
routeCl.numNights = Int32.Parse(numNight);
var priceR = reader["price"].ToString().Trim();
routeCl.price = float.Parse(priceR);
routeList.Add(routeCl);
}
}
}
FillDGV();
MessageBox.Show($"Совпадения найдены!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show($"Совпадений не найдено!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}