ASP.NET Optimistic Concurrency Control

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

	}
}