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