Lab6 Task2 For Dudko

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Lab62
{
    public partial class Form2 : Form
    {
        public string ConnectionString { get; set; } = "Data Source=DESKTOP-83FUTMU\\SQLEXPRESS;Initial Catalog=Автосервис;Persist Security Info=True;User ID=sa;Password=2007;Pooling=False";

        public class Car
        {
            /// <summary>Рег знак</summary>
            public string PlateNumber { get; set; }
            /// <summary>Марка</summary>
            public string Mark { get; set; }
            /// <summary>Цвет</summary>
            public string Color { get; set; }
            /// <summary>Год выпуска</summary>
            public string YearOfRelease { get; set; }
        }

        public Form2()
        {
            InitializeComponent();
        }

        /// <summary>Нажата кнопка "Обновить"</summary>
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM Автомобили";
            dataGridView1.Rows.Clear();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
                dataGridView1.Rows.Add(rdr["РегЗнак"].ToString(),
                                    rdr["Марка"].ToString(),
                                    rdr["Цвет"].ToString(),
                                    rdr["ГодВыпуска"].ToString());
            rdr.Close();
            con.Close();

        }

        /// <summary>Нажата кнопка "Сохранить"</summary>
        private void button2_Click(object sender, EventArgs e)
        {
            var carOrms = GetCarsFromDb();
            var carsFromGrid = GetCarsFromGrid();

            DeleteAllCars();
            InsertCars(carsFromGrid);
        }

        public IList<Car> GetCarsFromDb()
        {
            var cars = new List<Car>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand getCarsCommand = connection.CreateCommand();
                getCarsCommand.CommandText = "SELECT * FROM Автомобили";
                SqlDataReader reader = getCarsCommand.ExecuteReader();
                while (reader.Read())
                {
                    var carOrm = new Car
                    {
                        PlateNumber = reader["РегЗнак"].ToString(),
                        Mark = reader["Марка"].ToString(),
                        Color = reader["Цвет"].ToString(),
                        YearOfRelease = reader["ГодВыпуска"].ToString()
                    };

                    cars.Add(carOrm);
                }
            }

            return cars;
        }

        public IList<Car> GetCarsFromGrid()
        {
            var cars = new List<Car>();
            foreach (DataGridViewRow row in this.dataGridView1.Rows)
            {
                var car = new Car
                {
                    PlateNumber = row.Cells[0].Value?.ToString(),
                    Mark = row.Cells[1].Value?.ToString(),
                    Color = row.Cells[2].Value?.ToString(),
                    YearOfRelease = row.Cells[3].Value?.ToString()
                };

                if (car.PlateNumber != null)
                    cars.Add(car);
            }

            return cars;
        }

        public void DeleteAllCars()
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand insertCarsCommand = connection.CreateCommand();
                insertCarsCommand.CommandText = "DELETE * FROM Автомобили";
                insertCarsCommand.ExecuteNonQuery();
            }
        }

        public void InsertCars(IEnumerable<Car> cars)
        {
            if (cars == null || !cars.Any())
                return;

            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand insertCarsCommand = connection.CreateCommand();
                insertCarsCommand.CommandText = GetTextOfInsertCarsCommand(cars);
                insertCarsCommand.ExecuteNonQuery();
            }
        }

        public string GetTextOfInsertCarsCommand(IEnumerable<Car> cars)
        {
            var commandText = new StringBuilder("INSERT INTO Автомобили VALUES ");

            foreach (var car in cars)
                commandText
                    .Append("(")
                    .Append(StringForInsert(car.PlateNumber))
                    .Append(", ")
                    .Append(StringForInsert(car.Mark))
                    .Append(", ")
                    .Append(StringForInsert(car.Color))
                    .Append(", ")
                    .Append(IntForInsert(car.YearOfRelease))
                    .Append("),");
            commandText.Length--; // Удаляем последнюю запятую

            return commandText.ToString();
        }

        public string StringForInsert(string str)
        {
            if (string.IsNullOrEmpty(str))
                return "NULL";

            return "'" + str.Trim() + "'";
        }

        public string IntForInsert(string num)
        {
            if (string.IsNullOrEmpty(num))
                return "NULL";

            return num.Trim();
        }
    }
}