log inhelp

 | 

  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Creating-a-Simple-Category-Code-Management-System

Page history last edited by PBworks 13 years, 3 months ago

 Creating a Simple Category Code Management System

 

 

Prerequisites

 

 

 

Description of Video

 

Categories and code are used extensively in programming. A category such as "States" could have many associated codes (Florida - FL, Georgia - GA, etc...). Codes typically have a display value (sucha as Florida, Georgia, ...) and a return value (such as FL, GA, ...). This lecture introduces the concept of categories and codes and how to use them. It uses ASP.NET and Visual Basic.

  

Video 

 

Download Link (right click and save target as ...) 

 

Reference Materials

 

SQL Script for Category Code Management System

 

IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'ccm')
EXEC dbo.sp_grantdbaccess @loginame = N'ccm', @name_in_db = N'ccm'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Codes]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Codes](
        [CodeID] [int] IDENTITY(1,1) NOT NULL,
        [CategoryID] [int] NOT NULL,
        [CodeNameText] [varchar](250) NOT NULL,
        [DescriptionText] [varchar](250) NULL,
        [DeletedBit] [bit] NOT NULL DEFAULT (0),
        [LastUpdUserLoginID] [int] NULL,
        [LastUpdDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_Codes] PRIMARY KEY CLUSTERED 
(
        [CodeID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Categories]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Categories](
        [CategoryID] [int] IDENTITY(1,1) NOT NULL,
        [CategoryNameText] [varchar](50) NOT NULL,
        [DescriptionText] [varchar](250) NULL,
        [ParentCategoryID] [int] NULL,
        [ParentCodeID] [int] NULL,
        [DeletedBit] [bit] NOT NULL DEFAULT (0),
        [LastUpdUserLoginID] [int] NULL,
        [LastUpdDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
        [CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetAllCategories]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[spGetAllCategories]
AS 
BEGIN

select * from categories
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spInsertCategories]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[spInsertCategories]
     @CategoryNameText varchar(50),
     @DescriptionText varchar(250) = null,
     @ParentCategoryID int = null,
     @ParentCodeID int = null,
     @DeletedBit bit = 0,
     @LastUpdUserLoginID int = null
AS
BEGIN
INSERT INTO Categories(
      CategoryNameText,
      DescriptionText,
      ParentCategoryID,
      ParentCodeID,
      DeletedBit,
      LastUpdUserLoginID,
      LastUpdDate)
VALUES(
       @CategoryNameText,
       @DescriptionText,
       @ParentCategoryID,
       @ParentCodeID,
       @DeletedBit,
       @LastUpdUserLoginID,
       GetDate())
SELECT MAX(CategoryID) as CategoryID  FROM Categories 
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spUpdateCategories]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUpdateCategories]
/* This assumes the first line is the PK */
     @CategoryID int,
     @CategoryNameText varchar(50),
     @DescriptionText varchar(250) = null,
     @ParentCategoryID int = null,
     @ParentCodeID int = null,
     @DeletedBit bit,
     @LastUpdUserLoginID int = null
AS
BEGIN
UPDATE Categories SET
      CategoryNameText = @CategoryNameText,
      DescriptionText = @DescriptionText,
      ParentCategoryID = @ParentCategoryID,
      ParentCodeID = @ParentCodeID,
      DeletedBit = @DeletedBit,
      LastUpdUserLoginID = @LastUpdUserLoginID,
      LastUpdDate = GetDate()
WHERE CategoryID = @CategoryID

SELECT * FROM Categories WHERE CategoryID = @CategoryID
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetCategories]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetCategories]
     @CategoryID int
AS
BEGIN

SELECT * FROM Categories WHERE CategoryID = @CategoryID
END;
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spInsertCodes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[spInsertCodes]
/* If first field is identity - it should be removed
    from the passed parameters and the insert code */
     @CategoryID int,
     @CodeNameText varchar(250),
     @DescriptionText varchar(250) = null,
     @DeletedBit bit = 0,
     @LastUpdUserLoginID int = null
AS
BEGIN
INSERT INTO Codes(
      CategoryID,
      CodeNameText,
      DescriptionText,
      DeletedBit,
      LastUpdUserLoginID,
      LastUpdDate)
VALUES(
       @CategoryID,
       @CodeNameText,
       @DescriptionText,
       @DeletedBit,
       @LastUpdUserLoginID,
       GetDate())

SELECT MAX(CodeID) FROM Codes 
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spUpdateCodes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUpdateCodes]
/* This assumes the first line is the PK */
     @CodeID int,
     @CategoryID int,
     @CodeNameText varchar(250),
     @DescriptionText varchar(250) = null,
     @DeletedBit bit,
     @LastUpdUserLoginID int = null
AS
BEGIN
UPDATE Codes SET
      CategoryID = @CategoryID,
      CodeNameText = @CodeNameText,
      DescriptionText = @DescriptionText,
      DeletedBit = @DeletedBit,
      LastUpdUserLoginID = @LastUpdUserLoginID,
      LastUpdDate = GetDate()
WHERE CodeID = @CodeID

SELECT * FROM Codes WHERE CodeID = @CodeID
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetCodes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetCodes]
     @CodeID int
AS
BEGIN

SELECT * FROM Codes WHERE CodeID = @CodeID
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spCodesForCategory]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCodesForCategory]
     @CategoryID int
AS
BEGIN

SELECT * FROM Codes WHERE CategoryID = @CategoryID
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetCodesForCategory]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetCodesForCategory]
     @CategoryID int
AS
BEGIN

SELECT * FROM Codes WHERE CategoryID = @CategoryID AND DeletedBit = 0
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetAllCodesForCategory]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetAllCodesForCategory]
     @CategoryID int
AS
BEGIN

SELECT * FROM Codes WHERE CategoryID = @CategoryID 
END' 
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Codes_Categories]') AND type = 'F')
ALTER TABLE [dbo].[Codes]  WITH NOCHECK ADD  CONSTRAINT [FK_Codes_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([CategoryID])
GO
ALTER TABLE [dbo].[Codes] CHECK CONSTRAINT [FK_Codes_Categories]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Categories_Categories]') AND type = 'F')
ALTER TABLE [dbo].[Categories]  WITH NOCHECK ADD  CONSTRAINT [FK_Categories_Categories] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Categories] ([CategoryID])
GO
ALTER TABLE [dbo].[Categories] CHECK CONSTRAINT [FK_Categories_Categories]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Categories_Codes1]') AND type = 'F')
ALTER TABLE [dbo].[Categories]  WITH NOCHECK ADD  CONSTRAINT [FK_Categories_Codes1] FOREIGN KEY([ParentCodeID])
REFERENCES [dbo].[Codes] ([CodeID])
GO
ALTER TABLE [dbo].[Categories] CHECK CONSTRAINT [FK_Categories_Codes1]

 

 

Additional Information

 

 

 

Comments (0)

You don't have permission to comment on this page.

Printable version