//properties
//methods
/// <summary>
/// Saves the public property values contained in the current instance to the database specified by the connection string.
/// </summary>
/// <param name="list">List object that will be saved to the 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(PFList <T> list, 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 = list.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;
}
public void ToXmlTest()
{
PFList <string> testList = new PFList <string>();
try
{
_msg.Length = 0;
_msg.Append("ToXmlTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
testList.Add("First item");
testList.Add("Second item");
testList.Add("Third item");
string xmlString = testList.ToXmlString();
XmlDocument xmlDoc = testList.ToXmlDocument();
PFList <string> testList2 = PFList <string> .LoadFromXmlString(xmlString);
_msg.Length = 0;
_msg.Append("\r\n\r\n");
_msg.Append(xmlString);
_msg.Append("\r\n\r\n");
_msg.Append(xmlDoc.OuterXml);
_msg.Append("\r\n\r\n");
_msg.Append(testList2.ToString());
_msg.Append("\r\n\r\n");
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
{
_msg.Length = 0;
_msg.Append("\r\n... ToXmlTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public void RandomizeListsTest()
{
PFList <string> list1 = new PFList <string>();
PFKeyValueList <int, string> kvlist2 = new PFKeyValueList <int, string>();
PFKeyValueListSorted <int, string> sortlist3 = new PFKeyValueListSorted <int, string>();
try
{
_msg.Length = 0;
_msg.Append("RandomizeListsTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
list1.Add("First");
list1.Add("Second");
list1.Add("Third");
list1.Add("fourth");
list1.Add("fifth");
list1.Add("sixth");
list1.Add("Seventh");
list1.Add("Eighth");
list1.Add("Ninth");
list1.Add("Tenth");
PFList <string> randomizedList = list1.Randomize();
_msg.Length = 0;
_msg.Append("randomizedList: ");
_msg.Append(Environment.NewLine);
_msg.Append(randomizedList.ToXmlString());
Program._messageLog.WriteLine(_msg.ToString());
kvlist2.Add(new stKeyValuePair <int, string>(1, "First"));
kvlist2.Add(new stKeyValuePair <int, string>(2, "Second"));
kvlist2.Add(new stKeyValuePair <int, string>(3, "Third"));
kvlist2.Add(new stKeyValuePair <int, string>(4, "fourth"));
kvlist2.Add(new stKeyValuePair <int, string>(5, "fifth"));
kvlist2.Add(new stKeyValuePair <int, string>(6, "sixth"));
kvlist2.Add(new stKeyValuePair <int, string>(7, "Seventh"));
kvlist2.Add(new stKeyValuePair <int, string>(8, "Eighth"));
kvlist2.Add(new stKeyValuePair <int, string>(9, "Ninth"));
kvlist2.Add(new stKeyValuePair <int, string>(10, "Tenth"));
PFKeyValueList <int, string> randomizedKvList = kvlist2.Randomize();
_msg.Length = 0;
_msg.Append("randomizedKvList: ");
_msg.Append(Environment.NewLine);
_msg.Append(randomizedKvList.ToXmlString());
Program._messageLog.WriteLine(_msg.ToString());
sortlist3.Add(1, "First");
sortlist3.Add(2, "Second");
sortlist3.Add(3, "Third");
sortlist3.Add(4, "fourth");
sortlist3.Add(5, "fifth");
sortlist3.Add(6, "sixth");
sortlist3.Add(7, "Seventh");
sortlist3.Add(8, "Eighth");
sortlist3.Add(9, "Ninth");
sortlist3.Add(10, "Tenth");
PFKeyValueList <int, string> randomizedSortList = sortlist3.Randomize();
_msg.Length = 0;
_msg.Append("randomizedSortList: ");
_msg.Append(Environment.NewLine);
_msg.Append(randomizedSortList.ToXmlString());
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
{
_msg.Length = 0;
_msg.Append("\r\n... RandomizeListsTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
//tests
public void GenericListTest()
{
PFList <string> testList = new PFList <string>();
PFList <string> testList2 = new PFList <string>();
PFList <string> testList3 = new PFList <string>();
PFList <string> testList4 = new PFList <string>();
string testListXmlFile = @"c:\temp\testlist.xml";
string connectionString = string.Empty;
string configValue = string.Empty;
PFDBListProcessor <string> listProcessor = new PFDBListProcessor <string>();
try
{
_msg.Length = 0;
_msg.Append("GenericListTest started ...");
Program._messageLog.WriteLine(_msg.ToString());
configValue = AppConfig.GetStringValueFromConfigFile("ListsDatabaseFile", string.Empty);
if (configValue.Length > 0)
{
connectionString = configValue;
}
else
{
connectionString = _listsDatabaseConnectionString;
}
_msg.Length = 0;
_msg.Append("First = ");
_msg.Append(testList.FirstItem == null ? "<null>" : testList.FirstItem);
Program._messageLog.WriteLine(_msg.ToString());
testList.Add("First item");
testList.Add("Second item");
testList.Add("Third item");
_msg.Length = 0;
_msg.Append("Number of items in list: ");
Program._messageLog.WriteLine(_msg.ToString());
foreach (string s in testList)
{
_msg.Length = 0;
_msg.Append(s);
Program._messageLog.WriteLine(_msg.ToString());
}
_msg.Length = 0;
_msg.Append("First = ");
_msg.Append(testList.FirstItem);
Program._messageLog.WriteLine(_msg.ToString());
_msg.Length = 0;
_msg.Append("Last = ");
_msg.Append(testList.LastItem);
Program._messageLog.WriteLine(_msg.ToString());
_msg.Length = 0;
_msg.Append("NextItem loop: ");
Program._messageLog.WriteLine(_msg.ToString());
string res = testList.FirstItem;
while (!testList.EOF)
{
_msg.Length = 0;
_msg.Append(res);
Program._messageLog.WriteLine(_msg.ToString());
res = testList.NextItem;
}
_msg.Length = 0;
_msg.Append("PrevItem loop: ");
Program._messageLog.WriteLine(_msg.ToString());
res = testList.LastItem;
while (!testList.EOF)
{
_msg.Length = 0;
_msg.Append(res);
Program._messageLog.WriteLine(_msg.ToString());
res = testList.PrevItem;
}
_msg.Length = 0;
_msg.Append("ToString Test: ");
Program._messageLog.WriteLine(_msg.ToString());
_msg.Length = 0;
_msg.Append(testList.ToString());
Program._messageLog.WriteLine(_msg.ToString());
_msg.Length = 0;
_msg.Append("SaveToXmlFile Test: ");
_msg.Append(testListXmlFile);
Program._messageLog.WriteLine(_msg.ToString());
testList.SaveToXmlFile(testListXmlFile);
_msg.Length = 0;
_msg.Append("LoadFromXmlFile Test: ");
_msg.Append(testListXmlFile);
Program._messageLog.WriteLine(_msg.ToString());
testList2 = PFList <string> .LoadFromXmlFile(testListXmlFile);
_msg.Length = 0;
_msg.Append("testList2 ToString Test: ");
Program._messageLog.WriteLine(_msg.ToString());
_msg.Length = 0;
_msg.Append(testList2.ToString());
Program._messageLog.WriteLine(_msg.ToString());
//save and read to database tests next
listProcessor.SaveToDatabase(testList, connectionString, "TestprogCollections_testList");
testList3 = listProcessor.LoadFromDatabase(connectionString, "TestprogCollections_testList");
if (testList3 != null)
{
_msg.Length = 0;
_msg.Append("testList3 ToXmlString Test: ");
Program._messageLog.WriteLine(_msg.ToString());
_msg.Length = 0;
_msg.Append(testList3.ToXmlString());
_msg.Append(Environment.NewLine);
_msg.Append("+++ testlist3 retrieved. +++");
Program._messageLog.WriteLine(_msg.ToString());
}
else
{
_msg.Length = 0;
_msg.Append("testList3 ToXmlString Test: ");
Program._messageLog.WriteLine(_msg.ToString());
_msg.Length = 0;
_msg.Append("*** TESTLIST3 IS NULL ***");
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
{
listProcessor = null;
_msg.Length = 0;
_msg.Append("... GenericListTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}//end GenericListTest
public void ListMergeTest()
{
PFList <string> list1 = new PFList <string>();
PFList <string> list2 = new PFList <string>();
PFList <string> list3 = new PFList <string>();
try
{
_msg.Length = 0;
_msg.Append("ListMergeTest started ...\r\n");
Program._messageLog.WriteLine(_msg.ToString());
list1.Add("First");
list1.Add("Second");
list1.Add("Third");
list2.Add("fourth");
list2.Add("fifth");
list2.Add("sixth");
list3.Add("Seventh");
list3.Add("Eighth");
list3.Add("Ninth");
list3.Add("Tenth");
PFList <string>[] listArray = { list1, list2, list3 };
PFList <PFList <string> > listOfLists = new PFList <PFList <string> >();
listOfLists.Add(list1);
listOfLists.Add(list2);
listOfLists.Add(list3);
PFList <string> concatListFromArray = PFList <string> .ConcatenateLists(listArray);
_msg.Length = 0;
_msg.Append(Environment.NewLine);
_msg.Append("Concatenated list from Array");
_msg.Append(Environment.NewLine);
_msg.Append(concatListFromArray.ToXmlString());
Program._messageLog.WriteLine(_msg.ToString());
PFList <string> concatListFromList = PFList <string> .ConcatenateLists(listOfLists);
_msg.Length = 0;
_msg.Append(Environment.NewLine);
_msg.Append("Concatenated list from List");
_msg.Append(Environment.NewLine);
_msg.Append(concatListFromList.ToXmlString());
Program._messageLog.WriteLine(_msg.ToString());
PFList <string> mergedList = list1.Merge(list2);
_msg.Length = 0;
_msg.Append(Environment.NewLine);
_msg.Append("Merged list");
_msg.Append(Environment.NewLine);
_msg.Append(mergedList.ToXmlString());
Program._messageLog.WriteLine(_msg.ToString());
mergedList.Clear();
mergedList = list3.Merge(new PFList <string>[2] {
list1, list2
});
_msg.Length = 0;
_msg.Append(Environment.NewLine);
_msg.Append("Merged list from Array");
_msg.Append(Environment.NewLine);
_msg.Append(mergedList.ToXmlString());
Program._messageLog.WriteLine(_msg.ToString());
mergedList.Clear();
mergedList = list3.Merge(new PFList <PFList <string> > {
list2, list1
});
_msg.Length = 0;
_msg.Append(Environment.NewLine);
_msg.Append("Merged list from List");
_msg.Append(Environment.NewLine);
_msg.Append(mergedList.ToXmlString());
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
{
_msg.Length = 0;
_msg.Append("\r\n... ListMergeTest finished.");
Program._messageLog.WriteLine(_msg.ToString());
}
}
public void ShowFilterBuilderForm(string tableName)
{
PFFilterBuilderForm frm = new PFFilterBuilderForm();
DialogResult res = DialogResult.None;
string dbPlatformDesc = DatabasePlatform.Unknown.ToString();
PFDatabase db = null;
string connStr = string.Empty;
string nmSpace = string.Empty;
string clsName = string.Empty;
string dllPath = string.Empty;
DataTable dt = null;
PFList <PFFilterDef> filterDefs = null;
PFColumnDefinitionsExt colDefsExt = null;
string configValue = string.Empty;
string configKey = string.Empty;
try
{
_msg.Length = 0;
_msg.Append("ShowFilterBuilderForm started ...\r\n");
WriteToMessageLog(_msg.ToString());
configValue = AppConfig.GetStringValueFromConfigFile("DefaultConnection_SQLServerCE35", string.Empty);
if (configValue == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find config.sys entry for DefaultConnection_SQLServerCE35");
throw new System.Exception(_msg.ToString());
}
dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
connStr = configValue;
dbPlatformDesc = DatabasePlatform.SQLServerCE35.ToString();
configValue = AppConfig.GetStringValueFromConfigFile(dbPlatformDesc, string.Empty);
string[] parsedConfig = configValue.Split('|');
nmSpace = parsedConfig[0];
clsName = parsedConfig[1];
dllPath = parsedConfig[2];
db = new PFDatabase(dbPlatformDesc, dllPath, nmSpace + "." + clsName);
db.ConnectionString = connStr;
db.OpenConnection();
configKey = dbPlatformDesc + "_" + tableName;
configValue = AppConfig.GetStringValueFromConfigFile(configKey, string.Empty);
if (configValue == string.Empty)
{
_msg.Length = 0;
_msg.Append("Unable to find ");
_msg.Append(configKey);
_msg.Append(" in config.sys.");
AppMessages.DisplayErrorMessage(_msg.ToString());
return;
}
//db.SQLQuery = "select CustomerKey ,GeographyKey ,CustomerAlternateKey ,Title ,FirstName ,MiddleName ,LastName ,NameStyle ,BirthDate ,MaritalStatus ,Suffix ,Gender ,EmailAddress ,YearlyIncome ,TotalChildren ,NumberChildrenAtHome ,EnglishEducation ,SpanishEducation ,FrenchEducation ,EnglishOccupation ,SpanishOccupation ,FrenchOccupation ,HouseOwnerFlag ,NumberCarsOwned ,AddressLine1 ,AddressLine2 ,Phone ,DateFirstPurchase ,CommuteDistance from DimCustomer where 1=0";
db.SQLQuery = configValue;
db.CommandType = CommandType.Text;
dt = db.GetQueryDataSchema();
colDefsExt = PFColumnDefinitionsExt.GetColumnDefinitionsExt(dt);
filterDefs = new PFList <PFFilterDef>();
frm.QueryDataTable = dt;
frm.FilterDefs = filterDefs;
frm.MessageLogUI = _messageLog;
res = frm.ShowDialog();
filterDefs = frm.FilterDefs;
_msg.Length = 0;
_msg.Append("Form closed with DialogResult = ");
_msg.Append(res.ToString());
WriteToMessageLog(_msg.ToString());
if (res == DialogResult.OK)
{
_msg.Length = 0;
_msg.Append(Environment.NewLine);
_msg.Append("FILTER DEFINITIONS:\r\n\r\n");
_msg.Append(filterDefs.ToXmlString());
_msg.Append(Environment.NewLine);
_msg.Append(Environment.NewLine);
WriteToMessageLog(_msg.ToString());
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
WriteToMessageLog(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
if (frm != null)
{
if (FormIsOpen(frm.Name))
{
frm.Close();
}
}
frm = null;
_msg.Length = 0;
_msg.Append("\r\n... ShowFilterBuilderForm finished.");
WriteToMessageLog(_msg.ToString());
}
}