Diplom/AddEditClient.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace ARM_spec_otdelenia
{
    public partial class AddEditClient : Form
    {
        SqlConnection connection;
        SqlCommand command;
        SqlDataAdapter adapter;
        string conn = "Server=DESKTOP-2UUHBK8\\SQLEXPRESS; Database=Спец_отделение; Trusted_connection=true;";
        public AddEditClient()
        {
            InitializeComponent();
            connection = new SqlConnection("Server=DESKTOP-2UUHBK8\\SQLEXPRESS; Database=Спец_отделение; Trusted_connection=true;");
            command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            adapter = new SqlDataAdapter(command);
        }
        private void AddEditClient_Load(object sender, EventArgs e)
        {
            DataTable clientTable = new DataTable();
            using (SqlConnection coon = new SqlConnection(conn))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = coon;
                cmd.CommandText = "SELECT * FROM Личные_данные";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(clientTable);
                comboBox1.DataSource = clientTable;
                comboBox1.DisplayMember = "Фамилия";
                comboBox1.ValueMember = "ID";
                comboBox2.DataSource = clientTable;
                comboBox2.DisplayMember = "Имя";
                comboBox2.ValueMember = "ID";
                comboBox3.DataSource = clientTable;
                comboBox3.DisplayMember = "Отчество";
                comboBox3.ValueMember = "ID";
            }
            DataTable contactTable = new DataTable();
            using (SqlConnection coon = new SqlConnection(conn))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = coon;
                cmd.CommandText = "SELECT * FROM Контакты";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(contactTable);
                comboBox4.DataSource = contactTable;
                comboBox4.DisplayMember = "Телефон";
                comboBox4.ValueMember = "ID";
            }
            DataTable uslugiTable = new DataTable();
            using (SqlConnection coon = new SqlConnection(conn))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = coon;
                cmd.CommandText = "SELECT * FROM Услуги";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(uslugiTable);
                comboBox5.DataSource = uslugiTable;
                comboBox5.DisplayMember = "Наименование_услуги";
                comboBox5.ValueMember = "ID";
            }
            DataTable zdorovTable = new DataTable();
            using (SqlConnection coon = new SqlConnection(conn))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = coon;
                cmd.CommandText = "SELECT * FROM Здоровье";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(zdorovTable);
                comboBox6.DataSource =zdorovTable;
                comboBox6.DisplayMember = "Трудоспособность";
                comboBox6.ValueMember = "ID";
                comboBox7.DataSource = zdorovTable;
                comboBox7.DisplayMember = "Заболевание";
                comboBox7.ValueMember = "ID";
            }
            DataTable zapTable = new DataTable();
            using (SqlConnection coon = new SqlConnection(conn))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = coon;
                cmd.CommandText = "SELECT * FROM На_дом";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(zapTable);
                comboBox8.DataSource = zapTable;
                comboBox8.DisplayMember = "Время_записи";
                comboBox8.ValueMember = "ID";
                comboBox9.DataSource = zapTable;
                comboBox9.DisplayMember = "ФИО_соц_работника";
                comboBox9.ValueMember = "ID";
            }
            DataTable socpolTable = new DataTable();
            using (SqlConnection coon = new SqlConnection(conn))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = coon;
                cmd.CommandText = "SELECT * FROM Социальное_положение";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(socpolTable);
                comboBox10.DataSource = socpolTable;
                comboBox10.DisplayMember = "Место_работы";
                comboBox10.ValueMember = "ID";
            }
            if (ClientClass.add == true)
            {
                comboBox1.SelectedIndex = -1;
                comboBox2.SelectedIndex = -1;
                comboBox3.SelectedIndex = -1;
                comboBox4.SelectedIndex = -1;
                comboBox5.SelectedIndex = -1;
                comboBox6.SelectedIndex = -1;
                comboBox7.SelectedIndex = -1;
                comboBox8.SelectedIndex = -1;
                comboBox9.SelectedIndex = -1;
                comboBox10.SelectedIndex = -1;
            }
            if (ClientClass.change == true)
            {
                comboBox1.SelectedValue = ClientClass.Фамилия;
                comboBox2.SelectedValue = ClientClass.Имя;
                comboBox3.SelectedValue = ClientClass.Отчество;
                comboBox4.SelectedValue = ClientClass.Телефон;
                comboBox5.SelectedValue = ClientClass.Наименование_услуги;
                comboBox6.SelectedValue = ClientClass.Трудоспособность;
                comboBox7.SelectedValue = ClientClass.Заболевание;
                comboBox8.SelectedValue = ClientClass.Время_записи;
                comboBox9.SelectedValue = ClientClass.Соц_работник;
                comboBox10.SelectedValue = ClientClass.Место_работы;
            }
        }
        private void saveBtn_MouseMove(object sender, MouseEventArgs e)
        {
            saveBtn.ForeColor = Color.FromArgb(24, 186, 96);
            saveBtn.FlatAppearance.BorderColor = Color.FromArgb(24, 186, 96);
        }
        private void backBtn_MouseMove(object sender, MouseEventArgs e)
        {
            backBtn.ForeColor = Color.FromArgb(24, 186, 96);
            backBtn.FlatAppearance.BorderColor = Color.FromArgb(24, 186, 96);
        }
        private void backBtn_MouseLeave(object sender, EventArgs e)
        {
            backBtn.ForeColor = Color.Black;
            backBtn.FlatAppearance.BorderColor = Color.Black;
        }
        private void saveBtn_MouseLeave(object sender, EventArgs e)
        {
            saveBtn.ForeColor = Color.Black;
            saveBtn.FlatAppearance.BorderColor = Color.Black;
        }
        private void saveBtn_Click(object sender, EventArgs e)
        {
            if (ClientClass.add == true)
            {
                if (comboBox1.SelectedIndex == -1 || comboBox2.SelectedIndex == -1 || comboBox3.SelectedIndex == -1 || comboBox4.SelectedIndex == -1 || comboBox5.SelectedIndex == -1 || comboBox6.SelectedIndex == -1 || comboBox7.SelectedIndex == -1 || comboBox8.SelectedIndex == -1 || comboBox9.SelectedIndex == -1 || comboBox10.SelectedIndex == -1)
                {
                    MessageBox.Show("Не все элементы выбраны!");
                    return;
                }
                else
                {
                    if (MessageBox.Show("Вы действительно хотите добавить запись?", "Внимание", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        try
                        {
                            connection.Open();
                            command.CommandText = "INSERT INTO Клиенты(Контакты, Личные_данные, Услуги, Соц_положение, Здоровье, На_дом) VALUES(@kont,@lich,@usl,@socpol,@zdor,@home)";
                            command.Parameters.AddWithValue("@kont", comboBox4.SelectedValue);
                            command.Parameters.AddWithValue("@lich", comboBox1.SelectedValue);
                            command.Parameters.AddWithValue("@usl", comboBox5.SelectedValue);
                            command.Parameters.AddWithValue("@socpol", comboBox10.SelectedValue);
                            command.Parameters.AddWithValue("@zdor", comboBox6.SelectedValue);
                            command.Parameters.AddWithValue("@home", comboBox9.SelectedValue);
                            command.ExecuteReader();
                            command.Parameters.Clear();
                            connection.Close();
                            this.Close();
                            MessageBox.Show("Запись успешно добавлена!");
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                            command.Parameters.Clear();
                            connection.Close();
                        }
                    }
                    else
                    {

                    }
                }
            }
            if (ClientClass.change == true)
            {
                if (comboBox1.SelectedIndex == -1 || comboBox2.SelectedIndex == -1 || comboBox3.SelectedIndex == -1 || comboBox4.SelectedIndex == -1 || comboBox5.SelectedIndex == -1 || comboBox6.SelectedIndex == -1 || comboBox7.SelectedIndex == -1 || comboBox8.SelectedIndex == -1 || comboBox9.SelectedIndex == -1 || comboBox10.SelectedIndex == -1)
                {
                    MessageBox.Show("Не все элементы выбраны!");
                }
                else
                {
                    if (MessageBox.Show("Вы действительно хотите изменить запись?", "Внимание", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        try
                        {
                            connection.Open();
                            command.CommandText = "UPDATE Клиенты SET Контакты=@kont, Личные_данные=@lich, Услуги=@usl, Соц_положение=@socpol, Здоровье=@zdor, На_дом=@home WHERE ID=@id";
                            command.Parameters.AddWithValue("@kont", comboBox4.SelectedValue);
                            command.Parameters.AddWithValue("@lich", comboBox1.SelectedValue);
                            command.Parameters.AddWithValue("@usl", comboBox5.SelectedValue);
                            command.Parameters.AddWithValue("@socpol", comboBox10.SelectedValue);
                            command.Parameters.AddWithValue("@zdor", comboBox6.SelectedValue);
                            command.Parameters.AddWithValue("@home", comboBox9.SelectedValue);
                            command.Parameters.AddWithValue("@id", ClientClass.id);
                            command.ExecuteReader();
                            command.Parameters.Clear();
                            connection.Close();
                            this.Close();
                            MessageBox.Show("Запись успешно изменена!");
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                            command.Parameters.Clear();
                            connection.Close();
                        }
                    }
                    else
                    {

                    }
                }
            }
        }
    }
}