public void UpdateEmployee(EmployeeDetails emp)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = emp.FirstName;
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = emp.LastName;
cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException err)
{
// Replace the error with something less specific.
// You could also log the error now.
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public EmployeeDetails[] GetEmployees()
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
// Create a collection for all the employee records.
ArrayList employees = new ArrayList();
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
EmployeeDetails emp = new EmployeeDetails(
(int)reader["EmployeeID"], (string)reader["FirstName"],
(string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
employees.Add(emp);
}
reader.Close();
return((EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails)));
}
catch (SqlException err)
{
// Replace the error with something less specific.
// You could also log the error now.
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public void DeleteEmployee(EmployeeDetails emp)
{
DeleteEmployee(emp.EmployeeID);
}
public EmployeeDetails[] GetEmployees()
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
// Create a collection for all the employee records.
ArrayList employees = new ArrayList();
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
EmployeeDetails emp = new EmployeeDetails(
(int)reader["EmployeeID"], (string)reader["FirstName"],
(string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
employees.Add(emp);
}
reader.Close();
return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
}
catch (SqlException err)
{
// Replace the error with something less specific.
// You could also log the error now.
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public EmployeeDetails GetEmployee(int employeeID)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Value = employeeID;
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// Get the first row.
reader.Read();
EmployeeDetails emp = new EmployeeDetails(
(int)reader["EmployeeID"], (string)reader["FirstName"],
(string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
reader.Close();
return emp;
}
catch (SqlException err)
{
// Replace the error with something less specific.
// You could also log the error now.
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
public IList <EmployeeDetails> GetEmployees()
{
var sqlConnection = new SqlConnection(_connectionString);
var sqlCommand = new SqlCommand("GetAllEmployees", sqlConnection)
{
CommandType = CommandType.StoredProcedure
};
// Create a collection for all the employee records.
var employees = new List <EmployeeDetails>();
using (sqlConnection)
{
sqlConnection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var emp = new EmployeeDetails
{
EmployeeID = (int)reader["EmployeeID"],
FirstName = (string)reader["FirstName"],
LastName = (string)reader["LastName"],
TitleOfCourtesy = (string)reader["TitleOfCourtesy"]
};
employees.Add(emp);
}
reader.Close();
return(employees);
}
#region try/catch/finally
//try
//{
// sqlConnection.Open();
// SqlDataReader reader = sqlCommand.ExecuteReader();
// while (reader.Read())
// {
// EmployeeDetails emp = new EmployeeDetails(
// (int)reader["EmployeeID"], (string)reader["FirstName"],
// (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
// employees.Add(emp);
// }
// reader.Close();
// return employees;
//}
//catch (SqlException err)
//{
// // Replace the error with something less specific.
// // You could also log the error now.
// throw new ApplicationException("Data error.");
//}
//finally
//{
// sqlConnection.Close();
//}
#endregion
}
public EmployeeDetails[] GetEmployees(string sortExpression)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
con.Open();
adapter.Fill(ds, "Employees");
}
catch (SqlException err)
{
// Replace the error with something less specific.
// You could also log the error now.
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
// Apply sort.
DataView view = ds.Tables[0].DefaultView;
view.Sort = sortExpression;
// Create a collection for all the employee records.
ArrayList employees = new ArrayList();
foreach (DataRowView row in view)
{
EmployeeDetails emp = new EmployeeDetails(
(int)row["EmployeeID"], (string)row["FirstName"],
(string)row["LastName"], (string)row["TitleOfCourtesy"]);
employees.Add(emp);
}
return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
}
// For paging
public List<EmployeeDetails> GetEmployees(int startRowIndex, int maximumRows)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetEmployeePage", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.Int, 4));
cmd.Parameters["@Start"].Value = startRowIndex + 1;
cmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int, 4));
cmd.Parameters["@Count"].Value = maximumRows;
// Create a collection for all the employee records.
List<EmployeeDetails> employees = new List<EmployeeDetails>();
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
EmployeeDetails emp = new EmployeeDetails(
(int)reader["EmployeeID"], (string)reader["FirstName"],
(string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
employees.Add(emp);
}
reader.Close();
return employees;
}
catch (SqlException err)
{
// Replace the error with something less specific.
// You could also log the error now.
throw new ApplicationException("Data error.");
}
finally
{
con.Close();
}
}
