up/Учебная практика 47 гр. 2 сем. - 5/СУБД_Кадры/SUBD_Kadry/fMain.cs

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

namespace СУБД_Кадры
{
    public partial class fMain : Form
    {
        public fMain()
        {
            InitializeComponent();
        }

        public static string TxtCon = "Data Source = (LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\SUBD_Kadr.mdf;Integrated Security = True; Connect Timeout = 30\r\n";

        struct Client
        {
            public string ID, Surname,Name,Pathronymic, Email, Phone, Gender;
        }
        List<Client> LstClient = new List<Client>();
        int CountPerPage = 5,NBegin=0;

        void GetDataFromDB()
        {
            NBegin = 0;
            SqlConnection con = new SqlConnection(TxtCon);
            con.Open();

            string Uslovie = "";
            if (CmbFind.Text == "фамилии")
                Uslovie += $" where Surname Like '{TbxFind.Text}%'";
            if (CmbFind.Text == "имени")
                Uslovie += $" where Name Like '{TbxFind.Text}%'";
            if (CmbFind.Text == "отчеству")
                Uslovie += $" where Pathronymic Like '{TbxFind.Text}%'";

            string Gender = "";
            if (CmbGender.Text == "мужской")
                Gender += " and Gender = 'мужской'";
            if (CmbGender.Text == "женский")
                Gender += " and Gender = 'женский'";

            string Sort = "";
            if (CmbSort.Text == "фамилии")
                Sort += " order by Surname";

            SqlCommand comm = new SqlCommand($"Select ID_Client,Surname,Name,Pathronymic,Phone,Email,Gender From Client" +Uslovie+Gender+Sort,con);
            SqlDataReader Res = comm.ExecuteReader();

            LstClient.Clear();

            while(Res.Read())
            {
                Client NEWclient = new Client();
                NEWclient.ID = Res["ID_Client"].ToString();
                NEWclient.Surname = Res["Surname"].ToString();
                NEWclient.Name = Res["Name"].ToString();
                NEWclient.Pathronymic = Res["Pathronymic"].ToString();
                NEWclient.Email = Res["Email"].ToString();
                NEWclient.Phone = Res["Phone"].ToString();
                NEWclient.Gender = Res["Gender"].ToString();
                LstClient.Add(NEWclient);
            }
            con.Close();
        }

        void FillDgvClient()
        {
            DgvClients.Rows.Clear();
            for(int i=NBegin;i<NBegin+CountPerPage;i++)
            {
                if (i > LstClient.Count - 1)
                    break;

               
                string Mess = $"{LstClient[i].Surname} {LstClient[i].Name} {LstClient[i].Pathronymic}\r\nТелефон: {LstClient[i].Phone} Почта: {LstClient[i].Email} \r\n Пол: {LstClient[i].Gender}";

                DgvClients.Rows.Add(Mess,LstClient[i].ID);
            }
            int Cnt = LstClient.Count / CountPerPage;
            if (LstClient.Count % CountPerPage != 0)
                Cnt++;

            PanelPages.Controls.Clear();
            LblBack.Dock = DockStyle.Left;
            LblNext.Dock = DockStyle.Left;

            PanelPages.Controls.Add(LblNext);
            for (int i = Cnt; i > 0; i--)
            {
                Label Lbl = new Label();
                Lbl.Text = i.ToString();
                Lbl.Dock = DockStyle.Left;
                Lbl.AutoSize = true;
                Lbl.Click += Lbl_Click;
                //если отоброжается номер текущей страницы
                if ((NBegin + CountPerPage) / CountPerPage == i)
                {
                    Lbl.BackColor = Color.LightGray;
                    Lbl.Font = new Font(Lbl.Font, FontStyle.Underline);
                }
                PanelPages.Controls.Add(Lbl);
            }
            PanelPages.Controls.Add(LblBack);

            TsIInfo.Text = $"Всего записей: {LstClient.Count} отображается: {DgvClients.RowCount}";
        }

        private void fMain_Load(object sender, EventArgs e)
        {
            CmbSort.SelectedIndex = 0;
            CmbFind.SelectedIndex = 0;
            CmbGender.SelectedIndex = 0;
            GetDataFromDB();
            FillDgvClient();
        }

        private void LblBack_Click(object sender, EventArgs e)
        {
            if (NBegin > 0)
            {
                NBegin = NBegin - CountPerPage;
                
                FillDgvClient();
            }
        }

        private void LblNext_Click(object sender, EventArgs e)
        {
            if (NBegin + CountPerPage < LstClient.Count)
            {
                NBegin = NBegin + CountPerPage;
                FillDgvClient();
            }
        }

        private void TbxFind_TextChanged(object sender, EventArgs e)
        {
            GetDataFromDB();
            FillDgvClient();
        }

        private void CmbGender_SelectedIndexChanged(object sender, EventArgs e)
        {
            GetDataFromDB();
            FillDgvClient();
        }

        private void CmbSort_SelectedIndexChanged(object sender, EventArgs e)
        {
            GetDataFromDB();
            FillDgvClient();
        }

        private void bAdd_Click(object sender, EventArgs e)
        {
            fAddClient FAdd = new fAddClient();
            FAdd.ShowDialog();

            int CurrentNBegin = NBegin;
            GetDataFromDB();
            NBegin = CurrentNBegin;
            FillDgvClient();
        }

        private void BtnDel_Click(object sender, EventArgs e)      
        {
            DialogResult Res = MessageBox.Show("Вы действительно хотите удалить выделенного клиента?", "Внимание!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (Res == DialogResult.No)
                return;
            SqlConnection con = new SqlConnection(TxtCon);
            con.Open();
            string TxtQuery1 = $"delete from Client where ID_Client={DgvClients.CurrentRow.Cells[1].Value}";
            SqlCommand comm1 = new SqlCommand(TxtQuery1, con);
            comm1.ExecuteNonQuery();
            con.Close();

            int CurrentNBegin = NBegin;
            GetDataFromDB();
            NBegin = CurrentNBegin;
            FillDgvClient();
        }

        private void Lbl_Click(object sender, EventArgs e)
        {
            int n = int.Parse((sender as Label).Text);
            NBegin = (n - 1) * CountPerPage;
            FillDgvClient();
        }
    }
}