コード例 #1
0
ファイル: EmployeeDB.cs プロジェクト: bq-wang/aspnet
        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);

            // check to see if it returns one records
            if (!reader.HasRows) return null;

            reader.Read();
            EmployeeDetails emp = new EmployeeDetails((int)reader["EmployeeID"], (string)reader["FirstName"], (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
            reader.Close();
            return emp;

              }
              catch (Exception)
              {
            throw new ApplicationException("Data Error!");
              }
              finally
              {
            con.Close();
              }
        }
コード例 #2
0
ファイル: EmployeeDB.cs プロジェクト: bq-wang/aspnet
        public int InsertEmployee(EmployeeDetails emp)
        {
            SqlConnection con = new SqlConnection(connectionString);
              SqlCommand cmd = new SqlCommand("InsertEmployee", 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"].Direction = ParameterDirection.Output;

              try
              {
            con.Open();
            cmd.ExecuteNonQuery();
            return (int)cmd.Parameters["@EmployeeID"].Value;
              }
              catch (Exception)
              {
            throw new ApplicationException("Data Error!");
              }
              finally
              {
            con.Close();
              }
        }
コード例 #3
0
ファイル: EmployeeDB.cs プロジェクト: bq-wang/aspnet
        /// <summary>
        /// Get Employees between start row indx and maximum rows - allow for pagination
        /// </summary>
        /// <param name="startRowIndex">Start row index</param>
        /// <param name="maxinumRows">maximum rows </param>
        /// <returns>employees array</returns>
        public 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;

              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 (Exception ex)
              {
            throw new ApplicationException("Data Error!");
              }
              finally
              {
            con.Close();
              }
        }
コード例 #4
0
ファイル: EmployeeDB.cs プロジェクト: bq-wang/aspnet
        public List<EmployeeDetails> GetEmployees()
        {
            SqlConnection con = new SqlConnection(connectionString);
              SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
              cmd.CommandType = CommandType.StoredProcedure;
              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 (Exception ex)
              {
            throw new ApplicationException("Data Error!");
              }
              finally
              {
            con.Close();
              }
        }
コード例 #5
0
ファイル: EmployeeDB.cs プロジェクト: bq-wang/aspnet
        /// <summary>
        /// Get Employees
        /// </summary>
        /// <param name="sortExpression">sort parameters</param>
        /// <returns>sorted employees</returns>
        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 (Exception ex)
              {
            throw new ApplicationException("Data Error!");
              }
              finally
              {
            con.Close();
              }

              DataView view = ds.Tables[0].DefaultView;
              view.Sort = sortExpression;

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