public List <EmployeeDetailsDataPackage> GetEmployees() { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("GetAllEmployees", con); cmd.CommandType = CommandType.StoredProcedure; List <EmployeeDetailsDataPackage> list = new List <EmployeeDetailsDataPackage>(); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { EmployeeDetailsDataPackage emp = new EmployeeDetailsDataPackage((int)reader.GetValue(0), reader.GetValue(1).ToString(), reader.GetValue(2).ToString(), reader.GetValue(3).ToString()); list.Add(emp); } reader.Close(); return(list); } catch (SqlException error) { throw new ApplicationException("Data error"); } finally { con.Close(); } }
public int InsertEmployee(EmployeeDetailsDataPackage emp) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.CommandText = "InsertEmployee"; cmd.Parameters.Add("@EmployeeID", emp.EmployeeID); cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 256)); cmd.Parameters["@FirstName"].Value = emp.FirstName; cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 256)); cmd.Parameters["@LastName"].Value = emp.LastName; cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 256)); cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy; try { con.Open(); cmd.ExecuteNonQuery(); return((int)cmd.Parameters["@EmployeeID"].Value); } catch (SqlException error) { throw new ApplicationException("Data error"); } finally { con.Close(); } }
public int InsertEmployee(EmployeeDetailsDataPackage emp) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.CommandText = "InsertEmployee"; cmd.Parameters.Add("@EmployeeID", emp.EmployeeID); cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 256)); cmd.Parameters["@FirstName"].Value = emp.FirstName; cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 256)); cmd.Parameters["@LastName"].Value = emp.LastName; cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 256)); cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy; try { con.Open(); cmd.ExecuteNonQuery(); return (int)cmd.Parameters["@EmployeeID"].Value; } catch (SqlException error) { throw new ApplicationException("Data error"); } finally { con.Close(); } }
public EmployeeDetailsDataPackage 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.CloseConnection); if (!reader.Read()) { return(null); } //reader.Read(); EmployeeDetailsDataPackage emp = new EmployeeDetailsDataPackage((int)reader.GetValue(0), reader.GetValue(1).ToString(), reader.GetValue(2).ToString(), reader.GetValue(3).ToString()); reader.Close(); return(emp); } catch (SqlException error) { throw new ApplicationException("Data error"); } finally { con.Close(); } }
public List <EmployeeDetailsDataPackage> GetObjectEmployees(string sortExpression) { List <EmployeeDetailsDataPackage> myList = new List <EmployeeDetailsDataPackage>(); SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["Nash"].ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from Employees"; cmd.Connection = con; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; DataSet dataSet = new DataSet(); try { con.Open(); adapter.Fill(dataSet, "Employees"); con.Close(); } catch (Exception) { throw new ApplicationException("Data error"); } DataView view = dataSet.Tables["Employees"].DefaultView; view.Sort = sortExpression; foreach (DataRowView item in view) { EmployeeDetailsDataPackage emp = new EmployeeDetailsDataPackage((int)item[0], item[1].ToString(), item[2].ToString(), item[3].ToString()); myList.Add(emp); } return(myList); }
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { //int id = (int)DataBinder.Eval(e.Row.DataItem, "EmployeeID"); EmployeeDetailsDataPackage emp = (EmployeeDetailsDataPackage)e.Row.DataItem; int id = emp.EmployeeID; if (id == 48090) { e.Row.BackColor = Color.DarkGreen; } } }
public EmployeeDetailsDataPackage[] 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 = 5; // Create a collection for all the employee records. ArrayList employees = new ArrayList(); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { EmployeeDetailsDataPackage emp = new EmployeeDetailsDataPackage( (int)reader["EmployeeID"], (string)reader["FirstName"], (string)reader["LastName"], (string)reader["TitleOfCourtesy"]); employees.Add(emp); } reader.Close(); return((EmployeeDetailsDataPackage[])employees.ToArray(typeof(EmployeeDetailsDataPackage))); } catch (SqlException err) { throw new ApplicationException("Data error."); } finally { con.Close(); } }
public List<EmployeeDetailsDataPackage> GetEmployees() { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("GetAllEmployees", con); cmd.CommandType = CommandType.StoredProcedure; List<EmployeeDetailsDataPackage> list = new List<EmployeeDetailsDataPackage>(); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { EmployeeDetailsDataPackage emp = new EmployeeDetailsDataPackage((int)reader.GetValue(0), reader.GetValue(1).ToString(), reader.GetValue(2).ToString(), reader.GetValue(3).ToString()); list.Add(emp); } reader.Close(); return list; } catch (SqlException error) { throw new ApplicationException("Data error"); } finally { con.Close(); } }
public EmployeeDetailsDataPackage 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.CloseConnection); if (!reader.Read()) return null; //reader.Read(); EmployeeDetailsDataPackage emp = new EmployeeDetailsDataPackage((int)reader.GetValue(0), reader.GetValue(1).ToString(), reader.GetValue(2).ToString(), reader.GetValue(3).ToString()); reader.Close(); return emp; } catch (SqlException error) { throw new ApplicationException("Data error"); } finally { con.Close(); } }
public EmployeeDetailsDataPackage[] 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 = 5; // Create a collection for all the employee records. ArrayList employees = new ArrayList(); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { EmployeeDetailsDataPackage emp = new EmployeeDetailsDataPackage( (int)reader["EmployeeID"], (string)reader["FirstName"], (string)reader["LastName"], (string)reader["TitleOfCourtesy"]); employees.Add(emp); } reader.Close(); return (EmployeeDetailsDataPackage[])employees.ToArray(typeof(EmployeeDetailsDataPackage)); } catch (SqlException err) { throw new ApplicationException("Data error."); } finally { con.Close(); } }
public List<EmployeeDetailsDataPackage> GetObjectEmployees(string sortExpression) { List<EmployeeDetailsDataPackage> myList = new List<EmployeeDetailsDataPackage>(); SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["Nash"].ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from Employees"; cmd.Connection = con; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; DataSet dataSet = new DataSet(); try { con.Open(); adapter.Fill(dataSet, "Employees"); con.Close(); } catch (Exception) { throw new ApplicationException("Data error"); } DataView view = dataSet.Tables["Employees"].DefaultView; view.Sort = sortExpression; foreach (DataRowView item in view) { EmployeeDetailsDataPackage emp = new EmployeeDetailsDataPackage((int)item[0], item[1].ToString(), item[2].ToString(), item[3].ToString()); myList.Add(emp); } return myList; }