public async Task DatabaseQuery(CommandContext ctx,
[RemainingText, Description("SQL Query.")] string query)
{
if (string.IsNullOrWhiteSpace(query))
{
throw new InvalidCommandUsageException("Query missing.");
}
var res = new List <IReadOnlyDictionary <string, string> >();
using (DatabaseContext db = this.Database.CreateContext())
using (Microsoft.EntityFrameworkCore.Storage.RelationalDataReader dr = await db.Database.ExecuteSqlQueryAsync(query)) {
DbDataReader reader = dr.DbDataReader;
while (await reader.ReadAsync())
{
var dict = new Dictionary <string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
dict[reader.GetName(i)] = reader[i] is DBNull ? "<null>" : reader[i].ToString();
}
res.Add(new ReadOnlyDictionary <string, string>(dict));
}
}
if (!res.Any() || !res.First().Any())
{
await this.InformAsync(ctx, StaticDiscordEmoji.Information, "No results returned (this is alright if your query wasn't a SELECT query).");
return;
}
int maxlen = 1 + res
.First()
.Select(r => r.Key)
.OrderByDescending(r => r.Length)
.First()
.Length;
await ctx.SendCollectionInPagesAsync(
"Query results",
res.Take(25),
row => {
var sb = new StringBuilder();
foreach ((string col, string val) in row)
{
sb.Append(col).Append(new string(' ', maxlen - col.Length)).Append("| ").AppendLine(val);
}
return(Formatter.BlockCode(sb.ToString()));
},
this.ModuleColor,
1
);
}
public IActionResult Index()
{
List <Models.TblMembers> _TblMembers =
UnitOfWork.Repository <DevelopersHub.Models.TblMembers>().GetAll().ToList <Models.TblMembers>();
int _Count = _TblMembers.Count();
QueryParameters _queryparameters = new QueryParameters();
_queryparameters.SqlSourceObjectName = "Tbl_Members";
_queryparameters.PageIndex = 0;
_queryparameters.PageSize = 11;
Microsoft.EntityFrameworkCore.Storage.RelationalDataReader
_RelationalDataReader =
UnitOfWork.GetResult <DevelopersHub.Models.TblMembers>(_queryparameters);
IndexViewModel __indexViewModel = new ViewModels.IndexViewModel();
__indexViewModel.Members = new List <TblMembers>();
while (
_RelationalDataReader.DbDataReader.Read())
{
TblMembers tblmembers = new TblMembers();
tblmembers.Fname = _RelationalDataReader.DbDataReader["FName"].ToString();
tblmembers.Sname = _RelationalDataReader.DbDataReader["SName"].ToString();
tblmembers.Id = Convert.ToInt32(_RelationalDataReader.DbDataReader["Id"].ToString());
__indexViewModel.Members.Add(tblmembers);
}
UnitOfWork.CloseReader();
List <TblProposals> __list_proposals = UnitOfWork.Repository <TblProposals>().GetAll().ToList();
__indexViewModel.Proposals = __list_proposals;
foreach (TblProposals item in __indexViewModel.Proposals)
{
item.M =
UnitOfWork.Repository <DevelopersHub.Models.TblMembers>().Get(x => x.Id == item.Id);
}
ViewModel = __indexViewModel;
ViewData["Message"] = "Forums " + _Count.ToString();
return(View(__indexViewModel));
}
public IActionResult MemberProposals(int Id)
{
TblMembers _member = UnitOfWork.Repository <DevelopersHub.Models.TblMembers>().Get(x => x.Id == Id);
//List<TblProposals> _list_TblProposals =
//UnitOfWork.Repository<DevelopersHub.Models.TblProposals>().GetAll(x => x.Mid == MemberId).ToList();
//int _Count = _TblMembers.Count();
QueryParameters _queryparameters = new QueryParameters();
_queryparameters.SqlSourceObjectName = "Tbl_Proposals";
_queryparameters.Where = "mid=@mid";
_queryparameters.SqlParams.Add(new System.Data.SqlClient.SqlParameter("@mid", Id));
_queryparameters.PageIndex = Convert.ToInt32(RouteParams["Page"].value);
_queryparameters.PageSize = 2;
Microsoft.EntityFrameworkCore.Storage.RelationalDataReader
_RelationalDataReader =
UnitOfWork.GetResult <DevelopersHub.Models.TblProposals>(_queryparameters);
MemberProposalsViewModel __MemberProposalsViewModel = new ViewModels.MemberProposalsViewModel();
__MemberProposalsViewModel.PageCount = _queryparameters.PageCount;
__MemberProposalsViewModel.PageNumber = Convert.ToInt32(RouteParams["Page"].value);
__MemberProposalsViewModel.Proposals = new List <TblProposals>();
ViewModel = __MemberProposalsViewModel;
while (
_RelationalDataReader.DbDataReader.Read())
{
TblProposals tblproposals = new TblProposals();
tblproposals.Title = _RelationalDataReader.DbDataReader["Title"].ToString();
tblproposals.Technologies = _RelationalDataReader.DbDataReader["Technologies"].ToString();
tblproposals.SnapshotFile = _RelationalDataReader.DbDataReader["SnapshotFile"].ToString();
tblproposals.Id = Convert.ToInt32(_RelationalDataReader.DbDataReader["Id"].ToString());
__MemberProposalsViewModel.Proposals.Add(tblproposals);
}
__MemberProposalsViewModel.Member = _member;
return(View(__MemberProposalsViewModel));
}
public IActionResult Index()
{
List <Models.TblMembers> _TblMembers =
UnitOfWork.Repository <DevelopersHub.Models.TblMembers>().GetAll().ToList <Models.TblMembers>();
int _Count = _TblMembers.Count();
QueryParameters _queryparameters = new QueryParameters();
_queryparameters.SqlSourceObjectName = "Tbl_Members";
_queryparameters.PageIndex = Convert.ToInt32(RouteParams["Page"].value);;
_queryparameters.PageSize = 2;
Microsoft.EntityFrameworkCore.Storage.RelationalDataReader
_RelationalDataReader =
UnitOfWork.GetResult <DevelopersHub.Models.TblMembers>(_queryparameters);
MembersViewModel __membersViewModel = new ViewModels.MembersViewModel();
__membersViewModel.PageCount = _queryparameters.PageCount;
__membersViewModel.PageNumber = Convert.ToInt32(RouteParams["Page"].value);
__membersViewModel.Members = new List <TblMembers>();
ViewModel = __membersViewModel;
while (
_RelationalDataReader.DbDataReader.Read())
{
TblMembers tblmembers = new TblMembers();
tblmembers.Fname = _RelationalDataReader.DbDataReader["FName"].ToString();
tblmembers.Sname = _RelationalDataReader.DbDataReader["SName"].ToString();
tblmembers.Bdate = _RelationalDataReader.DbDataReader["Bdate"].ToString();
tblmembers.Id = Convert.ToInt32(_RelationalDataReader.DbDataReader["Id"].ToString());
__membersViewModel.Members.Add(tblmembers);
}
ViewData["Message"] = "Forums " + _Count.ToString();
return(View(__membersViewModel));
}
