Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Database Helper Class Library to Ease Database Operation

, 14 Apr 2007 CPOL
Database Helper Class Library to Ease Database Operation
article_demo.zip
DBHelperQuickRelationshipSamples
DBHelperQuickRelationshipSamples
App.ico
DBHelperQuickRelationshipSamples.csproj.user
DBHelperQuickRelationshipSamples.suo
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples
DBHelperQuickSelectSamples.csproj.user
DBHelperQuickSelectSamples.suo
DBHelperQuickHelperSamples
DBHelperQuickHelperSamples
App.ico
DBHelperQuickHelperSamples.csproj.user
DBHelperQuickHelperSamples.suo
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples
DBHelperQuickInsUpdDelSamples.csproj.user
DBHelperQuickInsUpdDelSamples.suo
article_src.zip
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces
Microsoft.ApplicationBlocks.ExceptionManagement.Interfaces.csproj.user
Microsoft.ApplicationBlocks.ExceptionManagement.suo
DbHelper
DBHelper.chm
DBHelper.csproj.user
DbHelper.ndoc
DBHelper.suo
Microsoft.ApplicationBlocks.ExceptionManagement
ExceptionManagerText.xsx
Microsoft.ApplicationBlocks.ExceptionManagement.csproj.user
Microsoft.ApplicationBlocks.suo
��CREATE DATABASE SwComponent

GO



USE SwComponent

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[x_Item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[x_Item]

GO



CREATE TABLE [dbo].[x_Item] (

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

	[ItemName] [nvarchar] (50) NOT NULL ,

	[ItemPrice] [smallmoney] NOT NULL ,

	[ItemOnHand] [int] NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[x_Item] ADD 

	CONSTRAINT [PK_x_Item] PRIMARY KEY  CLUSTERED 

	(

		[ItemID]

	)  ON [PRIMARY] 

GO



INSERT INTO [dbo].[x_Item]

(ItemName, ItemPrice, ItemOnHand)

VALUES

('Pen', 1.20, 500)

GO



INSERT INTO [dbo].[x_Item]

(ItemName, ItemPrice, ItemOnHand)

VALUES

('Eraser', 1.00, 600)

GO



INSERT INTO [dbo].[x_Item]

(ItemName, ItemPrice, ItemOnHand)

VALUES

('Pencil', 0.80, 1000)

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[x_OrderHeader]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[x_OrderHeader]

GO



CREATE TABLE [dbo].[x_OrderHeader] (

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

	[OrderHeaderCust] [nvarchar] (50) NOT NULL ,

	[OrderHeaderTel] [nvarchar] (30) NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[x_OrderHeader] ADD 

	CONSTRAINT [PK_x_OrderHeader] PRIMARY KEY  CLUSTERED 

	(

		[OrderHeaderID]

	)  ON [PRIMARY] 

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[x_OrderDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[x_OrderDetails]

GO



CREATE TABLE [dbo].[x_OrderDetails] (

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

	[OrderDetailsOrderHeaderID] [int] NOT NULL ,

	[OrderDetailsItemID] [int] NOT NULL ,

	[OrderDetailsQty] [int] NOT NULL

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[x_OrderDetails] ADD 

	CONSTRAINT [PK_x_OrderDetails] PRIMARY KEY  CLUSTERED 

	(

		[OrderDetailsID]

	)  ON [PRIMARY] 

GO



INSERT INTO [dbo].[x_OrderHeader]

(OrderHeaderCust, OrderHeaderTel)

VALUES

('Neowave', '035667789')

GO



INSERT INTO [dbo].[x_OrderHeader]

(OrderHeaderCust, OrderHeaderTel)

VALUES

('Softailor', '033667788')

GO



INSERT INTO [dbo].[x_OrderDetails]

(OrderDetailsOrderHeaderID, OrderDetailsItemID, OrderDetailsQty)

VALUES

(1, 1, 50)

GO



INSERT INTO [dbo].[x_OrderDetails]

(OrderDetailsOrderHeaderID, OrderDetailsItemID, OrderDetailsQty)

VALUES

(1, 2, 100)

GO



INSERT INTO [dbo].[x_OrderDetails]

(OrderDetailsOrderHeaderID, OrderDetailsItemID, OrderDetailsQty)

VALUES

(2, 1, 50)

GO



INSERT INTO [dbo].[x_OrderDetails]

(OrderDetailsOrderHeaderID, OrderDetailsItemID, OrderDetailsQty)

VALUES

(2, 3, 300)

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_UpdItem]

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



CREATE PROCEDURE [dbo].[sp_UpdItem]

(@iItemID int,

@dblItemPrice smallmoney,

@iItemOnHand int,

@iItemOnHand3x int output)

AS



UPDATE x_Item

SET ItemPrice = @dblItemPrice,

ItemOnHand = @iItemOnHand

WHERE ItemID = @iItemID



set @iItemOnHand3x = @iItemOnHand * 3



RETURN @@ERROR



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SelItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_SelItem]

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS OFF 

GO



CREATE PROCEDURE [dbo].[sp_SelItem]

(@strItemName nvarchar(50))

AS



SELECT *, ItemOnHand * 3 As ItemOnHand3x 

FROM x_Item

WHERE ItemName = @strItemName



RETURN @@ERROR

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

falconsoon

Malaysia Malaysia
Had worked as analyst programmer for 4 years. Now helping in family business but still involved actively in .Net development whenever there is a free time.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150302.1 | Last Updated 14 Apr 2007
Article Copyright 2007 by falconsoon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid