//properties
//methods
/// <summary>
/// Saves the public property values contained in the current instance to the database specified by the connection string.
/// </summary>
/// <param name="kvlist">Key/Value list object to be saved to a database.</param>
/// <param name="connectionString">Contains information needed to open the database.</param>
/// <param name="listName">Name of the list in the database.</param>
public void SaveToDatabase(PFKeyValueList <K, V> kvlist, string connectionString, string listName)
{
string sqlStmt = string.Empty;
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
int numRecsAffected = 0;
DateTime currdate = DateTime.Now;
string currBatchId = string.Empty;
string listObject = string.Empty;
db.ConnectionString = connectionString;
db.OpenConnection();
//create batch id for this list
currBatchId = "'" + Guid.NewGuid().ToString().Trim() + "'";
listObject = kvlist.ToXmlString().Replace("'", ""); //get rid of any single quotes in the object. they will mess up the sql syntax e.g. values(1, 'two' ,'this is the 'object'')
//insert current list to the database
sqlStmt = _listsInsertSQL.Replace("<listname>", listName).Replace("<id>", currBatchId).Replace("<listobject>", listObject);
numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
//get rid of any previous PFListEx objects in the database
sqlStmt = _listsDeleteOldSQL.Replace("<listname>", listName).Replace("<id>", currBatchId);
numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
db.CloseConnection();
db = null;
}
/// <summary>
/// Saves the public property values contained in the current instance to the database specified by the connection string.
/// </summary>
/// <param name="connectionString">Contains information needed to open the database.</param>
/// <remarks>Task name must be unique in the database. SQL Server CE 3.5 local file used for database storage.</remarks>
public void SaveToDatabase(string connectionString)
{
string sqlStmt = string.Empty;
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
DbDataReader rdr = null;
int numRecsFound = 0;
int numRecsAffected = 0;
db.ConnectionString = connectionString;
db.OpenConnection();
//check if already exists
sqlStmt = _taskHistoryDefinitionsIfTaskHistoryExistsSQL.Replace("<taskname>", this.TaskName);
sqlStmt = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
rdr = db.RunQueryDataReader(sqlStmt, CommandType.Text);
numRecsFound = 0;
while (rdr.Read())
{
numRecsFound = rdr.GetInt32(0);
break; //should be only one record
}
// if exists update it
if (numRecsFound > 0)
{
//update the record
sqlStmt = _taskHistoryDefinitionsUpdateSQL.Replace("<taskname>", this.TaskName);
sqlStmt = sqlStmt.Replace("<taskobject>", this.ToXmlString());
sqlStmt = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
}
else
{
//insert the new record
sqlStmt = _taskHistoryDefinitionsInsertSQL.Replace("<taskname>", this.TaskName);
sqlStmt = sqlStmt.Replace("<rundate>", this.ActualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
sqlStmt = sqlStmt.Replace("<taskobject>", this.ToXmlString());
numRecsAffected = db.RunNonQuery(sqlStmt, CommandType.Text);
}
db.CloseConnection();
}
private bool WriteMessageToDatabaseLog(PFLogMessage logMessage, bool saveFailedWriteToRetryQueue)
{
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
bool logWriteSucceeded = false;
string sqlStmt = string.Empty;
string messageText = string.Empty;
string logObject = string.Empty;
try
{
db.ConnectionString = _logFileConnectionString;
db.OpenConnection();
if (logMessage.LogMessageDateTime == DateTime.MinValue)
{
logMessage.LogMessageDateTime = DateTime.Now;
}
messageText = logMessage.MessageText.Replace("'", "");
logObject = logMessage.ToXmlString().Replace("'", "");
sqlStmt = _dbLogInsertStatement.Replace("<LogEntryDateTime>", "'" + logMessage.LogMessageDateTime.ToString("MM/dd/yyyy HH:mm:ss") + "'")
.Replace("<ApplicationName>", "'" + logMessage.ApplicationName + "'")
.Replace("<MachineName>", "'" + logMessage.MachineName + "'")
.Replace("<Username>", "'" + logMessage.Username + "'")
.Replace("<MessageLevel>", "'" + logMessage.LogMessageType.ToString() + "'")
.Replace("<MessageText>", "'" + messageText + "'")
.Replace("<LogMessageObject>", "'" + logObject + "'")
;
int numRecsAffected = db.RunNonQuery(sqlStmt, System.Data.CommandType.Text);
if (numRecsAffected > 0)
{
logWriteSucceeded = true;
}
else
{
logWriteSucceeded = false;
}
}
catch
{
logWriteSucceeded = false;
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
}
if (!logWriteSucceeded && saveFailedWriteToRetryQueue)
{
SaveLogMessageToRetryQueue(logMessage);
}
return(logWriteSucceeded);
}
