public static List <CPostal> Search(string searchString, int limit, int offset) { string sql = new StringBuilder(BSql) .Append(" WHERE ") .Append(searchString) .ToString(); List <MySqlParameter> lp = new List <MySqlParameter>(); return(CPostal.ExecuteQuery(sql, lp, limit, offset)); }
public static List <CPostal> SerachByCliente(string cliente, int limit, int offset) { string sql = new StringBuilder(BSql) .Append(" WHERE ") .Append(string.Format("(`{0}` like @{0})", CPostal.Column.cliente.ToString())) .ToString(); List <MySqlParameter> lp = new List <MySqlParameter>(); lp.Add(new MySqlParameter("@" + CPostal.Column.cliente.ToString(), "%" + cliente.Trim().Replace(' ', '%') + "%")); return(CPostal.ExecuteQuery(sql, lp, limit, offset)); }
/// <summary> /// Get a client from database /// </summary> /// <param name="client"></param> /// <returns></returns> public static List <CPostal> GetClient(string client) { string sql = new StringBuilder(BSql) .Append(" WHERE ") .Append(string.Format("(`{0}` = @{0})", CPostal.Column.cliente.ToString())) .ToString(); List <MySqlParameter> lp = new List <MySqlParameter>(); lp.Add(new MySqlParameter("@" + CPostal.Column.cliente.ToString(), client)); return(CPostal.ExecuteQuery(sql, lp, 1, 0)); }
public static List <CPostal> SearchByCpostal(string cp, int limit, int offset) { string p1 = "p"; string sql = new StringBuilder(BSql) .Append(" WHERE ") .Append(string.Format("(CONCAT(`{0}`,`{1}`) like @{2})", CPostal.Column.cp4, CPostal.Column.cp3, p1)) .ToString(); List <MySqlParameter> lp = new List <MySqlParameter>(); lp.Add(new MySqlParameter("@" + p1, "%" + Regex.Replace(cp.Trim().Replace("-", "%").Replace(" ", "%"), @"\s?\D?", "") + "%")); return(CPostal.ExecuteQuery(sql, lp, limit, offset)); }
public static List <CPostal> SearchByDisAddress(string distrito, string address, int limit, int offset) { string sql = new StringBuilder(BSql) .Append(" WHERE ") .Append(string.Format("(`{0}` like @{0})", CPostal.Column.morada.ToString())) .Append(" AND ") .Append(string.Format("(`{0}` = @{0})", CPostal.Column.distrito.ToString())) .ToString(); List <MySqlParameter> lp = new List <MySqlParameter>(); lp.Add(new MySqlParameter("@" + CPostal.Column.morada.ToString(), "%" + address.Trim().Replace(' ', '%') + "%")); lp.Add(new MySqlParameter("@" + CPostal.Column.distrito.ToString(), distrito)); return(CPostal.ExecuteQuery(sql, lp, limit, offset)); }
/// <summary> /// Serach fpr distrito /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="limit"></param> /// <param name="offset"></param> /// <returns></returns> protected static List <CPostal> ExecuteQuery(string sql, List <MySqlParameter> param, int limit, int offset) { List <CPostal> l = new List <CPostal>(); CpMySql my = new CpMySql(); MySqlDataReader r = my.ExecuteCommand(sql, param, limit, offset); while (r.Read()) { CPostal c = new CPostal { art_desig = r.GetString(r.GetOrdinal(CPostal.Column.art_desig.ToString())), art_local = r.GetString(r.GetOrdinal(CPostal.Column.art_local.ToString())), art_tipo = r.GetString(r.GetOrdinal(CPostal.Column.art_tipo.ToString())), art_titulo = r.GetString(r.GetOrdinal(CPostal.Column.art_titulo.ToString())), cc = r.GetString(r.GetOrdinal(CPostal.Column.cc.ToString())), cliente = r.GetString(r.GetOrdinal(CPostal.Column.cliente.ToString())), cod_arteria = r.GetString(r.GetOrdinal(CPostal.Column.cod_arteria.ToString())), cod_localidade = r.GetString(r.GetOrdinal(CPostal.Column.cod_localidade.ToString())), concelho = r.GetString(r.GetOrdinal(CPostal.Column.concelho.ToString())), cp3 = r.GetString(r.GetOrdinal(CPostal.Column.cp3.ToString())), cp4 = r.GetString(r.GetOrdinal(CPostal.Column.cp4.ToString())), cpalf = r.GetString(r.GetOrdinal(CPostal.Column.cpalf.ToString())), dd = r.GetString(r.GetOrdinal(CPostal.Column.dd.ToString())), distrito = r.GetString(r.GetOrdinal(CPostal.Column.distrito.ToString())), localidade = r.GetString(r.GetOrdinal(CPostal.Column.localidade.ToString())), morada = r.GetString(r.GetOrdinal(CPostal.Column.morada.ToString())), porta = r.GetString(r.GetOrdinal(CPostal.Column.porta.ToString())), pri_prep = r.GetString(r.GetOrdinal(CPostal.Column.pri_prep.ToString())), seg_prep = r.GetString(r.GetOrdinal(CPostal.Column.seg_prep.ToString())), troco = r.GetString(r.GetOrdinal(CPostal.Column.troco.ToString())) }; l.Add(c); } my.Command.Dispose(); r.Close(); my.Conection.Close(); return(l); }