Developing Methods for the StudentDB Class for ASP.NET 2.0 - Creating the INSERT, UPDATE and DELETE methods

(Page 2 of 5 )

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.

Next: Explaining the code for the methods >>
 

More ASP.NET Articles
More By Michael Youssef

Comments On: Developing Methods for the StudentDB Class for ASP.NET 2.0

· Today we complete the StudentDB class, you need to read the previous 2 articles to ...    · i used your code in my application and it's nice. thank you very much for the...    · It would be better to use the code with the ObjectDataSource control and using a...   >>> Post your comment now!