public static bool UpdateStudent(Student currStu)
        {
            SqlCommand updateCmd = new SqlCommand();

            updateCmd.CommandText = "UPDATE Student" +
                                    "SET StudentFName = @FirstName, " +
                                    "StudentPassword = @Password, " +
                                    "StudentLName = @LastName, " +
                                    "BirthDate = @BirthDate, " +
                                    "Email = @Email, " +
                                    "WHERE StudentID = @StudentID";

            updateCmd.Parameters.AddWithValue("@FirstName", currStu.StudentFName);
            updateCmd.Parameters.AddWithValue("@LastName", currStu.StudentLName);
            updateCmd.Parameters.AddWithValue("@BirthDate", currStu.BirthDate);
            updateCmd.Parameters.AddWithValue("@Email", currStu.StudentEmail);
            updateCmd.Parameters.AddWithValue("@id", currStu.StudentID);
            updateCmd.Parameters.AddWithValue("@Password", currStu.StudentPassword);

            using (SqlConnection con = StuDB.GetConnection())
            {
                updateCmd.Connection = con;
                con.Open();

                int rows = updateCmd.ExecuteNonQuery();
                if (rows == 1)
                {
                    return(true);
                }
                return(false);
            }
        }
Exemple #2
0
        private void CheckID(out int exists, out string password)
        {
            exists   = 0;
            password = null;
            SqlConnection con = new SqlConnection();

            con = StuDB.GetConnection();
            try
            {
                con.Open();
                SqlCommand    cmd = new SqlCommand("select * from Students full join Employees on Students.StudentID = Employees.EmployeeID", con);
                SqlDataReader r   = cmd.ExecuteReader();
                while (r.Read())
                {
                    if (txtID.Text == r["StudentID"].ToString().Trim())
                    {
                        exists   = 1;
                        password = r["StudentPassword"].ToString().Trim();
                    }
                    else if (txtID.Text == r["EmployeeID"].ToString().Trim())
                    {
                        exists   = 2;
                        password = r["EmployeePassword"].ToString().Trim();
                    }
                }
            }
            finally
            {
                con.Dispose();
            }
        }
        public static bool DeleteStudent(Student stu)
        {
            SqlConnection con = StuDB.GetConnection();

            SqlCommand delCmd = new SqlCommand();

            delCmd.Connection  = con;
            delCmd.CommandText = "DELETE FROM Student " +
                                 "WHERE StudentID=@StudentID";
            delCmd.Parameters.AddWithValue("@StudentID", stu.StudentID);

            try
            {
                con.Open();
                int rowsAffected = delCmd.ExecuteNonQuery();
                //check that one student was deleted
                if (rowsAffected == 1)
                {
                    return(true);
                }
                return(false);
            }
            finally
            {
                con.Dispose();
            }
        }
        public static List <Student> GetAllStudent()
        {
            var selCommand = new SqlCommand();

            selCommand.Connection  = StuDB.GetConnection();
            selCommand.CommandText =
                "SELECT StudentID, StudentPassword, StudentFName, StudentLName, BirthDate, StudentEmail" +
                "FROM Student";

            try
            {
                selCommand.Connection.Open();
                SqlDataReader rdr =
                    selCommand.ExecuteReader();

                var studentList = new List <Student>();
                while (rdr.Read())
                {
                    var Stu = new Student();
                    Stu.StudentID       = (int)rdr["Id"];
                    Stu.StudentPassword = (string)rdr["Password"];
                    Stu.StudentFName    = (string)rdr["FirstName"];
                    Stu.StudentLName    = (string)rdr["LastName"];
                    Stu.BirthDate       = (DateTime)rdr["BirthDate"];
                    Stu.StudentEmail    = (string)rdr["Email"];
                    studentList.Add(Stu);
                }

                return(studentList);
            }
            finally
            {
                selCommand.Connection.Dispose();
            }
        }
Exemple #5
0
        private void btnLogin_Click(object sender, EventArgs e)
        {
            int valid = 0;

            if (string.IsNullOrEmpty(txtID.Text) | string.IsNullOrEmpty(txtPassword.Text))
            {
                MessageBox.Show("Please enter User ID and Password");
                valid = 1;
            }

            int    exists   = 0;
            string password = null;

            if (valid == 0)
            {
                try
                {
                    SqlConnection con = new SqlConnection();
                    con = StuDB.GetConnection();
                    con.Open();
                    SqlCommand    cmd = new SqlCommand("select * from Student", con);
                    SqlDataReader r   = cmd.ExecuteReader();
                    while (r.Read())
                    {
                        if (txtID.Text == r["StudentID"].ToString().Trim())
                        {
                            exists   = 1;
                            password = r["StudentPassword"].ToString().Trim();
                        }
                    }
                    con.Close();
                }
                catch
                {
                    MessageBox.Show("Unable to connect to database at this time");
                }
            }

            if (exists == 1)
            {
                if (txtPassword.Text == password)
                {
                    Show();
                }
            }
        }
Exemple #6
0
        /// <summary>
        /// returns the StudentID of the most recent addition to the Student table
        /// </summary>
        /// <returns></returns>
        public static int GetNewStudentID()
        {
            SqlConnection con = new SqlConnection();

            con = StuDB.GetConnection();
            try
            {
                con.Open();
                SqlCommand    cmd    = new SqlCommand("SELECT StudentID FROM Students WHERE StudentID = (SELECT MAX(StudentID) FROM Students)", con);
                SqlDataReader r      = cmd.ExecuteReader();
                int           userID = 0;
                while (r.Read())
                {
                    userID = Convert.ToInt32(r["StudentID"]);
                }
                return(userID);
            }
            finally
            {
                con.Dispose();
            }
        }
        public static bool Add(Student newStu)
        {
            SqlConnection dbConnection = StuDB.GetConnection();

            SqlCommand addCommand = new SqlCommand();

            addCommand.Connection  = dbConnection;
            addCommand.CommandText = "INSERT INTO Student " +
                                     "(StudentFName, StudentLName, BirthDate, Email, Password)" +
                                     "VALUES" +
                                     "(@FirstName, @LastName, @BirthDate, @Email, @Password)";

            addCommand.Parameters.AddWithValue("@FirstName", newStu.StudentFName);
            addCommand.Parameters.AddWithValue("@LastName", newStu.StudentLName);
            addCommand.Parameters.AddWithValue("@BirthDate", newStu.BirthDate);
            addCommand.Parameters.AddWithValue("@Email", newStu.StudentEmail);
            addCommand.Parameters.AddWithValue("@Password", newStu.StudentPassword);

            try
            {
                dbConnection.Open();
                int rowsAffected = addCommand.ExecuteNonQuery();
                //Student added successfully
                if (rowsAffected == 1)
                {
                    return(true);
                }

                return(false);
            }
            finally
            {
                dbConnection.Dispose();
                //con.Close();
            }
        }