/// <summary>
/// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
/// </summary>
/// <param name="connectionString">Connection parameters for the database.</param>
/// <param name="taskName">Name of the the to retrieve.</param>
/// <param name="actualStartTime">Actual start time for the history entry to be retrieved.</param>
/// <returns>TaskHistoryEntry object.</returns>
public static PFTaskHistoryEntry LoadFromDatabase(string connectionString, string taskName, DateTime actualStartTime)
{
string sqlStmt = string.Empty;
PFTaskHistoryEntry objectInstance = null;
PFTaskHistoryEntry tempObjectInstance = new PFTaskHistoryEntry();
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
DbDataReader rdr = null;
string taskHistoryDefXml = string.Empty;
db.ConnectionString = connectionString;
db.OpenConnection();
sqlStmt = tempObjectInstance._taskHistoryDefinitionsSelectTaskEntrySQL.Replace("<taskname>", taskName);
sqlStmt = sqlStmt.Replace("<rundate>", actualStartTime.ToString("MM/dd/yyyy HH:mm:ss"));
rdr = db.RunQueryDataReader(sqlStmt, CommandType.Text);
while (rdr.Read())
{
taskHistoryDefXml = rdr.GetString(0);
objectInstance = PFTaskHistoryEntry.LoadFromXmlString(taskHistoryDefXml);
break; //should be only one record
}
return(objectInstance);
}
/// <summary>
/// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
/// </summary>
/// <param name="connectionString">Connection parameters for the database.</param>
/// <param name="listName">Name of the list in the database.</param>
/// <returns>PFListEx object.</returns>
public PFKeyValueList <K, V> LoadFromDatabase(string connectionString, string listName)
{
string sqlStmt = string.Empty;
PFKeyValueList <K, V> objectInstance = null;
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
DbDataReader rdr = null;
string pfKeyValueListExXml = string.Empty;
db.ConnectionString = connectionString;
db.OpenConnection();
sqlStmt = _listsSelectSQL.Replace("<listname>", listName);
rdr = db.RunQueryDataReader(sqlStmt, CommandType.Text);
while (rdr.Read())
{
pfKeyValueListExXml = rdr.GetString(0);
objectInstance = PFKeyValueList <K, V> .LoadFromXmlString(pfKeyValueListExXml);
break; //should be only one record
}
db.CloseConnection();
db = null;
if (objectInstance == null)
{
objectInstance = new PFKeyValueList <K, V>();
}
return(objectInstance);
}
/// <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 PFList <PFTaskHistoryEntry> GetTaskListDatabase(string taskName)
{
PFList <PFTaskHistoryEntry> taskHistoryEntryList = new PFList <PFTaskHistoryEntry>();
PFDatabase db = null;
string sqlStmt = string.Empty;
try
{
db = new PFDatabase(DatabasePlatform.SQLServerCE35);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
sqlStmt = _taskHistoryDefinitionsSelectTaskSQL.Replace("<taskname>", taskName);
DbDataReader rdr = db.RunQueryDataReader(sqlStmt, System.Data.CommandType.Text);
while (rdr.Read())
{
string str = rdr["TaskHistoryObject"].ToString();
PFTaskHistoryEntry the = PFTaskHistoryEntry.LoadFromXmlString(str);
taskHistoryEntryList.Add(the);
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
}
return(taskHistoryEntryList);
}
private PFList <PFSchedule> GetScheduleListDatabase()
{
PFList <PFSchedule> scheduleList = new PFList <PFSchedule>();
PFDatabase db = null;
try
{
db = new PFDatabase(DatabasePlatform.SQLServerCE35);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
DbDataReader rdr = db.RunQueryDataReader(_scheduleDefinitionsSelectAllSQL, System.Data.CommandType.Text);
while (rdr.Read())
{
string str = rdr["ScheduleObject"].ToString();
PFSchedule schedule = PFSchedule.LoadFromXmlString(str);
scheduleList.Add(schedule);
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
}
}
return(scheduleList);
}
/// <summary>
/// Creates and initializes an instance of the class by loading a serialized version of the instance from a database record.
/// </summary>
/// <param name="connectionString">Connection parameters for the database.</param>
/// <param name="taskName">Name of the task to retrieve.</param>
/// <returns>Task object.</returns>
public static PFTask LoadFromDatabase(string connectionString, string taskName)
{
string sqlStmt = string.Empty;
PFTask objectInstance = null;
PFTask tempObjectInstance = new PFTask();
PFDatabase db = new PFDatabase(DatabasePlatform.SQLServerCE35);
DbDataReader rdr = null;
string taskDefXml = string.Empty;
db.ConnectionString = connectionString;
db.OpenConnection();
sqlStmt = tempObjectInstance._taskDefinitionsSelectTaskSQL.Replace("<taskname>", taskName);
rdr = db.RunQueryDataReader(sqlStmt, CommandType.Text);
while (rdr.Read())
{
taskDefXml = rdr.GetString(0);
objectInstance = PFTask.LoadFromXmlString(taskDefXml);
break; //should be only one record
}
return(objectInstance);
}
public static void DataReaderToDataTableTest(MainForm frm)
{
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
_msg.Length = 0;
_msg.Append("DataReaderToDataTableTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
string[] parsedConnectionInfo = frm.cboConnectionString.Text.Split('|');
dbPlatformDesc = parsedConnectionInfo[0];
connStr = parsedConnectionInfo[1];
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
string[] parsedConfig = configValue.Split('|');
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
if (frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
_msg.Length = 0;
_msg.Append("Connecting to ");
_msg.Append(dbPlatformDesc);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
db.SQLQuery = frm.txtSQLQuery.Text;
db.CommandType = CommandType.Text;
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
DbDataReader rdr = (DbDataReader)db.RunQueryDataReader();
DataTable tab = db.ConvertDataReaderToDataTable(rdr);
Program._messageLog.WriteLine("Table columns count: " + tab.Columns.Count.ToString());
rdr.Close();
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow r = tab.Rows[i];
_msg.Length = 0;
int maxColInx = tab.Columns.Count - 1;
for (int ci = 0; ci <= maxColInx; ci++)
{
_msg.Append(tab.Columns[ci].ColumnName);
_msg.Append(": ");
_msg.Append(r[ci].ToString());
if (ci < maxColInx)
{
_msg.Append(", ");
}
}
Program._messageLog.WriteLine(_msg.ToString());
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Table read time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... DataReaderToDataTableTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public static void DataReaderTest(MainForm frm)
{
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
_msg.Length = 0;
_msg.Append("DataReaderTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
string[] parsedConnectionInfo = frm.cboConnectionString.Text.Split('|');
dbPlatformDesc = parsedConnectionInfo[0];
connStr = parsedConnectionInfo[1];
string configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
string[] parsedConfig = configValue.Split('|');
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
if (frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
_msg.Length = 0;
_msg.Append("Connecting to ");
_msg.Append(dbPlatformDesc);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
db.SQLQuery = frm.txtSQLQuery.Text;
db.CommandType = CommandType.Text;
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
Program._messageLog.WriteLine("\r\nRunning data extract tests ...\r\n");
db.returnResultAsString += new PFDatabase.ResultAsStringDelegate(OutputResultsToFile);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\ReaderDelimitedTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
sw.Start();
DbDataReader rdr = (DbDataReader)db.RunQueryDataReader();
db.ExtractDelimitedDataFromDataReader(rdr, ",", "\r\n", true);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Delimiated Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\ReaderFixedLengthTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
rdr.Close();
sw.Start();
rdr = (DbDataReader)db.RunQueryDataReader();
db.ExtractFixedLengthDataFromDataReader(rdr, true, true, false);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Fixed Length Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
rdr.Close();
rdr = (DbDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlFile(rdr, @"c:\temp\Testrdr.xml");
rdr.Close();
rdr = (DbDataReader)db.RunQueryDataReader();
db.SaveDataReaderWithSchemaToXmlFile(rdr, @"c:\temp\Testrdrplus.xml");
rdr.Close();
rdr = (DbDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlSchemaFile(rdr, @"c:\temp\Testrdr.xsd");
rdr.Close();
rdr = (DbDataReader)db.RunQueryDataReader();
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(db.ConvertDataReaderToDataTable(rdr));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
rdr.Close();
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
_msg.Length = 0;
_msg.Append("\r\n... DataReaderTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
