Adding Methods to the StudentDataAccess Class for ASP.NET 2.0 - Creating the DeleteStudent() method
(Page 4 of 5 )
We have provided the functionality of selecting, inserting and updating records from the Students table. Now we need to provide the capability of deleting records. Add the following DeleteStudent() method to the StudentDataAccess class:
public void DeleteStudent()
{
try
{
if (this.studentId != -1)
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand("DeleteStudent",
connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@StudentID", SqlDbType.Int).Value =
this.studentId;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 1)
{
this.studentId = -1;
this.firstName = String.Empty;
this.lastName = String.Empty;
this.major = String.Empty;
this.dateOfBirth = DateTime.MinValue;
this.admissionDate = DateTime.MinValue;
this.active = false;
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException("An error has occurred.");
}
}
The DeleteStudent() method executes the DeleteStudent stored procedure which takes the studentId field as a value to its @StudentID parameter. Note that we check whether the record has been deleted from the database through the ExecuteNonQuery() method, and if so we set the studentId field to -1 and the rest of the private fields to values other than the matching student record. To test the method, replace the code in the Page_Load() event handler of the Default.aspx with the following:
StudentDataAccessCollection students =
StudentDataAccess.GetAllStudents();
foreach (StudentDataAccess student in students)
{
Response.Write("<b>Student ID:<b/> " + student.StudentId +
"<br />" +
"<b>Student First Name:<b/> " + student.FirstName + "<br />" +
"<b>Student Last Name:<b/> " + student.LastName + "<br />" +
"<b>Student Date Of Birth:<b/> " + student.DateOfBirth +
"<br />" +
"<b>Student Admission Date:<b/> " + student.AdmissionDate +
"<br />" +
"<b>Student Major:<b/> " + student.Major + "<br />" +
"<b>Student Active:<b/> " + student.Active + "<br />"
);
Response.Write("<br />");
if (student.Active == false)
student.DeleteStudent();
}
Response.Write("After deleting inactive students, we have the
following students in the database <br />");
students = StudentDataAccess.GetAllStudents();
foreach (StudentDataAccess student in students)
{
Response.Write("<b>Student ID:<b/> " + student.StudentId +
"<br />" +
"<b>Student First Name:<b/> " + student.FirstName + "<br />" +
"<b>Student Last Name:<b/> " + student.LastName + "<br />" +
"<b>Student Date Of Birth:<b/> " + student.DateOfBirth +
"<br />" +
"<b>Student Admission Date:<b/> " + student.AdmissionDate +
"<br />" +
"<b>Student Major:<b/> " + student.Major + "<br />" +
"<b>Student Active:<b/> " + student.Active + "<br />"
);
Response.Write("<br />");
}
The result of running the page would be:
Student ID: 1
Student First Name: Jack
Student Last Name: Roberts
Student Date Of Birth: 2/15/1984 12:00:00 AM
Student Admission Date: 7/7/2006 12:00:00 AM
Student Major: Computer Science
Student Active: True
Student ID: 2
Student First Name: Mary
Student Last Name: Paul
Student Date Of Birth: 5/19/1984 12:00:00 AM
Student Admission Date: 7/7/2006 12:00:00 AM
Student Major: Information Systems
Student Active: True
Student ID: 3
Student First Name: Mark
Student Last Name: David
Student Date Of Birth: 8/6/1984 12:00:00 AM
Student Admission Date: 7/7/2006 12:00:00 AM
Student Major: Physics
Student Active: False
Student ID: 4
Student First Name: Julia
Student Last Name: Anderson
Student Date Of Birth: 3/27/1983 12:00:00 AM
Student Admission Date: 7/10/2006 12:00:00 AM
Student Major: Computer Science
Student Active: True
Student ID: 9
Student First Name: Jackson
Student Last Name: Robin
Student Date Of Birth: 11/5/1982 12:00:00 AM
Student Admission Date: 8/25/2007 10:14:05 AM
Student Major: Computer Science
Student Active: False
After deleting inactive students, we have the following students in the database:
Student ID: 1
Student First Name: Jack
Student Last Name: Roberts
Student Date Of Birth: 2/15/1984 12:00:00 AM
Student Admission Date: 7/7/2006 12:00:00 AM
Student Major: Computer Science
Student Active: True
Student ID: 2
Student First Name: Mary
Student Last Name: Paul
Student Date Of Birth: 5/19/1984 12:00:00 AM
Student Admission Date: 7/7/2006 12:00:00 AM
Student Major: Information Systems
Student Active: True
Student ID: 4
Student First Name: Julia
Student Last Name: Anderson
Student Date Of Birth: 3/27/1983 12:00:00 AM
Student Admission Date: 7/10/2006 12:00:00 AM
Student Major: Computer Science
Student Active: True
What we did is call the DeleteStudent() method on StudentDataAccess objects that have false as their value to the property Active, in the foreach statement. Also you might test the DeleteStudent() method with the following code:
StudentDataAccess student = new StudentDataAccess(4);
Response.Write("<b>Student ID:<b/> " + student.StudentId +
"<br />" +
"<b>Student First Name:<b/> " + student.FirstName + "<br />" +
"<b>Student Last Name:<b/> " + student.LastName + "<br />" +
"<b>Student Date Of Birth:<b/> " + student.DateOfBirth +
"<br />" +
"<b>Student Admission Date:<b/> " + student.AdmissionDate +
"<br />" +
"<b>Student Major:<b/> " + student.Major + "<br />" +
"<b>Student Active:<b/> " + student.Active + "<br />"
);
Response.Write("<br />");
student.DeleteStudent();
Response.Write("<b>Student ID:<b/> " + student.StudentId +
"<br />" +
"<b>Student First Name:<b/> " + student.FirstName + "<br />" +
"<b>Student Last Name:<b/> " + student.LastName + "<br />" +
"<b>Student Date Of Birth:<b/> " + student.DateOfBirth +
"<br />" +
"<b>Student Admission Date:<b/> " + student.AdmissionDate +
"<br />" +
"<b>Student Major:<b/> " + student.Major + "<br />" +
"<b>Student Active:<b/> " + student.Active + "<br />"
);
Response.Write("<br />");
Run the page and you will get the following result:
As you can see, we have deleted the student Julia, and when we printed out the values of the properties again we found that all the information for Julia is gone. I think that the StudentDataAccess class is complete by now so let's take a look at it.
Next: The StudentDataAccess class, the complete code >>
More ASP.NET Articles
More By Michael Youssef
