public async Task <IActionResult> Edit(int id, [Bind("Id,Name,Location,EmpName,Email,DepartmentId")] EmpDep empDep)
{
if (id != empDep.Id)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(empDep);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!EmpDepExists(empDep.Id))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
return(View(empDep));
}
public async Task <IActionResult> Create([Bind("Id,Name,Location,EmpName,Email,DepartmentId")] EmpDep empDep)
{
if (ModelState.IsValid)
{
_context.Add(empDep);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(empDep));
}
public ActionResult GetDataViewModel()
{
EmpDep objedt = new EmpDep();
EmployeeDetails objsiri = new EmployeeDetails();
objsiri.EmpId = 1111;
objsiri.Empname = "siri";
objsiri.Empsalary = 100000;
DepartmentDetails dt = new DepartmentDetails();
dt.DeptId = 1212;
dt.DeptName = "Dotnet";
objedt.Emp = objsiri;
objedt.Dept = dt;
return(View(objedt));
}
public async Task <IActionResult> ComplexSqlQuery()
{
List <EmpDep> emps = new List <EmpDep>();
var conn = _context.Database.GetDbConnection();
try
{
await conn.OpenAsync();
using (var command = conn.CreateCommand())
{
//var query = "SELECT E.Name,E.Email,E.DepartmentId,D.Name,D.Location"
// +"FROM Employee E , Department D"+ "WHERE E.DepartmentId = D.Id ";
var query = "SELECT E.Name,E.Email,E.DepartmentId,D.Name,D.[Location]"
+ "FROM Employee E , Department D WHERE E.DepartmentId = D.Id";
command.CommandText = query;
DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var row = new EmpDep
{
EmpName = reader.GetString(0),
Email = reader.GetString(1),
DepartmentId = reader.GetInt32(2),
Name = reader.GetString(3),
Location = reader.GetString(4)
};
emps.Add(row);
}
}
reader.Dispose();
}
}
finally
{
conn.Close();
}
return(View(emps));
}
