Module2_2.md

Module 2.2

Task 1

Task 2

Task 3

Task 4

Task1.cs

using System.Data.SqlClient;

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

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

                string query1_1 = "INSERT INTO Products (name, type) VALUES (@name, @type)";
                using (SqlCommand command = new SqlCommand(query1_1, connection))
                {
                    command.Parameters.AddWithValue("@name", "Карандаш");
                    command.Parameters.AddWithValue("@type", "Канцтовары");
                    command.ExecuteNonQuery();
                }

                string query1_2 = "INSERT INTO Managers (name, role) VALUES (@name, @role)";
                using (SqlCommand command = new SqlCommand(query1_2, connection))
                {
                    command.Parameters.AddWithValue("@name", "Дмитрий");
                    command.Parameters.AddWithValue("@role", "Главный менеджер");
                    command.ExecuteNonQuery();
                }

                string query1_3 = "INSERT INTO CustomerCompanies (name) VALUES (@name)";
                using (SqlCommand command = new SqlCommand(query1_3, connection))
                {
                    command.Parameters.AddWithValue("@name", "ООО Ком-пания");
                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("\nОшибка при подключении к базе данных: " + ex.Message);
            }
            finally
            {
                connection.Close();
                Console.WriteLine("\nПодключение к базе данных 'Stationery' успешно закрыто.");
            }
        }
    }
}

Task2.cs

using System.Data.SqlClient;

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

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

                string query2_1 = "UPDATE Products SET type = @type WHERE name = @name";
                using (SqlCommand command = new SqlCommand(query2_1, connection))
                {
                    command.Parameters.AddWithValue("@name", "Карандаш");
                    command.Parameters.AddWithValue("@type", "Письменные принадлежности");
                    command.ExecuteNonQuery();
                }

                string query2_2 = "UPDATE Managers SET role = @role WHERE name = @name";
                using (SqlCommand command = new SqlCommand(query2_2, connection))
                {
                    command.Parameters.AddWithValue("@name", "Иван");
                    command.Parameters.AddWithValue("@role", "Главный менеджер");
                    command.ExecuteNonQuery();
                }

                string query2_3 = "UPDATE CustomerCompanies SET name = @new_name WHERE name = @name";
                using (SqlCommand command = new SqlCommand(query2_3, connection))
                {
                    command.Parameters.AddWithValue("@name", "ООО Ком-пания");
                    command.Parameters.AddWithValue("@new_name", "ООО Нли-ю");
                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("\nОшибка при подключении к базе данных: " + ex.Message);
            }
            finally
            {
                connection.Close();
                Console.WriteLine("\nПодключение к базе данных 'Stationery' успешно закрыто.");
            }
        }
    }
}

Task3.cs

using System.Data.SqlClient;

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

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

                string query3_1 = "INSERT INTO ArchivedProducts SELECT * FROM Products WHERE name = @name "
                    + "DELETE FROM Products WHERE name = @name";
                using (SqlCommand command = new SqlCommand(query3_1, connection))
                {
                    command.Parameters.AddWithValue("@name", "Ручка");
                    command.ExecuteNonQuery();
                }

                string query3_2 = "INSERT INTO ArchivedManagers SELECT * FROM Managers WHERE name = @name "
                    + "DELETE FROM Managers WHERE name = @name";
                using (SqlCommand command = new SqlCommand(query3_2, connection))
                {
                    command.Parameters.AddWithValue("@name", "Дмитрий");
                    command.ExecuteNonQuery();
                }

                string query3_3 = "INSERT INTO ArchivedCustomerCompanies SELECT * FROM CustomerCompanies WHERE name = @name "
                    + "DELETE FROM CustomerCompanies WHERE name = @name";
                using (SqlCommand command = new SqlCommand(query3_3, connection))
                {
                    command.Parameters.AddWithValue("@name", "ООО Нли-ю");
                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("\nОшибка при подключении к базе данных: " + ex.Message);
            }
            finally
            {
                connection.Close();
                Console.WriteLine("\nПодключение к базе данных 'Stationery' успешно закрыто.");
            }
        }
    }
}

Task4.cs

using System.Data.SqlClient;

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

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

                string query4_1 = "SELECT TOP (1) name FROM Managers ORDER BY units_sold DESC";
                using (SqlCommand command = new SqlCommand(query4_1, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            Console.WriteLine(reader["name"]);
                    }
                }

                string query4_2 = "SELECT TOP (1) name FROM Managers ORDER BY total_profit DESC";
                using (SqlCommand command = new SqlCommand(query4_2, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            Console.WriteLine(reader["name"]);
                    }
                }

                string query4_3 = "SELECT TOP (1) name FROM Managers WHERE last_sale_date BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE() ORDER BY total_profit DESC";
                using (SqlCommand command = new SqlCommand(query4_3, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            Console.WriteLine(reader["name"]);
                    }
                }

                string query4_4 = "SELECT TOP (1) name FROM CustomerCompanies ORDER BY total_purchase DESC";
                using (SqlCommand command = new SqlCommand(query4_4, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            Console.WriteLine(reader["name"]);
                    }
                }

                string query4_5 = "SELECT TOP (1) type FROM Products ORDER BY units_sold DESC";
                using (SqlCommand command = new SqlCommand(query4_5, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            Console.WriteLine(reader["type"]);
                    }
                }

                string query4_6 = "SELECT TOP (1) type FROM Products ORDER BY total_profit DESC";
                using (SqlCommand command = new SqlCommand(query4_6, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            Console.WriteLine(reader["type"]);
                    }
                }

                string query4_7 = "SELECT TOP (1) name FROM Products ORDER BY units_sold DESC";
                using (SqlCommand command = new SqlCommand(query4_7, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            Console.WriteLine(reader["name"]);
                    }
                }

                string query4_8 = "SELECT name FROM Products WHERE DATEDIFF(day, last_sold_date, GETDATE()) > @days";
                int days = 30;
                using (SqlCommand command = new SqlCommand(query4_8, connection))
                {
                    command.Parameters.AddWithValue("@days", days);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            Console.WriteLine(reader["name"]);
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("\nОшибка при подключении к базе данных: " + ex.Message);
            }
            finally
            {
                connection.Close();
                Console.WriteLine("\nПодключение к базе данных 'Stationery' успешно закрыто.");
            }
        }
    }
}