Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » Data » Downloads
 
Add your own
alternative version
Go to top

ASP.NET Optimistic Concurrency Control

, 19 Aug 2003
How to implement the optimistic concurrency control without the DataSet
Concurrency_Control_src.zip
Concurrency
BLL
Concurrency.csproj.webinfo
Concurrency.vsdisco
Global.asax
Images
spacer.gif
Thumbs.db
SQLHelper
Stored Procedures
_vti_cnf
_vti_pvt
access.cnf
deptodoc.btr
doctodep.btr
service.cnf
service.lck
services.cnf
_vti_script
_vti_txt
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CategoriesList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CategoriesList]
GO

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE CategoriesList

AS
	SET NOCOUNT ON
	
	SELECT 
		CategoryID,
		CategoryName,
		Description
	FROM
		Categories
	ORDER BY
		CategoryName
	
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE ProductsByCategory
(
	@categoryID int
)
AS
	SET NOCOUNT ON
	
	SELECT 
		ProductID,
		ProductName,
		SupplierID,
		QuantityPerUnit,
		UnitPrice,
		UnitsInStock,
		UnitsOnOrder,
		ReorderLevel,
		Discontinued,
		Concurrency
	FROM
		Products
	WHERE
		CategoryID = @categoryID
	ORDER BY
		ProductName
	
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE ProductsRead
(
	@productID int
)
AS

	SET NOCOUNT ON
	
	SELECT 
		ProductName,
		SupplierID,
		CategoryID,
		QuantityPerUnit,
		UnitPrice,
		UnitsInStock,
		UnitsOnOrder,
		ReorderLevel,
		Discontinued,
		Concurrency
	FROM
		Products
	WHERE
		ProductID = @productID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE ProductsUpdate
(
		@ProductID int,
		@CategoryID int,
		@SupplierID int,
		@Name varchar(40),
		@QuantityPerUnit varchar(20),
		@UnitPrice decimal(19,4),
		@UnitsInStock smallint,
		@UnitsOnOrder smallint,
		@ReorderLevel smallint,
		@Discontinued bit,
		@Concurrency datetime
)
AS
	
	UPDATE
		Products
	SET
		ProductName = @Name,
		SupplierID = @SupplierID,
		CategoryID = @CategoryID,
		QuantityPerUnit = @QuantityPerUnit,
		UnitPrice = @UnitPrice,
		UnitsInStock = @UnitsInStock,
		UnitsOnOrder = @UnitsOnOrder,
		ReorderLevel = @ReorderLevel,
		Discontinued = @Discontinued,
		Concurrency = GETDATE()			-- When updated, set the Concurrency to the server's date
	WHERE
		ProductID = @productID AND
		Concurrency = @Concurrency
		
	IF @@ROWCOUNT = 0
		BEGIN
			IF EXISTS( SELECT ProductID FROM products WHERE ProductID = @productID )
				RETURN 2	-- Concurrency conflict
			ELSE
				RETURN 1	-- The record has been deleted
		END
	ELSE
		RETURN 0			-- The record could be updated

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE SuppliersList

AS

	SELECT 
		SupplierID,
		CompanyName
	FROM
		Suppliers
	ORDER BY
		CompanyName

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 has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Teo
Web Developer
United States United States
No Biography provided

| Advertise | Privacy | Mobile
Web01 | 2.8.140921.1 | Last Updated 20 Aug 2003
Article Copyright 2003 by Teo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid