public List <Reg> GetRegs(Reg selectParameters) { string where = ""; if (selectParameters.Lname != null) { where += $@" UPPER(r.lname) LIKE '{selectParameters.Lname.ToUpper()}%'"; } if (selectParameters.Fname != null) { if (where.Length > 0) { where += " AND"; } where += $@" UPPER(r.fname) LIKE '{selectParameters.Fname.ToUpper()}%'"; } if (selectParameters.Pname != null) { if (where.Length > 0) { where += " AND"; } where += $@" UPPER(r.pname) LIKE '{selectParameters.Pname.ToUpper()}%'"; } if (selectParameters.Country != null) { if (where.Length > 0) { where += " AND"; } where += $@" UPPER(r.country) LIKE '%{selectParameters.Country.ToUpper()}%'"; } if (selectParameters.City != null) { if (where.Length > 0) { where += " AND"; } where += $@" UPPER(r.city) LIKE '%{selectParameters.City.ToUpper()}%'"; } if (selectParameters.Phone != null) { if (where.Length > 0) { where += " AND"; } where += $@" REPLACE(REPLACE(REPLACE(r.phone,'-',''),'(',''),')','') LIKE '%{selectParameters.Phone.ToUpper()}%'"; } if (selectParameters.StayWhere != null) { if (where.Length > 0) { where += " AND"; } where += $@" sw.name LIKE '{selectParameters.StayWhere}'"; } if (selectParameters.StayLength != null) { if (where.Length > 0) { where += " AND"; } where += $@" sl.name LIKE '{selectParameters.StayLength}'"; } if (where.Length > 0) { where = " WHERE " + where; } List <Reg> regs = new List <Reg>(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connect; cmd.CommandText = $@"SELECT r.id, r.numcard, r.lname, r.fname, r.pname, r.phone, r.birthday, sw.id, sw.name, sl.id, sl.name, r.country, r.city, n.id, n.name" + " FROM reg r" + " LEFT JOIN stay_where sw ON r.stay_where_id = sw.id" + " LEFT JOIN stay_length sl ON r.stay_length_id = sl.id" + " LEFT JOIN nutrition n ON r.nutrition_id = n.id" + where + " ORDER BY r.id"; using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Reg reg = new Reg() { Id = reader.GetInt32(0), Numcard = reader.GetValue(1).ToString(), Lname = reader.GetValue(2).ToString(), Fname = reader.GetValue(3).ToString(), Pname = reader.GetValue(4).ToString(), Phone = reader.GetValue(5).ToString(), Birthday = reader.GetValue(6) != DBNull.Value ? reader.GetDateTime(6) : (DateTime?)null, Age = reader.GetValue(6) != DBNull.Value ? Reg.GetAge(reader.GetDateTime(6)) : null, StayWhereId = reader.GetValue(7) != DBNull.Value ? reader.GetInt32(7) : 0, StayWhere = reader.GetValue(8).ToString(), StayLengthId = reader.GetValue(9) != DBNull.Value ? reader.GetInt32(7) : 0, StayLength = reader.GetValue(10).ToString(), Country = reader.GetValue(11).ToString(), City = reader.GetValue(12).ToString(), NutritionId = reader.GetValue(13) != DBNull.Value ? reader.GetInt32(7) : 0, Nutrition = reader.GetValue(14).ToString(), }; regs.Add(reg); } } } return(regs); }