public void Add(Employee employeeToSave)
{
using (UnitOfWork.Start())
{
Session.Save(employeeToSave);
}
}
public Employee LoadEmployeeWithId(string employeeId)
{
var sqlConnection = sqlConnectionManager.OpenConnection();
var query = string.Format("SELECT * FROM EMPLOYEE WHERE EMPLOYEEID = " + employeeId);
var sqlCommand = new SqlCommand(query, sqlConnection);
var reader = sqlCommand.ExecuteReader();
var employee = new Employee();
while (reader.Read())
{
employee.EmployeeId = (int)reader["EmployeeId"];
employee.EmployeeCode = (string)reader["EmployeeCode"];
employee.EmployeeName = (string)reader["EmployeeName"];
employee.PostCode = (string)reader["PostCode"];
employee.Address = (string)reader["Address"];
employee.Salary = (decimal)reader["Salary"];
}
sqlConnectionManager.ClosConnection();
return employee;
}
public Employee(db.Employee dataAccess_emp)
{
EmployeeId = dataAccess_emp.EmployeeId;
EmployeeName = dataAccess_emp.EmployeeName;
FatherName = dataAccess_emp.FatherName;
MobNo = dataAccess_emp.MobNo;
MotherName = dataAccess_emp.MotherName;
DesignationId = dataAccess_emp.DesignationId;
ReportingManagerId = dataAccess_emp.ReportingManagerId;
WorkLocation = dataAccess_emp.WorkLocation;
Team = dataAccess_emp.Team;
PortalPassword = dataAccess_emp.PortalPassword;
Address = dataAccess_emp.Address;
}
partial void DeleteEmployee(Employee instance);
partial void UpdateEmployee(Employee instance);
partial void InsertEmployee(Employee instance);
private void detach_Employees(Employee entity)
{
this.SendPropertyChanging();
entity.Designation = null;
}
private void detach_Employees(Employee entity)
{
this.SendPropertyChanging();
entity.Department = null;
}
private void attach_Employees(Employee entity)
{
this.SendPropertyChanging();
entity.JobLocation = this;
}
public static List<Employee> GetAll(SqlConnection conn, SqlTransaction txn)
{
List<Employee> eList = new List<Employee>();
string sqlCmd = "SELECT EmployeeID, Name, HireDate, Notes, SupervisorName, ReportsTo, CanBeDeleted FROM vwEmployees ORDER BY LastName";
using (SqlCommand cmd = new SqlCommand(sqlCmd, conn, txn))
{
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Employee e = new Employee
{
EmployeeID = rdr.GetInt32(rdr.GetOrdinal("EmployeeID")),
Name = rdr.GetString(rdr.GetOrdinal("Name")),
HireDate = rdr.IsDBNull(rdr.GetOrdinal("HireDate"))
? (DateTime?)null : rdr.GetDateTime(rdr.GetOrdinal("HireDate")),
Notes = rdr.IsDBNull(rdr.GetOrdinal("Notes")) ? "" : rdr.GetString(rdr.GetOrdinal("Notes")),
SupervisorName = rdr.IsDBNull(rdr.GetOrdinal("SupervisorName"))
? null : rdr.GetString(rdr.GetOrdinal("SupervisorName")),
ReportsTo = rdr.IsDBNull(rdr.GetOrdinal("ReportsTo"))
? (int?)null : rdr.GetInt32(rdr.GetOrdinal("ReportsTo")),
CanBeDeleted = rdr.GetBoolean(rdr.GetOrdinal("CanBeDeleted")),
};
eList.Add(e);
}
}
}
foreach (Employee e in eList)
{
e.TerritoryNames = Territory.GetNames(conn, txn, e.EmployeeID);
}
return eList;
}
public void Save(Employee employee)
{
var sqlConnection = sqlConnectionManager.OpenConnection();
var query =
string.Format(
"Insert into " +
"Employee(EmployeeCode," +
"EmployeeName," +
"Address," +
"PostCode," +
"Salary) values ('{0}','{1}','{2}','{3}',{4})",
employee.EmployeeCode,
employee.EmployeeName,
employee.Address,
employee.PostCode,
employee.Salary);
var sqlCommand = new SqlCommand(query, sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlConnectionManager.ClosConnection();
}
public void Update(Employee employee)
{
var sqlConnection = sqlConnectionManager.OpenConnection();
var query =
string.Format(
"Update Employee set EmployeeCode = '{0}',EmployeeName = '{1}',Address = '{2}',PostCode = '{3}',Salary = '{4}' where EmployeeId = {5}",
employee.EmployeeCode,
employee.EmployeeName,
employee.Address,
employee.PostCode,
employee.Salary,
employee.EmployeeId);
var sqlCommand = new SqlCommand(query, sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlConnectionManager.ClosConnection();
}
public DA.Employee GetEmployee(int id, bool newEmployee)
{
Thread.Sleep(TimeSpan.FromSeconds(1));
if (newEmployee)
{
return new DA.Employee();
}
DA.Employee e = new DA.Employee();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlTransaction txn = conn.BeginTransaction())
{
string sqlCmd = "SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, Name, HireDate, Notes FROM vwEmployees WHERE (EmployeeID = @id)";
using (SqlCommand cmd = new SqlCommand(sqlCmd, conn, txn))
{
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
e.EmployeeID = rdr.GetInt32(rdr.GetOrdinal("EmployeeID"));
e.LastName = rdr.GetString(rdr.GetOrdinal("LastName"));
e.FirstName = rdr.GetString(rdr.GetOrdinal("FirstName"));
e.Title = rdr.IsDBNull(rdr.GetOrdinal("Title")) ? "" : rdr.GetString(rdr.GetOrdinal("Title"));
e.TitleOfCourtesy = rdr.IsDBNull(rdr.GetOrdinal("TitleOfCourtesy")) ? "" : rdr.GetString(rdr.GetOrdinal("TitleOfCourtesy"));
e.Name = rdr.GetString(rdr.GetOrdinal("Name"));
e.HireDate = rdr.IsDBNull(rdr.GetOrdinal("HireDate"))
? (DateTime?)null : rdr.GetDateTime(rdr.GetOrdinal("HireDate"));
e.Notes = rdr.IsDBNull(rdr.GetOrdinal("Notes")) ? "" : rdr.GetString(rdr.GetOrdinal("Notes"));
}
}
}
}
return e;
}
public List<DA.Employee> GetCanReportTo(int id)
{
List<DA.Employee> eList = new List<DA.Employee>();
Thread.Sleep(TimeSpan.FromSeconds(1));
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlTransaction txn = conn.BeginTransaction())
{
string sqlCmd = "SELECT EmployeeID, Name, ReportsTo FROM vwEmployees" +
" WHERE (dbo.CanReportTo(@id, EmployeeID) = 1) ORDER BY Name";
using (SqlCommand cmd = new SqlCommand(sqlCmd, conn, txn))
{
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DA.Employee e = new DA.Employee
{
EmployeeID = rdr.GetInt32(rdr.GetOrdinal("EmployeeID")),
Name = rdr.GetString(rdr.GetOrdinal("Name")),
ReportsTo = rdr.IsDBNull(rdr.GetOrdinal("ReportsTo"))
? (int?)null : rdr.GetInt32(rdr.GetOrdinal("ReportsTo")),
};
eList.Add(e);
}
}
}
txn.Commit();
}
}
return eList;
}
