/// <summary>
/// Update the specified Document
/// </summary>
/// <param name="theAsset"></param>
/// <returns></returns>
public int DocumentUpdate(Document aDocument)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
try
{
string cmdText =
"UPDATE DOCUMENTS " +
"SET _NAME = @cName, _PATH = @cPath, _SCOPE = @cScope, _PARENTID = @nParentId " +
"WHERE _DOCUMENTID = @nDocumentID";
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[5];
spParams[0] = new SqlCeParameter("@nDocumentID", aDocument.DocumentID);
spParams[1] = new SqlCeParameter("@cPath", aDocument.Path);
spParams[2] = new SqlCeParameter("@cName", aDocument.Name);
spParams[3] = new SqlCeParameter("@cScope", (int)aDocument.Scope);
spParams[4] = new SqlCeParameter("@nParentId", aDocument.ParentID);
using (SqlCeCommand command = new SqlCeCommand(cmdText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlParameter[] spParams = new SqlParameter[5];
spParams[0] = new SqlParameter("@nDocumentID", aDocument.DocumentID);
spParams[1] = new SqlParameter("@cPath", aDocument.Path);
spParams[2] = new SqlParameter("@cName", aDocument.Name);
spParams[3] = new SqlParameter("@cScope", (int)aDocument.Scope);
spParams[4] = new SqlParameter("@nParentId", aDocument.ParentID);
using (SqlCommand command = new SqlCommand(cmdText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
}
}
}
catch (SqlException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (SqlCeException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out");
}
return(0);
}
/// <summary>
/// Return a table of the intances of the specified application
/// </summary>
/// <param name="applicationID"></param>
/// <returns></returns>
public DataTable GetApplicationInstances(int applicationID)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
DataTable applicationsTable = new DataTable(TableNames.APPLICATION_INSTANCES);
if (compactDatabaseType)
{
try
{
using (SqlCeCommand command = new SqlCeCommand("SELECT APPLICATION_INSTANCES._INSTANCEID ,APPLICATION_INSTANCES._APPLICATIONID ,APPLICATION_INSTANCES._ASSETID ,APPLICATION_INSTANCES._PRODUCTID ,APPLICATION_INSTANCES._CDKEY " +
",APPLICATIONS._NAME ,APPLICATIONS._VERSION ,APPLICATIONS._PUBLISHER ,APPLICATIONS._GUID ,APPLICATIONS._IGNORED ,APPLICATIONS._ALIASED_TOID ,APPLICATIONS._USER_DEFINED " +
",ASSETS._NAME AS ASSETNAME " +
",ASSET_TYPES._ICON AS ASSETICON " +
",LOCATIONS._FULLNAME AS FULLLOCATIONNAME " +
",LOCATIONS._NAME AS LOCATIONNAME " +
",DOMAINS._NAME AS DOMAINNAME " +
"FROM APPLICATION_INSTANCES " +
"LEFT JOIN APPLICATIONS ON (APPLICATION_INSTANCES._APPLICATIONID = APPLICATIONS._APPLICATIONID) " +
"LEFT JOIN ASSETS ON (APPLICATION_INSTANCES._ASSETID = ASSETS._ASSETID) " +
"LEFT JOIN ASSET_TYPES ON (ASSETS._ASSETTYPEID = ASSET_TYPES._ASSETTYPEID) " +
"LEFT JOIN LOCATIONS ON (ASSETS._LOCATIONID = LOCATIONS._LOCATIONID) " +
"LEFT JOIN DOMAINS ON (ASSETS._DOMAINID = DOMAINS._DOMAINID) " +
"WHERE APPLICATION_INSTANCES._APPLICATIONID = @applicationID " +
"AND ASSETS._STOCK_STATUS <> 3", DatabaseConnection.OpenCeConnection()))
{
command.Parameters.AddWithValue("@applicationID", applicationID);
new SqlCeDataAdapter(command).Fill(applicationsTable);
}
}
catch (SqlCeException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
}
else
{
AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess();
applicationsTable = lAuditWizardDataAccess.GetApplicationInstances(applicationID);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out");
}
return(applicationsTable);
}
/// <summary>
/// Add a new Document to the database
/// </summary>
/// <param name="document">The document to add</param>
/// <returns></returns>
public int DocumentAdd(Document aDocument)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
int lItemID = 0;
if (compactDatabaseType)
{
try
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
string commandText =
"INSERT INTO DOCUMENTS " +
"(_SCOPE ,_NAME ,_PATH ,_PARENTID) " +
"VALUES " +
"(@nScope, @cName, @cPath, @nParentID)";
SqlCeParameter[] spParams = new SqlCeParameter[4];
spParams[0] = new SqlCeParameter("@nScope", (int)aDocument.Scope);
spParams[1] = new SqlCeParameter("@nParentID", aDocument.ParentID);
spParams[2] = new SqlCeParameter("@cName", aDocument.Name);
spParams[3] = new SqlCeParameter("@cPath", aDocument.Path);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
using (SqlCeCommand command = new SqlCeCommand("SELECT @@IDENTITY", conn))
{
lItemID = Convert.ToInt32(command.ExecuteScalar());
}
}
}
catch (SqlCeException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
}
else
{
AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess();
lItemID = lAuditWizardDataAccess.DocumentAdd(aDocument);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out with id : " + lItemID);
}
return(lItemID);
}
/// <summary>
/// Delete all application instance records for the specified asset
/// </summary>
/// <param name="assetID"></param>
/// <returns></returns>
public int ApplicationInstanceDelete(int assetID)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
string commandText =
"select ai._instanceid from application_instances ai " +
"left join applications a ON (a._applicationid = ai._applicationid ) " +
"where ai._assetid = " + assetID + " " +
"and a._assigned_fileid = 0";
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
using (SqlCeDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ApplicationInstanceDeleteByInstanceId(reader.GetInt32(0));
}
}
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ApplicationInstanceDeleteByInstanceId(reader.GetInt32(0));
}
}
}
}
}
}
catch (SqlException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (SqlCeException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out");
}
return(0);
}
public void Update(int aReportScheduleId, byte[] aAppointmentData)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
string commandText =
"UPDATE REPORT_SCHEDULES " +
"SET _APPOINTMENTDATA = @cAppointmentData " +
"WHERE _REPORTSCHEDULEID = @nReportScheduleId";
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[2];
spParams[0] = new SqlCeParameter("@cAppointmentData", aAppointmentData);
spParams[1] = new SqlCeParameter("@nReportScheduleId", aReportScheduleId);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlParameter[] spParams = new SqlParameter[2];
spParams[0] = new SqlParameter("@cAppointmentData", aAppointmentData);
spParams[1] = new SqlParameter("@nReportScheduleId", aReportScheduleId);
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
}
}
}
catch (SqlException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (SqlCeException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out");
}
}
/// <summary>
/// Return a table containing all of the Operations defined
/// </summary>
/// <returns></returns>
public DataTable EnumerateOperations(Operation.OPERATION aOperationType, Operation.STATUS aStatus)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
DataTable table = new DataTable(TableNames.OPERATIONS);
if (compactDatabaseType)
{
try
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
string commandText =
"SELECT OPERATIONS.* " +
",ASSETS._NAME AS ASSETNAME " +
"FROM OPERATIONS " +
"LEFT JOIN ASSETS ON (ASSETS._ASSETID = OPERATIONS._ASSETID) ";
if ((int)aOperationType != -1)
{
commandText += "WHERE OPERATIONS._OPERATION = @nOperation ";
}
if ((int)aStatus != -1)
{
if ((int)aOperationType == -1)
{
commandText += "WHERE OPERATIONS._STATUS = @nStatus ";
}
else
{
commandText += "AND OPERATIONS._STATUS = @nStatus ";
}
}
commandText += "ORDER BY _OPERATIONID";
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
if ((int)aOperationType != -1)
{
command.Parameters.AddWithValue("@nOperation", (int)aOperationType);
}
if ((int)aStatus != -1)
{
command.Parameters.AddWithValue("@nStatus", (int)aStatus);
}
new SqlCeDataAdapter(command).Fill(table);
}
}
}
catch (SqlCeException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
}
else
{
AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess();
table = lAuditWizardDataAccess.EnumerateOperations(aOperationType, aStatus);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out");
}
return(table);
}
/// <summary>
/// Update the specified Operation
/// </summary>
/// <param name="theAsset"></param>
/// <returns></returns>
public int OperationUpdate(Operation aOperation)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
if (compactDatabaseType)
{
try
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
string commandText =
"UPDATE OPERATIONS SET " +
"_END_DATE = @dtEndDate, " +
"_STATUS = @nStatus, " +
"_ERRORTEXT = @cErrorText " +
"WHERE _OPERATIONID = @nOperationID";
SqlCeParameter[] spParams = new SqlCeParameter[4];
spParams[0] = new SqlCeParameter("@nOperationID", aOperation.OperationID);
if (aOperation.EndDate.Ticks == 0)
{
spParams[1] = new SqlCeParameter("@dtEndDate", DBNull.Value);
}
else
{
spParams[1] = new SqlCeParameter("@dtEndDate", aOperation.EndDate);
}
spParams[2] = new SqlCeParameter("@nStatus", (int)aOperation.Status);
spParams[3] = new SqlCeParameter("@cErrorText", aOperation.ErrorText);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
}
}
catch (SqlCeException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
}
else
{
AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess();
lAuditWizardDataAccess.OperationUpdate(aOperation);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out");
}
return(0);
}
/// <summary>
/// Add a new Operation to the database
/// </summary>
/// <param name="operation">The Operation to add</param>
/// <returns></returns>
public int OperationAdd(Operation aOperation)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
int lItemID = 0;
if (compactDatabaseType)
{
try
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
string commandText =
"INSERT INTO OPERATIONS " +
"(_OPERATION ,_ASSETID ,_START_DATE ,_STATUS) " +
"VALUES " +
"(@nOperation, @nAssetID, @dtToday, 0)";
SqlCeParameter[] spParams = new SqlCeParameter[3];
spParams[0] = new SqlCeParameter("@nOperation", (int)aOperation.OperationType);
spParams[1] = new SqlCeParameter("@nAssetID", aOperation.AssetID);
spParams[2] = new SqlCeParameter("@dtToday", aOperation.StartDate);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
using (SqlCeCommand command = new SqlCeCommand("SELECT @@IDENTITY", conn))
{
lItemID = Convert.ToInt32(command.ExecuteScalar());
}
}
}
catch (SqlCeException ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
catch (Exception ex)
{
Utility.DisplayErrorMessage("A database error has occurred in AuditWizard." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
}
else
{
AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess();
lItemID = lAuditWizardDataAccess.OperationAdd(aOperation);
}
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " out");
}
return(lItemID);
}
