public EmployeeDeptCityViewModel employeeDetailsById(int id)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT Employee.EmployeeId,Employee.EmployeeName, Employee.Gender, Department.DepartmentName, City.CityName FROM Employee INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId INNER JOIN City ON Employee.CityId = City.CityId WHERE Employee.EmployeeId='" + id + "' ";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
var employee = new EmployeeDeptCityViewModel();
while (reader.Read())
{
int Id = Convert.ToInt32(reader["EmployeeId"]);
string Name = reader["EmployeeName"].ToString();
string Gender = reader["Gender"].ToString();
string Department = reader["DepartmentName"].ToString();
string City = reader["CityName"].ToString();
EmployeeDeptCityViewModel employeeDept = new EmployeeDeptCityViewModel(Id, Name, Gender, Department, City);
employee = employeeDept;
}
reader.Close();
connection.Close();
return(employee);
}
public List <EmployeeDeptCityViewModel> GetEmployees()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT Employee.EmployeeId, Employee.EmployeeName, Employee.Gender, Department.DepartmentName, City.CityName FROM Employee INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId INNER JOIN City ON Employee.CityId = City.CityId ORDER BY Employee.EmployeeId DESC ";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
var employeeList = new List <EmployeeDeptCityViewModel>();
while (reader.Read())
{
int Id = Convert.ToInt32(reader["EmployeeId"]);
string Name = reader["EmployeeName"].ToString();
string Gender = reader["Gender"].ToString();
string Department = reader["DepartmentName"].ToString();
string City = reader["CityName"].ToString();
EmployeeDeptCityViewModel employeeDept = new EmployeeDeptCityViewModel(Id, Name, Gender, Department, City);
employeeList.Add(employeeDept);
}
reader.Close();
connection.Close();
return(employeeList);
}
