Adding Methods to the StudentDataAccess Class for ASP.NET 2.0 - The StudentDataAccess class, the complete code
(Page 5 of 5 )
using System;
using System.Data;
// additional namespaces needed for this class
using System.Data.SqlClient;
using System.Web.Configuration;
public class StudentDataAccess
{
private int studentId = -1;
public int StudentId
{
get { return studentId; }
}
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; }
}
private readonly static string connString =
WebConfigurationManager.ConnectionStrings
["SchoolConnectionString"].ConnectionString;
public StudentDataAccess()
{ }
public StudentDataAccess(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 StudentDataAccess(int studentId)
{
this.studentId = studentId;
if (this.studentId != -1)
{
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 = this.studentId;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.Read())
{
this.firstName = reader.GetString(reader.GetOrdinal
("FirstName"));
this.lastName = reader.GetString(reader.GetOrdinal("LastName"));
this.dateOfBirth = reader.GetDateTime(reader.GetOrdinal
("DateOfBirth"));
this.admissionDate = reader.GetDateTime(reader.GetOrdinal
("AdmissionDate"));
this.major = reader.GetString(reader.GetOrdinal("Major"));
this.active = reader.GetBoolean(reader.GetOrdinal("Active"));
}
else
{
this.studentId = -1;
}
}
}
}
catch (Exception ex)
{
this.studentId = -1;
throw new ApplicationException("An error has occurred.");
}
}
}
private void InsertNewStudent()
{
try
{
if (this.studentId == -1)
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand("InsertStudent",
connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = this.firstName;
command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = this.lastName;
command.Parameters.Add("@DateOfBirth", SqlDbType.DateTime).Value
= this.dateOfBirth;
command.Parameters.Add("@AdmissionDate",
SqlDbType.DateTime).Value = this.admissionDate;
command.Parameters.Add("@Major", SqlDbType.NVarChar, 40).Value =
this.major;
command.Parameters.Add("@Active", SqlDbType.Bit).Value =
this.active;
command.Parameters.Add("@StudentID", SqlDbType.Int);
command.Parameters["@StudentID"].Direction =
ParameterDirection.Output;
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.");
}
}
private void UpdateStudent()
{
try
{
if (this.studentId != -1)
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand("UpdateStudent",
connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@StudentID", SqlDbType.Int).Value =
this.studentId;
command.Parameters.Add("@FirstName", SqlDbType.NVarChar,
20).Value = this.firstName;
command.Parameters.Add("@LastName", SqlDbType.NVarChar,
20).Value = this.lastName;
command.Parameters.Add("@DateOfBirth", SqlDbType.DateTime).Value
= this.dateOfBirth;
command.Parameters.Add("@AdmissionDate",
SqlDbType.DateTime).Value = this.admissionDate;
command.Parameters.Add("@Major", SqlDbType.NVarChar, 40).Value =
this.major;
command.Parameters.Add("@Active", SqlDbType.Bit).Value =
this.active;
connection.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new ApplicationException("An error has occurred.");
}
}
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.");
}
}
public void SaveStudent()
{
if (this.studentId == -1)
this.InsertNewStudent();
else
this.UpdateStudent();
}
public static StudentDataAccessCollection GetAllStudents()
{
try
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand("GetAllStudentsIDs",
connection);
command.CommandType = CommandType.StoredProcedure;
StudentDataAccessCollection students = new StudentDataAccessCollection();
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
students.Add(new StudentDataAccess(reader.GetInt32(0)));
}
}
return students;
}
}
catch (Exception ex)
{
throw new ApplicationException("An error has occurred.");
}
}
public static StudentDataAccessCollection GetAllStudentsData()
{
try
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand("GetAllStudents",
connection);
command.CommandType = CommandType.StoredProcedure;
StudentDataAccessCollection students = new StudentDataAccessCollection();
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
StudentDataAccess student = new StudentDataAccess(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 (Exception ex)
{
throw new ApplicationException("An error has occurred.");
}
}
}
And here is the StudentDataAccessCollection class too:
using System;
using System.Collections;
public class StudentDataAccessCollection
{
private ArrayList list;
public ArrayList List
{
get { return list; }
}
public StudentDataAccessCollection()
{
this.list = new ArrayList();
}
public void Add(StudentDataAccess student)
{
this.list.Add(student);
}
public void Remove(StudentDataAccess student)
{
this.list.Remove(student);
}
public int Count
{
get { return this.list.Count; }
}
public StudentDataAccess this[int index]
{
get { return (StudentDataAccess)this.list[index]; }
}
public IEnumerator GetEnumerator()
{
return list.GetEnumerator();
}
}
| 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. |
