public ActionResult Edit(Employee emp)
{
EmployeeRepository empRepo = new EmployeeRepository();
//empRepo.UpdateEmployee(emp);
empRepo.UpdateEmployeeStoredProc(emp);
return RedirectToAction("Index");
}
public IList<Employee> GetAllEmployees()
{
IList<Employee> empList = new List<Employee>();
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT [EmployeeId]
,[LastName]
,[FirstName]
,[Title]
,[ReportsTo]
,[BirthDate]
,[HireDate]
,[Address]
,[City]
,[State]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
,[Email]
FROM [dbo].[Employee]";
conn.Open();
using (DbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Employee e = new Employee()
{
EmployeeId = rdr.GetInt32(0),
LastName = rdr.GetString(1),
FirstName = rdr.GetString(2),
Title = rdr.IsDBNull(3) ? null : rdr.GetString(3),
ReportsTo = rdr.IsDBNull(4)
? (int?)null : rdr.GetInt32(4),
BirthDate = rdr.IsDBNull(5)
? null as DateTime? : rdr.GetDateTime(5),
HireDate = rdr.IsDBNull(6)
? null as DateTime? : rdr.GetDateTime(6),
Address = rdr.IsDBNull(7) ? null : rdr.GetString(7),
City = rdr.IsDBNull(8) ? null : rdr.GetString(8),
State = rdr.IsDBNull(9) ? null : rdr.GetString(9),
Country = rdr.IsDBNull(10) ? null : rdr.GetString(10),
/*add missing fields yourself!*/
PostalCode = rdr.IsDBNull(11) ? null : rdr.GetString(11),
Phone = rdr.IsDBNull(12) ? null : rdr.GetString(12),
Fax = rdr.IsDBNull(13) ? null : rdr.GetString(13),
Email = rdr.IsDBNull(14) ? null : rdr.GetString(14)
};
empList.Add(e);
}
}
}
}
return empList;
}
public void CreateEmployee(Employee emp)
{
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO [dbo].[Employee]
([LastName]
,[FirstName]
,[Title]
,[ReportsTo]
,[BirthDate]
,[HireDate]
,[Address]
,[City]
,[State]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
,[Email])
VALUES(
@LastName,
@FirstName,
@Title,
@ReportsTo,
@BirthDate,
@HireDate,
@Address,
@City,
@State,
@Country,
@PostalCode,
@Phone,
@Fax,
@Email)";
DbParameter pLn = cmd.CreateParameter();
pLn.DbType = System.Data.DbType.String;
pLn.ParameterName = "@LastName";
pLn.Value = emp.LastName;
cmd.Parameters.Add(pLn);
DbParameter pFn = cmd.CreateParameter();
pFn.DbType = System.Data.DbType.String;
pFn.ParameterName = "@FirstName";
pFn.Value = emp.FirstName;
cmd.Parameters.Add(pFn);
DbParameter pTitle = cmd.CreateParameter();
pTitle.DbType = System.Data.DbType.String;
pTitle.ParameterName = "@Title";
pTitle.Value = emp.Title;
cmd.Parameters.Add(pTitle);
/*add missing fields yourself!*/
DbParameter pReportsTo = cmd.CreateParameter();
pReportsTo.DbType = System.Data.DbType.Int32;
pReportsTo.ParameterName = "@ReportsTo";
pReportsTo.Value = emp.ReportsTo;
cmd.Parameters.Add(pReportsTo);
DbParameter pBirthDate = cmd.CreateParameter();
pBirthDate.DbType = System.Data.DbType.DateTime;
pBirthDate.ParameterName = "@BirthDate";
pBirthDate.Value = emp.BirthDate;
cmd.Parameters.Add(pBirthDate);
DbParameter pHireDate = cmd.CreateParameter();
pHireDate.DbType = System.Data.DbType.DateTime;
pHireDate.ParameterName = "@HireDate";
pHireDate.Value = emp.HireDate;
cmd.Parameters.Add(pHireDate);
DbParameter pAddress = cmd.CreateParameter();
pAddress.DbType = System.Data.DbType.String;
pAddress.ParameterName = "@Address";
pAddress.Value = emp.Address;
cmd.Parameters.Add(pAddress);
DbParameter pCity = cmd.CreateParameter();
pCity.DbType = System.Data.DbType.String;
pCity.ParameterName = "@City";
pCity.Value = emp.City;
cmd.Parameters.Add(pCity);
DbParameter pState = cmd.CreateParameter();
pState.DbType = System.Data.DbType.String;
pState.ParameterName = "@State";
pState.Value = emp.State;
cmd.Parameters.Add(pState);
DbParameter pCountry = cmd.CreateParameter();
pCountry.DbType = System.Data.DbType.String;
pCountry.ParameterName = "@Country";
pCountry.Value = emp.Country;
cmd.Parameters.Add(pCountry);
DbParameter pPostalCode = cmd.CreateParameter();
pPostalCode.DbType = System.Data.DbType.String;
pPostalCode.ParameterName = "@PostalCode";
pPostalCode.Value = emp.PostalCode;
cmd.Parameters.Add(pPostalCode);
DbParameter pPhone = cmd.CreateParameter();
pPhone.DbType = System.Data.DbType.String;
pPhone.ParameterName = "@Phone";
pPhone.Value = emp.Phone;
cmd.Parameters.Add(pPhone);
DbParameter pFax = cmd.CreateParameter();
pFax.DbType = System.Data.DbType.String;
pFax.ParameterName = "@Fax";
pFax.Value = emp.Fax;
cmd.Parameters.Add(pFax);
DbParameter pEmail = cmd.CreateParameter();
pEmail.DbType = System.Data.DbType.String;
pEmail.ParameterName = "@Email";
pEmail.Value = emp.Email;
cmd.Parameters.Add(pEmail);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
public void UpdateEmployeeStoredProc(Employee emp)
{
if (emp == null || !emp.EmployeeId.HasValue)
{
throw new Exception("Employee object is null or EmployeeId is null!");
}
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"udpUpdateEmployee";
cmd.CommandType = CommandType.StoredProcedure;
var pFn = cmd.CreateParameter();
pFn.DbType = System.Data.DbType.String;
pFn.ParameterName = "@FirstName";
pFn.Value = emp.FirstName;
cmd.Parameters.Add(pFn);
var pLn = cmd.CreateParameter();
pLn.DbType = System.Data.DbType.String;
pLn.ParameterName = "@LastName";
pLn.Value = emp.LastName;
cmd.Parameters.Add(pLn);
var pTitle = cmd.CreateParameter();
pTitle.DbType = System.Data.DbType.String;
pTitle.ParameterName = "@Title";
pTitle.Value = emp.Title;
cmd.Parameters.Add(pTitle);
var pR = cmd.CreateParameter();
pR.ParameterName = "@ReportsTo";
if (emp.ReportsTo.HasValue)
{
pR.DbType = System.Data.DbType.Int32;
pR.Value = emp.ReportsTo;
}
else
{
pR.Value = DBNull.Value;
}
cmd.Parameters.Add(pR);
//cmd.AddParameter("@ReportsTo", System.Data.DbType.Int32, emp.ReportsTo);
cmd.AddParameter("@BirthDate", System.Data.DbType.DateTime, emp.BirthDate);
cmd.AddParameter("@HireDate", System.Data.DbType.DateTime, emp.HireDate);
cmd.AddParameter("@Address", System.Data.DbType.String, emp.Address);
cmd.AddParameter("@City", System.Data.DbType.String, emp.City);
cmd.AddParameter("@State", System.Data.DbType.String, emp.State);
cmd.AddParameter("@Country", System.Data.DbType.String, emp.Country);
cmd.AddParameter("@PostalCode", System.Data.DbType.String, emp.PostalCode);
cmd.AddParameter("@Phone", System.Data.DbType.String, emp.Phone);
cmd.AddParameter("@Fax", System.Data.DbType.String, emp.Fax);
cmd.AddParameter("@Email", System.Data.DbType.String, emp.Email);
cmd.AddParameter("@EmployeeId", System.Data.DbType.Int32, emp.EmployeeId);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
public Employee GetEmployeeById(int? employeeId)
{
if (!employeeId.HasValue || employeeId == 0)
{
throw new Exception("Employee Id should be not null!");
}
Employee foundEmployee = null; ;
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT [EmployeeId]
,[LastName]
,[FirstName]
,[Title]
,[ReportsTo]
,[BirthDate]
,[HireDate]
,[Address]
,[City]
,[State]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
,[Email]
FROM [dbo].[Employee]
WHERE EmployeeId = @EmployeeId";
cmd.AddParameter("EmployeeId", DbType.Int32, employeeId);
conn.Open();
using (IDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
foundEmployee = new Employee()
{
EmployeeId = rdr.GetInt32(0),
LastName = rdr.GetString(1),
FirstName = rdr.GetString(2),
Title = rdr.IsDBNull(3) ? null : rdr.GetString(3),
ReportsTo = rdr.IsDBNull(4)
? (int?)null : rdr.GetInt32(4),
BirthDate = rdr.IsDBNull(5) ? null as DateTime? : rdr.GetDateTime(5),
HireDate = rdr.IsDBNull(6) ? null as DateTime? : rdr.GetDateTime(6),
Address = rdr.IsDBNull(7) ? null : rdr.GetString(7),
City = rdr.IsDBNull(8) ? null : rdr.GetString(8),
State = rdr.IsDBNull(9) ? null : rdr.GetString(9),
Country = rdr.IsDBNull(rdr.GetOrdinal("Country")) ? null : rdr.GetString(rdr.GetOrdinal("Country"))
/*add missing fields yourself!*/
};
}
}
}
}
return foundEmployee;
}
public ActionResult Create(Employee emp)
{
EmployeeRepository empRepo = new EmployeeRepository();
empRepo.CreateEmployee(emp);
return RedirectToAction("Index");
}