Creating a StudentDB Class for ASP.NET 2.0
(Page 1 of 5 )
In this article, and the next two, we are going to talk about how you can create a class called StudentDB and another class called Student. We will be using these classes with a database table called Students and T-SQL stored procedures to execute SELECT, INSERT, UPDATE and DELETE statements, through stored procedures, from our web page.
What you are going to learn is how you can separate your data access code from the ASP.NET pages so your code will look elegant and easier to debug, manage and modify as well. We will also discuss how you can use generics, one of the new great features of C# 2.0, to return a strongly typed collection that manipulates objects of type Student, and how nice that is for our website.
We will also see the different syntax involved in creating SqlParameter objects, using the Command.Behavior enumeration to close the connection and to return a single row result set. But today, we are going to create the database table and the stored procedures involved with creating the website and creating the Student class. Then we are going to talk about what we need to do after that.
Let's start by creating the database and the database table we need for this article and the next two; then we'll insert some data and start working on our website. Run the following code in Microsoft SQL Server Management Studio to create the necessary database, table and INSERT statements that insert a few rows into the Student table.
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
You will get the following displayed in the Messages panel:
We have simply created a database called School, and then created a table inside it called Students. The Students table has an identity and primary key column called StudentID, followed by the first name, last name, date of birth and admission date and finally a column of type BIT to determine whether the student has registered for this semester or not. The SQL Server BIT data type is an integer data type but with restricted values of 0, 1 or NULL.
Note that our table design doesn't make sense and the database should contain other tables to correctly store and manipulate the data. Still, for the sake of simplicity we are going to work on only one table to concentrate on learning the concepts and the technique discussed in this article and the two articles to follow. After creating the objects we have to insert a few records so we can test our website after writing the code. We have used the T-SQL INSERT statement to do that but you can use Management Studio visual tools to do it without writing any T-SQL code. Now let's move on to the ASP.NET website and see what we need to do.
Next: Creating the Website and the App_Code Folder >>
More ASP.NET Articles
More By Michael Youssef
