Developing Methods for the StudentDB Class for ASP.NET 2.0

(Page 1 of 5 )

We developed two of the methods of the StudentDB class in the previous article. Today we continue developing other methods of that class. In particular we are going to develop the InsertStudent(), UpdateStudent() and DeleteStudent() methods along with their T-SQL stored procedures and the test web page.

Let's start by creating the necessary stored procedures for the Students table. We need one stored procedure to insert a record into the table, another one to update records and a third one to delete a record from the table. The T-SQL code that you need to run in your SQL Server Management Studio is shown next.

USE School
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 first stored procedure, the InsertStudent procedure, accepts the student's record values and returns the Student ID as an output parameter using the SCOPE_IDENTITY() server function. This function returns the last identity value inserted in an identity column in the current scope; in our case the scope is the stored procedure. The second procedure simply deletes a record based on the StudentID column and the third stored procedure, the UpdateStudent procedure, is used to update a record based on the StudentID column as well. Now let's create the individual methods.

Next: Creating the INSERT, UPDATE and DELETE methods >>
 

More ASP.NET Articles
More By Michael Youssef

Comments On: Developing Methods for the StudentDB Class for ASP.NET 2.0

· Today we complete the StudentDB class, you need to read the previous 2 articles to ...    · i used your code in my application and it's nice. thank you very much for the...    · It would be better to use the code with the ObjectDataSource control and using a...   >>> Post your comment now!