using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Collections.Generic;
using ViziGenLtd.abcUtils;
using ViziGenLtd.abcSql;
using ViziGenLtd.ViziGenDS;
namespace ViziGenLtd.ViziGenDA
{
/// <summary>
/// ProductDA: The products that are, or have been, sold on the web site
/// </summary>
public class ProductDA : abcSqlObject
{
// ----------------------------------------------------------------------------------
// Data associated with the class
// ----------------------------------------------------------------------------------
/// <summary>
/// The data structure of the class
/// </summary>
private Product _Data;
// Declare embedded objects
// Declare embedded collections
// Declare the Ids of embedded objects
// ----------------------------------------------------------------------------------
// ADO Parameters
// ----------------------------------------------------------------------------------
/// <summary>
/// SqlParameter for IdProduct
/// </summary>
private SqlParameter _prm_IdProduct;
/// <summary>
/// SqlParameter for ProductCode
/// </summary>
private SqlParameter _prm_ProductCode;
/// <summary>
/// SqlParameter for ProductName
/// </summary>
private SqlParameter _prm_ProductName;
/// <summary>
/// SqlParameter for DateStartSubscription
/// </summary>
private SqlParameter _prm_DateStartSubscription;
/// <summary>
/// SqlParameter for DateEndSubscription
/// </summary>
private SqlParameter _prm_DateEndSubscription;
/// <summary>
/// SqlParameter for DateStartDownload
/// </summary>
private SqlParameter _prm_DateStartDownload;
/// <summary>
/// SqlParameter for DateEndDownload
/// </summary>
private SqlParameter _prm_DateEndDownload;
/// <summary>
/// SqlParameter for DownloadFileName
/// </summary>
private SqlParameter _prm_DownloadFileName;
/// <summary>
/// SqlParameter for Application
/// </summary>
private SqlParameter _prm_Application;
/// <summary>
/// SqlParameter for Version
/// </summary>
private SqlParameter _prm_Version;
/// <summary>
/// SqlParameter for Beta
/// </summary>
private SqlParameter _prm_Beta;
// ----------------------------------------------------------------------------------
// Enums
// ----------------------------------------------------------------------------------
/// <summary>
/// Enum for Column ordinals
/// </summary>
public enum Column
{
CCN = 0,
IdProduct,
ProductCode,
ProductName,
DateStartSubscription,
DateEndSubscription,
DateStartDownload,
DateEndDownload,
DownloadFileName,
Application,
Version,
Beta,
}
// Enum value for the column to load by
private Column m_evColumnToLoadVia;
// ----------------------------------------------------------------------------------
// Constructor(s), Destructor, and Dispose()
// ----------------------------------------------------------------------------------
/// <summary>
/// Constructor
/// </summary>
public ProductDA()
: base()
{
// Establish a connection to the database
Construct();
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="p_Database">The database connection object</param>
public ProductDA(abcSqlConnection p_Database)
: base(p_Database)
{
// Initialisation
Initialise();
}
/// <summary>
/// Destructor - only called if Dispose isn't called explicitly by client
/// </summary>
~ProductDA()
{
// Call Dispose() to ensure that all resources are released
Dispose();
}
/// <summary>
/// Release all resources, especially the database connection
/// </summary>
public void Dispose()
{
// Ensure that the Database Connection is closed
if (Connection != null)
{
Connection.Dispose();
}
// Release data members
Initialise();
// Tell the Garbage Collector there is no need to call the destructor
GC.SuppressFinalize(this);
}
// ----------------------------------------------------------------------------------
// Operations
// ----------------------------------------------------------------------------------
/// <summary>
/// Construct - establish connection to database and load object
/// </summary>
public void Construct()
{
// Initialise this object
Initialise();
// Create a database connection object
base.Connection = new abcSqlConnection();
// Connect to the database
base.Connection.OpenConnection(ViziGenConnectionString.ConnectionString);
}
/// <summary>
/// Initialises the data members
/// </summary>
private void Initialise()
{
// Initialise the column value parameters
_prm_IdProduct = new SqlParameter("@IdProduct",SqlDbType.Int);
_prm_ProductCode = new SqlParameter("@lProductCode",SqlDbType.BigInt);
_prm_ProductName = new SqlParameter("@sProductName",SqlDbType.VarChar);
_prm_DateStartSubscription = new SqlParameter("@dtDateStartSubscription",SqlDbType.DateTime);
_prm_DateEndSubscription = new SqlParameter("@dtDateEndSubscription",SqlDbType.DateTime);
_prm_DateStartDownload = new SqlParameter("@dtDateStartDownload",SqlDbType.DateTime);
_prm_DateEndDownload = new SqlParameter("@dtDateEndDownload",SqlDbType.DateTime);
_prm_DownloadFileName = new SqlParameter("@sDownloadFileName",SqlDbType.VarChar);
_prm_Application = new SqlParameter("@Application",SqlDbType.SmallInt);
_prm_Version = new SqlParameter("@Version",SqlDbType.SmallInt);
_prm_Beta = new SqlParameter("@Beta",SqlDbType.SmallInt);
// Initialise the data structure of the class
_Data.Init();
// Initialise the primary key value
_Data.IdProduct = -1;
// Initialise embedded objects
// Initialise embedded collections
}
/// <summary>
/// Returns the maximum length of string and blob attributes
/// <summary>
public static int MaxLength(Column p_evColumn)
{
int nMaxLen = 0; // this is returned
switch (p_evColumn)
{
case Column.ProductName:
{
nMaxLen = 50;
break;
}
case Column.DownloadFileName:
{
nMaxLen = 256;
break;
}
}
return nMaxLen;
}
/// <summary>
/// Load the object with values from the database
/// </summary>
public override void LoadFromReader()
{
// Load the column values from the database reader object
_Data.IdProduct = Connection.LoadInt((int)Column.IdProduct);
_Data.Application = (ViziGenEnums.eApplication)(Connection.LoadInt16((int)Column.Application));
_Data.Version = (ViziGenEnums.eVersion)(Connection.LoadInt16((int)Column.Version));
_Data.Beta = (ViziGenEnums.eBeta)(Connection.LoadInt16((int)Column.Beta));
_Data.ProductName = Connection.LoadString((int)Column.ProductName);
_Data.DownloadFileName = Connection.LoadString((int)Column.DownloadFileName);
_Data.DateStartSubscription = Connection.LoadDateTime((int)Column.DateStartSubscription);
_Data.DateEndSubscription = Connection.LoadDateTime((int)Column.DateEndSubscription);
_Data.DateStartDownload = Connection.LoadDateTime((int)Column.DateStartDownload);
_Data.DateEndDownload = Connection.LoadDateTime((int)Column.DateEndDownload);
_Data.ProductCode = Connection.LoadInt64((int)Column.ProductCode);
// Load the CCN
_Data.CCN = m_nCCN;
// Set the State of the object
eState = abcSqlEnums.eState.Loaded_Clean;
}
/// <summary>
/// Set everything up in preparation for calling the load stored procedure
/// </summary>
protected override void LoadPrepare()
{
// Clear the stored procedure
Connection.SetStoredProcedure(string.Empty);
// Set up the parameters for the load stored procedure
if (m_evColumnToLoadVia == Column.IdProduct)
{
// Specify the stored procedure
Connection.SetStoredProcedure("sp_vgProduct_LoadByIdProduct");
// Set up the parameter
_prm_IdProduct.Value = _Data.IdProduct;
_prm_IdProduct.Direction = ParameterDirection.Input;
// Add the parameter to the command object
Connection.AddParameter(_prm_IdProduct);
}
else if (m_evColumnToLoadVia == Column.ProductCode)
{
// Specify the stored procedure
Connection.SetStoredProcedure("sp_vgProduct_LoadByProductCode");
// Set up the parameter
_prm_ProductCode.Value = _Data.ProductCode;
_prm_ProductCode.Direction = ParameterDirection.Input;
// Add the parameter to the command object
Connection.AddParameter(_prm_ProductCode);
}
else if (m_evColumnToLoadVia == Column.ProductName)
{
// Specify the stored procedure
Connection.SetStoredProcedure("sp_vgProduct_LoadByProductName");
// Set up the parameter
_prm_ProductName.Value = _Data.ProductName;
_prm_ProductName.Direction = ParameterDirection.Input;
// Add the parameter to the command object
Connection.AddParameter(_prm_ProductName);
}
}
/// <summary>
/// Complete the process of loading the object from the database
/// </summary>
protected override void LoadComplete()
{
// Just a place holder at present
}
/// <summary>
/// Loads the object with values from thedatabase for the passed-in unique id
/// </summary>
public void LoadViaId(int p_nId)
{
// Initialise the column values
_Data.Init();
// Save the Id value
_Data.IdProduct = p_nId;
// Specify the field that the object should be loaded on
m_evColumnToLoadVia = Column.IdProduct;
// Let the base class control the loading process
Load();
return;
}
/// <summary>
/// Loads the object with values from the database for the passed-in key: ProductCode
/// </summary>
/// <param name = "p_lProductCode">eApplication + eVersion + eBeta = unique key</param>
public void LoadViaProductCode(long p_lProductCode)
{
// Initialise the column values
_Data.Init();
// Save the key value
_Data.ProductCode = p_lProductCode;
// Set the column to load by
m_evColumnToLoadVia = Column.ProductCode;
// Let the base class control the loading process
Load();
return;
}
/// <summary>
/// Loads the object with values from the database for the passed-in key: ProductName
/// </summary>
/// <param name = "p_sProductName">The display name of the product</param>
public void LoadViaProductName(string p_sProductName)
{
// Initialise the column values
_Data.Init();
// Save the key value
_Data.ProductName = p_sProductName;
// Set the column to load by
m_evColumnToLoadVia = Column.ProductName;
// Let the base class control the loading process
Load();
return;
}
/// <summary>
/// Preparation for inserting the object into the database
/// </summary>
protected override void InsertPrepare()
{
// Specify the stored procedure
Connection.SetStoredProcedure("sp_vgProduct_Insert");
// Set the values in the input parameters
_prm_ProductCode.Value = _Data.ProductCode;
_prm_ProductName.Value = _Data.ProductName;
_prm_DateStartSubscription.Value = _Data.DateStartSubscription;
_prm_DateEndSubscription.Value = _Data.DateEndSubscription;
_prm_DateStartDownload.Value = _Data.DateStartDownload;
_prm_DateEndDownload.Value = _Data.DateEndDownload;
_prm_Application.Value = (Int16)(_Data.Application);
_prm_Version.Value = (Int16)(_Data.Version);
_prm_Beta.Value = (Int16)(_Data.Beta);
if (_Data.DownloadFileName == null || _Data.DownloadFileName.Length == 0)
{
_prm_DownloadFileName.Value = DBNull.Value;
}
else
{
_prm_DownloadFileName.Value = _Data.DownloadFileName;
}
// Set the direction of the input parameters
_prm_IdProduct.Direction = ParameterDirection.Output;
_prm_ProductCode.Direction = ParameterDirection.Input;
_prm_ProductName.Direction = ParameterDirection.Input;
_prm_DateStartSubscription.Direction = ParameterDirection.Input;
_prm_DateEndSubscription.Direction = ParameterDirection.Input;
_prm_DateStartDownload.Direction = ParameterDirection.Input;
_prm_DateEndDownload.Direction = ParameterDirection.Input;
_prm_DownloadFileName.Direction = ParameterDirection.Input;
_prm_Application.Direction = ParameterDirection.Input;
_prm_Version.Direction = ParameterDirection.Input;
_prm_Beta.Direction = ParameterDirection.Input;
// Set the direction of the output parameters
//ParamDirectionOutput:Category-InitByDB
//ParamDirectionOutput:Category-SetByDB
// Add the input parameters to the command object
Connection.AddParameter(_prm_IdProduct);
Connection.AddParameter(_prm_ProductCode);
Connection.AddParameter(_prm_ProductName);
Connection.AddParameter(_prm_DateStartSubscription);
Connection.AddParameter(_prm_DateEndSubscription);
Connection.AddParameter(_prm_DateStartDownload);
Connection.AddParameter(_prm_DateEndDownload);
Connection.AddParameter(_prm_DownloadFileName);
Connection.AddParameter(_prm_Application);
Connection.AddParameter(_prm_Version);
Connection.AddParameter(_prm_Beta);
// Add the output parameters to the command object
//ParamAdd:Category-InitByDB
//ParamAdd:Category-SetByDB
}
/// <summary>
/// Complete the process of inserting the object into the database
/// </summary>
protected override void InsertComplete()
{
// Get the value of the Primary Key
_Data.IdProduct = (int)(_prm_IdProduct.Value);
// Set the Id in related objects
FireIdSet(_Data.IdProduct);
// Get the values that have been assigned by the database
//ParamValueLoad:Category-InitByDB
//ParamValueLoad:Category-SetByDB
// Set the key values in the embedded collections
// FixUp:DB Filtered Collection - is this handled by FireIdSet???
}
/// <summary>
/// Preparation for updating the object into the database
/// </summary>
protected override void UpdatePrepare()
{
// Specify the stored procedure
Connection.SetStoredProcedure("sp_vgProduct_Update");
// Set the CCN
m_nCCN = _Data.CCN;
// Set the values in the input parameters
_prm_IdProduct.Value = _Data.IdProduct;
_prm_ProductCode.Value = _Data.ProductCode;
_prm_ProductName.Value = _Data.ProductName;
_prm_DateStartSubscription.Value = _Data.DateStartSubscription;
_prm_DateEndSubscription.Value = _Data.DateEndSubscription;
_prm_DateStartDownload.Value = _Data.DateStartDownload;
_prm_DateEndDownload.Value = _Data.DateEndDownload;
_prm_Application.Value = (Int16)(_Data.Application);
_prm_Version.Value = (Int16)(_Data.Version);
_prm_Beta.Value = (Int16)(_Data.Beta);
if (_Data.DownloadFileName == null || _Data.DownloadFileName.Length == 0)
{
_prm_DownloadFileName.Value = DBNull.Value;
}
else
{
_prm_DownloadFileName.Value = _Data.DownloadFileName;
}
// Set the direction on the input parameters
_prm_IdProduct.Direction = ParameterDirection.Input;
_prm_ProductCode.Direction = ParameterDirection.Input;
_prm_ProductName.Direction = ParameterDirection.Input;
_prm_DateStartSubscription.Direction = ParameterDirection.Input;
_prm_DateEndSubscription.Direction = ParameterDirection.Input;
_prm_DateStartDownload.Direction = ParameterDirection.Input;
_prm_DateEndDownload.Direction = ParameterDirection.Input;
_prm_DownloadFileName.Direction = ParameterDirection.Input;
_prm_Application.Direction = ParameterDirection.Input;
_prm_Version.Direction = ParameterDirection.Input;
_prm_Beta.Direction = ParameterDirection.Input;
// Set the direction on the output parameters
//ParamDirectionOutput:Category-SetByDB
// Add the input parameters to the command object
Connection.AddParameter(_prm_IdProduct);
Connection.AddParameter(_prm_ProductCode);
Connection.AddParameter(_prm_ProductName);
Connection.AddParameter(_prm_DateStartSubscription);
Connection.AddParameter(_prm_DateEndSubscription);
Connection.AddParameter(_prm_DateStartDownload);
Connection.AddParameter(_prm_DateEndDownload);
Connection.AddParameter(_prm_DownloadFileName);
Connection.AddParameter(_prm_Application);
Connection.AddParameter(_prm_Version);
Connection.AddParameter(_prm_Beta);
// Add the output parameters to the command object
//ParamAdd:Category-SetByDB
}
/// <summary>
/// Complete the process of updating the object into the database
/// </summary>
protected override void UpdateComplete()
{
// Update the CCN
_Data.CCN = m_nCCN;
// Get the values that have been assigned by the database
//ParamValueLoad:Category-SetByDB
}
/// <summary>
/// Preparation for deleting the object from the database
/// </summary>
protected override void DeletePrepare()
{
// Clear the parameters
Connection.ClearParameters();
// Specify the stored procedure
Connection.SetStoredProcedure("sp_vgProduct_Delete");
// Set the values in the input parameters
_prm_IdProduct.Value = _Data.IdProduct;
// Set the directions of the parameters
_prm_IdProduct.Direction = ParameterDirection.Input;
// Add the parameters to the command object
Connection.AddParameter(_prm_IdProduct);
}
/// <summary>
/// Complete the process of deleting the object in the database
/// </summary>
protected override void DeleteComplete()
{
// Reset the object's Id
_Data.IdProduct = -1;
}
/// <summary>
/// Copy the passed-in data structure into this object
/// </summary>
public void Attach(Product p_Product)
{
_Data.Copy(p_Product,abcUtility.CopyType.All);
}
/// <summary>
/// Return a copy of this object's data structure in the passed-in parameter
/// </summary>
public void Detach(ref Product p_Product)
{
p_Product.Copy(_Data,abcUtility.CopyType.All);
return;
}
/// <summary>
/// Returns true if the Attribute is required (NotNull)
/// </summary>
public bool IsRequired(Column p_Ordinal)
{
bool bIsRequired = false; // This is returned
switch (p_Ordinal)
{
case Column.ProductCode:
{
bIsRequired = true;
break;
}
case Column.ProductName:
{
bIsRequired = true;
break;
}
case Column.DateStartSubscription:
{
bIsRequired = true;
break;
}
case Column.DateEndSubscription:
{
bIsRequired = true;
break;
}
case Column.DateStartDownload:
{
bIsRequired = true;
break;
}
case Column.DateEndDownload:
{
bIsRequired = true;
break;
}
case Column.DownloadFileName:
{
bIsRequired = true;
break;
}
case Column.Application:
{
bIsRequired = true;
break;
}
case Column.Version:
{
bIsRequired = true;
break;
}
case Column.Beta:
{
bIsRequired = true;
break;
}
}
return bIsRequired;
}
// ----------------------------------------------------------------------------------
// Properties
// ----------------------------------------------------------------------------------
/// <summary>
/// IdProduct - The unique database identifier of the object
/// </summary>
public virtual int IdProduct
{
get
{
return _Data.IdProduct;
}
}
/// <summary>
/// The Concurrency Control Number
/// </summary>
public virtual int CCN
{
get
{
return _Data.CCN;
}
}
/// <summary>
/// The date from which subscriptions can be taken out
/// </summary>
public virtual DateTime? DateStartSubscription
{
get
{
return _Data.DateStartSubscription;
}
set
{
if (!abcDateUtils.AreDatesEqualToSecond(_Data.DateStartSubscription,value))
{
_Data.DateStartSubscription = value;
IsDirty = true;
}
}
}
/// <summary>
/// The date up to which subscriptions can be taken out until
/// </summary>
public virtual DateTime? DateEndSubscription
{
get
{
return _Data.DateEndSubscription;
}
set
{
if (!abcDateUtils.AreDatesEqualToSecond(_Data.DateEndSubscription,value))
{
_Data.DateEndSubscription = value;
IsDirty = true;
}
}
}
/// <summary>
/// The date from which the product can be downloaded
/// </summary>
public virtual DateTime? DateStartDownload
{
get
{
return _Data.DateStartDownload;
}
set
{
if (!abcDateUtils.AreDatesEqualToSecond(_Data.DateStartDownload,value))
{
_Data.DateStartDownload = value;
IsDirty = true;
}
}
}
/// <summary>
/// The date up to which the product can be downloaded
/// </summary>
public virtual DateTime? DateEndDownload
{
get
{
return _Data.DateEndDownload;
}
set
{
if (!abcDateUtils.AreDatesEqualToSecond(_Data.DateEndDownload,value))
{
_Data.DateEndDownload = value;
IsDirty = true;
}
}
}
/// <summary>
/// The display name of the product
/// </summary>
public virtual string ProductName
{
get
{
return _Data.ProductName;
}
set
{
if (!abcStringUtils.AreStringsEqual(_Data.ProductName,value))
{
_Data.ProductName = value;
IsDirty = true;
}
}
}
/// <summary>
/// The name of the file for download purposes
/// </summary>
public virtual string DownloadFileName
{
get
{
return _Data.DownloadFileName;
}
set
{
if (!abcStringUtils.AreStringsEqual(_Data.DownloadFileName,value))
{
_Data.DownloadFileName = value;
IsDirty = true;
}
}
}
/// <summary>
/// Enum value denoting the Application
/// </summary>
public virtual ViziGenEnums.eApplication Application
{
get
{
return _Data.Application;
}
set
{
if (_Data.Application != value)
{
_Data.Application = value;
IsDirty = true;
}
}
}
/// <summary>
/// Enum value indicating the version
/// </summary>
public virtual ViziGenEnums.eVersion Version
{
get
{
return _Data.Version;
}
set
{
if (_Data.Version != value)
{
_Data.Version = value;
IsDirty = true;
}
}
}
/// <summary>
/// Enum value indicating whether beta or release
/// </summary>
public virtual ViziGenEnums.eBeta Beta
{
get
{
return _Data.Beta;
}
set
{
if (_Data.Beta != value)
{
_Data.Beta = value;
IsDirty = true;
}
}
}
/// <summary>
/// eApplication + eVersion + eBeta = unique key
/// </summary>
public virtual long? ProductCode
{
get
{
return _Data.ProductCode;
}
set
{
if (_Data.ProductCode != value)
{
_Data.ProductCode = value;
IsDirty = true;
}
}
}
// ----------------------------------------------------------------------------------
// Unique Key validation functions
// ----------------------------------------------------------------------------------
/// <summary>
/// Check that ProductName is unique
/// </summary>
/// <param name="p_ProductDA">Object for which ProductName will be checked</param>
/// <returns>True if ProductName is unique within the database else false</returns>
public static bool IsProductNameUnique(ProductDA p_ProductDA)
{
bool bUnique = true; // this is returned
ProductDA oProductDA = null;
bool bDispose = false;
try
{
if (p_ProductDA.Connection == null)
{
oProductDA = new ProductDA();
bDispose = true;
}
else
{
oProductDA = new ProductDA(p_ProductDA.Connection);
}
oProductDA.LoadViaProductName(p_ProductDA.ProductName);
if (oProductDA.IsLoaded() && oProductDA.IdProduct != p_ProductDA.IdProduct)
{
bUnique = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (bDispose && oProductDA != null)
{
oProductDA.Dispose();
}
}
return bUnique;
}
/// <summary>
/// Check that ProductCode is unique
/// </summary>
/// <param name="p_ProductDA">Object for which ProductCode will be checked</param>
/// <returns>True if ProductCode is unique within the database else false</returns>
public static bool IsProductCodeUnique(ProductDA p_ProductDA)
{
bool bUnique = true; // this is returned
ProductDA oProductDA = null;
bool bDispose = false;
try
{
if (p_ProductDA.Connection == null)
{
oProductDA = new ProductDA();
bDispose = true;
}
else
{
oProductDA = new ProductDA(p_ProductDA.Connection);
}
oProductDA.LoadViaProductCode(p_ProductDA.ProductCode ?? -1);
if (oProductDA.IsLoaded() && oProductDA.IdProduct != p_ProductDA.IdProduct)
{
bUnique = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (bDispose && oProductDA != null)
{
oProductDA.Dispose();
}
}
return bUnique;
}
/// <summary>
/// Check that all unique keys are unique
/// </summary>
/// <param name="p_ProductDA">the object to be checked</param>
/// <returns></returns>
public static void ValidateUniqueKeys(ProductDA p_ProductDA, List<ErrorDetail> p_ErrorDetails)
{
// Check ProductCode
if (!IsProductCodeUnique(p_ProductDA))
{
ErrorDetail oErrorDetail = new ErrorDetail();
oErrorDetail.Ordinal = (int)(Column.ProductCode);
oErrorDetail.ErrorMessage = ViziGenDAResources.GetString("Validation.NotUnique");
p_ErrorDetails.Add(oErrorDetail);
}
// Check ProductName
if (!IsProductNameUnique(p_ProductDA))
{
ErrorDetail oErrorDetail = new ErrorDetail();
oErrorDetail.Ordinal = (int)(Column.ProductName);
oErrorDetail.ErrorMessage = ViziGenDAResources.GetString("Validation.NotUnique");
p_ErrorDetails.Add(oErrorDetail);
}
}
}
}