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(); } }