public async Task <IActionResult> About()
{
var groups = new List <EnrollmentDateGroup>();
var conn = _context.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;
using var reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
}
finally
{
conn.Close();
}
return(View(groups));
}
public async Task <IActionResult> About()
{
ViewData["Message"] = "学生统计信息";
var groups = new List <EnrollmentDateGroup>();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
string sqlQuery = @"select EnrollmentDate, COUNT(*) as StudentCount from person where Discriminator='Student' group by EnrollmentDate";
command.CommandText = sqlQuery;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup()
{
EnrollmenDate = reader.GetDateTime(0),
StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(groups));
//var entities = from entity in _context.Students
// group entity by entity.EnrollmentDate
// into dateGroup
// select new EnrollmentDateGroup()
// {
// EnrollmenDate = dateGroup.Key,
// StudentCount = dateGroup.Count()
// };
//var dtos = await entities.AsNoTracking().ToListAsync();
//return View(dtos);
}
public async Task <ActionResult> About()
{
//Method1
//IQueryable<EnrollmentDateGroup> data =
// from student in _context.Students
// group student by student.EnrollmentDate into dateGroup
// select new EnrollmentDateGroup()
// {
// EnrollmentDate = dateGroup.Key,
// StudentCount = dateGroup.Count()
// };
//return View(await data.AsNoTracking().ToListAsync());
//Method2
//IQueryable<EnrollmentDateGroup> data = _context.Students.GroupBy(c => c.EnrollmentDate).
// Select(x => new EnrollmentDateGroup
// {
// EnrollmentDate = x.Key,
// StudentCount = x.Count()
// });
//return View(await data.AsNoTracking().ToListAsync());
//Method3
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
+ "FROM Student "
+ "GROUP BY EnrollmentDate";
command.CommandText = query;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(groups));
}
private async Task <ActionResult> TestRawSql2(int?id)
{
if (id == null)
{
return(NotFound());
}
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
string query2 = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
+ "FROM Person "
+ "WHERE Discriminator = 'Student' "
+ "GROUP BY EnrollmentDate";
command.CommandText = query2;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
string query = "select * from Department where DepartmentID={0}";
var department = await _context.Departments
.FromSql(query, id)
.Include(d => d.Administrator)
.AsNoTracking()
.SingleOrDefaultAsync();
if (department == null)
{
return(NotFound());
}
return(View(department));
}
public async Task <IActionResult> About()
{
ViewData["Message"] = "学生统计信息";
var groups = new List <EnrollmentDateGroup>();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
string sqlQuery = @"SELECT EnrollmentDate,COUNT(*) AS StudentCount FROM dbo.Person WHERE Discriminator='Student' GROUP BY EnrollmentDate";
command.CommandText = sqlQuery;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup()
{
EnrollmentDate = reader.GetDateTime(0),
StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
}
}
catch (System.Exception)
{
throw;
}
finally
{
conn.Close();
}
return(View(groups));
//var entities = from student in _context.Students
// group student by student.EnrollmentDate into dateGroup
// select new EnrollmentDateGroup()
// {
// EnrollmentDate = dateGroup.Key,
// StudentCount = dateGroup.Count()
// };
//var dtos = await entities.AsNoTracking().ToListAsync();
//return View(dtos);
}
public async Task <IActionResult> About()
{
/*IQueryable<EnrollmentDateGroup> data =
* from student in _context.Students
* group student by student.EnrollmentDate into dateGroup
* select new EnrollmentDateGroup()
* {
* EnrollmentDate = dateGroup.Key,
* StudentCount = dateGroup.Count()
* };
*
* return View(await data.AsNoTracking().ToListAsync());*/
//tak samo z sql:
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
var conn = _context.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 EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(groups));
}
// dykstra3
// controller for the about page
// the code first select student count and year rank, instead of enrollment date in the tutorial
// then use IQueyable to group student's count by year rank
public async Task <ActionResult> About()
{
// group student by their year rank
// store the list into a collection of EnrollementDateGroup view model
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
// conn to connect with Data tables
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync(); // open the connection with database to get entity value
// create a variable for command
using (var command = conn.CreateCommand())
{
// query to group student by their year rank
string query = "SELECT YearRank, COUNT(*) AS StudentCount " // select year rank column and count how many student in a rank
+ "FROM Person " // from person table
+ "WHERE Discriminator = 'Student' " // all people who are 'student' are selected
+ "GROUP BY YearRank"; // group by year rank
command.CommandText = query; // let the system know that string 'query' is a query
// send the CommandText to the connection and builds a SqlDataReader
DbDataReader reader = await command.ExecuteReaderAsync();
// if it reads that there is a table with rows
if (reader.HasRows)
{
// then add rows YearRank and Student Count
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup {
YearRank = reader.GetString(0), StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose(); // releasing unmanaged resourses to improve performance
}
}
// if there are not rows, then the connection with database will be closed and all actions are not successful
finally
{
conn.Close();
}
// configure the view for model
return(View(groups));
}
public async Task <IActionResult> About()
{
/*
* // The LINQ statement groups the student entities by enrollment date,
* // calculates the number of entities in each group, and stores the results
* // in a collection of EnrollmentDateGroup view model objects.
* IQueryable<EnrollmentDateGroup> data =
* from student in _context.Students
* group student by student.EnrollmentDate into dateGroup
* select new EnrollmentDateGroup()
* {
* EnrollmentDate = dateGroup.Key,
* StudentCount = dateGroup.Count()
* };
* return View(await data.AsNoTracking().ToListAsync());
*/
// Raw SQL with Underlying ADO.NET
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
var conn = _context.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 EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(groups));
}
public async Task <ActionResult> About()
{
//IQueryable<EnrollmentDateGroup> data =
// from student in _context.Students
// group student by student.EnrollmentDate into dateGroup
// select new EnrollmentDateGroup()
// {
// EnrollmentDate = dateGroup.Key,
// StudentCount = dateGroup.Count()
// };
//return View(await data.AsNoTracking().ToListAsync());
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
//获取连接
var conn = _context.Database.GetDbConnection();
//打开连接
await conn.OpenAsync();
try
{
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 EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(groups));
}
/*
* An about method that returns the number of students per enrollment date (e.g. 5 students enrolled on 9/2005)
* Updated to use raw SQL queries vs. LINQ
*/
public async Task <ActionResult> About()
{
// Create a collection to store what will be passed to the view
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
// Open the connection with our DB
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
// Generate a command to pass to our DB.
using (var command = conn.CreateCommand())
{
// Create the equivalent SQL statement to count all students stored within the DB
string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
+ "FROM Person "
+ "WHERE Discriminator = 'Student' "
+ "GROUP BY EnrollmentDate";
// set the commands query text to the string we generated
command.CommandText = query;
// Pass the stream generated by our command to the reader for processing
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
// Give values to our wrapper model from the rows we retrieved from the DB.
var row = new EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0) /* Grab from the first col*/, StudentCount = reader.GetInt32(1) /* Grab from 2nd col*/
};
// When we're done, add it to our collection being passed to the view.
groups.Add(row);
}
}
// free up all the resources our reader was allocated
reader.Dispose();
}
}
// Close our connections (we're done retreiving)
finally
{
conn.Close();
}
return(View(groups));
}
//ef使用原生sql贼麻烦
public async Task <IActionResult> About()
{
ViewData["Message"] = "学生统计信息";
var groups = new List <EnrollmentDateGroup>();
var conn = _dbContext.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
string sqlQuery = @"select EnrollmentDate, COUNT(*) as StudentCount from people where Discriminator='Student' group by EnrollmentDate";
command.CommandText = sqlQuery;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup()
{
EnrollmentDate = reader.GetDateTime(0),
StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(groups));
}
private async Task <List <EnrollmentDateGroup> > Stats()
{
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
var conn = _studentRepo.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
// todo: read from configuration
var dbSchema = "Contoso.";
if (ContosoUniversity.Common.OperatingSystem.IsMacOs())
{
dbSchema = string.Empty;
}
string query = $"SELECT EnrollmentDate, COUNT(*) AS StudentCount FROM {dbSchema}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 EnrollmentDateGroup
{
EnrollmentDate = reader.GetDateTime(0),
StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(groups);
}
/*public async Task<ActionResult> About()
* {
* IQueryable<EnrollmentDateGroup> data = from student in _context.Students
* group student by student.EnrollmentDate
* into dateGroup
* select new EnrollmentDateGroup
* {
* EnrollmentDate = dateGroup.Key,
* StudentCount = dateGroup.Count()
* };
*
* return View(await data.AsNoTracking().ToListAsync());
* }*/
public async Task <ActionResult> About()
{
var groups = new List <EnrollmentDateGroup>();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
var query = @"select EnrollmentDate,Count(*) as StudentCount
from Person
where Discriminator = 'Student'
group by EnrollmentDate";
command.CommandText = query;
var reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup
{
EnrollmentDate = reader.GetDateTime(0),
StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(groups));
}
public async Task <ActionResult> About()
{
//replacing entire code of about
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync(); // await the 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 EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1)
}; // fill up the row
groups.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(groups));
// when run the code the about will show the same result as before not sure why it changes then
}
public async Task OnGetAsync()
{
List <EnrollmentDateGroup> groups = new List <EnrollmentDateGroup>();
var connect = _context.Database.GetDbConnection();
try
{
await connect.OpenAsync();
using (var command = connect.CreateCommand())
{
var query = "select EnrollmentDate, COUNT(*) as StudentCount "
+ "FROM Person "
+ "WHERE Discriminator = 'Student' "
+ "GROUP BY EnrollmentDate";
command.CommandText = query;
using (DbDataReader reader = await command.ExecuteReaderAsync())
{
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EnrollmentDateGroup {
EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1)
};
groups.Add(row);
}
}
}
}
}
finally
{
connect.Close();
}
Students = groups;
}
