public async Task <ListViewModel <PasswordViewModel> > GetPasswordsAsync(PasswordListInputModel model)
{
IQueryable <Passwords> BaseQuery = dbContext.Passwords;
switch (model.Orderby)
{
case "Id":
if (model.Ascending)
{
BaseQuery = BaseQuery.OrderBy(Var_Password => Var_Password.Id);
}
else
{
BaseQuery = BaseQuery.OrderByDescending(Var_Password => Var_Password.Id);
}
break;
case "Descrizione":
if (model.Ascending)
{
BaseQuery = BaseQuery.OrderBy(Var_Password => Var_Password.Descrizione);
}
else
{
BaseQuery = BaseQuery.OrderByDescending(Var_Password => Var_Password.Descrizione);
}
break;
case "Sito":
if (model.Ascending)
{
BaseQuery = BaseQuery.OrderBy(Var_Password => Var_Password.Sito);
}
else
{
BaseQuery = BaseQuery.OrderByDescending(Var_Password => Var_Password.Sito);
}
break;
}
IQueryable <Passwords> Qry_listapsw = BaseQuery
.Where(Var_password => Var_password.Descrizione.Contains(model.Search))
.AsNoTracking();
List <PasswordViewModel> listapsw = await Qry_listapsw
.Skip(model.Offset)
.Take(model.Limit)
.Select(var_Password => PasswordViewModel.FromEntity(var_Password))
.ToListAsync();
int totalCount = await Qry_listapsw.CountAsync();
ListViewModel <PasswordViewModel> result = new ListViewModel <PasswordViewModel>
{
Results = listapsw,
TotalCount = totalCount
};
return(result);
}
public async Task <IActionResult> index(PasswordListInputModel input)
{
ListViewModel <PasswordViewModel> passwords = await prop_PasswordService.GetPasswordsAsync(input);
PasswordListViewModel viewModel = new PasswordListViewModel
{
Passwords = passwords,
Input = input
};
ViewData["Title"] = "Lista Password";
return(View(viewModel));
}
public Task<ListViewModel<PasswordViewModel>> GetPasswordsAsync(PasswordListInputModel model)
{
bool canCache = model.Page <= 5 && string.IsNullOrEmpty(model.Search);
if (canCache)
{
return memoryChache.GetOrCreateAsync($"Passwords{model.Page} = {model.Orderby} = {model.Ascending}", cacheEntry => {
cacheEntry.SetSize(2);
cacheEntry.SetAbsoluteExpiration(TimeSpan.FromSeconds(60));
return prop_PasswordService.GetPasswordsAsync(model);
});
}
return prop_PasswordService.GetPasswordsAsync(model);
}
public Task BindModelAsync(ModelBindingContext bindingContext)
{
string search = bindingContext.ValueProvider.GetValue("Search").FirstValue;
string orderby = bindingContext.ValueProvider.GetValue("OrderBy").FirstValue;
int page = Convert.ToInt32(bindingContext.ValueProvider.GetValue("Page").FirstValue);
bool ascending = Convert.ToBoolean(bindingContext.ValueProvider.GetValue("Ascending").FirstValue);
PasswordsOptions Var_OpzioniPassword = opzioniPasswords.CurrentValue;
var inputModel = new PasswordListInputModel(search, page, orderby, ascending, (int)Var_OpzioniPassword.PerPage, Var_OpzioniPassword.Order);
bindingContext.Result = ModelBindingResult.Success(inputModel);
return(Task.CompletedTask);
}
public async Task <List <PasswordViewModel> > GetListUltimePasswordAsync()
{
PasswordListInputModel List_InputModel = new PasswordListInputModel(
search: "",
page: 1,
orderby: "Id",
ascending: false,
limit: (int)OpzioniPassword.CurrentValue.InHome,
orderPassword: OpzioniPassword.CurrentValue.Order
);
ListViewModel <PasswordViewModel> List_PassViewModel = await GetPasswordsAsync(List_InputModel);
return(List_PassViewModel.Results);
}
public async Task <ListViewModel <PasswordViewModel> > GetPasswordsAsync(PasswordListInputModel model)
{
string direction = model.Ascending ? "ASC" : "DESC";
FormattableString query = $@"SELECT * FROM Passwords where Descrizione LIKE {"%" + model.Search + "%"} ORDER BY {(Sql)model.Orderby} {(Sql)direction} LIMIT {model.Limit} OFFSET {model.Offset};
SELECT COUNT(*) FROM Passwords where Descrizione LIKE {"%" + model.Search + "%"} ";
DataSet dset = await db.QueryAsync(query);
var dtable = dset.Tables[0];
var listaPass = new List <PasswordViewModel>();
foreach (DataRow passRow in dtable.Rows)
{
PasswordViewModel pass = PasswordViewModel.FromDataRow(passRow);
listaPass.Add(pass);
}
ListViewModel <PasswordViewModel> result = new ListViewModel <PasswordViewModel>
{
Results = listaPass,
TotalCount = Convert.ToInt32(dset.Tables[1].Rows[0][0])
};
return(result);
}
