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