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));
}
