public IList <MailClass> List(string sortOrder, System.Web.Helpers.SortDirection sortDir, int page, int pagesize, out int count) { List <MailClass> mails = new List <MailClass>(); using (MySqlConnection objConnect = new MySqlConnection(Base.strConnect)) { string sort = " ORDER BY "; if (sortOrder != null && sortOrder != String.Empty) { sort += "`" + sortOrder + "`"; if (sortDir == System.Web.Helpers.SortDirection.Descending) { sort += " DESC"; } sort += ","; } sort += "`Idmail`"; // по умолчанию string limit = ""; if (pagesize > 0) { int start = (page - 1) * pagesize; limit = string.Concat(" LIMIT ", start.ToString(), ", ", pagesize.ToString()); } string strSQL = "SELECT SQL_CALC_FOUND_ROWS u.`Idmail`, u.`Title`, u.`Regdate`, u.`AdressEmail`, u.`SenderEmail`, u.`Tags`, u.`MailText`, FROM `maildb` AS u" + sort + limit; using (MySqlCommand cmd = new MySqlCommand(strSQL, objConnect)) { objConnect.Open(); cmd.Parameters.Add("page", MySqlDbType.Int32).Value = page; cmd.Parameters.Add("pagesize", MySqlDbType.Int32).Value = pagesize; using (MySqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { mails.Add(new MailClass( mailid: dr.GetInt32("Idmail"), title: dr.GetString("Title"), snd: dr.IsDBNull(dr.GetOrdinal("SenderEmail")) ? String.Empty : dr.GetString("SenderEmail"), adr: dr.IsDBNull(dr.GetOrdinal("AdressEmail")) ? String.Empty : dr.GetString("AdressEmail"), regdate: dr.IsDBNull(dr.GetOrdinal("Regdate")) ? (DateTime?)null : dr.GetDateTime("Regdate"), tags: dr.IsDBNull(dr.GetOrdinal("Tags")) ? String.Empty : dr.GetString("Tags"), text: dr.IsDBNull(dr.GetOrdinal("MailText")) ? String.Empty : dr.GetString("MailText"))); } } } using (MySqlCommand cmdrows = new MySqlCommand("SELECT FOUND_ROWS()", objConnect)) { int.TryParse(cmdrows.ExecuteScalar().ToString(), out count); } } return(mails); }
public static string parseSortForDB(string sortOrder, out string sortName, out System.Web.Helpers.SortDirection sortDir) {// проверка и разбор параметра для сортировки sortName = null; sortDir = System.Web.Helpers.SortDirection.Ascending; if (sortOrder != null && sortOrder != string.Empty) {// оставляем только буквы/цифры/_ System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("[^\\w]"); bool a = regex.IsMatch(sortOrder); sortOrder = regex.Replace(sortOrder, ""); // разбиваем на части - название столбца и направление string[] sort = sortOrder.Split('_'); if (sort.Length > 0) { sortName = sort[0]; } if (sort.Length > 1) { if (sort[1] == "desc") { sortDir = System.Web.Helpers.SortDirection.Descending; } } } if (sortName != null && sortName != string.Empty) { sortOrder = sortName; if (sortDir == System.Web.Helpers.SortDirection.Descending) { sortOrder += " desc"; } } else { sortOrder = ""; sortDir = System.Web.Helpers.SortDirection.Ascending; } return(sortOrder); }
// отображение списка пользователей public ViewResult Index(string sortOrder, int page = 1) { string sortName = null; System.Web.Helpers.SortDirection sortDir = System.Web.Helpers.SortDirection.Ascending; sortOrder = Base.parseSortForDB(sortOrder, out sortName, out sortDir); MailRepository rep = new MailRepository(); MailGrid users = new MailGrid { Mail = rep.List(sortName, sortDir, page, pageSize, out count), PagingInfo = new PagingInfo { currentPage = page, itemsPerPage = pageSize, totalItems = count, showPages = showPages }, SortingInfo = new SortingInfo { currentOrder = sortName, currentDirection = sortDir } }; return(View(users)); }