HomeASP.NET Adding Methods to the StudentDataAccess Cl...
Adding Methods to the StudentDataAccess Class for ASP.NET 2.0
Today we continue developing the StudentDataAccess class we discussed in the previous two articles. In particular, we will create the InsertNewStudent(), UpdateStudent(), DeleteStudent() and finally the SaveStudent() methods. Keep reading to learn more in this conclusion to a three-part series.
Contributed by Michael Youssef Rating: / 3 September 18, 2007
Both the InsertNewStudent() and the UpdateStudent() will be defined as private whereas the DeleteStudent() and the SaveStudent() methods will be defined as public. When the client application, the web page in our case, calls the SaveStudent() method it will determine whether it needs to insert a new student record in the database or update the existing one based on the studentId field's value of the StudentDataAccess class. As we have seen in the previous article, we have used the studentId field's value to make a few decisions, and we continue using it today.
For example, in the parameterized constructor of the StudentDataAccess class, the one that accepts the studentId value, we built our code to test whether the user has supplied a valid studentId value; if we didn't find a matching record in the database we set the studentId field's value to -1, which means that we couldn't initialize the object from the database.
Today, we use the studentId field to determine if the user is inserting a new record or updating an existing one. Add the following SaveStudent() method definition to the StudentDataAccess class:
public void SaveStudent() { if (this.studentId == -1) this.InsertNewStudent(); else this.UpdateStudent(); }
Note that the SaveStudent() method is defined as public, and it's not a static method, because it works on instances of the StudentDataAccess class, which makes sense. In this method we use the studentId field's value to determine what we want to do. If the value is equal to -1, which means that the current StudentDataAccess instance has not been initialized from the database, then we call the instance private method InsertNewStudent() to insert a new record in the Students table.
If the value of the studentId field is not equal to -1 then we know that we have initialized the StudentDataAccess instance from the database. This is correct only if the user has assigned values to the properties of the current StudentDataAccess object, and the caller called the SaveStudent() method to submit changes made to the current StudentDataAccess instance to the database, and we need to call the UpdateStudent() method.
Let's discuss both the InsertNewStudent() and UpdateStudent() methods that are conditionally called from the SaveStudent() method.
connection.Open(); int rowsAffected = command.ExecuteNonQuery(); if (rowsAffected == 1) { this.studentId = (int)command.Parameters["@StudentID"].Value; } } } } catch (Exception ex) { throw new ApplicationException("An error has occurred."); } }
Again we check to see whether the studentId is equal to -1. If the expression is evaluated to true we establish a connection to the database and create the command that executes the InsertStudent stored procedure which we have seen in the first part of this series. Note that we have passed the values of the private fields to the appropriate SqlParameter objects; if you haven't seen this syntax before I'm sure you are going to like it.
The syntax that we have used above, to create the SqlParameter objects and assign values to them to be associated and executed with the command, uses one of the available SqlCommand.Parameters.Add() method overloads. This method overload accepts the name of the parameter as a string value and the SQL Server data type of the parameter (through one of the values of the SqlDbType enumeration) and the size of the parameter. This overload creates the SqlParameter object, adds it to the SqlCommand.Parameters collection, and then returns it.
Because this method returns an object of type SqlParameter, you can use its Value property to assign a value for the parameter object, as if you were writing mySqlParameterObject.Value = myValue; which is a concise and elegant syntax. There is another overload that does the same thing but it has no third parameter. For the size of the parameter, we have used both of the overloads in the InsertNewStudent() method as shown next.
The @StudentID is defined as an OUTPUT parameter in the InsertStudent stored procedure. To tell the command that it's an output parameter you need to set that SqlParameter object's SqlParameter.Direction property to the enumeration value ParameterDirection.Output. After opening the connection and executing the stored procedure you can return the OUTPUT parameter's value through the indexer on the SqlCommand.Parameters, and then assign this value to the studentId field. This means that we have initialized the object from the database and the studentId field has a value such as 1 or 3 from those values of the StudentID column of the Students table.
We check the return value of the SqlCommand.ExecuteNonQuery() method to determine whether or not a record has been inserted into the database; if so we get the value of the OUTPUT parameter.
Let's add the UpdateStudent() method to the StudentDataAccess class.
connection.Open(); command.ExecuteNonQuery(); } } } catch (Exception ex) { throw new ApplicationException("An error has occurred."); } }
As you can see, the UpdateStudent() method is very similar to the InsertNewStudent() method. In this method we check to see if the value of the studentId field is not equal to -1. This makes sense because if we have an initialized StudentDataAccess object we can perform an update operation, but if the studentId value is equal to -1 it means that we don't have a record from the database in this StudentDataAccess object to perform an update on it.
We call the UpdateStudent stored procedure and pass it the values of the fields, through the SqlParameter objects that are added to the SqlCommand object, to perform the update using the UpdateStudent stored procedure. We need to test those methods from the Default.aspx page and see if they work. Before we go to the next section add a default constructor to the StudentDataAccess class as follows:
Now run the page and you will get the result shown below.
We created a StudentDataAccess object and assigned values to its properties. Then we called the SaveStudent() method, and because this is a new student those values are inserted into the Students table, by calling the private InsertNewStudent() method. After that we store the StudentId in a local variable and pass it to the StudentDataAccess constructor to create a populated object from that StudentId, as we have seen before. We then use the Response.Write() method to print out the values of the properties of that new object.
Next we update the Major and the Active properties of the the newStudent object, and call its SaveStudent() method. Because the newStudent.StudentId property's value has a valid studentId value, this call to the SaveStudent() method will update the record by calling the private UpdateStudent() method. Again we check the updates through creating another StudentDataAccess object with that studentId value and print out its values.
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:
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:
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.
ASP.NET 
Improving the StudentDataAccess Class for ASP.NET 2.0
