Click here to Skip to main content
11,581,397 members (62,626 online)
Click here to Skip to main content
Articles » Web Development » ASP.NET » Data » Downloads
Add your own
alternative version

ASP.NET Optimistic Concurrency Control

, 19 Aug 2003 171.1K 2.1K 111
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
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace Concurrency.BLL
{
	/// <summary>
	/// Represents product
	/// </summary>
	[Serializable]
	public class Product : CRUD
	{

		#region Constructors

		/// <summary>
		/// Initializes a new instance of the product class
		/// </summary>
		public Product()
		{
		}

		/// <summary>
		/// Initializes a new instance of the product class
		/// </summary>
		/// <param name="id">Product ID</param>
		/// <param name="name">Name</param></param>
		/// <param name="supplierID">Supplier ID</param>
		/// <param name="categoryID">Category ID</param>
		/// <param name="quantityPerUnit">Quantity per unit</param>
		/// <param name="unitPrice">Unit price</param>
		/// <param name="unitsInStock">Units in stock</param>
		/// <param name="unitsOnOrder">Units on order</param>
		/// <param name="reorderLevel">Reorder level</param>
		/// <param name="discontinued">True if discontinued</param>
		public Product(int id, string name, int supplierID, int categoryID, string quantityPerUnit, decimal unitPrice, int unitsInStock, int unitsOnOrder, int reorderLevel, bool discontinued)
		{
			_id = id;
			_name = name;
			_supplierID = supplierID;
			_categoryID = categoryID;
			_quantityPerUnit = quantityPerUnit;
			_unitPrice = unitPrice;
			_unitsInStock = unitsInStock;
			_unitsOnOrder = unitsOnOrder;
			_reorderLevel = reorderLevel;
			_discontinued = discontinued;
		}

		#endregion

		#region Methods

		/// <summary>
		/// Reads the product for the product ID
		/// </summary>
		/// <returns></returns>
		public override bool Read()
		{
			// Preconditions
			if (_id == 0)
				throw new Exception("The product ID was not supplied.");

			//Create and bind the parameter
			SqlParameter parm = new SqlParameter("@productID", SqlDbType.Int);
			parm.Value = _id;

			// Get the data reader
			using (SqlDataReader dr = SqlHelper.ExecuteReader(Util.ConnectionString, CommandType.StoredProcedure , "ProductsRead", parm))
			{
				if (!dr.Read())
					return false;

				// Get data for the product
				_name = dr.GetString(0);
				_supplierID = dr.GetInt32(1);
				_categoryID = dr.GetInt32(2);
				_quantityPerUnit = dr.GetString(3);
				_unitPrice = dr.GetDecimal(4);
				_unitsInStock = dr.GetInt16(5);
				_unitsOnOrder = dr.GetInt16(6);
				_reorderLevel = dr.GetInt16(7);
				_discontinued = dr.GetBoolean(8);
																		
				if (dr.IsDBNull(9))
					_concurrency = DateTime.MinValue;	// Take it as null
				else
					_concurrency = dr.GetDateTime(9);
				return true;
			}	
		}

		/// <summary>
		/// Updates the product. All the data including the ID must be set in the properties
		/// </summary>
		public void Update()
		{
			// Create the parameters
			SqlParameter[] parms = new SqlParameter[12];

			parms[0] = new SqlParameter("@productID", SqlDbType.Int);
			parms[0].Value = _id;
			parms[1] = new SqlParameter("@Name", SqlDbType.VarChar, 40);
			parms[1].Value = _name;
			parms[2] = new SqlParameter("@CategoryID", SqlDbType.Int);
			parms[2].Value = _categoryID;
			parms[3] = new SqlParameter("@SupplierID", SqlDbType.Int);
			parms[3].Value = _supplierID;
			parms[4] = new SqlParameter("@QuantityPerUnit", SqlDbType.VarChar, 20);
			parms[4].Value = _quantityPerUnit;
			parms[5] = new SqlParameter("@UnitPrice", SqlDbType.Decimal);
			parms[5].Precision = 19;
			parms[5].Scale = 4;
			parms[5].Value = _unitPrice;
			parms[6] = new SqlParameter("@UnitsInStock", SqlDbType.Int);
			parms[6].Value = _unitsInStock;
			parms[7] = new SqlParameter("@UnitsOnOrder", SqlDbType.Int);
			parms[7].Value = _unitsOnOrder;
			parms[8] = new SqlParameter("@ReorderLevel", SqlDbType.Int);
			parms[8].Value = _reorderLevel;
			parms[9] = new SqlParameter("@Discontinued", SqlDbType.Bit);
			parms[9].Value = _discontinued;
			parms[10] = new SqlParameter("@Concurrency", SqlDbType.DateTime);
			parms[10].Value = _concurrency;
			parms[11] = new SqlParameter("@Status", SqlDbType.Int);
			parms[11].Direction = ParameterDirection.ReturnValue;

			// Execute the stored procedure
			SqlHelper.ExecuteNonQuery(Util.ConnectionString, CommandType.StoredProcedure , "ProductsUpdate", parms);

			// Check for success
			switch ( (UpdateRecordStatus) parms[11].Value)
			{
				case UpdateRecordStatus.Concurrency:
					throw new DBConcurrencyException("The record has been modified by another user or process.");
				case UpdateRecordStatus.Deleted:
					throw new DeletedRowInaccessibleException();
			}
		}

		/// <summary>
		/// Gets the products grouped by category
		/// </summary>
		/// <param name="categoryID">Category ID to filter the prodcuts</param>
		/// <returns>Products list grouped by category</returns>
		public IList GetProductsByCategory(int categoryID)
		{
			IList products = new ArrayList();

			//Create and bind the parameter
			SqlParameter parm = new SqlParameter("@categoryID", SqlDbType.Int);
			parm.Value = categoryID;
			
			// Get the data reader
			using (SqlDataReader dr = SqlHelper.ExecuteReader(Util.ConnectionString, CommandType.StoredProcedure , "ProductsByCategory", parm))
			{
				while (dr.Read())
				{
					// Get data for the product
					Product product = new Product(	dr.GetInt32(0), 
													dr.GetString(1),
													dr.GetInt32(2),
													categoryID,
													dr.GetString(3),
													dr.GetDecimal(4),
													dr.GetInt16(5),
													dr.GetInt16(6),
													dr.GetInt16(7),
													dr.GetBoolean(8)
												);
					
					if (dr.IsDBNull(9))
						product.Concurrency = DateTime.MinValue;	// Take it as null
					else
						product.Concurrency = dr.GetDateTime(9);
					
					// Add the category to the collection
					products.Add(product);
				}
			}
			return products;
		}

		#endregion

		#region Properties

//		private int _id;
		private int _categoryID;
		private int _supplierID;
		private string _name;
		private string _quantityPerUnit;
		private decimal _unitPrice;
		private int _unitsInStock;
		private int _unitsOnOrder;
		private int _reorderLevel;
		private bool _discontinued;
//		private DateTime _concurrency;

//		public int ID
//		{
//			get { return _id; }
//			set { _id = value; }
//		}

		public int CategoryID
		{
			get { return _categoryID; }
			set { _categoryID = value; }
		}

		public int SupplierID
		{
			get { return _supplierID; }
			set { _supplierID = value; }
		}

		public string Name
		{
			get { return _name; }
			set { _name = value; }
		}

		public string QuantityPerUnit
		{
			get { return _quantityPerUnit; }
			set { _quantityPerUnit = value; }
		}
		
		public decimal UnitPrice
		{
			get { return _unitPrice; }
			set { _unitPrice = value; }
		}
		
		public int UnitsInStock
		{
			get { return _unitsInStock; }
			set { _unitsInStock = value; }
		}		
		
		public int UnitsOnOrder
		{
			get { return _unitsOnOrder; }
			set { _unitsOnOrder = value; }
		}	

		public int ReorderLevel
		{
			get { return _reorderLevel; }
			set { _reorderLevel = value; }
		}	

		public bool Discontinued
		{
			get { return _discontinued; }
			set { _discontinued = value; }
		}	

//		public DateTime Concurrency
//		{
//			get { return _concurrency; }
//			set { _concurrency = value; }
//		}	

		#endregion

	}
}

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

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 20 Aug 2003
Article Copyright 2003 by Teo
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid