Building the StudentDataAccess Class for ASP.NET 2.0
(Page 1 of 4 )
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.
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.
Next: Introducing the StudentDataAccess class >>
More ASP.NET Articles
More By Michael Youssef
