Otel/HotelCalifornia/Staff.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Windows.Threading;
using System.Data.SqlClient;
using System.Data;

namespace HotelCalifornia
{
    /// <summary>
    /// Логика взаимодействия для Staff.xaml
    /// </summary>
    public partial class Staff : Window
    {
        public Staff()
        {
            InitializeComponent();
            //Таймер на обновление времени
            DispatcherTimer timer = new DispatcherTimer();   
            timer.Tick += new EventHandler(Update_Timer_Tick);
            timer.Interval = new TimeSpan(0, 0, 1);
            timer.Start();
        }

        //Строка подключения
        SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=kursah;Integrated Security=True");

        //Вывод даты и время в textblock
        private void Update_Timer_Tick(object sender, EventArgs e)
        {
            timetxt.Text = DateTime.Now.ToString();
        }

        //Выход из приложения
        private void Close(object sender, RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }

        //Свернуть окно
        private void WindMin_Click(object sender, RoutedEventArgs e)
        {
            this.WindowState = WindowState.Minimized;
        }

        //Возврат к окну выбора функции
        private void Back(object sender, RoutedEventArgs e)
        {
            MainWindow mainWindow = new MainWindow();
            this.Close();
            mainWindow.Show();
        }

        //Выбор строки из БД
        private void datastaff_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataGrid gd = (DataGrid)sender;
                DataRowView rowView = gd.SelectedItem as DataRowView;
                if (rowView != null)
                {
                    idtxt.Text = rowView["ID_Administrator"].ToString();
                    nametxt.Text = rowView["FirstName"].ToString();
                    familiyatxt.Text = rowView["LastName"].ToString();
                    otchestvotxt.Text = rowView["MiddleName"].ToString();
                    logintxt.Text = rowView["Login"].ToString();
                    passwordtxt.Password = rowView["Password"].ToString();
                    rolecombo.Text = rowView["Name_Role"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        //Добавление администратора
        private void Add_Click(object sender, RoutedEventArgs e)
        {
            if (familiyatxt.Text == "" || nametxt.Text == "" || otchestvotxt.Text == "" || logintxt.Text == "" || passwordtxt.Password == "" || rolecombo.Text == "")
            {
                MessageBox.Show("Заполните все поля!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Select * from Administrator where Login ='" + logintxt.Text + "'", con);
                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        MessageBox.Show("Такой Администратор уже создан!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                        con.Close();
                    }
                    else
                    {
                        string idrole = "";
                        if (rolecombo.Text == "Администратор")
                            idrole = "1";
                        else
                            idrole = "2";
                        string sql = "INSERT INTO Administrator (LastName,FirstName,MiddleName,Login,Password,ID_Role) VALUES('" + familiyatxt.Text + "','" + nametxt.Text + "','" + otchestvotxt.Text + "','" + logintxt.Text + "','" + passwordtxt.Password + "', '" + idrole.ToString() + "')";
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                        dataAdapter.SelectCommand.ExecuteNonQuery();
                        con.Close();
                        showgrid();
                        idtxt.Text = "";
                        nametxt.Text = "";
                        familiyatxt.Text = "";
                        otchestvotxt.Text = "";
                        logintxt.Text = "";
                        passwordtxt.Password = "";
                        rolecombo.Text = "";
                        MessageBox.Show("Администратор был добавлен!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                    }
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        //Обновление администратора
        private void Update_Click(object sender, RoutedEventArgs e)
        {
            if (idtxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else if (familiyatxt.Text == "" || nametxt.Text == "" || otchestvotxt.Text == "" || logintxt.Text == "" || passwordtxt.Password == "" || rolecombo.Text == "")
            {
                MessageBox.Show("Заполните все поля!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string idrole = "";
                    if (rolecombo.Text == "Администратор")
                        idrole = "1";
                    else
                        idrole = "2";
                    string sql = "Update Administrator set FirstName ='" + nametxt.Text + "', LastName = '" + familiyatxt.Text + "', MiddleName = '" + otchestvotxt.Text + "', Login = '" + logintxt.Text + "', Password = '" + passwordtxt.Password + "', ID_Role = '"+idrole.ToString()+"' where ID_Administrator = '" + idtxt.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    idtxt.Text = "";
                    nametxt.Text = "";
                    familiyatxt.Text = "";
                    otchestvotxt.Text = "";
                    logintxt.Text = "";
                    passwordtxt.Password = "";
                    rolecombo.Text = "";
                    showgrid();
                    MessageBox.Show("Администратор был изменен!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        //Удаление администратора
        private void Delete_Click(object sender, RoutedEventArgs e)
        {
            if (idtxt.Text == "")
            {
                MessageBox.Show("Поле не выбрано! Выберите нужное поле!", "Предупреждение", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                try
                {
                    con.Open();
                    string sql = "DELETE FROM Administrator WHERE ID_Administrator = '" + idtxt.Text + "'";
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                    dataAdapter.SelectCommand.ExecuteNonQuery();
                    con.Close();
                    idtxt.Text = "";
                    nametxt.Text = "";
                    familiyatxt.Text = "";
                    otchestvotxt.Text = "";
                    logintxt.Text = "";
                    passwordtxt.Password = "";
                    rolecombo.Text = "";
                    showgrid();
                    MessageBox.Show("Администратор удален!", "Информация", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    con.Close();
                    MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
        }

        //Ограничение для ввода текста
        #region Ограничение
        private void nametxt_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (sender is TextBox textBox)
            {
                textBox.Text = new string
               (textBox.Text.Where(ch => (ch >= 'А' && ch <= 'Я') || (ch >= 'а' && ch <= 'я')).ToArray());
            }
        }

        private void familiyatxt_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (sender is TextBox textBox)
            {
                textBox.Text = new string
               (textBox.Text.Where(ch => (ch >= 'А' && ch <= 'Я') || (ch >= 'а' && ch <= 'я')).ToArray());
            }
        }

        private void otchestvotxt_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (sender is TextBox textBox)
            {
                textBox.Text = new string
               (textBox.Text.Where(ch => (ch >= 'А' && ch <= 'Я') || (ch >= 'а' && ch <= 'я')).ToArray());
            }
        }

        private void logintxt_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (sender is TextBox textBox)
            {
                textBox.Text = new string
               (textBox.Text.Where(ch => (ch >= 'A' && ch <= 'Z') || (ch >= 'a' && ch <= 'z') || (ch >= '0' && ch <= '9')).ToArray());
            }
        }

        private void passwordtxt_PasswordChanged(object sender, RoutedEventArgs e)
        {
            if (sender is TextBox textBox)
            {
                textBox.Text = new string
               (textBox.Text.Where(ch => (ch >= 'A' && ch <= 'Z') || (ch >= 'a' && ch <= 'z') || (ch >= '0' && ch <= '9')).ToArray());
            }
        }
        #endregion

        //Фомировка данных из БД
        void showgrid()
        {
            try
            {
                con.Open();
                string sql = "SELECT ID_Administrator,LastName, FirstName, MiddleName, Login, Password, [Name_Role]  From Administrator inner join Role on Administrator.ID_Role = Role.ID_Role";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, con);
                DataTable data = new DataTable("Administrator");
                dataAdapter.Fill(data);
                datastaff.ItemsSource = data.DefaultView;
                dataAdapter.Update(data);
                con.Close();
                datastaff.Columns[0].Header = "ID";
                datastaff.Columns[1].Header = "Фамилия";
                datastaff.Columns[2].Header = "Имя";
                datastaff.Columns[3].Header = "Отчество";
                datastaff.Columns[4].Header = "Логин";
                datastaff.Columns[6].Header = "Роль";
                datastaff.Columns[5].Visibility = Visibility.Collapsed;
                datastaff.Columns[0].Visibility = Visibility.Collapsed;
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        void fillrolecombo()
        {
            try
            {
                rolecombo.Items.Clear();
                con.Open();
                SqlCommand sql = con.CreateCommand();
                sql.CommandType = CommandType.Text;
                sql.CommandText = "Select Name_Role from Role";
                sql.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sql);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    rolecombo.Items.Add(dr["Name_Role"].ToString());
                }
                con.Close();
            }
            catch (Exception ex)
            {
                con.Close();
                MessageBox.Show("Возникла ошибка! " + ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        //Обновление
        private void Refresh_Click(object sender, RoutedEventArgs e)
        {
            showgrid();
            idtxt.Text = "";
            nametxt.Text = "";
            familiyatxt.Text = "";
            otchestvotxt.Text = "";
            logintxt.Text = "";
            passwordtxt.Password = "";
            rolecombo.Text = "";
        }

        //Перетаскивание окна
        private void Grid_MouseDown(object sender, MouseButtonEventArgs e)
        {
            try
            {
                DragMove();
            }
            catch
            {
            }
        }

        //Запуск в самом начале
        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            showgrid();
            fillrolecombo();
        }
    }
}