Developing Methods for the StudentDB Class for ASP.NET 2.0

Let's start by creating the necessary stored procedures for the Students table. We need one stored procedure to insert a record into the table, another one to update records and a third one to delete a record from the table. The T-SQL code that you need to run in your SQL Server Management Studio is shown next.

USE School
GO
CREATE PROCEDURE InsertStudent
@StudentID INT OUTPUT,
@FirstName NVARCHAR(20),
@LastName NVARCHAR(20),
@DateOfBirth DATETIME,
@AdmissionDate DATETIME,
@Major NVARCHAR(40),
@Active BIT
AS
INSERT INTO Students
(FirstName, LastName, DateOfBirth, AdmissionDate, Major, Active)
VALUES
(@FirstName, @LastName, @DateOfBirth, @AdmissionDate, @Major,
@Active)
SET @StudentID = SCOPE_IDENTITY()
GO

CREATE PROCEDURE DeleteStudent
@StudentID INT
AS
DELETE FROM Students
WHERE StudentID = @StudentID
GO

CREATE PROCEDURE UpdateStudent
@StudentID INT,
@FirstName NVARCHAR(20),
@LastName NVARCHAR(20),
@DateOfBirth DATETIME,
@AdmissionDate DATETIME,
@Major NVARCHAR(40),
@Active BIT
AS
UPDATE Students
SET FirstName = @FirstName,
LastName = @LastName,
DateOfBirth = @DateOfBirth,
AdmissionDate = @AdmissionDate,
Major = @Major,
Active = @Active
WHERE StudentID = @StudentID
GO

The first stored procedure, the InsertStudent procedure, accepts the student's record values and returns the Student ID as an output parameter using the SCOPE_IDENTITY() server function. This function returns the last identity value inserted in an identity column in the current scope; in our case the scope is the stored procedure. The second procedure simply deletes a record based on the StudentID column and the third stored procedure, the UpdateStudent procedure, is used to update a record based on the StudentID column as well. Now let's create the individual methods.

Creating the INSERT, UPDATE and DELETE methods

Next we'll create the code for the three methods, InsertStudent(), UpdateStudent() and DeleteStudent(), that call the stored procedures created in the previous section which provide us with INSERT, UPDATE and DELETE operations on the Students table. Add the following methods to the StudentDB class.

public static int InsertStudent(Student student){
  try{
    using (SqlConnection connection = new SqlConnection
(connString)){
      SqlCommand command = new SqlCommand("InsertStudent",
connection);
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = student.FirstName;
      command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = student.LastName;
      command.Parameters.Add("@DateOfBirth",
SqlDbType.DateTime).Value = student.DateOfBirth;
      command.Parameters.Add("@AdmissionDate",
SqlDbType.DateTime).Value = student.AdmissionDate;
      command.Parameters.Add("@Major", SqlDbType.NVarChar,
40).Value = student.Major;
      command.Parameters.Add("@Active", SqlDbType.Bit).Value =
student.Active;
      command.Parameters.Add("@StudentID", SqlDbType.Int);
command.Parameters["@StudentID"].Direction =
ParameterDirection.Output;

      connection.Open();
      command.ExecuteNonQuery();
      return (int) command.Parameters["@StudentID"].Value;
    }
  }
  catch (SqlException ex){
    throw new ApplicationException("A database error
has occurred.");
  }
  catch (Exception ex){
    throw new ApplicationException("An error has occurred.");
  }
}

public static void UpdateStudent(Student student){
  try{
    using (SqlConnection connection = new SqlConnection
(connString)){
      SqlCommand command = new SqlCommand("UpdateStudent",
connection);
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@StudentID", SqlDbType.Int).Value =
student.StudentId;
      command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = student.FirstName;
      command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = student.LastName;
      command.Parameters.Add("@DateOfBirth",
SqlDbType.DateTime).Value = student.DateOfBirth;
      command.Parameters.Add("@AdmissionDate",
SqlDbType.DateTime).Value = student.AdmissionDate;
      command.Parameters.Add("@Major", SqlDbType.NVarChar,
40).Value = student.Major;
      command.Parameters.Add("@Active", SqlDbType.Bit).Value =
student.Active;

      connection.Open();
      command.ExecuteNonQuery();
    }
  }
  catch (SqlException ex){
    throw new ApplicationException("A database error has
occurred.");
  }
  catch (Exception ex){
    throw new ApplicationException("An error has occurred.");
  }
}

public static int DeleteStudent(int studentId){
  try{
    using (SqlConnection connection = new SqlConnection
(connString)){
      SqlCommand command = new SqlCommand("DeleteStudent",
connection);
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@StudentID", SqlDbType.Int).Value =
studentId;
      connection.Open();
      return command.ExecuteNonQuery();
    }
  }
  catch (SqlException ex){
    throw new ApplicationException("A database error has
occurred.");
  }
  catch (Exception ex){
    throw new ApplicationException("An error has occurred.");
  }
}

Let's talk about those methods.

Explaining the code for the methods

I think that by now you have a good idea of how we retrieve the data, from the previous article's code. Today we execute INSERT, UPDATE and DELETE statements using SqlCommand instances. As you can see, we have put the data access code inside a try/catch block to handle exceptions. The InsertStudent() method takes a Student object as a parameter, inserts a record using this object into the Students table, and returns an integer value that represents the newly inserted StudentID. The UpdateStudent() method also accepts a Student object as a parameter but doesn't return a value. The DeleteStudent() method accepts an integer value that represents the StudentID which will be deleted and returns the number of rows that have been affected by the command. The value will be 1 if it executes without problems, because we are deleting one record from the Students table.

Those three methods use another two versions of the SqlCommand.Parameters.Add() method that are different from the one we used in the previous article. The first overload accepts the parameter name and its data type; you specify the data type of SqlParameter objects through the use of the enumeration SqlDbType values. It then adds the parameter to the Parameters collection of the SqlCommand object. The return value of calling this Add() overload is the SqlParameter object that has been newly added to the SqlCommand.Parameters collection, and that's why we used the following syntax, which creates the parameter, adds it to the collection and assigns its value all in one line of code:

command.Parameters.Add("@AdmissionDate",
SqlDbType.DateTime).Value = student.AdmissionDate;

The Value property of the SqlParameter object in the above line is called on the return object of calling the Add() method, which is a SqlParameter object as I just said. We simply assign the student.AdmissionDate to the SqlParameter.Value. Note that this code is very concise and elegant too, but it can get even better.

The second overload of the Add() method that we have used looks like this:

command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = student.FirstName;

It's the same as the first overload that we just talked about, but it also accepts the size of the parameter's data type in the third parameter.

There is one more thing we need to talk about regarding these methods. We used the SqlCommand.ExecuteNonQuery() to execute the INSERT, UPDATE and DELETE statements. This method returns an integer value that represents the number of rows that have been affected by executing the command. In our example we didn't use the return value of calling the SqlCommand.ExecuteNonQuery() method in the UpdateStudent() and InsertStudent() method, we simply called it to execute the command. But we have used the returned value with the DeleteStudent() method to find out whether or not the record has been deleted. Note that if you are inserting a record with a matching StudentID in the database, an exception will be thrown. You can try that yourself by creating a student object in the Page_Load() event handler method with an existing StudentID record, and then passing it to the InsertStudent() method and observing what happens. 

The InsertStudent() method calls the InsertStudent stored procedure which has an OUTPUT parameter. It returns the newly created StudentID using that parameter, and we show that we have an OUTPUT parameter to get its value using the following syntax:

command.Parameters.Add("@StudentID", SqlDbType.Int);
command.Parameters["@StudentID"].Direction =
ParameterDirection.Output;

connection.Open();
command.ExecuteNonQuery();
return (int) command.Parameters["@StudentID"].Value;

We created and added the @StudentID SqlParameter object in the same manner, without assigning it a value of course, then used the indexer of the Parameters collection to get access to that parameter. We simply told the SqlCommand that this is an output parameter by assigning the enumeration value ParameterDirection.Output to that SqlParameter's Direction property. After calling the SqlCommand.ExecuteNonQuery() method we can get the value of the OUTPUT parameter; we just return this value to the caller.

Now let's write some code to test those methods in the Page_Load() event handler method.

Testing the Data Access Methods

First of all create another constructor overload in the Student class which doesn't accept the Student ID value, as in the following code:

public Student(string firstName, string lastName,
DateTime dateOfBirth, DateTime admissionDate, string major, bool active){
  this.firstName = firstName;
  this.lastName = lastName;
  this.dateOfBirth = dateOfBirth;
  this.admissionDate = admissionDate;
  this.major = major;
  this.active = active;
}

This constructor is used when we want to insert a new student record, because the StudentID column is being generated on the server, and it doesn't make any sense to pass a value for it. Anyway, if your code uses the first constructor overload that accepts the studentId it wouldn't do anything with it. It will generate the StudentID on the server because the column is defined as IDENTITY.

The following is the code of the Page_Load() event handler that is needed to call and test the three methods we have created in this article.

protected void Page_Load(object sender, EventArgs e){
  // creating a new Student object
  Student newStudent = new Student("Suzan", "Chris",
DateTime.Parse("11/2/1983"),
DateTime.Parse("3/8/2006"), "Information Systems", true);
  // using the InsertStudent method
  int studentId = StudentDB.InsertStudent(newStudent);
  Response.Write("The student with the ID of " + studentId + "
has been inserted into the database");

  // retrieving the newly inserted student record
  Student student = StudentDB.GetStudent(studentId);
  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 />");

  // updating the newly inserted student record
  student.FirstName = "Suzane";
  student.Major = "Computer Science";

  // submitting the changes to the database through
the UpdateStudent() method
  Response.Write("Updating the first name and the major of the
student");
  StudentDB.UpdateStudent(student);

  // retrieving the newly updated student record
  student = StudentDB.GetStudent(studentId);
  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 />");

  // deleting the new student record
  int rowsAffected = StudentDB.DeleteStudent(student.StudentId);
  if (rowsAffected == 1)
    Response.Write("The Student with the Student ID of " +
studentId + " has been deleted from the database");
  }

When you run the page it will look like the following screen shot.

We have inserted, updated and deleted a record from the Students table. The code creates the newStudent object which uses the second constructor overload to initialize the object without assigning a value to the studentId field. It makes sense because this value is generated by the database, and whatever value you are going to pass to the constructor will be ignored, because our InsertStudent stored procedure doesn't have an input parameter for studentId; it only has an output parameter. So even if you called the first constructor and passed a value like 100 or even 0 it will not affect the update operation, but it may break your application if you depend on this value. We are going to discuss a better technique for solving this problem later.

We then call the StudentDB.InsertStudent() method and pass the newStudent object as a value to its parameter. The method returns the newly inserted StudentID value which we assign to the local variable studentId. We then use the StudentDB.GetStudent() method to print out this newly inserted record; we simply pass the studentId value of the newly inserted record to it. After that we change the values of some of the properties of the returned Student object, namely the FirstName and the Major properties.

The next step is to call the method UpdateStudent() which accepts a student object and updates its record in the database. We print out the updated record again and finally we delete the record. We pass the student.StudentId property as a value to the DeleteStudent() method's parameter. This method returns the number of rows that have been affected by the delete operation. Then we test the return value and if it is equal to 1, which means that 1 record with a matching student.StudentId has been deleted, we print out an appropriate message to the user.

I think by now you have a clear idea of how to create stored procedures, create a Data Access class and represent records in object-oriented classes. In the next three-article series I will show you another, and better, technique for doing the same database operations with an object-oriented Data Access class.

If you need the full code of the Student and the StudentDB classes go to the next section.

The complete code for the Student and StudentDB Classes

Here is the Student class:

using System;
public class Student{
  private int studentId;

  public int StudentId{
    get { return studentId; }
    set { studentId = value; }
  }
  private string firstName;

  public string FirstName{
    get { return firstName; }
    set { firstName = value; }
  }
  private string lastName;

  public string LastName{
    get { return lastName; }
    set { lastName = value; }
  }
  private DateTime dateOfBirth;

  public DateTime DateOfBirth{
    get { return dateOfBirth; }
    set { dateOfBirth = value; }
  }
  private DateTime admissionDate;

  public DateTime AdmissionDate{
    get { return admissionDate; }
    set { admissionDate = value; }
  }
  private string major;

  public string Major{
    get { return major; }
    set { major = value; }
  }
  private bool active;

  public bool Active{
    get { return active; }
    set { active = value; }
  }

  public Student(int studentId, string firstName, string
lastName, DateTime dateOfBirth, DateTime admissionDate, string
major, bool active){
    this.studentId = studentId;
    this.firstName = firstName;
    this.lastName = lastName;
    this.dateOfBirth = dateOfBirth;
    this.admissionDate = admissionDate;
    this.major = major;
    this.active = active;
  }

  public Student(string firstName, string lastName,
DateTime dateOfBirth, DateTime admissionDate, string major, bool
active){
    this.firstName = firstName;
    this.lastName = lastName;
    this.dateOfBirth = dateOfBirth;
    this.admissionDate = admissionDate;
    this.major = major;
    this.active = active;
  }
}

And here is the StudentDB class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

// additional namespaces needed for this class
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;

public class StudentDB{
  private readonly static string connString;

  static StudentDB(){
    connString = WebConfigurationManager.ConnectionStrings
["SchoolConnectionString"].ConnectionString;
  }

  public static Student GetStudent(int studentId){
    try{
      using (SqlConnection connection = new SqlConnection
(connString)){
        SqlCommand command = new SqlCommand("GetStudent",
connection);
        command.CommandType = CommandType.StoredProcedure;
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@StudentID";
        command.Parameters.Add(parameter);
        command.Parameters["@StudentID"].Value = studentId;

        Student student;
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader
(CommandBehavior.SingleRow)){
          reader.Read();
          student = new Student(reader.GetInt32(0),
reader.GetString(1),reader.GetString(2),reader.GetDateTime(3),
reader.GetDateTime(4), reader.GetString(5),reader.GetBoolean(6));
        }
      return student;
    }
  }
  catch(SqlException ex){
    throw new ApplicationException("A database error has
occurred.");
  }
  catch (Exception ex){
    throw new ApplicationException("An error has occurred.");
  }
}

public static List<Student> GetAllStudentsInCollection(){
  try{
    using (SqlConnection connection = new SqlConnection
(connString)){
    SqlCommand command = new SqlCommand("GetAllStudents",
connection);
    command.CommandType = CommandType.StoredProcedure;

    List<Student> students = new List<Student>();

    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader()){
      while (reader.Read()){
        Student student;
        student = new Student(reader.GetInt32(reader.GetOrdinal
("StudentID")),
        reader.GetString(reader.GetOrdinal("FirstName")),
        reader.GetString(reader.GetOrdinal("LastName")),
        reader.GetDateTime(reader.GetOrdinal("DateOfBirth")),
        reader.GetDateTime(reader.GetOrdinal("AdmissionDate")),
        reader.GetString(reader.GetOrdinal("Major")),
        reader.GetBoolean(reader.GetOrdinal("Active")));
        students.Add(student);
      }
    }
    return students;
  }
}
catch (SqlException ex){
  throw new ApplicationException("A database error has
occurred.");
}
catch (Exception ex){
  throw new ApplicationException("An error has occurred.");
}
}

public static int InsertStudent(Student student){
  try{
    using (SqlConnection connection = new SqlConnection
(connString)){
      SqlCommand command = new SqlCommand("InsertStudent",
connection);
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = student.FirstName;
      command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = student.LastName;
      command.Parameters.Add("@DateOfBirth",
SqlDbType.DateTime).Value = student.DateOfBirth;
      command.Parameters.Add("@AdmissionDate",
SqlDbType.DateTime).Value = student.AdmissionDate;
      command.Parameters.Add("@Major", SqlDbType.NVarChar,
40).Value = student.Major;
      command.Parameters.Add("@Active", SqlDbType.Bit).Value =
student.Active;
      command.Parameters.Add("@StudentID", SqlDbType.Int);
command.Parameters["@StudentID"].Direction =
ParameterDirection.Output;

      connection.Open();
      command.ExecuteNonQuery();
      return (int) command.Parameters["@StudentID"].Value;
    }
  }
  catch (SqlException ex){
    throw new ApplicationException("A database error has
occurred.");
  }
  catch (Exception ex){
    throw new ApplicationException("An error has occurred.");
  }
}

public static void UpdateStudent(Student student){
  try{
    using (SqlConnection connection = new SqlConnection
(connString)){
      SqlCommand command = new SqlCommand("UpdateStudent",
connection);
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@StudentID", SqlDbType.Int).Value = student.StudentId;
      command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = student.FirstName;
      command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = student.LastName;
      command.Parameters.Add("@DateOfBirth",
SqlDbType.DateTime).Value = student.DateOfBirth;
      command.Parameters.Add("@AdmissionDate",
SqlDbType.DateTime).Value = student.AdmissionDate;
      command.Parameters.Add("@Major", SqlDbType.NVarChar,
40).Value = student.Major;
      command.Parameters.Add("@Active", SqlDbType.Bit).Value =
student.Active;

      connection.Open();
      command.ExecuteNonQuery();
    }
  }
  catch (SqlException ex){
    throw new ApplicationException("A database error has
occurred.");
  }
  catch (Exception ex){
    throw new ApplicationException("An error has occurred.");
  }
}

public static int DeleteStudent(int studentId){
  try{
    using (SqlConnection connection = new SqlConnection
(connString)){
      SqlCommand command = new SqlCommand("DeleteStudent",
connection);
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Add("@StudentID", SqlDbType.Int).Value =
studentId;
      connection.Open();
      return command.ExecuteNonQuery();
    }
  }
  catch (SqlException ex){
    throw new ApplicationException("A database error has
occurred.");
  }
  catch (Exception ex){
    throw new ApplicationException("An error has occurred.");
  }
 }
}