public IEnumerable <StudentTicketBLL> searchBy(StudentTicketBLL student) { IEnumerable <StudentTicketDAL> listDal = new List <StudentTicketDAL>(); StudentTicketDAL searchPattern = new StudentTicketDAL { lastName = student.lastName, name = student.name, patronimic = student.patronimic, number = student.number, serial = student.serial, facult = student.facult, kurs = student.kurs, group = student.group, speciality = student.speciality, roomNumber = student.roomNumber }; PGUserRepository repository = repositoryFactory.getUserRepository(); listDal = (repository.searchBy(searchPattern) as IEnumerable <StudentTicketDAL>); return(listDal.Select(s => new StudentTicketBLL { lastName = s.lastName, name = s.name, number = s.number, patronimic = s.patronimic, serial = s.serial, facult = s.facult, group = s.group, kurs = s.kurs, speciality = s.speciality, roomNumber = s.roomNumber }).AsEnumerable()); }
public StudentTicketBLL searchBySerial(StudentTicketBLL student) { StudentTicketDAL stDal = new StudentTicketDAL { serial = student.serial, number = student.number, lastName = student.lastName, name = student.name, patronimic = student.patronimic, kurs = student.kurs, facult = student.facult, group = student.group, speciality = student.speciality, roomNumber = student.roomNumber }; StudentTicketDAL resultStud; PGUserRepository repository = repositoryFactory.getUserRepository(); resultStud = repository.findBySerial(stDal); return(new StudentTicketBLL { serial = resultStud.serial, number = resultStud.number, lastName = resultStud.lastName, name = resultStud.name, patronimic = resultStud.patronimic, kurs = resultStud.kurs, facult = resultStud.facult, group = resultStud.group, speciality = resultStud.speciality, roomNumber = resultStud.roomNumber }); }
private static StudentTicketDAL fromReaderToStudent(NpgsqlDataReader reader) { var stud = new StudentTicketDAL(); if (reader.HasRows) { stud = new StudentTicketDAL { lastName = clearFromSpaces(Convert.ToString(reader["u_last_name"])), name = clearFromSpaces(Convert.ToString(reader["u_name"])), kurs = Convert.ToInt32(reader["kurs"]), facult = clearFromSpaces(Convert.ToString(reader["facult"])), speciality = clearFromSpaces(Convert.ToString(reader["spec"])), group = Convert.ToInt32(reader["s_group"]), number = clearFromSpaces(Convert.ToString(reader["u_number"])), serial = clearFromSpaces(Convert.ToString(reader["u_serial"])), roomNumber = Convert.ToInt32(reader["room_num"]) }; if (reader["u_patr"] != DBNull.Value) { stud.patronimic = clearFromSpaces(Convert.ToString(reader["u_patr"])); } } return(stud); }
public StudentTicketDAL findBySerial(StudentTicketDAL student) { string searchQuery = "SELECT u_serial,u_number,u_last_name,u_name,u_patr, kurs,facult,spec,s_group,room_num FROM stud_view WHERE u_serial=@serial AND u_number=@number"; using (NpgsqlConnection conn = new NpgsqlConnection(connectionString)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(searchQuery, conn); cmd.Parameters.AddWithValue("@serial", student.serial); cmd.Parameters.AddWithValue("@number", student.number); using (NpgsqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); return(fromReaderToStudent(reader)); } } }
public void deleteUser(StudentTicketBLL student) { PGUserRepository repository = repositoryFactory.getUserRepository(); StudentTicketDAL studentDAL = new StudentTicketDAL { serial = student.serial, number = student.number, lastName = student.lastName, name = student.name, patronimic = student.patronimic, kurs = student.kurs, speciality = student.speciality, facult = student.facult, group = student.group, roomNumber = student.roomNumber }; repository.removeBySerial(studentDAL); }
public void updateData(StudentTicketBLL updatedStudent) { StudentTicketDAL sDal = new StudentTicketDAL { name = updatedStudent.name, lastName = updatedStudent.lastName, patronimic = updatedStudent.patronimic, number = updatedStudent.number, serial = updatedStudent.serial, kurs = updatedStudent.kurs, facult = updatedStudent.facult, group = updatedStudent.group, speciality = updatedStudent.speciality, roomNumber = updatedStudent.roomNumber }; PGUserRepository repository = repositoryFactory.getUserRepository(); repository.updateInfo(sDal); }
public void addUser(StudentTicketBLL student) { StudentTicketDAL studDAL = new StudentTicketDAL() { lastName = student.lastName, name = student.name, patronimic = student.patronimic, number = student.number, serial = student.serial, facult = student.facult, kurs = student.kurs, group = student.group, speciality = student.speciality, roomNumber = student.roomNumber }; PGUserRepository repository = repositoryFactory.getUserRepository(); repository.addUser(studDAL); }
public void updateInfo(StudentTicketDAL updatedStudent) { string updateQuery = "UPDATE students " + "SET u_last_name=@last_name," + "u_name=@name, " + "u_patr=@patr, " + "kurs=@kurs, " + "spec=@spec, " + "facult=@facult, " + "s_group=@group " + "WHERE u_serial=@serial AND u_number=@number"; using (NpgsqlConnection conn = new NpgsqlConnection(connectionString)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(updateQuery, conn); cmd.Parameters.AddWithValue("@last_name", updatedStudent.lastName); cmd.Parameters.AddWithValue("@name", updatedStudent.name); cmd.Parameters.AddWithValue("@kurs", updatedStudent.kurs); cmd.Parameters.AddWithValue("@spec", updatedStudent.speciality); cmd.Parameters.AddWithValue("@facult", updatedStudent.facult); cmd.Parameters.AddWithValue("@group", updatedStudent.group); cmd.Parameters.AddWithValue("@serial", updatedStudent.serial); cmd.Parameters.AddWithValue("@number", updatedStudent.number); if (updatedStudent.patronimic != null) { cmd.Parameters.AddWithValue("@patr", updatedStudent.patronimic); } else { cmd.Parameters.AddWithValue("@patr", DBNull.Value); } try { cmd.ExecuteNonQuery(); MessageBox.Show("Информация успешно обновлена:", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("Ошибка обновления записи в БД! Сообщение:" + ex.Message.Substring(6, ex.Message.Length - 6), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
public void removeBySerial(StudentTicketDAL student) { string delQuery = "SELECT remove_stud(@u_serial,@u_number)"; using (NpgsqlConnection conn = new NpgsqlConnection(connectionString)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(delQuery, conn); cmd.Parameters.AddWithValue("@u_serial", student.serial); cmd.Parameters.AddWithValue("@u_number", student.number); try { cmd.ExecuteNonQuery(); MessageBox.Show("Студент успешно удален из БД", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("Ошибка удаления записи БД! Сообщение:" + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
public void resettleStudent(StudentTicketDAL student) { string query = "SELECT resettle(@serial,@number,@room)"; using (NpgsqlConnection conn = new NpgsqlConnection(connectionString)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(query, conn); cmd.Parameters.AddWithValue("@serial", student.serial); cmd.Parameters.AddWithValue("@number", student.number); cmd.Parameters.AddWithValue("@room", student.roomNumber); try { cmd.ExecuteNonQuery(); MessageBox.Show("Студент успешно переселен!", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("Ошибка переселения! Текст ошибки:" + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
public void addUser(StudentTicketDAL student) { string addQuery = "SELECT settling('С',@last_name,@name,@patr,@kurs,@facult,@spec,@group,@serial,@number,@room)"; using (NpgsqlConnection conn = new NpgsqlConnection(connectionString)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(addQuery, conn); cmd.Parameters.AddWithValue("@serial", student.serial); cmd.Parameters.AddWithValue("@number", student.number); cmd.Parameters.AddWithValue("@last_name", student.lastName); cmd.Parameters.AddWithValue("@name", student.name); cmd.Parameters.AddWithValue("@kurs", student.kurs); cmd.Parameters.AddWithValue("@facult", student.facult); cmd.Parameters.AddWithValue("@spec", student.speciality); cmd.Parameters.AddWithValue("@group", student.group); cmd.Parameters.AddWithValue("@room", student.roomNumber); if (student.patronimic != null) { cmd.Parameters.AddWithValue("@patr", student.patronimic); } else { cmd.Parameters.AddWithValue("@patr", DBNull.Value); } try { cmd.ExecuteNonQuery(); MessageBox.Show("Студент успешно добавлен", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("Ошибка добавления записи в БД! Сообщение:" + ex.Message.Substring(6, ex.Message.Length - 6), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
public IEnumerable <StudentTicketDAL> searchBy(StudentTicketDAL student) { List <StudentTicketDAL> list = new List <StudentTicketDAL>(); string getQuery = "SELECT u_serial,u_number,u_last_name,u_name,u_patr, kurs,facult,spec,s_group,room_num FROM stud_view "; //дополнить поиск по вхождению подстрок SIMILAR TO bool hasPrev = false; #region LastName if (!string.IsNullOrWhiteSpace(student.lastName)) { getQuery += "WHERE u_last_name SIMILAR TO '%" + student.lastName.ToString() + "%' "; hasPrev = true; } #endregion #region Name if (!string.IsNullOrWhiteSpace(student.name)) { if (hasPrev) { getQuery += " AND "; getQuery += " u_name SIMILAR TO'%" + student.name.ToString() + "%'"; } else { getQuery += "WHERE u_name SIMILAR TO '%" + student.name.ToString() + "%'"; } hasPrev = true; } #endregion #region Patr if (!string.IsNullOrWhiteSpace(student.patronimic)) { if (hasPrev) { getQuery += " AND "; getQuery += " u_patr SIMILAR TO '%" + student.patronimic.ToString() + "%' "; } else { getQuery += "WHERE u_patr='%" + student.patronimic.ToString() + "%' "; } hasPrev = true; } #endregion #region Faculty if (!string.IsNullOrWhiteSpace(student.facult)) { if (hasPrev) { getQuery += " AND "; getQuery += " facult SIMILAR TO '%" + student.facult.ToString() + "%' "; } else { getQuery += "WHERE facult SIMILAR TO '%" + student.facult.ToString() + "%' "; } hasPrev = true; } #endregion #region Kurs if (student.kurs > 0) { if (hasPrev) { getQuery += " AND "; getQuery += " kurs='" + student.kurs + "' "; } else { getQuery += "WHERE kurs='" + student.kurs + "' "; } hasPrev = true; } #endregion #region Speciality if (!string.IsNullOrWhiteSpace(student.speciality)) { if (hasPrev) { getQuery += " AND "; getQuery += " spec SIMILAR TO '%" + student.speciality + "%' "; } else { getQuery += "WHERE spec SIMILAR TO '%" + student.speciality + "%' "; } hasPrev = true; } #endregion #region Group if (student.group > 0) { if (hasPrev) { getQuery += " AND "; getQuery += " s_group='" + student.group + "' "; } else { getQuery += "WHERE s_group='" + student.group + "' "; } hasPrev = true; } #endregion #region Serial if (!string.IsNullOrWhiteSpace(student.serial)) { if (hasPrev) { getQuery += " AND "; getQuery += " u_serial='" + student.serial + "' "; } else { getQuery += "WHERE u_serial='" + student.serial + "' "; } hasPrev = true; } #endregion #region Number if (!string.IsNullOrWhiteSpace(student.number)) { if (hasPrev) { getQuery += " AND "; getQuery += " u_number='" + student.number + "' "; } else { getQuery += "WHERE u_number='" + student.number + "' "; } hasPrev = true; } #endregion #region Room if (student.roomNumber > 0) { if (hasPrev) { getQuery += " AND "; getQuery += " room_num=" + student.roomNumber + " "; } else { getQuery += "WHERE room_num=" + student.roomNumber + " "; } hasPrev = true; } #endregion using (NpgsqlConnection conn = new NpgsqlConnection(connectionString)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(getQuery, conn); using (NpgsqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { list.Add(fromReaderToStudent(reader)); } } } return(list.AsEnumerable()); }