Заглавная страница Избранные статьи Случайная статья Познавательные статьи Новые добавления Обратная связь FAQ Написать работу КАТЕГОРИИ:  Археология Биология Генетика География Информатика История Логика Маркетинг Математика Менеджмент Механика Педагогика Религия Социология Технологии Физика Философия Финансы Химия ЭкологияТОП 10 на сайте Приготовление дезинфицирующих растворов различной концентрацииТехника нижней прямой подачи мяча. Франко-прусская война (причины и последствия) Организация работы процедурного кабинета Смысловое и механическое запоминание, их место и роль в усвоении знаний Коммуникативные барьеры и пути их преодоления Обработка изделий медицинского назначения многократного применения Образцы текста публицистического стиля Четыре типа изменения баланса Задачи с ответами для Всероссийской олимпиады по праву 
Мы поможем в написании ваших работ! ЗНАЕТЕ ЛИ ВЫ?
Влияние общества на человека
Приготовление дезинфицирующих растворов различной концентрации Практические работы по географии для 6 класса Организация работы процедурного кабинета Изменения в неживой природе осенью Уборка процедурного кабинета Сольфеджио. Все правила по сольфеджио Балочные системы. Определение реакций опор и моментов защемления |
Список использованных источниковСодержание книги Поиск на нашем сайте 1. Стиллмен Э., Грин Д. Изучаем C# / Э. Стиллмен, Д. Грин – Head First O’Reilly: Питер, 2014. – 816 с. 2. Дюбуа П. MySQL / П. Дюбуа – Вильямс, 2007. – 1168 с. 3. Петцольд Ч. Программирование для Microsoft Windows 8. Разработка приложений для Windows Store на C# и XAML – Питер, 2014. – 1008 с. 4. Актуальность компьютерного сервиса в современном мире – URL: http://www.allpchelp.ru/poleznoe/service/ (дата обращения 2020-10-05). 5. MySQL. Модель клиент-сервер – URL: https://www.hostinger.ru/rukovodstva/shto-takoje-mysql/ (дата обращения 2020-10-06). 6. MySQL Workbench – URL: https://ru.wikipedia.org/wiki/MySQL_Workbench (дата обращения 2020-10-07). 7. Microsoft Visual Studio – URL: https://ru.wikipedia.org/wiki/Microsoft_Visual_Studio (дата обращения 2020-10-08). 8. C Sharp – URL: https://ru.wikipedia.org/wiki/C_Sharp (дата обращения 2020-10-09). 9. Система защиты баз данных – URL: https://www.anti-malware.ru/security/database-firewall (дата обращения 2020-10-10). 10. Параметризированные запросы к БД – URL: https://scask.ru/a_book_cbd.php?id=33 (дата обращения 2020-10-11). 11. Хранимая процедура – URL: https://ru.wikipedia.org/wiki/Хранимая_процедура (дата обращения 2020-10-12).
ПРИЛОЖЕНИЕ А Таблицы с исходными данными
Рисунок 14 – Таблица computer_components
Рисунок 15 – Таблица customers
Рисунок 16 – Таблица employees
Рисунок 17 – Таблица employment
Рисунок 18 – Таблица orders
Рисунок 19 – Таблица orders_and_computer_components
Рисунок 20 – Таблица orders_and_services
Рисунок 21 – Таблица services
Рисунок 22 – Таблица types_of_computer_components
Рисунок 23 – Таблица users
ПРИЛОЖЕНИЕ Б Листинг триггеров и хранимых процедур Листинг хранимой процедуры «show_employees». CREATE DEFINER=`root`@`localhost` PROCEDURE `show_employees`() BEGIN SELECT * FROM employees; END
Листинг хранимой процедуры «add_employee». CREATE DEFINER=`root`@`localhost` PROCEDURE `add_employee`( IN Employee_Full_Name varchar(100), IN Date_of_Birth datetime, IN Gender enum('male', 'female'), IN Address varchar(45), IN Employee_Phone_Number varchar(45), IN Passport varchar(45), IN ID_Employment int) BEGIN INSERT INTO employees( Employee_Full_Name, Date_of_Birth, Gender, Address, Employee_Phone_Number, Passport, ID_Employment) values( Employee_Full_Name, Date_of_Birth, Gender, Address, Employee_Phone_Number, Passport, ID_Employment); END
ПРИЛОЖЕНИЕ В Листинг приложения Листинг модуля Form 1. static string str = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection con = new MySqlConnection(str); MySqlCommand cmd = new MySqlCommand(); //DataSet ds = new DataSet(); private void button1_Click(object sender, EventArgs e) { try { string Login = textBox1.Text; string Password = textBox2.Text;
if (Login == "" || Password == "") { MessageBox.Show("Поля не заполнены"); } if (Login.Length < 3 || Password.Length < 3) { MessageBox.Show("Минимальная длина поля 3 символа"); } { MySqlConnection con = new MySqlConnection(str); using (MySqlCommand cmd = new MySqlCommand("SELECT User_ID, User_Password, User_Guid FROM users WHERE User_Login=@Login;", con)) { cmd.Parameters.AddWithValue("@Login", Login); con.Open();
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read()) { // dr.Read() = we found user(s) with matching login int db_User_ID = Convert.ToInt32(dr["User_ID"]); string db_Password = Convert.ToString(dr["User_Password"]); string db_User_Guid = Convert.ToString(dr["User_Guid"]); // Now we hash the UserGuid from the database with the password we want to check string hashedPassword = Security.HashSHA1(Password + db_User_Guid); // If its correct password the result of the hash is the same as in the database if (db_Password!= hashedPassword) { Form3 form3 = new Form3(); form3.Show(); this.Hide(); } else { Form5 form5 = new Form5(); form5.Show(); this.Hide(); } } con.Close(); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } public class Security { public static string HashSHA1(string value) { var sha1 = System.Security.Cryptography.SHA1.Create(); var inputBytes = Encoding.ASCII.GetBytes(value); var hash = sha1.ComputeHash(inputBytes);
var sb = new StringBuilder(); for (var i = 0; i < hash.Length; i++) { sb.Append(hash[i].ToString("X2")); } return sb.ToString(); } } private void button2_Click(object sender, EventArgs e) { Form4 form4 = new Form4(); form4.Show(); this.Hide(); } }
Листинг модуля Form2. private void button1_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "SELECT * FROM computer_components;"; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button2_Click(object sender, EventArgs e) { dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); } private void button3_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "SELECT ID_Type_of_Computer_Component, Manufacturer_Country, Price FROM computer_components WHERE Price in (SELECT max(Price) WHERE Manufacturer_Country = 'Тайвань' and ID_Type_of_Computer_Component = '1100'); "; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button4_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "select * from orders where Order_ID IN(select Computer_Component_ID from computer_components where Warranty_Period = '24'); "; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button6_Click(object sender, EventArgs e) { Form1 form1 = new Form1(); form1.Show(); this.Hide(); } private void button5_Click(object sender, EventArgs e) { Form5 form5 = new Form5(); form5.Show(); this.Hide(); }
Листинг модуля Form3. public partial class Form3: Form { public Form3() { InitializeComponent(); } private void Form3_Load(object sender, EventArgs e) { } private void button1_Click(object sender, EventArgs e) { Form1 form1 = new Form1(); form1.Show(); this.Hide(); } private void button2_Click(object sender, EventArgs e) { Form4 form4 = new Form4(); form4.Show(); this.Hide(); } }
Листинг модуля Form4. static string str = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection con = new MySqlConnection(str); MySqlCommand cmd; DataSet ds = new DataSet();
private void button1_Click(object sender, EventArgs e) { try { string Login = textBox1.Text; string Password = textBox2.Text; string Password_Repeated = textBox3.Text;
// First create a new Guid (Global Unique ID) for the user. This will be unique for each user Guid user_Guid = System.Guid.NewGuid();
// Hash the password together with the unique userGuid string hashedPassword = Security.HashSHA1(Password + user_Guid.ToString());
if (Password == Password_Repeated) { cmd = new MySqlCommand("SELECT * FROM Users WHERE User_Login=@Login", con); MySqlDataAdapter da = new MySqlDataAdapter(cmd); cmd.Parameters.AddWithValue("@Login", Login); da.Fill(ds); int i = ds.Tables[0].Rows.Count; if (i > 0) { MessageBox.Show("Пользователь " + Login + " уже существует"); ds.Clear(); } } if (Login == "" || Password == "" || Password_Repeated == "") { MessageBox.Show("Поля не заполнены"); } else { cmd = new MySqlCommand("INSERT INTO users(User_Login, User_Password, User_Guid) VALUES(@Login, @Password, @user_Guid)", con); cmd.Parameters.AddWithValue("@Login", Login); cmd.Parameters.AddWithValue("@Password", hashedPassword); cmd.Parameters.AddWithValue("@User_Guid", user_Guid); con.Open(); cmd.ExecuteNonQuery(); con.Close();
this.Hide(); Form1 form1 = new Form1(); form1.Show(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } public class Security { public static string HashSHA1(string value) { var sha1 = System.Security.Cryptography.SHA1.Create(); var inputBytes = Encoding.ASCII.GetBytes(value); var hash = sha1.ComputeHash(inputBytes);
var sb = new StringBuilder(); for (var i = 0; i < hash.Length; i++) { sb.Append(hash[i].ToString("X2")); } return sb.ToString(); } }
Листинг модуля Form5. private void button4_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "select * from customers order by rand() limit 1; "; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button5_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "select avg(Salary) as 'Среднемесячная', avg(12 * Salary) as 'Среднегодовая' from employment; "; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button1_Click(object sender, EventArgs e) { dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); } private void button2_Click(object sender, EventArgs e) { Form2 form2 = new Form2(); form2.Show(); this.Hide(); } private void button3_Click(object sender, EventArgs e) { Form6 form6 = new Form6(); form6.Show(); this.Hide(); } }
Листинг модуля Form6. private void button4_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "select concat_ws(',', Employee_Full_Name, Address, Gender) as 'Address', substring_index(Employee_Full_Name, ' ', 1) as 'Last Name' from employees where Gender = 'male';"; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button5_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "select concat_ws(' ', Manufacturer_Company, Brand) as 'Комплектующие' from computer_components;"; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button2_Click(object sender, EventArgs e) { Form5 form5 = new Form5(); form5.Show(); this.Hide(); } private void button3_Click(object sender, EventArgs e) { Form7 form7 = new Form7(); form7.Show(); this.Hide(); } private void button1_Click(object sender, EventArgs e) { dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); } }
Листинг модуля Form7. private void button2_Click(object sender, EventArgs e) { Form6 form6 = new Form6(); form6.Show(); this.Hide(); } private void button3_Click(object sender, EventArgs e) { Form8 form8 = new Form8(); form8.Show(); this.Hide(); } private void button1_Click(object sender, EventArgs e) { dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); } private void button4_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "select DATE_FORMAT(Date_of_Birth, '%d.%m.%Y') as 'Дата рождения' from employees;"; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button5_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "select dayname(Order_Datе) as 'День заказа' from orders where Order_ID = '2'; "; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
Листинг модуля Form8. private void button5_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "select * from employees;"; MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection); DataSet ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } private void button3_Click(object sender, EventArgs e) { dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); }
private void Form8_Load(object sender, EventArgs e) { } private void radioButton1_CheckedChanged(object sender, EventArgs e) { gender = "male"; } private void radioButton2_CheckedChanged(object sender, EventArgs e) { gender = "female"; } private void radioButton3_CheckedChanged(object sender, EventArgs e) { employmentID = "100"; } private void radioButton4_CheckedChanged(object sender, EventArgs e) { employmentID = "200"; } private void radioButton5_CheckedChanged(object sender, EventArgs e) { employmentID = "300"; } private void button4_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306";
MySqlConnection connection = new MySqlConnection(conn); try { string Full_Name = textBox1.Text; string BirthDay = maskedTextBox1.Text; string address = textBox4.Text; string Phone_Number = maskedTextBox2.Text; string passport = textBox6.Text; connection.Open(); MySqlCommand cmd = new MySqlCommand("add_employee", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?Employee_Full_Name", Full_Name); cmd.Parameters.AddWithValue("?Date_of_Birth", BirthDay); cmd.Parameters.AddWithValue("?Gender", gender); cmd.Parameters.AddWithValue("?Address", address); cmd.Parameters.AddWithValue("?Employee_Phone_Number", Phone_Number); cmd.Parameters.AddWithValue("?Passport", passport); cmd.Parameters.AddWithValue("?ID_Employment", employmentID);
if(cmd.ExecuteNonQuery() == 1) { MessageBox.Show("Добавление прошло успешно"); } else { MessageBox.Show("Ошибка при добавлении"); } connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
Листинг модуля Form9. private void button5_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "insert into computer_components(ID_Type_of_Computer_Component, Brand, Manufacturer_Company, Manufacturer_Country, Release_Date, Specifications, Warranty_Period, Computer_Component_Description, Price) " + "values(@ID_Type_of_Computer_Component, @Brand, @Manufacturer_Company, @Manufacturer_Country, @Release_Date, @Specifications, @Warranty_Period, @Computer_Component_Description, @Price); "; MySqlCommand cmd = new MySqlCommand(query, connection); cmd.Parameters.AddWithValue("@ID_Type_of_Computer_Component", ComponentType); cmd.Parameters.AddWithValue("@Brand", textBox1.Text); cmd.Parameters.AddWithValue("@Manufacturer_Company", textBox2.Text); cmd.Parameters.AddWithValue("@Manufacturer_Country", textBox3.Text); cmd.Parameters.AddWithValue("@Release_Date", maskedTextBox1.Text); cmd.Parameters.AddWithValue("@Specifications", textBox4.Text); cmd.Parameters.AddWithValue("@Warranty_Period", Warranty); cmd.Parameters.AddWithValue("@Computer_Component_Description", textBox5.Text); cmd.Parameters.AddWithValue("@Price", textBox6.Text);
cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); textBox1.Clear(); textBox2.Clear(); textBox3.Clear(); textBox4.Clear(); textBox5.Clear(); textBox6.Clear(); maskedTextBox1.Clear(); MessageBox.Show("Добавление прошло успешно");
connection.Close();
} catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
Листинг модуля Form10. private void button1_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); string query = "update employment set Salary='"+textBox2.Text+"'where ID_Employment='"+employment+"'"; MySqlCommand cmd = new MySqlCommand(query, connection); cmd.Parameters.AddWithValue("@Salary", textBox2.Text); cmd.Parameters.AddWithValue("@ID_Employment", employment); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); textBox2.Clear(); textBox3.Clear(); textBox4.Clear(); MessageBox.Show("Изменение прошло успешно"); connection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
Листинг модуля Form11. private void button3_Click(object sender, EventArgs e) { string conn = "server=127.0.0.1;user=root;password=Plazma942427;database=computer_company;port=3306"; MySqlConnection connection = new MySqlConnection(conn); try { connection.Open(); // Delete a record string DeleteQuery = "DELETE FROM employees WHERE Employee_Full_Name='" + comboBox1.Text + "'"; MySqlCommand cmd = new MySqlCommand(DeleteQuery, connection); cmd.Parameters.AddWithValue("@Employee_Full_Name", comboBox1.Text); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); MessageBox.Show("Удаление прошло успешно"); comboBox1.SelectedIndex = -1; comboBox1.Items.Clear(); // Select a record after deleting string SelectQuery = "SELECT * FROM computer_company.employees"; MySqlCommand mySqlCommand = new MySqlCommand(SelectQuery, connection); MySqlDataReader reader = mySqlCommand.ExecuteReader(); while (reader.Read()) { comboBox1.Items.Add(reader.GetString("Employee_Full_Name")); } connection.Close(); reader.Close();
} catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
|
||
|
|
Последнее изменение этой страницы: 2020-11-11; просмотров: 142; Нарушение авторского права страницы; Мы поможем в написании вашей работы! infopedia.su Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Обратная связь - 216.73.216.156 (0.006 с.) |
