HomeASP.NET Building the StudentDataAccess Class for A...
Building the StudentDataAccess Class for ASP.NET 2.0
Confused about the difference between using generics and using a custom collection object? This three-part series will clarify the difference for you, while showing you how to create a single class that represents a student and its associated database operations.
Contributed by Michael Youssef Rating: / 8 September 12, 2007
If you read my series Creating the StudentDB Class for ASP.NET 2.0 you know that we have used the Student class to represent a record in the Students table of the School database we have created, and we also have used static methods in the StudentDB class to provide the SELECT, INSERT, UPDATE and DELETE operations.
In this article, we are going to create only one class that represents a student and its associated database operations. We are going to create the StudentDataAccess class. We are also going to create a class called StudentDataAccessCollection class to represent a collection of students.
In the previous article series we used C# generics to return a strongly typed collection as a result of calling the GetAllStudentsInCollection() method. In this series, we will create a simple collection, by hand, to clarify the difference between using generics and using a custom collection object. It's recommended that you use generics but we show how you can create that custom StudentDataAccessCollection class in case you haven't done it before.
If you didn't read my series "Creating the StudentDB Class for ASP.NET 2.0," you can read the first section of this article to create the database table and the stored procedures and continue reading the article and the next two parts. Then you can read the aforementioned series of articles about the StudentDB and understand the difference between using the two techniques.
The StudentDataAccess class has private members, public properties, static methods, instance methods and constructors. We will create them all in this series, also we are going to test those methods from an ASP.NET web page.
We start by creating the Students table, in a database called School. We insert some records in this Table and create the stored procedures needed by the StudentDataAccess class. Run the following T-SQL code into your SQL Server Management Studio to create the necessary database, the table and the stored procedures.
CREATE DATABASE School GO USE School GO CREATE TABLE Students ( StudentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, DateOfBirth DATETIME NOT NULL, AdmissionDate DATETIME NOT NULL, Major NVARCHAR(40) NOT NULL, Active BIT NOT NULL ) GO INSERT INTO Students VALUES ('Jack','Roberts','2/15/1984', '7/7/2006','Computer Science',1) INSERT INTO Students VALUES ('Mary','Paul','5/19/1984', '7/7/2006','Information Systems',1) INSERT INTO Students VALUES ('Mark','David','8/6/1984', '7/7/2006','Physics',0) INSERT INTO Students VALUES ('Julia','Anderson','3/27/1983', '7/10/2006','Computer Science',1) GO
CREATE PROCEDURE GetStudent @StudentID INT AS SELECT StudentID, FirstName, LastName, DateOfBirth, AdmissionDate, Major, Active FROM Students WHERE StudentID = @StudentID GO CREATE PROCEDURE GetAllStudentsIDs AS SELECT StudentID FROM Students
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 T-SQL code written above creates five stored procedures for executing SELECT, INSERT, UPDATE and DELETE operations after creating the database and its Students table. The Students table has seven columns; the first is the primary key and also an IDENTITY column. Let's move on to the StudentDataAccess and see what we want to accomplish with it.
The StudentDataAccess class represents a student record in the Students table and at the same time is capable of performing database operations like SELECT, INSERT, UPDATE and DELETE on the database record it presents, or on the table's records, as we are going to see soon. The first thing that we need to do is define the private fields and the public properties. Those fields and their associated properties represent a record in the Students table. The code of the StudentDataAccess table, after adding the fields and the properties, looks like this:
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; } } }
You need to create a new class file called StudentDataAccess.cs in the App_Code folder and copy, or write, this code into it.
Note that we have assigned the value -1 to the studentId field. This will become useful when you are inserting a new record, for example; I'll cover more on this later. Also we have defined the StudentId property as read only so you can't assign a value to the studentId field; as you saw in the previous section the StudentID column has been defined as an IDENTITY column so its values are generated by the database. This makes sense because you might break the code if you assigned a value that doesn't exist in the database, and if you call a method like DeleteStudent() or UpdateStudent() they wouldn't work as they should. All this is going to make sense later when you write code to test the class's methods.
Now before we continue you need to write the connection string to the Web.Config file. Replace the <connectionString /> element with the following:
Now you can use the private readonly static string variable connString to assign the connection string used by the SqlConnection objects to create connections to the database.
To retrieve the connection string from the C# code you need the following line of code:
The WebConfigurationManager class has a property called ConnectionStrings that is used to retrieve the connection string from the configuration file. This property is of type ConnectionStringSettingsCollection so we use the indexer syntax to access its individual objects, which, of type ConnectionStringSettings, we retrieve the connection string itself through the use of the ConnectionStringSettings.ConnectionString property.
Let's start developing the methods we need for this class. We start by developing the constructor.
We need to create one constructor for our StudentDataAccess class. The constructor accepts a studentId value as int and initializes the object from the database Students table, using the studentId value that is passed to it and by searching the table for a matching record. If it didn't find a matching row it sets the studentId field to -1. Let's see the code.
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;
What we have done in the constructor is that we have assigned the value of the studentId parameter to the private field studentId. Now we can use the studentId field to execute the GetStudent stored procedure and return a record from the Students table. The if statement tests if the studentId field contains a valid value; 1 is not a valid value (this will make sense later when we discuss other methods). If the field does contain a valid value we simply execute the database stored procedure and use the returned row to initialize the object's state (the object properties). We have created a connection object, in a using block to ensure that it will be properly closed when the execution of the using block completes -- or in case of an exception, we have created the command needed.
The SqlCommand object's constructor is passed the name of the stored procedure, or the T-SQL statements (like SELECT * FROM Students WHERE StudentID = @StudentID) if you want to do that, and the SqlConnection object that is used to communicate with the database. You define, to the SqlCommand object, that you want to execute a stored procedure using the SqlCommand.CommandType property which accepts values of the CommandType enumeration. In this case, we have used the enumeration value CommandType.StoredProcedure.
Then we have created a SqlParameter object, which is an object that represents a T-SQL parameter associated with the command we are executing, and assigned its name and value through the SqlParameter.ParameterName and SqlParameter.Value properties. Note how we have assigned those properties. we have assigned the Value property, after adding the SqlParameter object to the SqlCommand.Parameters collection, and by using the Parameters collection's indexer.
After that we simply opened the connection using the Open() method and called the SqCommand.ExecuteReader() method, which returns a SqlDataReader object. We know that we are going to get back only one student record and because of that we have passed the enumeration value CommandBehavior.SingleRow to the SqCommand.ExecuteReader(), which optimizes the command for retrieving a single row.
Inside the using block we have used the SqlDataReader.Read() method to read the returned row and to assign the returned fields to the StudentDataAccess fields. Note that we have used the Strongly Typed Get methods of the SqlDataReader object to return the values of the table fields in the appropriate data types. Please refer to my ADO.NET articles about SqlConnection, SqlCommand, SqlParameter and SqlDataReader objects for more information.
We have returned the values of the fields of the single record from the SqlDataReader object using the following syntax:
In case you don't know, the SqlDataReader object returns the field's value in an object data type and you have to convert it to an appropriate data type. But you can use the strongly typed Get() methods to return the values in an appropriate data type. Also note that we have used the SqlDataReader.GetOrdinal() method to return the column ordinal. We needed to do this because the strongly typed get() methods accept only column ordinals, not column names.
Note that if the SqlDataReader.Read() method didn't find a line to read, or in case of an exception, we assign the value -1 to the studentId field and this means that the object has not been initialized from the database table. Let's write code to test the constructor we have just written.
All we have to do to get an initialized StudentDataAccess object is pass a valid studentId value. What I mean by valid in this context is that the value you pass to the constructor matches one of the values of the StudentID column of the Students table. To see this in action write the following code in the Page_Load() event handler.
ASP.NET 
Improving the StudentDataAccess Class for ASP.NET 2.0
