/// <summary>
///
/// </summary>
/// <param name="hostName"></param>
/// <param name="instanceName"></param>
/// <param name="port"></param>
private void SetupDatabase(string hostName, string instanceName, int port)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder()
{
InitialCatalog = "DSS_MACHINE",
IntegratedSecurity = true,
MultipleActiveResultSets = true
};
// If a port is specified, use it. Otherwise use default port settings.
StringBuilder dataSource = new StringBuilder(hostName);
dataSource.Append("\\");
dataSource.Append(instanceName);
if (port > 0)
{
dataSource.Append(",");
dataSource.Append(port);
}
builder.DataSource = dataSource.ToString();
TraceFactory.Logger.Debug("Attempting connection to " + builder.DataSource);
_connectionString = builder.ToString();
using (SqlAdapter adapter = new SqlAdapter(_connectionString))
{
adapter.ExecuteNonQuery(Resource.CreateTransactionTableSql);
adapter.ExecuteNonQuery(Resource.DeleteTriggersSql);
adapter.ExecuteNonQuery(Resource.CreateInsertTriggerSql);
}
TraceFactory.Logger.Debug("Connected to database instance: " + builder.DataSource);
}
private void SetupDatabase(string hostName, string instanceName)
{
TraceFactory.Logger.Info("Connecting to {0}\\{1} ...".FormatWith(hostName, instanceName));
try
{
TraceFactory.Logger.Info("Attempting connection to " + instanceName);
var conn = "Server={0}; Database={1}; Trusted_Connection=true; MultipleActiveResultSets=true;"
.FormatWith(hostName.Trim(), instanceName);
TraceFactory.Logger.Info("Connection string: " + conn);
using (SqlAdapter adapter = new SqlAdapter(conn))
{
TraceFactory.Logger.Info("Adding column StfDigitalSendServerJobId to table ArchiveTable if not existing...");
var sql = @"
IF COL_LENGTH('ArchiveTable', 'StfDigitalSendServerJobId') IS NULL
BEGIN
ALTER TABLE ArchiveTable
ADD [StfDigitalSendServerJobId] [uniqueidentifier] NULL
END
";
adapter.ExecuteNonQuery(sql);
TraceFactory.Logger.Info("Adding column StfLogId to table ArchiveTable if not existing...");
sql = @"
IF COL_LENGTH('ArchiveTable', 'StfLogId') IS NULL
BEGIN
ALTER TABLE ArchiveTable
ADD [StfLogId] [uniqueidentifier] NULL
END
";
adapter.ExecuteNonQuery(sql);
sql = @"
IF COL_LENGTH('ArchiveTable', 'StfLogStatus') IS NULL
BEGIN
ALTER TABLE ArchiveTable
ADD [StfLogStatus] nvarchar(15) NULL
END
";
adapter.ExecuteNonQuery(sql);
}
TraceFactory.Logger.Info("Connected to database instance {0}\\{1}".FormatWith(hostName, instanceName));
_connectionString = conn;
return;
}
catch (SqlException ex)
{
TraceFactory.Logger.Error("Unsuccessful", ex);
}
// None of these connections worked
throw new InvalidOperationException("Could not connect to database.");
}
private void UpdateHpcrDatabase(SqlAdapter adapter, HpcrArchiveRecord record)
{
string sql = string.Empty;
if (string.IsNullOrEmpty(record.SessionId))
{
record.StfLogStatus = StfLogStatus.Ignore;
}
if ((record.JobType.Equals("HpcrScanToMe") || record.JobType.Equals("HpcrPublicDistributions")) && record.DateDelivered.HasValue)
{
sql = @"
update ArchiveTable
set StfDigitalSendServerJobId = '{0}', StfLogId = '{1}', StfLogStatus = '{2}', prDateDelivered = '{3}'
where RowIdentifier = {4}
".FormatWith(record.StfDigitalSendServerJobId.ToString(), record.StfLogId.ToString(), record.StfLogStatus, record.DateDelivered, record.RowIdentifier);
}
else
{
sql = @"
update ArchiveTable
set StfDigitalSendServerJobId = '{0}', StfLogId = '{1}', StfLogStatus = '{2}'
where RowIdentifier = {3}
".FormatWith(record.StfDigitalSendServerJobId.ToString(), record.StfLogId.ToString(), record.StfLogStatus, record.RowIdentifier);
}
TraceFactory.Logger.Debug(sql);
adapter.ExecuteNonQuery(sql);
}
/// <summary>
/// Executes the specified SQL text on the database specified in the connection string.
/// </summary>
/// <param name="connectionString"></param>
/// <param name="sqlText"></param>
private void ExecuteSql(string connectionString, string sqlText)
{
string[] delimiter = new string[] { "GO{0}".FormatWith(Environment.NewLine) };
string[] sqlCommands = sqlText.Split(delimiter, StringSplitOptions.RemoveEmptyEntries);
using (SqlAdapter adapter = new SqlAdapter(connectionString))
{
string command = string.Empty;
foreach (string line in sqlCommands)
{
command = line.Trim();
if (!string.IsNullOrEmpty(command))
{
adapter.ExecuteNonQuery(command);
}
}
}
}
/// <summary>
/// Retrieves the job records from the DSS database.
/// For the records that are not found in the database, the following may be logged to the data logger:
/// unavailable - The job ID was found in the DSS database, and partial data was retrieved for the job. Represents job data that could not be retrieved.
/// unknown - While attempting to find the job ID in the database, a database error occurred, and after retrying the operation several times, processing moved on.
/// missing - The job was not found in either the Master or the Device table.
/// </summary>
public void RetrieveRecords()
{
TraceFactory.Logger.Debug("Retrieving Job IDs from STFTransactionLog table.");
using (SqlAdapter adapter = new SqlAdapter(_connectionString))
{
List <Guid> jobIds = new List <Guid>(); //List of records that have been updated
try
{
using (DbDataReader idsReader = adapter.ExecuteReader(Resource.TransactionRetrieveSql))
{
while (idsReader.Read())
{
jobIds.Add((Guid)idsReader["JobId"]);
}
idsReader.Close();
}
}
catch (SqlException sqlEx)
{
TraceFactory.Logger.Error("Error retrieving list of Job IDs. Aborting.", sqlEx);
return;
}
TraceFactory.Logger.Debug("Found {0} records.".FormatWith(jobIds.Count));
if (jobIds.Any())
{
// Capture list of job IDs now while we have the entire list, to be used to cleanup the transaction table later.
string jobIdList = "'" + string.Join("','", jobIds) + "'";
string notFoundText = "<missing>";
try
{
//Log data from JobLogMaster.
LogData(adapter, DSSJobLogTable.JobLogMaster, Resource.JobLogMasterSql.FormatWith(jobIdList), ref jobIds);
}
catch (SqlException sqlEx)
{
TraceFactory.Logger.Error("Unable to process JobLogMaster.", sqlEx);
notFoundText = "<unknown>";
}
//Check to see if there were any jobs that weren't processed
if (jobIds.Any())
{
// Recreate list of job IDs, since some of the jobs have already been processed and removed from jobIds
string unprocessedJobIdList = "'" + string.Join("','", jobIds) + "'";
try
{
//Log data from JobLogDevice.
LogData(adapter, DSSJobLogTable.JobLogDevice, Resource.JobLogDeviceSql.FormatWith(unprocessedJobIdList), ref jobIds);
}
catch (SqlException sqlEx)
{
TraceFactory.Logger.Error("Unable to process JobLogDevice.", sqlEx);
notFoundText = "<unknown>";
}
}
// Any job Ids left over at this point were not found in either the master nor the device tables
// Log an entry in the data log to flag data that was not found.
foreach (Guid jobId in jobIds)
{
DigitalSendServerJobLogger log = CreateLogForNotFound(jobId, notFoundText);
SubmitLog(log);
}
//At this point, all jobs Ids have been processed
jobIds.Clear();
//Remove the JobIds from the transaction table.
string sql = Resource.TransactionCleanupSql.FormatWith(jobIdList);
//TraceFactory.Logger.Debug(sql);
Retry.WhileThrowing(() =>
{
adapter.ExecuteNonQuery(sql);
}, 5, TimeSpan.FromSeconds(5), _retryExceptions);
}
}
}