Module2_1.md

Module 2.1

Task 1-3

Task 4

Task1-2-3.cs

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=LAPTOP-MCLUN3BN\\SQLSERVER;Initial Catalog=Store;Integrated Security=True";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();

                string insertQuery = "INSERT INTO Product (name, id_category, price, quantity, id_producer, id_measurement, id_markup) VALUES (@name, @id_category, @price, @quantity, @id_producer, @id_measurement, @id_markup)";
                using (SqlCommand command = new SqlCommand(insertQuery, connection))
                {
                    command.Parameters.AddWithValue("@name", "Соль");
                    command.Parameters.AddWithValue("@id_category", 12);
                    command.Parameters.AddWithValue("@price", 30);
                    command.Parameters.AddWithValue("@quantity", 1000);
                    command.Parameters.AddWithValue("@id_producer", 17);
                    command.Parameters.AddWithValue("@id_measurement", 5);
                    command.Parameters.AddWithValue("@id_markup", 1);
                    command.ExecuteNonQuery();
                }

                string updateQuery = "UPDATE Product SET name = @new_name WHERE id = @id";
                using (SqlCommand command = new SqlCommand(updateQuery, connection))
                {
                    command.Parameters.AddWithValue("@new_name", "Темный шоколад");
                    command.Parameters.AddWithValue("@id", 5);
                    command.ExecuteNonQuery();
                }

                string deleteQuery = "DELETE FROM Product WHERE id = @id";
                using (SqlCommand command = new SqlCommand(deleteQuery, connection))
                {
                    command.Parameters.AddWithValue("@id", 8); // Вода газированная
                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("\nОшибка при подключении к базе данных: " + ex.Message);
            }
            finally
            {
                connection.Close();
                Console.WriteLine("\nПодключение к базе данных 'Store' успешно закрыто.");
            }
        }
    }
}

Task4.cs

using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=LAPTOP-MCLUN3BN\\SQLSERVER;Initial Catalog=Store;Integrated Security=True";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();

                string query1 = "SELECT TOP 1 s.id, s.name, COUNT(p.id) as product_count FROM Supplier s JOIN Product p ON s.id = p.id_producer GROUP BY s.id, s.name ORDER BY product_count DESC";
                using (SqlCommand command = new SqlCommand(query1, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("query 1:");

                        while (reader.Read())
                            Console.WriteLine(
                                $"supplier_id:\t{reader["id"]}," +
                                $"\tname:\t{reader["name"]}," +
                                $"\tproduct_count:\t{reader["product_count"]}"
                            );
                    }
                }

                string query2 = "SELECT TOP 1 s.id, s.name, COUNT(p.id) as product_count FROM Supplier s JOIN Product p ON s.id = p.id_producer GROUP BY s.id, s.name ORDER BY product_count";
                using (SqlCommand command = new SqlCommand(query2, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("\nquery 2:");

                        while (reader.Read())
                            Console.WriteLine(
                                $"supplier_id:\t{reader["id"]}," +
                                $"\tname:\t{reader["name"]}," +
                                $"\tproduct_count:\t{reader["product_count"]}"
                            );
                    }
                }

                string query3 = "SELECT TOP 1 c.id, c.name, SUM(p.quantity) as total_quantity FROM Category c JOIN Product p ON c.id = p.id_category GROUP BY c.id, c.name ORDER BY total_quantity DESC";
                using (SqlCommand command = new SqlCommand(query3, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("\nquery 3:");

                        while (reader.Read())
                            Console.WriteLine(
                                $"category_id:\t{reader["id"]}," +
                                $"\tname:\t{reader["name"]}," +
                                $"\ttotal_quantity:\t{reader["total_quantity"]}"
                            );
                    }
                }

                string query4 = "SELECT TOP 1 c.id, c.name, SUM(p.quantity) as total_quantity FROM Category c JOIN Product p ON c.id = p.id_category GROUP BY c.id, c.name ORDER BY total_quantity";
                using (SqlCommand command = new SqlCommand(query4, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("\nquery 4:");

                        while (reader.Read())
                            Console.WriteLine(
                                $"category_id:\t{reader["id"]}," +
                                $"\tname:\t{reader["name"]}," +
                                $"\ttotal_quantity:\t{reader["total_quantity"]}"
                            );
                    }
                }

                string query5 = "SELECT p.id, p.name FROM Product p JOIN Delivery d ON p.id = d.id_product WHERE DATEDIFF(day, d.date_of_delivery, GETDATE()) > @days";
                using (SqlCommand command = new SqlCommand(query5, connection))
                {
                    command.Parameters.AddWithValue("@days", 30);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("\nquery 5:");

                        while (reader.Read())
                            Console.WriteLine(
                                $"product_id:\t{reader["id"]}," +
                                $"\tname:\t{reader["name"]}"
                            );
                    }
                }

            }
            catch (SqlException ex)
            {
                Console.WriteLine("\nОшибка при подключении к базе данных: " + ex.Message);
            }
            finally
            {
                connection.Close();
                Console.WriteLine("\nПодключение к базе данных 'Store' успешно закрыто.");
            }
        }
    }
}