Developing Methods for the StudentDB Class for ASP.NET 2.0 - The complete code for the Student and StudentDB Classes
(Page 5 of 5 )
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.");
}
}
}
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
