private void students_Load(object sender, EventArgs e) { update_btn.Enabled = false; string query, name; int id; query = "SELECT id,name FROM faculty"; try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); while (dt_reader.Read()) { name = dt_reader.GetString("name"); id = dt_reader.GetInt32("id"); faculty_cmbx.Items.Add(name); list_faculty.Add(new faculty(id, name)); } DatabaseProcess.conn_close(); } catch {} }
private void record_btn_Click(object sender, EventArgs e) { string department_name, faculty_id, query; int index; Boolean result; department_name = department_name_txtbx.Text; index = faculty_name_cmbx.SelectedIndex; if (department_name != "" && index > -1) { faculty_id = Utils.faculty_list[index].Id.ToString(); query = "INSERT INTO department(id,faculty_id,name,create_date) " + "values(new_id,{0},'{1}',NOW());"; query = string.Format(query, faculty_id, department_name); result = DatabaseProcess.insert(query, "department"); if (result) { MessageBox.Show("Bölüm başarıyla kayıt ededildi"); } else { error_msg_lbl.Text = DatabaseProcess.error_msg; } DatabaseProcess.conn_close(); } }
private void student_result_lstbx_SelectedIndexChanged(object sender, EventArgs e) { string query = "", query2 = "", query3, book_name, code, waiting_recieve = ""; int student_id; List <int> book_id = new List <int>(); if (student_result_lstbx.SelectedIndex > -1) { student_index = student_result_lstbx.SelectedIndex; student_info_lbl.Text = student_result_lstbx.SelectedItem.ToString(); student_id = list_student[student_index].Id; query = "SELECT book_id FROM lend_book WHERE student_id={0} && recieve_case=0"; query2 = "SELECT name,code FROM book WHERE id={0};"; query = string.Format(query, student_id.ToString()); } try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader; dt_reader = cmd.ExecuteReader(); while (dt_reader.Read()) { book_id.Add(dt_reader.GetInt32("book_id")); } DatabaseProcess.conn_close(); if (book_id.Count == 0) { waiting_recive_book_lbl.Text = "Ödünç aldığı kitap yok"; } else { for (int i = 0; i < book_id.Count; i++) { DatabaseProcess.conn_open(); query3 = string.Format(query2, book_id[i].ToString()); cmd = new MySqlCommand(query3, DatabaseProcess.myconn); dt_reader = cmd.ExecuteReader(); dt_reader.Read(); code = dt_reader.GetString("code"); book_name = dt_reader.GetString("name"); if (i > 0) { waiting_recieve += "\n" + book_name + " | " + code; } else { waiting_recieve = book_name + " | " + code; } waiting_recive_book_lbl.Text = waiting_recieve; DatabaseProcess.conn_close(); } } } catch (Exception) { } }
private void faculty_cmbx_SelectedIndexChanged(object sender, EventArgs e) { department_cmbx.Items.Clear(); string query, name; int id, faculty_id, index; index = faculty_cmbx.SelectedIndex; faculty_id = list_faculty[index].Id; query = "SELECT id,name FROM department WHERE faculty_id={0}"; query = string.Format(query, faculty_id); try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); while (dt_reader.Read()) { name = dt_reader.GetString("name"); id = dt_reader.GetInt32("id"); department_cmbx.Items.Add(name); list_department.Add(new department(id, name)); } DatabaseProcess.conn_close(); } catch { } }
private void faculty_cmbx_SelectedIndexChanged(object sender, EventArgs e) { department_cmbx.Items.Clear(); int index, id; string faculty_id, query, value, name; index = faculty_cmbx.SelectedIndex; faculty_id = Utils.faculty_list[index].Id.ToString(); query = "SELECT id, name FROM department WHERE faculty_id={0};"; query = string.Format(query, faculty_id); DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); while (dt_reader.Read()) { id = dt_reader.GetInt32("id"); name = dt_reader.GetString("name"); value = id.ToString() + " - " + name; department_cmbx.Items.Add(value); list_department.Add(new department(id, name)); } DatabaseProcess.conn_close(); }
private void student_search_txtbx_TextChanged(object sender, EventArgs e) { student_result_lstbx.Items.Clear(); string query = "SELECT * FROM student WHERE {0}"; string search_with_name = " concat_ws(' ',name, surname) like '%{0}%'"; string search_with_number = "number={0}"; if (student_search_txtbx.Text.Length > 2) { list_student.Clear(); string name, surname = "", string_number; int number, id; if (student_search_txtbx.Text.Any(char.IsLetter)) { name = student_search_txtbx.Text; query = string.Format(query, search_with_name); query = string.Format(query, name); } else { string_number = student_search_txtbx.Text; query = string.Format(query, search_with_number); query = string.Format(query, string_number); } try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); while (dt_reader.Read()) { id = dt_reader.GetInt32("id"); name = dt_reader.GetString("name"); surname = dt_reader.GetString("surname"); number = dt_reader.GetInt32("number"); string_number = number.ToString(); list_student.Add(new student(id)); student_result_lstbx.Items.Add(name + " " + surname + " | " + string_number); } DatabaseProcess.conn_close(); } catch { } if (student_result_lstbx.Items.Count == 0) { student_result_lstbx.Items.Add("Eşleşme yok"); } } //if else { student_result_lstbx.Items.Add("Arama için en az 3 karakter girmelisiniz."); } }//student_search_txtbx_TextChanged
public static void list_faculty() { faculty_list.Clear(); int new_id; string new_name; string query = "SELECT id,name FROM faculty;"; DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); while (dt_reader.Read()) { new_id = dt_reader.GetInt32("id"); new_name = dt_reader.GetString("name"); faculty_list.Add(new faculty(new_id, new_name)); } DatabaseProcess.conn_close(); }
private void giris_btn_Click(object sender, EventArgs e) { string email, password; email = email_txtbx.Text; password = psswrd_txtbx.Text; string hash = Utils.get_hash(password); string query = "SELECT * FROM User WHERE email='" + email + "' and password='******';"; try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); if (dt_reader.Read()) { Utils.User.id = dt_reader.GetInt32("id"); Utils.User.name = dt_reader.GetString("name"); Utils.User.surname = dt_reader.GetString("surname"); int id = dt_reader.GetInt32("id"); main_panel main_panel_form = new main_panel(); main_panel_form.show_singin = this; this.Visible = false; main_panel_form.Show(); } else { msg_lbl.Text = "Email, parola combinasyonu bulunamadı. Bilgileri kontrol ediniz."; } DatabaseProcess.conn_close(); } catch (Exception) { } }
private void record_btn_Click(object sender, EventArgs e) { string name, surname, number, address, home_address, gsm, department_id; string query; int index; Boolean result; name = name_txtbx.Text; surname = surname_txtbx.Text; number = number_txtbx.Text; address = address_txtbx.Text; home_address = home_address_txtbx.Text; gsm = gsm_txtbx.Text; index = department_cmbx.SelectedIndex; department_id = list_department[index].Id.ToString(); if (name != "" && surname != "" && number != "" && address != "" && home_address != "" && department_id != "" && gsm.Length == 11) { query = "INSERT INTO student() values(new_id,{0},'{1}','{2}','{3}','{4}',{5},{6});"; query = string.Format(query, number, name, surname, address, home_address, gsm, department_id); result = DatabaseProcess.insert(query, "student"); if (result) { MessageBox.Show("Yeni öğrenci kaydı başarıyla tamamlandı"); } else { MessageBox.Show(DatabaseProcess.error_msg); } } else { MessageBox.Show("hatalı"); } DatabaseProcess.conn_close(); }
private void search_btn_Click(object sender, EventArgs e) { string query, query2, query3, code, student_id = "", book_id = "", name, surname, number, author, temp = ""; DateTime lend_date, expiry_date, today = DateTime.Today; int remaining_time; query = "SELECT id,student_id,lend_date,expiry_date FROM lend_book WHERE book_id={0} AND recieve_case=0;"; query2 = "SELECT name, surname, number FROM student WHERE id={0};"; query3 = "SELECT id,name, author FROM book WHERE code='{0}';"; code = search_txtbx.Text; query3 = string.Format(query3, code); try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query3, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); dt_reader.Read(); book_id = dt_reader.GetInt32("id").ToString(); name = dt_reader.GetString("name"); author = dt_reader.GetString("author"); book_info_lbl.Text = name + " " + author + " | " + code; DatabaseProcess.conn_close(); } catch {} if (book_id != "") { query = string.Format(query, book_id); try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); dt_reader.Read(); lend_book_id = dt_reader.GetInt32("id").ToString(); student_id = dt_reader.GetInt32("student_id").ToString(); lend_date = dt_reader.GetDateTime("lend_date"); expiry_date = dt_reader.GetDateTime("expiry_date"); lend_date_lbl.Text = lend_date.ToShortDateString(); remaining_time = (int)today.Subtract(expiry_date).TotalDays; if (remaining_time <= 0) { remaining_time_lbl.Text = Math.Abs((int)remaining_time) + " gün kaldı"; } else { remaining_time_lbl.Text = Math.Abs((int)remaining_time) + " gün geçti"; } DatabaseProcess.conn_close(); } catch { DatabaseProcess.conn_close(); } if (student_id != "") { query2 = string.Format(query2, student_id); try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query2, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); dt_reader.Read(); name = dt_reader.GetString("name"); surname = dt_reader.GetString("surname"); number = dt_reader.GetInt32("number").ToString(); student_info_lbl.Text = name + " " + surname + " | " + number; DatabaseProcess.conn_close(); } catch { } groupBox1.Visible = true; }//if(student_id != "") else { query = "SELECT name FROM book WHERE code='{0}';"; query = string.Format(query, code); MessageBox.Show(query); try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); while (dt_reader.Read()) { temp = dt_reader.GetString("name"); } DatabaseProcess.conn_close(); } catch { } if (temp != "") { MessageBox.Show("Bu kitap zaten rafta"); } else { MessageBox.Show("Kitap kodu hatalı!"); } } }//if(book_id != "") else { MessageBox.Show("Kitap kodu hatalı!"); } }//student_search_txtbx_TextChanged
private void singup_Load(object sender, EventArgs e) { gsm_txtbx.Text = "0"; error_lbl.Text = ""; //// create database ///// //try //{ Boolean result = DatabaseProcess.conn_open(); if (!result) { string connline = "server=127.0.0.1;port=3306;user id=root;password=1234"; string query = "CREATE DATABASE library"; MySqlConnection conn = new MySqlConnection(connline); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = query; cmd.Connection.Open(); try { cmd.ExecuteNonQuery(); query = "use library; CREATE TABLE book (id int(11) NOT NULL,code varchar(45) NOT NULL,name varchar(45) NOT NULL,category varchar(45) NOT NULL,published_year int(11) NOT NULL,ISBN_number varchar(45) NOT NULL,page_count int(11) NOT NULL,search_keys varchar(250) NOT NULL,author varchar(45) NOT NULL,PRIMARY KEY (id),UNIQUE KEY id_UNIQUE (id),UNIQUE KEY code_UNIQUE (code));"; DatabaseProcess.conn_open(); cmd = new MySqlCommand(query, DatabaseProcess.myconn); cmd.ExecuteNonQuery(); DatabaseProcess.conn_close(); query = "CREATE TABLE faculty (" + "id int(11) NOT NULL," + "name varchar(100) NOT NULL," + "create_time datetime NOT NULL," + "PRIMARY KEY (id)," + "UNIQUE KEY id_UNIQUE (id)," + "UNIQUE KEY name_UNIQUE (name))"; cmd = new MySqlCommand(query, DatabaseProcess.myconn); DatabaseProcess.conn_open(); cmd.ExecuteNonQuery(); DatabaseProcess.conn_close(); query = "CREATE TABLE department (" + "id int(11) NOT NULL," + "faculty_id int(11) NOT NULL," + "name varchar(100) NOT NULL," + "create_date datetime NOT NULL," + "PRIMARY KEY (id)," + "UNIQUE KEY id_UNIQUE (id)," + "UNIQUE KEY name_UNIQUE (name)," + "KEY fakulty_id_idx (faculty_id)," + "CONSTRAINT fakulty_id FOREIGN KEY (faculty_id) " + "REFERENCES faculty (id) ON UPDATE NO ACTION)"; cmd = new MySqlCommand(query, DatabaseProcess.myconn); DatabaseProcess.conn_open(); cmd.ExecuteNonQuery(); DatabaseProcess.conn_close(); query = "CREATE TABLE student (" + "id int(11) NOT NULL," + "number int(11) NOT NULL," + "name varchar(45) NOT NULL," + "surname varchar(45) NOT NULL," + "adress varchar(255) NOT NULL," + "hometown_address varchar(255) NOT NULL," + "gsm varchar(11) NOT NULL," + "department_id int(11) NOT NULL," + "PRIMARY KEY (id)," + " UNIQUE KEY id_UNIQUE (id)," + "UNIQUE KEY number_UNIQUE (number)," + "UNIQUE KEY gsm_number_UNIQUE (gsm)," + "KEY department_id_idx (department_id)," + "CONSTRAINT department_id FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE NO ACTION ON UPDATE NO ACTION)"; cmd = new MySqlCommand(query, DatabaseProcess.myconn); DatabaseProcess.conn_open(); cmd.ExecuteNonQuery(); DatabaseProcess.conn_close(); query = "CREATE TABLE user (" + "id int(11) NOT NULL," + "name varchar(45) NOT NULL," + "surname varchar(45) NOT NULL," + "singup_date datetime NOT NULL," + "gsm varchar(11) NOT NULL," + "email varchar(45) NOT NULL," + "password varchar(255) NOT NULL," + "PRIMARY KEY (id)," + " UNIQUE KEY id_UNIQUE (id)," + " UNIQUE KEY tel_UNIQUE (gsm)," + " UNIQUE KEY email_UNIQUE (email))"; cmd = new MySqlCommand(query, DatabaseProcess.myconn); DatabaseProcess.conn_open(); cmd.ExecuteNonQuery(); DatabaseProcess.conn_close(); query = "CREATE TABLE lend_book (" + "id int(11) NOT NULL," + "student_id int(11) NOT NULL," + "book_id int(11) NOT NULL," + "recieve_by_id int(11) DEFAULT NULL," + "lend_date datetime NOT NULL," + "expiry_date datetime NOT NULL," + "delivery_date datetime DEFAULT NULL," + "recieve_case tinyint(1) DEFAULT 0," + "PRIMARY KEY (id)," + "UNIQUE KEY id_UNIQUE (id)," + "KEY student_id_idx (student_id)," + "KEY book_id_idx (book_id)," + "KEY recieve_by_id_idx (recieve_by_id)," + "CONSTRAINT book_id FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE NO ACTION ON UPDATE NO ACTION," + "CONSTRAINT recieve_by_id FOREIGN KEY (recieve_by_id) REFERENCES user (id) ON DELETE NO ACTION ON UPDATE NO ACTION," + "CONSTRAINT student_id FOREIGN KEY (student_id) REFERENCES student (id) ON DELETE NO ACTION ON UPDATE NO ACTION) "; cmd = new MySqlCommand(query, DatabaseProcess.myconn); DatabaseProcess.conn_open(); cmd.ExecuteNonQuery(); DatabaseProcess.conn_close(); } catch (Exception) { } } else { DatabaseProcess.conn_close(); } //} //catch //{ // MessageBox.Show("Hata verdi"); //} }
private void lend_btn_Click(object sender, EventArgs e) { Boolean result; int user_id, student_id, book_id, expiry_date, recieve_case; string query = "INSERT INTO lend_book(id,student_id,book_id,recieve_by_id,lend_date,expiry_date)" + "values(new_id,{0},{1},{2},NOW()," + "DATE_ADD(NOW(),INTERVAL {3} DAY));"; string query2 = "SELECT recieve_case FROM lend_book WHERE book_id={0}"; user_id = Utils.User.id; student_id = list_student[student_index].Id; book_id = list_book[book_index].Id; query2 = string.Format(query2, book_id.ToString()); DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query2, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); if (dt_reader.Read()) { recieve_case = dt_reader.GetByte("recieve_case"); } else { recieve_case = 1; } DatabaseProcess.conn_close(); if (change_lend_time_rdbtn.Enabled == false) { expiry_date = Convert.ToInt32(lend_time_cmbx.Text); } else { expiry_date = 15; } if (student_index > -1 && book_index > -1 && recieve_case == 1) { query = string.Format(query, student_id, book_id, user_id, "15"); result = DatabaseProcess.insert(query, "lend_book"); if (result) { MessageBox.Show("Tamamlandı!"); } else { error_msg_lbl.Text = DatabaseProcess.error_msg; } } else { if (recieve_case == 0) { MessageBox.Show("Bu kitap şu an rafta değil!"); } else { error_msg_lbl.Text = "Lütfen gerekli alanları doldurunuz."; } } }
private void book_search_txtbx_TextChanged(object sender, EventArgs e) { // Keys ile arama çalışmıyo!! book_result_lstbx.Items.Clear(); int recieve_case; int id; string search, name, author, code; string query = "SELECT * FROM book WHERE {0}"; string search_with_name = "name LIKE '%{0}%' OR code LIKE '%{1}%' OR ISBN_number LIKE '%{2}%' OR author LIKE '%{3}%';"; string query2 = "SELECT recieve_case FROM lend_book WHERE book_id={0}" + " AND lend_date=(SELECT MAX(lend_date) FROM lend_book WHERE book_id={0});"; string query3; string[] temp; if (book_search_txtbx.Text.Length > 2) { list_book.Clear(); search = book_search_txtbx.Text; query = string.Format(query, search_with_name); query = string.Format(query, search, search, search, search); try { DatabaseProcess.conn_open(); MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn); MySqlDataReader dt_reader = cmd.ExecuteReader(); while (dt_reader.Read()) { id = dt_reader.GetInt32("id"); name = dt_reader.GetString("name").ToUpper(); author = dt_reader.GetString("author"); code = dt_reader.GetString("code"); list_book.Add(new book(id)); book_result_lstbx.Items.Add(name + " | " + author + " | " + code); } DatabaseProcess.conn_close(); } catch { } if (book_result_lstbx.Items.Count <= 0) { book_result_lstbx.Items.Add("Eşleşen kayıt yok"); } else if (book_search_txtbx.Text.Length > 2) { // Start with ">>>" substring which books on students temp = new string[list_book.Count]; for (int i = 0; i < list_book.Count; i++) { temp[i] = book_result_lstbx.Items[i].ToString(); } book_result_lstbx.Items.Clear(); for (int i = 0; i < list_book.Count; i++) { query3 = string.Format(query2, list_book[i].Id.ToString()); DatabaseProcess.conn_open(); MySqlCommand cmd2 = new MySqlCommand(query3, DatabaseProcess.myconn); MySqlDataReader dt_reader2 = cmd2.ExecuteReader(); if (dt_reader2.Read()) { recieve_case = dt_reader2.GetByte("recieve_case"); if (recieve_case == 0) { temp[i] = "<!> | " + temp[i]; } } book_result_lstbx.Items.Add(temp[i]); DatabaseProcess.conn_close(); } } }//if(book_search_txtbx.Text.Length > 2) else { book_result_lstbx.Items.Add("Arama için en az 3 karakter girmelisiniz."); } }