示例#1
0
        public static Employee SearchRecord(int searchId)
        {
            Employee      emp     = new Employee();
            SqlConnection sqlConn = UtilityDB.ConnectDB();
            SqlCommand    cmd     = new SqlCommand();

            cmd.Connection  = sqlConn;
            cmd.CommandText = "SELECT * from Employees " +
                              "WHERE EmployeeId = @EmployeeId ";
            cmd.Parameters.AddWithValue("@EmployeeId", searchId);
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
                emp.FirstName  = reader["FirstName"].ToString();
                emp.LastName   = reader["LastName"].ToString();
                emp.JobTitle   = reader["JobTitle"].ToString();
            }
            else
            {
                emp = null;
            }

            return(emp);
        }
        //SEARCH
        public static Employee SearchRecord(int empId)
        {
            Employee emp = new Employee();
            //Connect DB
            SqlConnection connDB = UtilityDB.ConnectDB();
            //SqlCommand object
            SqlCommand cmd = new SqlCommand();

            cmd.Connection  = connDB;
            cmd.CommandText = "SELECT * FROM Employees " +
                              "WHERE EmployeeId = @EmployeeId ";
            cmd.Parameters.AddWithValue("@EmployeeId", empId);
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
                emp.FirstName  = reader["FirstName"].ToString();
                emp.LastName   = reader["LastName"].ToString();
                emp.JobTitle   = reader["JobTitle"].ToString();
            }
            else
            {
                emp = null;
            }
            connDB.Close();
            //Close DB

            return(emp);
        }
示例#3
0
        public static List <Employee> GetRecordList()
        {
            List <Employee> listEmp = new List <Employee>();
            SqlConnection   connDB  = UtilityDB.ConnectDB();
            SqlCommand      cmd     = new SqlCommand("SELECT * FROM Employees", connDB);
            SqlDataReader   reader  = cmd.ExecuteReader();
            Employee        emp;

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    emp            = new Employee();//create the object here, not outside
                    emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
                    emp.FirstName  = reader["FirstName"].ToString();
                    emp.LastName   = reader["LastName"].ToString();
                    emp.JobTitle   = reader["JobTitle"].ToString();
                    listEmp.Add(emp);
                }
            }
            else
            {
                listEmp = null;
            }

            return(listEmp);
        }
示例#4
0
        public static void DeleteRecord(int id)
        {
            SqlConnection connDb = UtilityDB.ConnectDB();
            SqlCommand    cmd    = new SqlCommand();

            cmd.Connection  = connDb;
            cmd.CommandText = "delete from Employees where EmployeeId = @EmployeeId";
            cmd.Parameters.AddWithValue("@EmployeeId", id);
            cmd.ExecuteNonQuery();
            connDb.Close();
        }
        //version 1: Not good
        //public static void SaveRecord(Employee emp)
        //{
        //    SqlConnection connDB = UtilityDB.ConnectDB();
        //    SqlCommand cmd = new SqlCommand();
        //    cmd.Connection = connDB;
        //    cmd.CommandText = "INSERT INTO Employees " +
        //                      "VALUES(" + emp.EmployeeId + "," +
        //                      "'" + emp.FirstName + "'," +
        //                      "'" + emp.LastName + "'," +
        //                      "'" + emp.JobTitle + "')";
        //    cmd.ExecuteNonQuery();
        //    connDB.Close();

        //}

        public static void SaveRecord(Employee emp)
        {
            SqlConnection connDB = UtilityDB.ConnectDB();
            SqlCommand    cmd    = new SqlCommand();

            cmd.Connection  = connDB;
            cmd.CommandText = "INSERT INTO Employees(EmployeeId,FirstName,LastName,JobTitle) " +
                              " VALUES(@EmployeeId,@FirstName,@LastName,@JobTitle) ";
            cmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId);
            cmd.Parameters.AddWithValue("@FirstName", emp.FirstName);
            cmd.Parameters.AddWithValue("@LastName", emp.LastName);
            cmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle);
            cmd.ExecuteNonQuery();
            connDB.Close();
        }
 public static void UpdateRecord(Employee emp)
 {
     using (SqlConnection connDB = UtilityDB.ConnectDB())
     {
         SqlCommand cmd = new SqlCommand();
         cmd.Connection  = connDB;
         cmd.CommandText = "UPDATE Employees " +
                           " set FirstName = @FirstName, " +
                           " LastName = @LastName, " +
                           " JobTitle = @JobTitle " +
                           " WHERE EmployeeId = @EmployeeId ";
         cmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId);
         cmd.Parameters.AddWithValue("@FirstName", emp.FirstName);
         cmd.Parameters.AddWithValue("@LastName", emp.LastName);
         cmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle);
         cmd.ExecuteNonQuery();
     }
 }
示例#7
0
        public static bool IsUniqueId(int tempId)
        {
            SqlConnection connDB = UtilityDB.ConnectDB();
            SqlCommand    cmd    = new SqlCommand();

            cmd.Connection  = connDB;
            cmd.CommandText = "SELECT * FROM Employees " +
                              " WHERE EmployeeId =" + tempId;
            int id = Convert.ToInt32(cmd.ExecuteScalar());

            if (id != 0)
            {
                connDB.Close();
                return(false);
            }
            connDB.Close();
            return(true);
        }
        public static List <Employee> ListRecord()
        {
            List <Employee> listEmp = new List <Employee>();

            SqlConnection connDB = UtilityDB.ConnectDB();
            SqlCommand    cmd    = new SqlCommand("SELECT * FROM Employees", connDB);
            SqlDataReader reader = cmd.ExecuteReader();
            Employee      emp;

            while (reader.Read())
            {
                emp            = new Employee();
                emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
                emp.FirstName  = reader["FirstName"].ToString();
                emp.LastName   = reader["LastName"].ToString();
                emp.JobTitle   = reader["JobTitle"].ToString();
                listEmp.Add(emp);
            }
            return(listEmp);
        }
        //public static bool IsUniqueId(int tempId)
        //{

        //    //connect the database
        //    SqlConnection connDB = UtilityDB.ConnectDB();
        //    //create and customize an object of type SqlCommand
        //    SqlCommand cmd = new SqlCommand();
        //    cmd.Connection = connDB;
        //    cmd.CommandText = "SELECT * FROM Employees ";
        //    SqlDataReader dReader = cmd.ExecuteReader();
        //    if (dReader.HasRows)
        //    {
        //        while (dReader.Read())
        //        {
        //            //Duplicate
        //            if ((Convert.ToInt32(dReader["EmployeeId"])) == tempId)
        //                return false;
        //        }
        //    }

        //    // true if unique
        //    return true;

        //}

        public static bool IsUniqueId(int tempId)
        {
            //connect the database
            SqlConnection connDB = UtilityDB.ConnectDB();
            //create and customize an object of type SqlCommand
            SqlCommand cmd = new SqlCommand();

            cmd.Connection  = connDB;
            cmd.CommandText = "SELECT * FROM Employees " +
                              " WHERE EmployeeId= " + tempId;
            int id = Convert.ToInt32(cmd.ExecuteScalar());

            MessageBox.Show(id.ToString()); // debug code
            if (id != 0)
            {
                return(false);
            }

            // true if unique
            return(true);
        }
示例#10
0
        public static void UpdateRecord(Employee emp)
        {
            //connect the database
            SqlConnection connDB = UtilityDB.ConnectDB();

            //create and customize the object
            SqlCommand cmd = new SqlCommand();

            cmd.Connection  = connDB;
            cmd.CommandText = "Update Employees " +
                              "SET FirstName = @FirstName, " +
                              "LastName = @LastName, " +
                              "JobTitle = @JobTitle " +
                              "WHERE EmployeeId = @EmployeeId ";
            // MessageBox.Show(cmd.CommandText);
            cmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId);
            cmd.Parameters.AddWithValue("@FirstName", emp.FirstName);
            cmd.Parameters.AddWithValue("@LastName", emp.LastName);
            cmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle);
            cmd.ExecuteNonQuery();

            //close connection
            connDB.Close();
        }
        public static List <Employee> SearchRecord(int option, string name)
        {
            List <Employee> listEmp = new List <Employee>();

            using (SqlConnection connDB = UtilityDB.ConnectDB())
            {
                switch (option)
                {
                case 1:     // search by FirstName
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection  = connDB;
                    cmd.CommandText = "SELECT * FROM Employees " +
                                      "WHERE FirstName = @FirstName ";

                    cmd.Parameters.AddWithValue("@FirstName", name);
                    SqlDataReader reader = cmd.ExecuteReader();
                    Employee      emp;
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            emp            = new Employee();
                            emp.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
                            emp.FirstName  = reader["FirstName"].ToString();
                            emp.LastName   = reader["LastName"].ToString();
                            emp.JobTitle   = reader["JobTitle"].ToString();
                            listEmp.Add(emp);
                        }
                    }
                    else
                    {
                        listEmp = null;
                    }
                    break;

                case 2:     // search by LastName
                    SqlCommand cmd2 = new SqlCommand();
                    cmd2.Connection  = connDB;
                    cmd2.CommandText = "SELECT * FROM Employees " +
                                       "WHERE LastName = @LastName ";

                    cmd2.Parameters.AddWithValue("@LastName", name);
                    SqlDataReader reader2 = cmd2.ExecuteReader();
                    Employee      emp2;
                    if (reader2.HasRows)
                    {
                        while (reader2.Read())
                        {
                            emp2            = new Employee();
                            emp2.EmployeeId = Convert.ToInt32(reader2["EmployeeId"]);
                            emp2.FirstName  = reader2["FirstName"].ToString();
                            emp2.LastName   = reader2["LastName"].ToString();
                            emp2.JobTitle   = reader2["JobTitle"].ToString();
                            listEmp.Add(emp2);
                        }
                    }
                    else
                    {
                        listEmp = null;
                    }
                    break;

                default:
                    break;
                }
            }

            return(listEmp);
        }