public async Task <IActionResult> About()
{
List <EnrollmentDateGroupDto> groups = new List <EnrollmentDateGroupDto>();
// 获取数据库链接
var conn = _dbContext.Database.GetDbConnection();
try
{
await conn.OpenAsync(); // 打开数据库链接
using (var command = conn.CreateCommand())
{
string query = "SELECT EnrollmentDate,COUNT(*) AS StudentCount FROM Person Where Discriminator='Student' Group by EnrollmentDate";
command.CommandText = query;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroupDto
{
EnrollmentDate = reader.GetDateTime(0),
StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally { conn.Close(); }
return(View(groups));
}
public async Task <IActionResult> About()
{
//var data = from Student in _studentRepository.GetAll()
// group Student by Student.EnrollmentDate into dategroup
// select new EnrollmentDateGroupDto()
// {
// EnrollmentDate=dategroup.Key,
// StudentCount=dategroup.Count()
// };
//var dtos = await data.AsNoTracking().ToListAsync();
List <EnrollmentDateGroupDto> groups = new List <EnrollmentDateGroupDto>();
//获取数据库上下文连接
var conn = _dbContext.Database.GetDbConnection();
try
{
//打开数据库连接
await conn.OpenAsync();
//建立连接，因为非委托资源，所以需要释放
using (var command = conn.CreateCommand())
{
string query = $"SELECT School.Student.EnrollmentDate,COUNT(*) FROM School.Student GROUP BY School.Student.EnrollmentDate";
command.CommandText = query;
var reader = await command.ExecuteReaderAsync();
if (reader.HasRows) //判断是否有返回行
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroupDto
{
EnrollmentDate = reader.GetDateTime(0),
StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
await reader.DisposeAsync();
}
}
catch (Exception)
{
}
finally
{
conn.Close();
}
return(View(groups));
}
public async Task <ActionResult> About()
{
List <EnrollmentDateGroupDto> groups = new List <EnrollmentDateGroupDto>();
//获取数据库的上下文链接
var conn = _dbcontext.Database.GetDbConnection();
try
{ //打开数据库链接
await conn.OpenAsync();
//建立链接，因为非委托资源所以需要使用using进行内存资源的释放
using (var command = conn.CreateCommand())
{
string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount FROM Person WHERE Discriminator = 'Student' GROUP BY EnrollmentDate";
command.CommandText = query; //赋值需要执行的sql命令
DbDataReader reader = await command.ExecuteReaderAsync(); //执行命令
if (reader.HasRows)
{
//读取数据并填充到DTO中
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroupDto
{
EnrollmentDate = reader.GetDateTime(0),
StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
//释放使用的所有的资源
await reader.DisposeAsync();
}
}
finally
{
await conn.CloseAsync();
}
return(View(groups));
}
