3 Layered ASP.NET Application Example - Part 1

We are going to  implement a Personal Task Tracker, a 3-layered ASP.NET application. This application supports the functions to add, edit, delete and view tasks. Each task has properties: description, summary,  date of creation and status (denotes whether the task is closed or open). The user interface will look something like:




First of all, we have to setup the database that we are going to use.

• Open Sql Server Management Studio.
• Then, one can either select Windows Authentication or SQL Server Authentication. We will use SQL Server Authentication in this example.
• Create a Database named PTT.
• Run the following Sql Scripts one by one.

Status_Table.sql

USE PTT;

GO

CREATE TABLE Status
(
 StatusID int identity(1,1) NOT NULL PRIMARY KEY,
 StatusName varchar(20) NOT NULL,
)

Note: Run the following script to populate the Status table.

USE PTT;

GO

INSERT INTO Status(StatusName)
VALUES('Open')

INSERT INTO Status(StatusName)
VALUES('Closed')

Task_Table.sql

USE PTT;

GO

CREATE TABLE Task
(
 TaskID int identity(1,1) NOT NULL PRIMARY KEY,
 Summary varchar(250),
 Description varchar(1500) NOT NULL,
 StatusID int NOT NULL,
 CreateOn datetime,

 FOREIGN KEY(StatusID) references Status(StatusID)
)

AddTask.sql

USE PTT;

GO

CREATE PROCEDURE AddTask
(
 @taskId int OUTPUT,
 @summary varchar(250),
 @description varchar(500),
 @status int,
 @createdOn datetime
)
AS
BEGIN
  INSERT INTO dbo.Task(Summary, Description, StatusID, CreateOn)
  VALUES(@summary, @description, @status, @createdOn)

  SELECT SCOPE_IDENTITY()

END
GO

UpdateTask.sql

USE PTT;

GO

CREATE PROCEDURE UpdateTask
(
 @taskId int,
 @summary varchar(250),
 @description varchar(500),
 @status int,
 @createdOn datetime
)
AS
BEGIN
  UPDATE Task
  SET Summary = @summary, Description = @description, StatusID = @status, CreateOn = @createdOn
  WHERE TaskID = @taskId
END
GO

DeleteTask.sql

USE PTT;

GO

CREATE PROCEDURE DeleteTask
(
 @taskId int
)
AS
BEGIN
  DELETE FROM Task
  WHERE TaskID = @taskid
END
GO

GetTask.sql

USE PTT;

GO

CREATE PROCEDURE GetTask
(
 @taskId int
)
AS
BEGIN
  SELECT T.TaskID, T.Summary, T.Description, T.CreateOn, S.StatusID, S.StatusName
  FROM Task T
  INNER JOIN Status S
  ON T.StatusID = S.StatusID
  WHERE T.TaskID = @taskId
END
GO

GetAllTasks.sql

USE PTT;

GO

CREATE PROCEDURE GetAllTasks
AS
BEGIN
  SELECT T.TaskID, T.Summary, T.Description, T.CreateOn, S.StatusName
  FROM Task T
  INNER JOIN Status S
  ON T.StatusID = S.StatusID
END
GO

We will see about DAL in the next post.