public ActionResult Create_Post()
{
try // handle exogenous exceptions
{
try // log all exceptions
{
EmployeesBusinessModelLayers employeesLUBusinessModelLayers = new EmployeesBusinessModelLayers();
ViewBag.Employeess = new SelectList(employeesLUBusinessModelLayers.EmployeesSelect, "EmployeeID", "LastName");
EmployeesBusinessModelLayers employeesBusinessModelLayers = new EmployeesBusinessModelLayers();
BusinessModelLayer.EmployeesSingle employees = new BusinessModelLayer.EmployeesSingle();
TryUpdateModel(employees);
if (ModelState.IsValid)
{
//mm
employeesBusinessModelLayers.AddEmployees(employees);
return(RedirectToAction("List"));
}
else
{
return(View());
}
}
catch (Exception ex)
{
BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging();
exlog.SendExcepToDB(ex);
throw;
}
}
catch (Exception)
{
throw;
}
}
public ActionResult Details(int EmployeeID)
{
try // handle exogenous exceptions
{
try // log all exceptions
{
EmployeesBusinessModelLayers employeesLUBusinessModelLayers = new EmployeesBusinessModelLayers();
ViewBag.Employeess = new SelectList(employeesLUBusinessModelLayers.EmployeesSelect, "EmployeeID", "LastName");
EmployeesBusinessModelLayers employeesBusinessModelLayers = new EmployeesBusinessModelLayers();
BusinessModelLayer.EmployeesSingle employees = employeesBusinessModelLayers.GetAllEmployeess().FirstOrDefault(x => x.EmployeeID == EmployeeID);
return(View(employees));
}
catch (Exception ex)
{
BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging();
exlog.SendExcepToDB(ex);
throw;
}
}
catch (Exception)
{
throw;
}
}
//END - delete
//BEGIN - read
public List <EmployeesSingle> GetAllEmployeess()
{
try // handle exogenous exceptions
{
try // log all exceptions
{
List <EmployeesSingle> employeess = new List <EmployeesSingle>();
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("spGetAllEmployees", con)
{
CommandType = CommandType.StoredProcedure
};
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
EmployeesSingle employees = new EmployeesSingle
{
// EXAMPLES:
//EmployeeId = Convert.ToInt32(rdr["EmployeeId"]),
//Name = rdr["Name"].ToString(),
//IsPermanent = (bool)rdr["IsPermanent"],
//Salary = Convert.ToDecimal(rdr["Salary"]),
//DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"])
//EmployeeID = (int)rdr["EmployeeID"]
EmployeeID = rdr["EmployeeID"] == DBNull.Value ? default(int) : (int)rdr["EmployeeID"]
//,LastName = (string)rdr["LastName"]
,
LastName = rdr["LastName"] == DBNull.Value ? "" : (string)rdr["LastName"]
//,FirstName = (string)rdr["FirstName"]
,
FirstName = rdr["FirstName"] == DBNull.Value ? "" : (string)rdr["FirstName"]
//,Title = (string)rdr["Title"]
,
Title = rdr["Title"] == DBNull.Value ? "" : (string)rdr["Title"]
//,TitleOfCourtesy = (string)rdr["TitleOfCourtesy"]
,
TitleOfCourtesy = rdr["TitleOfCourtesy"] == DBNull.Value ? "" : (string)rdr["TitleOfCourtesy"]
//,BirthDate = (System.DateTime?)rdr["BirthDate"]
,
BirthDate = rdr["BirthDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["BirthDate"]
//,HireDate = (System.DateTime?)rdr["HireDate"]
,
HireDate = rdr["HireDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["HireDate"]
//,Address = (string)rdr["Address"]
,
Address = rdr["Address"] == DBNull.Value ? "" : (string)rdr["Address"]
//,City = (string)rdr["City"]
,
City = rdr["City"] == DBNull.Value ? "" : (string)rdr["City"]
//,Region = (string)rdr["Region"]
,
Region = rdr["Region"] == DBNull.Value ? "" : (string)rdr["Region"]
//,PostalCode = (string)rdr["PostalCode"]
,
PostalCode = rdr["PostalCode"] == DBNull.Value ? "" : (string)rdr["PostalCode"]
//,Country = (string)rdr["Country"]
,
Country = rdr["Country"] == DBNull.Value ? "" : (string)rdr["Country"]
//,HomePhone = (string)rdr["HomePhone"]
,
HomePhone = rdr["HomePhone"] == DBNull.Value ? "" : (string)rdr["HomePhone"]
//,Extension = (string)rdr["Extension"]
,
Extension = rdr["Extension"] == DBNull.Value ? "" : (string)rdr["Extension"]
//,Photo = (byte[])rdr["Photo"]
,
Photo = rdr["Photo"] == DBNull.Value ? default(byte[]) : (byte[])rdr["Photo"]
//,Notes = (string)rdr["Notes"]
,
Notes = rdr["Notes"] == DBNull.Value ? "" : (string)rdr["Notes"]
//,ReportsTo = (int?)rdr["ReportsTo"]
,
ReportsTo = rdr["ReportsTo"] == DBNull.Value ? default(int?) : (int?)rdr["ReportsTo"]
//,PhotoPath = (string)rdr["PhotoPath"]
,
PhotoPath = rdr["PhotoPath"] == DBNull.Value ? "" : (string)rdr["PhotoPath"]
};
employeess.Add(employees);
}
con.Close();
cmd.Dispose();
}
return(employeess);
}
catch (Exception ex)
{
BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging();
exlog.SendExcepToDB(ex);
//errResult = "A Technical Error occurred, Please visit after some time.";
throw;
}
}
catch (Exception fx)
{
errResult = fx.Message.ToString();
throw;
}
}
//END - create
//BEGIN - update
public void UpdateEmployees(EmployeesSingle employees)
{
try // handle exogenous exceptions
{
try // log all exceptions
{
BusinessLayer.Validation isdte = new BusinessLayer.Validation();
if (isdte.IsDate(employees.BirthDate) == false)
{
employees.BirthDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
}
if (isdte.IsDate(employees.HireDate) == false)
{
employees.HireDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
}
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("spUpdateEmployees", con)
{
CommandType = CommandType.StoredProcedure
})
{
cmd.Parameters.AddWithValue("@EmployeeID", employees.EmployeeID);
cmd.Parameters.AddWithValue("@LastName", employees.LastName);
cmd.Parameters.AddWithValue("@FirstName", employees.FirstName);
cmd.Parameters.AddWithValue("@Title", (object)employees.Title ?? DBNull.Value);
cmd.Parameters.AddWithValue("@TitleOfCourtesy", (object)employees.TitleOfCourtesy ?? DBNull.Value);
cmd.Parameters.AddWithValue("@BirthDate", (object)employees.BirthDate ?? DBNull.Value);
cmd.Parameters.AddWithValue("@HireDate", (object)employees.HireDate ?? DBNull.Value);
cmd.Parameters.AddWithValue("@Address", (object)employees.Address ?? DBNull.Value);
cmd.Parameters.AddWithValue("@City", (object)employees.City ?? DBNull.Value);
cmd.Parameters.AddWithValue("@Region", (object)employees.Region ?? DBNull.Value);
cmd.Parameters.AddWithValue("@PostalCode", (object)employees.PostalCode ?? DBNull.Value);
cmd.Parameters.AddWithValue("@Country", (object)employees.Country ?? DBNull.Value);
cmd.Parameters.AddWithValue("@HomePhone", (object)employees.HomePhone ?? DBNull.Value);
cmd.Parameters.AddWithValue("@Extension", (object)employees.Extension ?? DBNull.Value);
cmd.Parameters.AddWithValue("@Photo", (object)employees.Photo ?? DBNull.Value);
cmd.Parameters.AddWithValue("@Notes", (object)employees.Notes ?? DBNull.Value);
cmd.Parameters.AddWithValue("@ReportsTo", (object)employees.ReportsTo ?? DBNull.Value);
cmd.Parameters.AddWithValue("@PhotoPath", (object)employees.PhotoPath ?? DBNull.Value);
con.Open();
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch (Exception ex)
{
BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging();
exlog.SendExcepToDB(ex);
//errResult = "A Technical Error occurred, Please visit after some time.";
throw;
}
}
catch (Exception fx)
{
errResult = fx.Message.ToString();
throw;
}
}
//BEGIN - readBy
public EmployeesSingle GetEmployeesData(int EmployeeID)
{
try // handle exogenous exceptions
{
try // log all exceptions
{
EmployeesSingle employees = new EmployeesSingle();
using (SqlConnection con = new SqlConnection(connectionString))
{
string sqlQuery = "SELECT * FROM [Employees] WHERE EmployeeID= " + EmployeeID.ToString();
using (SqlCommand cmd = new SqlCommand(sqlQuery, con))
{
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
//employees.EmployeeID = (int)rdr["EmployeeID"];
employees.EmployeeID = rdr["EmployeeID"] == DBNull.Value ? default(int) : (int)rdr["EmployeeID"];
//employees.LastName = (string)rdr["LastName"];
employees.LastName = rdr["LastName"] == DBNull.Value ? "" : (string)rdr["LastName"];
//employees.FirstName = (string)rdr["FirstName"];
employees.FirstName = rdr["FirstName"] == DBNull.Value ? "" : (string)rdr["FirstName"];
//employees.Title = (string)rdr["Title"];
employees.Title = rdr["Title"] == DBNull.Value ? "" : (string)rdr["Title"];
//employees.TitleOfCourtesy = (string)rdr["TitleOfCourtesy"];
employees.TitleOfCourtesy = rdr["TitleOfCourtesy"] == DBNull.Value ? "" : (string)rdr["TitleOfCourtesy"];
//employees.BirthDate = (System.DateTime?)rdr["BirthDate"];
employees.BirthDate = rdr["BirthDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["BirthDate"];
//employees.HireDate = (System.DateTime?)rdr["HireDate"];
employees.HireDate = rdr["HireDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["HireDate"];
//employees.Address = (string)rdr["Address"];
employees.Address = rdr["Address"] == DBNull.Value ? "" : (string)rdr["Address"];
//employees.City = (string)rdr["City"];
employees.City = rdr["City"] == DBNull.Value ? "" : (string)rdr["City"];
//employees.Region = (string)rdr["Region"];
employees.Region = rdr["Region"] == DBNull.Value ? "" : (string)rdr["Region"];
//employees.PostalCode = (string)rdr["PostalCode"];
employees.PostalCode = rdr["PostalCode"] == DBNull.Value ? "" : (string)rdr["PostalCode"];
//employees.Country = (string)rdr["Country"];
employees.Country = rdr["Country"] == DBNull.Value ? "" : (string)rdr["Country"];
//employees.HomePhone = (string)rdr["HomePhone"];
employees.HomePhone = rdr["HomePhone"] == DBNull.Value ? "" : (string)rdr["HomePhone"];
//employees.Extension = (string)rdr["Extension"];
employees.Extension = rdr["Extension"] == DBNull.Value ? "" : (string)rdr["Extension"];
//employees.Photo = (byte[])rdr["Photo"];
employees.Photo = rdr["Photo"] == DBNull.Value ? default(byte[]) : (byte[])rdr["Photo"];
//employees.Notes = (string)rdr["Notes"];
employees.Notes = rdr["Notes"] == DBNull.Value ? "" : (string)rdr["Notes"];
//employees.ReportsTo = (int?)rdr["ReportsTo"];
employees.ReportsTo = rdr["ReportsTo"] == DBNull.Value ? default(int?) : (int?)rdr["ReportsTo"];
//employees.PhotoPath = (string)rdr["PhotoPath"];
employees.PhotoPath = rdr["PhotoPath"] == DBNull.Value ? "" : (string)rdr["PhotoPath"];
//EXAMPLES:
//employee.EmployeeId = Convert.ToInt32(rdr["EmployeeID"]);
//employee.Name = rdr["Name"].ToString();
//employee.Gender = rdr["Gender"].ToString();
//employee.Salary = (decimal)rdr["Salary"];
//employee.City = rdr["City"].ToString();
//employee.IsPermanent = (bool)rdr["IsPermanent"];
//employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]);
}
}
}
return(employees);
}
catch (Exception ex)
{
BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging();
exlog.SendExcepToDB(ex);
//errResult = "A Technical Error occurred, Please visit after some time.";
throw;
}
}
catch (Exception fx)
{
errResult = fx.Message.ToString();
throw;
}
}
//END - readBy
//BEGIN - create
public void AddEmployees(EmployeesSingle employees)
{
try // handle exogenous exceptions
{
try // log all exceptions
{
BusinessLayer.Validation isdte = new BusinessLayer.Validation();
if (isdte.IsDate(employees.BirthDate) == false)
{
employees.BirthDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
}
if (isdte.IsDate(employees.HireDate) == false)
{
employees.HireDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
}
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("spAddEmployees", con)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter paramEmployeeID = new SqlParameter
{
ParameterName = "@EmployeeID",
Value = employees.EmployeeID
};
cmd.Parameters.Add(paramEmployeeID);
SqlParameter paramLastName = new SqlParameter
{
ParameterName = "@LastName",
Value = employees.LastName
};
cmd.Parameters.Add(paramLastName);
SqlParameter paramFirstName = new SqlParameter
{
ParameterName = "@FirstName",
Value = employees.FirstName
};
cmd.Parameters.Add(paramFirstName);
SqlParameter paramTitle = new SqlParameter
{
ParameterName = "@Title",
Value = employees.Title
};
cmd.Parameters.Add(paramTitle);
SqlParameter paramTitleOfCourtesy = new SqlParameter
{
ParameterName = "@TitleOfCourtesy",
Value = employees.TitleOfCourtesy
};
cmd.Parameters.Add(paramTitleOfCourtesy);
SqlParameter paramBirthDate = new SqlParameter
{
ParameterName = "@BirthDate",
Value = employees.BirthDate
};
cmd.Parameters.Add(paramBirthDate);
SqlParameter paramHireDate = new SqlParameter
{
ParameterName = "@HireDate",
Value = employees.HireDate
};
cmd.Parameters.Add(paramHireDate);
SqlParameter paramAddress = new SqlParameter
{
ParameterName = "@Address",
Value = employees.Address
};
cmd.Parameters.Add(paramAddress);
SqlParameter paramCity = new SqlParameter
{
ParameterName = "@City",
Value = employees.City
};
cmd.Parameters.Add(paramCity);
SqlParameter paramRegion = new SqlParameter
{
ParameterName = "@Region",
Value = employees.Region
};
cmd.Parameters.Add(paramRegion);
SqlParameter paramPostalCode = new SqlParameter
{
ParameterName = "@PostalCode",
Value = employees.PostalCode
};
cmd.Parameters.Add(paramPostalCode);
SqlParameter paramCountry = new SqlParameter
{
ParameterName = "@Country",
Value = employees.Country
};
cmd.Parameters.Add(paramCountry);
SqlParameter paramHomePhone = new SqlParameter
{
ParameterName = "@HomePhone",
Value = employees.HomePhone
};
cmd.Parameters.Add(paramHomePhone);
SqlParameter paramExtension = new SqlParameter
{
ParameterName = "@Extension",
Value = employees.Extension
};
cmd.Parameters.Add(paramExtension);
SqlParameter paramPhoto = new SqlParameter
{
ParameterName = "@Photo",
Value = employees.Photo
};
cmd.Parameters.Add(paramPhoto);
SqlParameter paramNotes = new SqlParameter
{
ParameterName = "@Notes",
Value = employees.Notes
};
cmd.Parameters.Add(paramNotes);
SqlParameter paramReportsTo = new SqlParameter
{
ParameterName = "@ReportsTo",
Value = employees.ReportsTo
};
cmd.Parameters.Add(paramReportsTo);
SqlParameter paramPhotoPath = new SqlParameter
{
ParameterName = "@PhotoPath",
Value = employees.PhotoPath
};
cmd.Parameters.Add(paramPhotoPath);
con.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
catch (Exception ex)
{
BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging();
exlog.SendExcepToDB(ex);
//errResult = "A Technical Error occurred, Please visit after some time.";
throw;
}
}
catch (Exception fx)
{
errResult = fx.Message.ToString();
throw;
}
}