public static DataTable GetDataTable(SqlCommand cmd)
{
DataTable dt = new DataTable();
try
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
cmd.Connection = conn;
new SqlDataAdapter(cmd).Fill(dt);
}
}
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 (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);
}
return(dt);
}
private string PerformScalarQuery(string commandText)
{
string returnValue = String.Empty;
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
object result = command.ExecuteScalar();
if (result != null)
{
returnValue = command.ExecuteScalar().ToString();
}
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
object result = command.ExecuteScalar();
if (result != null)
{
returnValue = command.ExecuteScalar().ToString();
}
}
}
}
}
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);
}
return(returnValue);
}
private DataTable PerformQuery(string aCommandText)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
DataTable statisticsTable = new DataTable();
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(aCommandText, conn))
{
new SqlCeDataAdapter(command).Fill(statisticsTable);
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(aCommandText, conn))
{
new SqlDataAdapter(command).Fill(statisticsTable);
}
}
}
}
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(statisticsTable);
}
public void ApplicationInstanceDeleteByInstanceId(int aInstanceId)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
try
{
string commandText = "DELETE FROM APPLICATION_INSTANCES WHERE _INSTANCEID = " + aInstanceId;
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
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");
}
}
public int GetApplicationInstanceCountByAssetName(string aPublisher, string aApplicationName, int aAssetId)
{
string commandText = String.Format(
"SELECT COUNT(*) " +
"FROM APPLICATIONS ap " +
"LEFT JOIN APPLICATION_INSTANCES ai ON (ai._applicationid = ap._applicationid) " +
"WHERE ai._assetid = '{0}' " +
"AND ap._publisher = '{1}' " +
"AND ap._name = '{2}'", aAssetId, aPublisher, aApplicationName);
int returnValue = 0;
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
returnValue = (int)command.ExecuteScalar();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
returnValue = (int)command.ExecuteScalar();
}
}
}
}
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);
}
return(returnValue);
}
public void PurgeNewsFeed()
{
try
{
if (compactDatabaseType)
{
string commandText = String.Format(
"DELETE " +
"FROM NEWS_FEED " +
"WHERE ID NOT IN (SELECT TOP (250) ID FROM NEWS_FEED ORDER BY news_date DESC)");
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.ExecuteNonQuery();
}
}
}
else
{
string commandText = String.Format(
"DELETE " +
"FROM NEWS_FEED " +
"WHERE ID NOT IN (SELECT TOP 250 ID FROM NEWS_FEED ORDER BY news_date DESC)");
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
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);
}
}
public int CheckPublisherExists(string aPublisherName)
{
int lCount = 0;
string commandText = String.Format(
"SELECT COUNT(*) FROM PUBLISHER_ALIAS " +
"WHERE _ALIASED_PUBLISHER = '{0}'", aPublisherName);
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
lCount = Convert.ToInt32(command.ExecuteScalar());
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
lCount = Convert.ToInt32(command.ExecuteScalar());
}
}
}
}
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);
}
return(lCount);
}
public void DeleteAppt(int aAppointmentId)
{
try
{
string commandText =
"DELETE FROM REPORT_SCHEDULES " +
"WHERE _REPORTSCHEDULEID = " + aAppointmentId;
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
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);
}
}
private void ExecuteNonQuery(string aCommandText)
{
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(aCommandText, conn))
{
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(aCommandText, conn))
{
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.");
if (ex.Message != "Cannot alter column of type NTEXT or IMAGE [ Column Name = _REPORTDATA ]")
{
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);
}
}
public string RunManintenanceScriptFull(string aCmdText)
{
string lMsg = "Command(s) completed successfully.";
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlTransaction transaction = conn.BeginTransaction();
try
{
string[] commandTexts = aCmdText.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Transaction = transaction;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception ex)
{
lMsg = ex.Message;
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
try
{
transaction.Rollback();
}
catch (Exception)
{
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
}
}
return(lMsg);
}
public void CheckHBStoredProcs()
{
try
{
if (compactDatabaseType)
{
return;
}
else
{
SettingsDAO lSettingsDao = new SettingsDAO();
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
string latestHBSPVersion = "1.1";
if (lSettingsDao.GetSetting("HBSPAdded", false) != latestHBSPVersion)
{
ExecuteDatabaseCreateScript(Application.StartupPath + @".\db\CreateHBStoredProcedures.sql", conn);
lSettingsDao.SetSetting("HBSPAdded", latestHBSPVersion, false);
}
}
}
}
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 (Exception ex)
{
Utility.DisplayErrorMessage("There was an error adding the HelpBox stored procedures." + Environment.NewLine + Environment.NewLine +
"Please see the log file for further details.");
logger.Error("Exception in " + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
}
/// <summary>
/// Return a table containing a single supplier
/// </summary>
/// <returns></returns>
public DataTable SelectSupplierByID(int aSupplierID)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
DataTable supplierTable = new DataTable(TableNames.ALERTS);
try
{
string commandText =
"SELECT _SUPPLIERID, _NAME ,_ADDRESS1 ,_ADDRESS2 ,_CITY, _STATE ,_ZIP ,_TELEPHONE " +
",_CONTACT_NAME ,_CONTACT_EMAIL, _WWW ,_FAX ,_NOTES " +
"FROM SUPPLIERS " +
"WHERE _SUPPLIERID = " + aSupplierID +
" ORDER BY _SUPPLIERID";
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
new SqlCeDataAdapter(command).Fill(supplierTable);
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
new SqlDataAdapter(command).Fill(supplierTable);
}
}
}
}
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(supplierTable);
}
public void SetSetting(string key, string value)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
int lCount = 0;
string commandText = "SELECT COUNT(*) FROM LOCATION_SETTINGS WHERE _KEY = @key";
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddWithValue("@key", key);
lCount = Convert.ToInt32(command.ExecuteScalar());
}
if (lCount == 0)
{
commandText = "INSERT INTO LOCATION_SETTINGS (_KEY,_VALUE) VALUES (@key ,@value)";
}
else
{
commandText = "UPDATE LOCATION_SETTINGS SET _VALUE = @value WHERE _KEY = @key";
}
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddWithValue("@key", key);
if (value == null)
{
value = String.Empty;
}
command.Parameters.AddWithValue("@value", value);
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
int lCount = 0;
string commandText = "SELECT COUNT(*) FROM LOCATION_SETTINGS WHERE _KEY = @key";
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddWithValue("@key", key);
lCount = Convert.ToInt32(command.ExecuteScalar());
}
if (lCount == 0)
{
commandText = "INSERT INTO LOCATION_SETTINGS (_KEY,_VALUE) VALUES (@key ,@value)";
}
else
{
commandText = "UPDATE LOCATION_SETTINGS SET _VALUE = @value WHERE _KEY = @key";
}
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddWithValue("@key", key);
if (value == null)
{
value = String.Empty;
}
command.Parameters.AddWithValue("@value", value);
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");
}
}
public void Update(int aReportId, string aReportName, string aReportData)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
string commandText =
"UPDATE REPORTS " +
"SET _REPORTNAME = @cReportName, _REPORTDATA = @cReportData " +
"WHERE _REPORTID = @nReportId";
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[3];
spParams[0] = new SqlCeParameter("@cReportName", aReportName);
spParams[1] = new SqlCeParameter("@cReportData", aReportData);
spParams[2] = new SqlCeParameter("@nReportId", aReportId);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlParameter[] spParams = new SqlParameter[3];
spParams[0] = new SqlParameter("@cReportName", aReportName);
spParams[1] = new SqlParameter("@cReportData", aReportData);
spParams[2] = new SqlParameter("@nReportId", aReportId);;
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");
}
}
public string Add(IPAddressRange ipAddress)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
string returnSetting = String.Empty;
try
{
string cmdText =
"INSERT INTO IP_ADDRESSES ( " +
"_IP_START, _IP_END, _ACTIVE, _IP_TYPE) " +
"VALUES (@ipStart, @ipEnd, @active, @type)";
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(cmdText, conn))
{
command.Parameters.AddWithValue("@ipStart", ipAddress.IpStart);
command.Parameters.AddWithValue("@ipEnd", ipAddress.IpEnd);
command.Parameters.AddWithValue("@active", ipAddress.Active);
command.Parameters.AddWithValue("@type", (int)ipAddress.TypeOfIp);
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(cmdText, conn))
{
command.Parameters.AddWithValue("@ipStart", ipAddress.IpStart);
command.Parameters.AddWithValue("@ipEnd", ipAddress.IpEnd);
command.Parameters.AddWithValue("@active", ipAddress.Active);
command.Parameters.AddWithValue("@type", (int)ipAddress.TypeOfIp);
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 with setting : " + returnSetting);
}
return(returnSetting);
}
/// <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>
/// Update the specified Note
/// </summary>
/// <param name="aNote"></param>
/// <returns></returns>
public int NoteUpdate(Note aNote)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
try
{
string cmdText =
"UPDATE NOTES " +
"SET " +
"_DATE = @dDate, " +
"_TEXT = @cText, " +
"_PARENTID = @nParentId, " +
"_SCOPE = @cScope " +
"WHERE _NOTEID = @nNoteID";
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[5];
spParams[0] = new SqlCeParameter("@nNoteID", aNote.NoteID);
spParams[1] = new SqlCeParameter("@dDate", DateTime.Now);
spParams[2] = new SqlCeParameter("@cText", aNote.Text);
spParams[3] = new SqlCeParameter("@cScope", (int)aNote.Scope);
spParams[4] = new SqlCeParameter("@nParentId", aNote.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("@nNoteID", aNote.NoteID);
spParams[1] = new SqlParameter("@dDate", DateTime.Now);
spParams[2] = new SqlParameter("@cText", aNote.Text);
spParams[3] = new SqlParameter("@cScope", (int)aNote.Scope);
spParams[4] = new SqlParameter("@nParentId", aNote.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>
/// Add a new Note to the database
/// </summary>
/// <param name="aNote"></param>
/// <returns></returns>
public int NoteAdd(Note aNote)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
int lItemId = 0;
try
{
string cmdText =
"INSERT INTO NOTES " +
"(_DATE ,_SCOPE ,_PARENTID ,_TEXT ,_USER) " +
"VALUES " +
"(@dtDate, @nScope, @nParentID, @cText, @cUser)";
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[5];
spParams[0] = new SqlCeParameter("@dtDate", aNote.DateOfNote);
spParams[1] = new SqlCeParameter("@nScope", (int)aNote.Scope);
spParams[2] = new SqlCeParameter("@nParentID", aNote.ParentID);
spParams[3] = new SqlCeParameter("@cText", aNote.Text);
spParams[4] = new SqlCeParameter("@cUser", aNote.User);
using (SqlCeCommand command = new SqlCeCommand(cmdText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
using (SqlCeCommand command = new SqlCeCommand("SELECT @@IDENTITY", conn))
{
lItemId = Convert.ToInt32(command.ExecuteScalar());
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlParameter[] spParams = new SqlParameter[5];
spParams[0] = new SqlParameter("@dtDate", aNote.DateOfNote);
spParams[1] = new SqlParameter("@nScope", (int)aNote.Scope);
spParams[2] = new SqlParameter("@nParentID", aNote.ParentID);
spParams[3] = new SqlParameter("@cText", aNote.Text);
spParams[4] = new SqlParameter("@cUser", aNote.User);
using (SqlCommand command = new SqlCommand(cmdText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
using (SqlCommand command = new SqlCommand("SELECT @@IDENTITY", conn))
{
lItemId = Convert.ToInt32(command.ExecuteScalar());
}
}
}
}
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(lItemId);
}
//public void PurgeNewsFeed(int days)
//{
// string commandText = String.Format(
// "delete " +
// "from news_feed " +
// "where (datediff(day, news_date, getdate()) >= {0})", days);
// try
// {
// if (compactDatabaseType)
// {
// using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
// {
// using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
// {
// command.ExecuteNonQuery();
// }
// }
// }
// else
// {
// using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
// {
// using (SqlCommand command = new SqlCommand(commandText, conn))
// {
// 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);
// }
//}
public void AddNewsFeed(int priority, string newsText)
{
string commandText = String.Format(
"INSERT INTO NEWS_FEED " +
"(news_text, news_date, priority) " +
"VALUES (@newsText, @newsDate, @newsPriority)", newsText, DateTime.Now, priority);
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[3];
spParams[0] = new SqlCeParameter("@newsText", newsText);
spParams[1] = new SqlCeParameter("@newsDate", DateTime.Now);
spParams[2] = new SqlCeParameter("@newsPriority", priority);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlParameter[] spParams = new SqlParameter[3];
spParams[0] = new SqlParameter("@newsText", newsText);
spParams[1] = new SqlParameter("@newsDate", DateTime.Now);
spParams[2] = new SqlParameter("@newsPriority", priority);
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");
}
}
public string GetSetting(string aKey)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
string returnSetting = String.Empty;
try
{
string commandText = String.Format("SELECT _VALUE FROM LOCATION_SETTINGS WHERE _KEY = '{0}'", aKey);
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
object result = command.ExecuteScalar();
if ((result != null) && (result.GetType() != typeof(DBNull)))
{
returnSetting = Convert.ToString(result);
}
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
object result = command.ExecuteScalar();
if ((result != null) && (result.GetType() != typeof(DBNull)))
{
returnSetting = Convert.ToString(result);
}
}
}
}
}
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 with setting : " + returnSetting);
}
return(returnSetting);
}
/// <summary>
/// Return a list of all of the audit trail entries which are audit history records for
/// the specified asset
/// </summary>
/// <returns></returns>
public DataTable GetAssetLastAuditDate(Asset aAsset, int aDays, bool aHasBeenAudited)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
DataTable ateTable = new DataTable(TableNames.AUDITTRAIL);
try
{
string commandText =
"SELECT a._ASSETID, a._LASTAUDIT AS _DATE, a._NAME AS ASSETNAME, l._FULLNAME AS FULLLOCATIONNAME " +
"FROM ASSETS a, LOCATIONS l " +
"WHERE a._LOCATIONID = l._LOCATIONID ";
if (aHasBeenAudited)
{
commandText += "AND a._LASTAUDIT IS NOT NULL ";
}
if (aAsset.AssetID != 0)
{
commandText += "AND a._ASSETID = @nAssetID ";
}
else
{
commandText += "AND a._ASSETID <> 0 ";
}
if (aDays != 0)
{
if (aHasBeenAudited)
{
commandText += "AND (datediff(day, a._LASTAUDIT ,GETDATE()) <= @nDays) ";
}
else
{
commandText += "AND (a._LASTAUDIT IS NULL OR datediff(day, a._LASTAUDIT ,GETDATE()) > @nDays) ";
}
}
commandText += "ORDER BY a._NAME";
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
if (aAsset.AssetID != 0)
{
command.Parameters.AddWithValue("@nAssetID", aAsset.AssetID);
}
if (aDays != 0)
{
command.Parameters.AddWithValue("@nDays", aDays);
}
new SqlCeDataAdapter(command).Fill(ateTable);
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
if (aAsset.AssetID != 0)
{
command.Parameters.AddWithValue("@nAssetID", aAsset.AssetID);
}
if (aDays != 0)
{
command.Parameters.AddWithValue("@nDays", aDays);
}
new SqlDataAdapter(command).Fill(ateTable);
}
}
//AuditWizardDataAccess lAuditWizardDataAccess = new AuditWizardDataAccess();
//ateTable = lAuditWizardDataAccess.GetAssetLastAuditDate(aAsset, aDays, aHasBeenAudited);
}
}
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(ateTable);
}
/// <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);
}
/// <summary>
/// Purge unaudited assets given a date before which items should be discarded
/// </summary>
/// <param name="purgeBeforeDate"></param>
/// <returns></returns>
public int DatabasePurgeAssets(DateTime purgeBeforeDate)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
AssetDAO lAssetDAO = new AssetDAO();
int lAssetsPurged = 0;
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
string commandText =
"SELECT _ASSETID FROM ASSETS " +
"WHERE ( (_LASTAUDIT IS NOT NULL) AND (_LASTAUDIT < @dtPurgeDate) ) OR (_LASTAUDIT IS NULL)";
SqlCeParameter[] spParams = new SqlCeParameter[1];
spParams[0] = new SqlCeParameter("@dtPurgeDate", purgeBeforeDate);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
using (SqlCeDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
lAssetDAO.AssetDelete(reader.GetInt32(0));
lAssetsPurged++;
}
}
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
string commandText =
"SELECT _ASSETID FROM ASSETS " +
"WHERE ( (_LASTAUDIT IS NOT NULL) AND (_LASTAUDIT < @dtPurgeDate) ) OR (_LASTAUDIT IS NULL)";
SqlParameter[] spParams = new SqlParameter[1];
spParams[0] = new SqlParameter("@dtPurgeDate", purgeBeforeDate);
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
lAssetDAO.AssetDelete(reader.GetInt32(0));
lAssetsPurged++;
}
}
}
}
}
}
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 with assets purged : " + lAssetsPurged);
}
return(lAssetsPurged);
}
/// <summary>
/// Purge the Internet table given a date before which items should be discarded
/// </summary>
/// <param name="purgeBeforeDate"></param>
/// <returns></returns>
public int DatabasePurgeInternet(DateTime purgeBeforeDate)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
int lPurgedRecords = 0;
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
string commandText =
"DELETE FROM AUDITEDITEMS WHERE _CATEGORY LIKE 'Internet|Cookie|%' " +
"AND convert(datetime, _VALUE, 103) < @dtPurgeDate";
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddWithValue("@dtPurgeDate", purgeBeforeDate);
lPurgedRecords = command.ExecuteNonQuery();
}
commandText =
"DELETE FROM AUDITEDITEMS WHERE _AUDITEDITEMID IN " +
"(SELECT _AUDITEDITEMID FROM AUDITEDITEMS " +
"WHERE _CATEGORY LIKE 'Internet|History|%' " +
"AND CONVERT(datetime ,SUBSTRING(_CATEGORY, 18, 10), 103) < @dtPurgeDate)";
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddWithValue("@dtPurgeDate", purgeBeforeDate);
lPurgedRecords += command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
string commandText =
"DELETE FROM AUDITEDITEMS WHERE _CATEGORY LIKE 'Internet|Cookie|%' " +
"AND convert(datetime, _VALUE, 103) < @dtPurgeDate";
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddWithValue("@dtPurgeDate", purgeBeforeDate);
lPurgedRecords = command.ExecuteNonQuery();
}
commandText =
"DELETE FROM AUDITEDITEMS WHERE _AUDITEDITEMID IN " +
"(SELECT _AUDITEDITEMID FROM AUDITEDITEMS " +
"WHERE _CATEGORY LIKE 'Internet|History|%' " +
"AND CONVERT(datetime ,SUBSTRING(_CATEGORY, 18, 10), 103) < @dtPurgeDate)";
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddWithValue("@dtPurgeDate", purgeBeforeDate);
lPurgedRecords += 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 with purged count : " + lPurgedRecords);
}
return(lPurgedRecords);
}
public string GetSetting(string key, bool decrypt)
{
if (IsDebugEnabled)
{
Logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
string returnSetting = String.Empty;
try
{
const string commandText = "SELECT _VALUE FROM SETTINGS WHERE _KEY = @key";
if (_compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddWithValue("@key", key);
object result = command.ExecuteScalar();
if ((result != null) && (result.GetType() != typeof(DBNull)))
{
string resultValue = Convert.ToString(result);
returnSetting = (decrypt) ? AES.Decrypt(resultValue) : resultValue;
}
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddWithValue("@key", key);
object result = command.ExecuteScalar();
if ((result != null) && (result.GetType() != typeof(DBNull)))
{
string resultValue = Convert.ToString(result);
returnSetting = (decrypt) ? AES.Decrypt(resultValue) : resultValue;
}
}
}
}
}
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 with setting : " + returnSetting);
}
return(returnSetting);
}
public DataTable GetNotesNameAndIdByScopeAndParent(SCOPE aScope, int aParentID)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
DataTable table = new DataTable(TableNames.NOTES);
try
{
string cmdText =
"SELECT _NOTEID, _NAME " +
"FROM NOTES " +
"WHERE _SCOPE = @nScope AND _PARENTID = @nParentID " +
"ORDER BY _DATE";
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[2];
spParams[0] = new SqlCeParameter("@nScope", (int)aScope);
spParams[1] = new SqlCeParameter("@nParentID", aParentID);
using (SqlCeCommand command = new SqlCeCommand(cmdText, conn))
{
command.Parameters.AddRange(spParams);
new SqlCeDataAdapter(command).Fill(table);
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlParameter[] spParams = new SqlParameter[2];
spParams[0] = new SqlParameter("@nScope", (int)aScope);
spParams[1] = new SqlParameter("@nParentID", aParentID);
using (SqlCommand command = new SqlCommand(cmdText, conn))
{
command.Parameters.AddRange(spParams);
new SqlDataAdapter(command).Fill(table);
}
}
}
}
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(table);
}
public DataTable GetAssetAuditHistoryForReport()
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
DataTable ateTable = new DataTable(TableNames.AUDITTRAIL);
string commandText =
"SELECT AUDITTRAIL.* ,ASSETS._NAME AS ASSETNAME ,LOCATIONS._FULLNAME AS FULLLOCATIONNAME " +
"FROM AUDITTRAIL " +
"LEFT JOIN ASSETS ON (AUDITTRAIL._ASSETID = ASSETS._ASSETID) " +
"LEFT JOIN LOCATIONS ON (ASSETS._LOCATIONID = LOCATIONS._LOCATIONID) " +
"WHERE _CLASS <= 3 " +
"AND AUDITTRAIL._ASSETID <> 0 ";
string lAssetIds = new AssetDAO().GetSelectedAssets();
if (lAssetIds != "")
{
commandText += "AND ASSETS._ASSETID IN (" + lAssetIds + ") ";
}
commandText += "ORDER BY _AUDITTRAILID";
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
new SqlCeDataAdapter(command).Fill(ateTable);
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
using (SqlCommand command = new SqlCommand(commandText, conn))
{
new SqlDataAdapter(command).Fill(ateTable);
}
}
}
}
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(ateTable);
}
/// <summary>
/// Purge the AuditTrail table given a date before which items should be discarded
/// </summary>
/// <param name="purgeBeforeDate"></param>
/// <returns></returns>
public int DatabasePurgeAuditTrail(DateTime purgeBeforeDate)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
int lPurgedRecords = 0;
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
string commandText = "DELETE FROM AUDITTRAIL where _DATE < @dtPurgeDate";
SqlCeParameter[] spParams = new SqlCeParameter[1];
spParams[0] = new SqlCeParameter("@dtPurgeDate", purgeBeforeDate);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
lPurgedRecords = command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
string commandText = "DELETE FROM AUDITTRAIL where _DATE < @dtPurgeDate";
SqlParameter[] spParams = new SqlParameter[1];
spParams[0] = new SqlParameter("@dtPurgeDate", purgeBeforeDate);
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
lPurgedRecords = 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 with purged count : " + lPurgedRecords);
}
return(lPurgedRecords);
}
/// <summary>
/// Purge the Audit Trail records from the database
/// </summary>
/// <returns></returns>
public int AuditTrailPurge(DateTime dtPurge)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
int purgeCount = 0;
try
{
string commandText = "DELETE FROM AUDITTRAIL where (datediff(day, _DATE, @dtPurgeDate) > 0)";
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[2];
spParams[0] = new SqlCeParameter("@dtPurgeDate", SqlDbType.DateTime);
spParams[0].Value = dtPurge;
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddWithValue("@dtPurgeDate", dtPurge);
purgeCount = command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlParameter[] spParams = new SqlParameter[2];
spParams[0] = new SqlParameter("@dtPurgeDate", SqlDbType.DateTime);
spParams[0].Value = dtPurge;
using (SqlCommand command = new SqlCommand(commandText, conn))
{
command.Parameters.AddWithValue("@dtPurgeDate", dtPurge);
purgeCount = 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(purgeCount);
}
public void Insert(byte[] aAppointmentData)
{
if (isDebugEnabled)
{
logger.Debug(System.Reflection.MethodBase.GetCurrentMethod().Name + " in");
}
string commandText =
"INSERT INTO TASK_SCHEDULES (_APPOINTMENTDATA) " +
" VALUES (@cAppointmentData)";
try
{
if (compactDatabaseType)
{
using (SqlCeConnection conn = DatabaseConnection.CreateOpenCEConnection())
{
SqlCeParameter[] spParams = new SqlCeParameter[1];
spParams[0] = new SqlCeParameter("@cAppointmentData", aAppointmentData);
using (SqlCeCommand command = new SqlCeCommand(commandText, conn))
{
command.Parameters.AddRange(spParams);
command.ExecuteNonQuery();
}
}
}
else
{
using (SqlConnection conn = DatabaseConnection.CreateOpenStandardConnection())
{
SqlParameter[] spParams = new SqlParameter[1];
spParams[0] = new SqlParameter("@cAppointmentData", aAppointmentData);
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");
}
}
