Generate the database used by the ASP.NET training blog
This mini-blog provides a means to generate the database used by our online ASP.NET tutorial.

Posted by Andy Brown on 17 October 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

SQL Server Database for ASP.NET Tutorial

This blog provides the data for our ASP.NET tutorial.  The first thing to do is to create a database in SQL Server:

Creating new database in SSMS

Right-click on Databases in SQL Server Management Studio and choose to create a new one.

 

Call your new database Blog:

New database called Blog

Type in a name for the new database and select OK.

 

Now copy the following script into a new SQL query and execute it, to create the tables and stored procedures referenced in this tutorial:

-- For learning purposes only.

-- Not to be distributed in any form without prior written permission of Wise Owl Business Solutions .

USE BLOG

GO

-- ***** Object: Table [dbo].[tblTask] Script Date: 10/17/2012 15:26:23 *****

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblTask](

[TaskId] [int] IDENTITY(1,1) NOT NULL,

[TaskName] [varchar](max) NULL,

[StatusId] [int] NULL,

CONSTRAINT [PK_tblTask] PRIMARY KEY CLUSTERED

(

[TaskId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[tblTask] ON

INSERT [dbo].[tblTask] ([TaskId], [TaskName], [StatusId]) VALUES (1, N'Learn quantum mechanics', 3)

INSERT [dbo].[tblTask] ([TaskId], [TaskName], [StatusId]) VALUES (2, N'New job - research', 2)

INSERT [dbo].[tblTask] ([TaskId], [TaskName], [StatusId]) VALUES (3, N'Do recycling', 1)

INSERT [dbo].[tblTask] ([TaskId], [TaskName], [StatusId]) VALUES (4, N'Finish demo website', 2)

INSERT [dbo].[tblTask] ([TaskId], [TaskName], [StatusId]) VALUES (5, N'Replace broken tile', 3)

SET IDENTITY_INSERT [dbo].[tblTask] OFF

-- ***** Object: Table [dbo].[tblStatus] Script Date: 10/17/2012 15:26:23 *****

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblStatus](

[StatusId] [int] IDENTITY(1,1) NOT NULL,

[StatusName] [varchar](10) NULL,

[SortOrder] [int] NULL,

CONSTRAINT [PK_tblStatus] PRIMARY KEY CLUSTERED

(

[StatusId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[tblStatus] ON

INSERT [dbo].[tblStatus] ([StatusId], [StatusName], [SortOrder]) VALUES (1, N'Not begun', 1)

INSERT [dbo].[tblStatus] ([StatusId], [StatusName], [SortOrder]) VALUES (2, N'Ongoing', 2)

INSERT [dbo].[tblStatus] ([StatusId], [StatusName], [SortOrder]) VALUES (3, N'Complete', 3)

SET IDENTITY_INSERT [dbo].[tblStatus] OFF

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[spTaskRecord](

@TaskId int

)

AS

-- get details of single task

SELECT

t.TaskId,

t.TaskName,

t.StatusId,

s.StatusName

FROM

tblTask AS t

INNER JOIN tblStatus AS s

ON t.StatusId=s.StatusId

WHERE

t.TaskId=@TaskId

GO

-- ***** Object: StoredProcedure [dbo].[spTaskList] Script Date: 10/17/2012 15:26:31 *****

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[spTaskList](

@StatusId int=null,

@ContainsText varchar(MAX)=null

)

AS

-- list of tasks

SELECT

t.TaskId,

t.TaskName,

s.StatusName,

s.SortOrder AS StatusSortOrder

FROM

tblTask AS t

INNER JOIN tblStatus AS s

ON t.StatusId=s.StatusId

WHERE

(@StatusId is null or s.StatusId=@StatusId) and

(@ContainsText is null or t.TaskName LIKE '%' + @ContainsText + '%')

ORDER BY

t.TaskId DESC

GO

-- ***** Object: StoredProcedure [dbo].[spTaskEdit] Script Date: 10/17/2012 15:26:31 *****

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[spTaskEdit](

@TaskId int,

@TaskName varchar(max),

@StatusId int

)

AS

UPDATE tblTask SET

TaskName=@TaskName,

StatusId=@StatusId

WHERE

TaskId=@TaskId

GO

-- ***** Object: StoredProcedure [dbo].[spTaskDelete] Script Date: 10/17/2012 15:26:31 *****

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[spTaskDelete](

@TaskId int

)

AS

DELETE FROM tblTask

WHERE

TaskId=@TaskId

GO

-- ***** Object: StoredProcedure [dbo].[spTaskAdd] Script Date: 10/17/2012 15:26:31 *****

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[spTaskAdd](

@TaskName varchar(max),

@StatusId int

)

AS

INSERT INTOtblTask(

TaskName,

StatusId

) VALUES (

@TaskName,

@StatusId

)

GO

-- ***** Object: StoredProcedure [dbo].[spStatusDropDown] Script Date: 10/17/2012 15:26:31 *****

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[spStatusDropDown] AS

-- list of statuses, with blank top row

SELECT

'' AS StatusName,

0 AS StatusId,

0 AS SortOrder

UNION

SELECT

StatusName,

StatusId,

SortOrder

FROM

tblStatus

ORDER BY

SortOrder ASC

GO

You may need to refresh your database, but you should now have two tables populated with data called tblStatus and tblTask, and 6 stored procedures.

This blog has 0 threads Add post