public List <Member> GetAllMembers()
{
List <Member> members = new List <Member>();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText = @"select MemberId, Name, Surname, Patronymic, Phone_number, Members.Position_id,
Members.Photo_path, Position_name, Sex, Note from Members join positions
on (positions.Position_id = Members.Position_id)";
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int memberId = reader.GetInt32(0);
string name = reader.GetString(1);
string surname = reader.GetString(2);
string patr = reader.GetString(3);
string phone = reader.GetString(4);
int positionId = reader.GetInt32(5);
string photopath = reader.GetString(6);
string positionName = reader.GetString(7);
string sex = reader.GetString(8);
string note = reader[9] == DBNull.Value ? null: reader.GetString(9);
PhotoClass photo = new PhotoClass(photopath);
Position position = new Position(positionId, positionName);
Member member = new Member(memberId, name, surname, patr, phone, sex, position, photo, note);
members.Add(member);
}
}
return(members);
}
public List <Member> GetAllMembersFound(string nameOrsurOrpatr, string position_name)
{
List <Member> members = new List <Member>();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "find_member";
//command.CommandText = $"select * from Members join positionson Members.Position_id = positions.Position_id " +
// $"where (Name like(N'%{nameOrsurOrpatr}%') or Surname like(N'%{nameOrsurOrpatr}%') or Patronymic like(N'%{nameOrsurOrpatr}%')) and Position_name Like(N'%{position_name}%')";
command.Parameters.Add("@name", SqlDbType.NVarChar, 50);
command.Parameters.Add("@position_name", SqlDbType.NVarChar, 50);
command.Parameters["@name"].Value = $"{nameOrsurOrpatr}";
command.Parameters["@position_name"].Value = $"{position_name}";
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int memberId = reader.GetInt32(0);
string name = reader.GetString(1);
string surname = reader.GetString(2);
string patr = reader.GetString(3);
string phone = reader.GetString(4);
int positionId = reader.GetInt32(5);
string photopath = reader.GetString(6);
string positionName = reader.GetString(7);
string sex = reader.GetString(8);
string note = reader[9] == DBNull.Value ? null: reader.GetString(9);
PhotoClass photo = new PhotoClass(photopath);
Position position = new Position(positionId, positionName);
Member member = new Member(memberId, name, surname, patr, phone, sex, position, photo, note);
members.Add(member);
}
}
return(members);
}
public Member(int id = 0, string name = null, string surname = null, string patronymic = null, string phoneNum = null, string sex = null, Position position = null, PhotoClass photo = null, string note = null) : this()
{
this.id = id;
this.name = name;
this.surname = surname;
this.patronymic = patronymic;
this.phoneNum = phoneNum;
this.sex = sex;
this.position = position;
this.photo = photo;
this.note = note;
fullInfo = $"{surname} {name.Substring(0, 1)}.{Patronymic.Substring(0, 1)}";
}
void InitClassObjects()
{
Members = new ObservableCollection <Member>(new Member().GetMembers());
photoClass = new PhotoClass(null);
createdMember = new Member();
}
