public DataTable GetCodes(string[] columns) {
StreamReader reader = new StreamReader(pcaxisFile);
/*
* String to hold PC-AXIS records.
*/
String record = "";
/*
* String to hold PC-AXIS keyword of each record.
*/
String keyword = "";
/*
* Strings to hold info for CSV header line.
*/
String heading = "";
String stub = "";
String contents = null;
// hold the TIMEVAL(field) field.
string timeVal = null;
// hold the TLIST frequency
char freq = '\0';
// hold the position of time val in key
int timeValKeyIdx = -1;
string contVariable = "";
//holds all the keywords to be used as columns
Dictionary<string, ColumnInformation> keywordsUsed = new Dictionary<string, ColumnInformation>();
/*
* ArrayList that will include all ArrayLists that we
* need to build tuples for.
*/
Dictionary<String, List<string>> values = new Dictionary<String, List<string>>();
Dictionary<String, List<string>> codes = new Dictionary<String, List<string>>();
Dictionary<String, String> keys = new Dictionary<String, String>();
//Regex extractRecord = new Regex("^(?<keyword>[^\\(=]+)(\\((?<var>\"[^\"]+\")\\))?=(TLIST\\((?<tlist>.+)\\),?)?(?<value>[^;]*);$", RegexOptions.IgnoreCase | RegexOptions.Singleline);
bool stopreading = false;
while (!reader.EndOfStream && !stopreading) {
/*
* The following is required because we are not sure if a delimiter
* has been used at the end of each line for the DATA record.
* Later on, we use \n as a delimiter.
*/
string variable = null;
string val = null;
record += '\n';
string firstSection = GetFirstLineSection(reader);
if (firstSection.ToLower() == "data=") {
stopreading = true;
break;
}
if (!reader.EndOfStream)
record += firstSection + reader.ReadLine().Trim();
else
record += firstSection;
if (record.EndsWith(";", StringComparison.InvariantCulture)) {
/*
* Record complete. Trim unecessary whitespace.
*/
record = record.Trim();
Match m = extractRecord.Match(record);
keyword = m.Groups["keyword"].Value;
variable = m.Groups["var"].Value.Trim().Replace("\"","");
val = m.Groups["value"].Value.Trim();
if (keyword.Equals("CONTENTS", StringComparison.InvariantCultureIgnoreCase)) {
contents = val;
} else if (keyword.Equals("STUB", StringComparison.InvariantCultureIgnoreCase)) {
stub = val;
} else if (keyword.Equals("HEADING", StringComparison.InvariantCultureIgnoreCase)) {
heading = val;
} else if (keyword.Equals("CONTVARIABLE", StringComparison.InvariantCultureIgnoreCase)) {
contVariable = val.Replace("\"", "").Replace("#", " ").Replace("\n", " ");
} else if (record.StartsWith(keyword + "=") && (
keyword.Equals("MATRIX", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SUBJECT-CODE", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SUBJECT-AREA", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("UNITS", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("DECIMALS", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("TITLE", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("DESCRIPTION", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("UPDATE-FREQUENCY", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("TABLE_ID", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SOURCE", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("DATABASE", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("REFPERIOD", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("BASEPERIOD", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CONTENTS", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("LAST-UPDATED", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("STOCKFA", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CFPRICES", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("DAYADJ", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SEASADJ", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("UNITS", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CONTACT", StringComparison.InvariantCultureIgnoreCase)
)
) {
if (!keywordsUsed.ContainsKey(keyword)) {
ColumnInformation ci = new ColumnInformation();
ci.ColumnName = FormatColumnHeader(keyword);
val = val.Replace("\"", "").Replace("#", " ").Replace("\n", " ");
ci.Value = val;
keywordsUsed[keyword] = ci;
}
} else if (!string.IsNullOrEmpty(contVariable) && record.StartsWith(keyword + "(") && (
keyword.Equals("REFPERIOD", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("BASEPERIOD", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CONTACT", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("LAST-UPDATED", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("STOCKFA", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CFPRICES", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("DAYADJ", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SEASADJ", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("UNITS", StringComparison.InvariantCultureIgnoreCase)
)
) {
if (!keywordsUsed.ContainsKey(keyword)) {
ColumnInformation ci = new ColumnInformation();
ci.ColumnName = FormatColumnHeader(keyword);
ci.IsContVar = true;
val = val.Replace("\"", "").Replace("#", " ").Replace("\n", " ");
ci.Values.Add(variable, val);
keywordsUsed[keyword] = ci;
} else {
ColumnInformation ci = keywordsUsed[keyword];
ci.ColumnName = FormatColumnHeader(keyword);
ci.IsContVar = true;
val = val.Replace("\"", "").Replace("#", " ").Replace("\n", " ");
ci.Values.Add(variable, val);
}
} else if (!String.IsNullOrEmpty(variable) && !keyword.Equals("KEYS", StringComparison.InvariantCultureIgnoreCase)) {
val = val.Replace("\n", "");
List<string> tmlList = new List<string>();
if (keyword.Equals("TIMEVAL", StringComparison.InvariantCultureIgnoreCase)) {
string tList = m.Groups["tlist"].Value;
val = handleTimeVal(val, tList);
timeVal = variable;
freq = tList[0];
//if (!tList.Equals("A1", StringComparison.InvariantCultureIgnoreCase) &&
// !tList.Equals("H1", StringComparison.InvariantCultureIgnoreCase) &&
// !tList.Equals("Q1", StringComparison.InvariantCultureIgnoreCase) &&
// !tList.Equals("M1", StringComparison.InvariantCultureIgnoreCase) &&
// !tList.Equals("W1", StringComparison.InvariantCultureIgnoreCase)) {
// val = buildTimeVal(tList);
//}
ColumnInformation ci = new ColumnInformation();
ci.ColumnName = "FREQ";
ci.Value = freq.ToString();
keywordsUsed["FREQ"] = ci;
ci = new ColumnInformation();
ci.ColumnName = "TIME_FORMAT";
ci.Value = TransformFREQToSDMXFormat(freq.ToString());
keywordsUsed["TIME_FORMAT"] = ci;
}
tmlList.AddRange(GetCommaSeperatedValues(val));
//if (val[0] == '"') {
// tmlList.AddRange(extractValues.Split(val.Substring(1, val.Length - 2)));
//} else {
// tmlList.AddRange(extractValues.Split(val));
//}
//tmlList.AddRange(val.Split(','));
if (keyword.Equals("CODES", StringComparison.InvariantCultureIgnoreCase) ||
keyword.Equals("TIMEVAL", StringComparison.InvariantCultureIgnoreCase)) {
codes.Add(variable, tmlList);
} else if (keyword.Equals("VALUES", StringComparison.InvariantCultureIgnoreCase)) {
values.Add(variable, tmlList);
}
} else if (keyword.Equals("KEYS", StringComparison.InvariantCultureIgnoreCase)) {
/*
* Build list of keys used in the file.
*/
keys.Add(variable, val);
if (variable.Equals(timeVal, StringComparison.InvariantCultureIgnoreCase))
{
timeValKeyIdx = keys.Count - 1;
}
}
if (keyword.Equals("DATA", StringComparison.InvariantCultureIgnoreCase)) {
record = record.Substring(0, record.Length - 1);
} else {
record = "";
}
}
}
ArrayList listsForTuples = new ArrayList();
foreach (string column in columns) {
string codesOrValues = "";
foreach (string col in keys.Keys) {
if (FormatColumnHeader(col).Replace("\"", "") == column) {
if (col.Equals(timeVal, StringComparison.InvariantCultureIgnoreCase) && keys[col].Equals("VALUES"))
{
if (values.ContainsKey(col))
{
trancodeValueList(values[col], freq.ToString());
}
else
{
codesOrValues = "CODES";
break;
}
}
codesOrValues = keys[col];
break;
}
}
string key = "";
bool found = false;
if (codesOrValues != "VALUES") {
foreach (string col in codes.Keys) {
if (FormatColumnHeader(col).Replace("\"", "") == column) {
key = col;
break;
}
}
if (key != "") {
listsForTuples.Add(codes[key]);
found = true;
}
}
if (key == "") {
foreach (string col in values.Keys) {
if (FormatColumnHeader(col).Replace("\"", "") == column) {
key = col;
break;
}
}
if (key != "") {
listsForTuples.Add(values[key]);
found = true;
}
}
foreach (string k in keywordsUsed.Keys) {
if (keywordsUsed[k].ColumnName == column) {
List<string> returnValues = new List<string>();
if (!string.IsNullOrEmpty(contVariable) && keywordsUsed[k].IsContVar) {
foreach (string cval in keywordsUsed[k].Values.Values) {
string contvarvalue = cval;
List<string> codevalues = null;
List<string> valuevalues = null;
if (keys.Count <= 0 || keys.Count > 0 && keys[contVariable] == "CODES") {
if (codes.TryGetValue(contVariable, out codevalues) && values.TryGetValue(contVariable, out valuevalues)) {
for (int l = 0; l < codes.Count; l++) {
if (valuevalues.Count > l && valuevalues[l].ToString() == cval) {
contvarvalue = codevalues[l].ToString();
break;
}
}
}
}
if(!returnValues.Contains(contvarvalue))
returnValues.Add(contvarvalue);
}
} else {
returnValues.Add(keywordsUsed[k].Value);
}
listsForTuples.Add(returnValues);
found = true;
break;
}
}
if (!found && column.ToLower() == "obs_status") {
List<string> retval = getAllDotsValuesInDataSection(reader);
listsForTuples.Add(retval);
}
}
DataTable dt = new DataTable();
foreach (string column in columns) {
DataColumn dc = new DataColumn(column);
dt.Columns.Add(dc);
}
int j = 0;
for (IEnumerator iter = new TupleIterator(listsForTuples); iter.MoveNext(); j++) {
DataRow dr = dt.NewRow();
int i=0;
foreach (object obj in (ArrayList)iter.Current) {
dr[i] = obj.ToString();
i++;
}
dt.Rows.Add(dr);
}
return dt;
}
public void InsertDataInMemoryDB(SQLiteCommand commandData, SQLiteCommand commandKeywordsSingleValue,SQLiteCommand commandContVariableKeywords) {
StreamReader reader = new StreamReader(pcaxisFile);
/*
* String to hold PC-AXIS records.
*/
StringBuilder record = new StringBuilder();
/// check if we have enough space
//if (reader.BaseStream.Length >= record.MaxCapacity) {
// throw new Exception("PC-Axis file is too big to fit in memory");
//}
//record.Capacity = (int)reader.BaseStream.Length;
/*
* String to hold PC-AXIS keyword of each record.
*/
String keyword = "";
/*
* Strings to hold info for CSV header line.
*/
String heading = "";
String stub = "";
String contents = null;
// hold the TIMEVAL(field) field.
string timeVal = null;
// hold the TLIST frequency
char freq = '\0';
// hold the position of time val in key
int timeValKeyIdx = -1;
string contVariable = "";
//holds all the keywords to be used as columns
Dictionary<string,ColumnInformation> keywordsUsed = new Dictionary<string,ColumnInformation>();
/*
* ArrayList that will include all ArrayLists that we
* need to build tuples for.
*/
ArrayList listsForTuples = new ArrayList();
Dictionary<String, ArrayList> values = new Dictionary<String, ArrayList>();
Dictionary<String, ArrayList> codes = new Dictionary<String, ArrayList>();
Dictionary<String, String> keys = new Dictionary<String, String>();
List<String> variablesList = new List<String>();
List<String> stubList = new List<String>();
List<String> headingList = new List<String>();
//Regex extractRecord = new Regex("^(?<keyword>[^\\(=]+)(\\((?<var>\"[^\"]+\")\\))?=(TLIST\\((?<tlist>.+)\\),?)?(?<value>[^;]*);$", RegexOptions.IgnoreCase | RegexOptions.Singleline);
//@aris: maybe the following regex is more correct as there might be a ';' character in the values list within double quotes (")
//Regex extractRecord = new Regex("^(?<keyword>[^\\(=]+)(\\((?<var>\"[^\"]+\")\\))?=(TLIST\\((?<tlist>.+)\\),?)?(?<value>.*);$", RegexOptions.IgnoreCase | RegexOptions.Singleline);
while (!reader.EndOfStream) {
/*
* The following is required because we are not sure if a delimiter
* has been used at the end of each line for the DATA record.
* Later on, we use \n as a delimiter.
*/
string variable = null;
string val = null;
record.Append('\n');
string firstSection = GetFirstLineSection(reader);
if (firstSection.ToLower() == "data=") {
break;
}
record.Append(firstSection);
if(!reader.EndOfStream)
record.Append(reader.ReadLine().Trim());
if (record[record.Length - 1] == ';') {
/*
* Record complete. Trim unecessary whitespace.
*/
string recordStr = record.ToString().Trim();
record = new StringBuilder(recordStr);//record.ToString().Trim());
Match m = extractRecord.Match(recordStr);//record.ToString());
keyword = m.Groups["keyword"].Value;
variable = m.Groups["var"].Value.Trim().Replace("\"","");
val = m.Groups["value"].Value.Trim();
if (keyword.Equals("CONTENTS", StringComparison.InvariantCultureIgnoreCase)) {
contents = val;
} else if (keyword.Equals("STUB", StringComparison.InvariantCultureIgnoreCase)) {
stub = val;
stubList.AddRange(GetCommaSeperatedValues(stub));
} else if (keyword.Equals("HEADING", StringComparison.InvariantCultureIgnoreCase)) {
heading = val;
headingList.AddRange(GetCommaSeperatedValues(heading));
} else if (keyword.Equals("CONTVARIABLE", StringComparison.InvariantCultureIgnoreCase)) {
contVariable = val.Replace("\"", "").Replace("#", " ").Replace("\n", " ");
} else if (recordStr.StartsWith(keyword + "=") && (
keyword.Equals("MATRIX", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SUBJECT-CODE", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SUBJECT-AREA", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("UNITS", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("DECIMALS", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("TITLE", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("DESCRIPTION", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("UPDATE-FREQUENCY", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("TABLE_ID", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SOURCE", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("DATABASE", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("REFPERIOD", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("BASEPERIOD", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CONTENTS", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("LAST-UPDATED", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("STOCKFA", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CFPRICES", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("DAYADJ", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SEASADJ", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("UNITS", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CONTACT", StringComparison.InvariantCultureIgnoreCase)
)
) {
if (!keywordsUsed.ContainsKey(keyword)) {
ColumnInformation ci = new ColumnInformation();
ci.ColumnName = FormatColumnHeader(keyword);
val = val.Replace("\"", "").Replace("#"," ").Replace("\n"," ");
ci.Value = val;
keywordsUsed[keyword] = ci;
}
} else if (!string.IsNullOrEmpty(contVariable) && recordStr.StartsWith(keyword + "(") && (
keyword.Equals("REFPERIOD", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("BASEPERIOD", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CONTACT", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("LAST-UPDATED", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("STOCKFA", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("CFPRICES", StringComparison.InvariantCultureIgnoreCase)
|| keyword.Equals("DAYADJ", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("SEASADJ", StringComparison.InvariantCultureIgnoreCase) || keyword.Equals("UNITS", StringComparison.InvariantCultureIgnoreCase)
)
) {
if (!keywordsUsed.ContainsKey(keyword)) {
ColumnInformation ci = new ColumnInformation();
ci.ColumnName = FormatColumnHeader(keyword);
ci.IsContVar = true;
val = val.Replace("\"", "").Replace("#", " ").Replace("\n", " ");
ci.Values.Add(variable, val);
keywordsUsed[keyword] = ci;
} else {
ColumnInformation ci = keywordsUsed[keyword];
ci.ColumnName = FormatColumnHeader(keyword);
ci.IsContVar = true;
val = val.Replace("\"", "").Replace("#", " ").Replace("\n", " ");
ci.Values.Add(variable, val);
//keywordsUsed[keyword] = ci;
}
} else if (!String.IsNullOrEmpty(variable) && !keyword.Equals("KEYS", StringComparison.InvariantCultureIgnoreCase)) {
val = val.Replace("\n", "");
ArrayList tmlList = new ArrayList();
if (keyword.Equals("TIMEVAL", StringComparison.InvariantCultureIgnoreCase)) {
string tList = m.Groups["tlist"].Value;
val = handleTimeVal(val, tList);
timeVal = variable;
freq = tList[0];
//if (!tList.Equals("A1", StringComparison.InvariantCultureIgnoreCase) &&
// !tList.Equals("H1", StringComparison.InvariantCultureIgnoreCase) &&
// !tList.Equals("Q1", StringComparison.InvariantCultureIgnoreCase) &&
// !tList.Equals("M1", StringComparison.InvariantCultureIgnoreCase) &&
// !tList.Equals("W1", StringComparison.InvariantCultureIgnoreCase))
//{
// val = buildTimeVal(tList);
//}
//else
//{
// transcodeTimeOutsideTlist(val, tList);
//}
ColumnInformation ci = new ColumnInformation();
ci.ColumnName = "FREQ";
ci.Value = freq.ToString();
keywordsUsed["FREQ"] = ci;
ci = new ColumnInformation();
ci.ColumnName = "TIME_FORMAT";
ci.Value = TransformFREQToSDMXFormat(freq.ToString());
keywordsUsed["TIME_FORMAT"] = ci;
}
tmlList.AddRange(GetCommaSeperatedValues(val));
//if (val[0] == '"') {
// tmlList.AddRange(extractValues.Split(val.Substring(1, val.Length - 2)));
//} else {
// tmlList.AddRange(extractValues.Split(val));
//}
if (keyword.Equals("CODES", StringComparison.InvariantCultureIgnoreCase) ||
keyword.Equals("TIMEVAL", StringComparison.InvariantCultureIgnoreCase)) {
codes.Add(variable, tmlList);
} else if (keyword.Equals("VALUES", StringComparison.InvariantCultureIgnoreCase)) {
values.Add(variable, tmlList);
}
} else if (keyword.Equals("KEYS", StringComparison.InvariantCultureIgnoreCase)) {
/*
* Build list of keys used in the file.
*/
keys.Add(variable, val);
if (variable.Equals(timeVal, StringComparison.InvariantCultureIgnoreCase)) {
timeValKeyIdx = keys.Count - 1;
}
}
if (keyword.Equals("DATA", StringComparison.InvariantCultureIgnoreCase)) {
record.Remove(record.Length - 1, 1);
} else {
record = new StringBuilder();
}
}
}
char separator = ',';
/*
* Prepare CSV header String and write it.
*/
if (!String.IsNullOrEmpty(stub)) {
stub += separator;
}
if (!String.IsNullOrEmpty(heading)) {
heading += separator;
}
if (contents == null) {
Console.WriteLine("NO CONTENTS RECORD DEFINED! MANDATORY!");
}
String csvHeaderString = stub + heading + contents;
/*
* Remove contents from CSV header in order to build up a varaibles List.
*/
String variablesString = csvHeaderString.Substring(0, csvHeaderString.LastIndexOf("\",\"") + 1);
variablesList.AddRange(GetCommaSeperatedValues(variablesString)); // TODO: Take into account that STUB or HEADING values may include commas (,).
/*
* Go through keys to identify whether values or codes are going to be used
* for the tuples. If no keys defined, codes are prefered, unless only values
* are defined.
*/
bool keysUsed = false;
if (keys.Count > 0) {
/*
* This means that DATA will contain
* a sparse table, thus including values/codes
* for the STUB in front of the observations.
* So, we need the tuples for the rest of the
* variables, i.e. the HEADING (rows).
*/
foreach (String headingVariable in headingList) {
ArrayList variableItems = null;
if (!codes.TryGetValue(headingVariable, out variableItems)) {
if (values.TryGetValue(headingVariable, out variableItems)) {
} else {
Console.WriteLine("NO ITEMS FOUND FOR: " + headingVariable);
}
}
listsForTuples.Add(variableItems);
}
keysUsed = true;
} else {
/*
* All values MUST exist in DATA record.
* VALUES or CODES should be taken for each variable.
*/
foreach (String variable in variablesList) {
ArrayList variableItems = null;
if (!codes.TryGetValue(variable, out variableItems)) {
if (values.TryGetValue(variable, out variableItems)) {
} else {
Console.WriteLine("Nor CODES or VALUES found for variable: " + variable);
}
}
listsForTuples.Add(variableItems);
//if (codes.Count > 0) {
// listsForTuples.Add(codes[variable]);
//} else if (values.Count > 0) {
// listsForTuples.Add(values[variable]);
//} else {
// Console.WriteLine("Nor CODES or VALUES found for variable: " + variable);
//}
}
}
/*
* KEYS are used, thus the prefix of each record from the DATA section
* will be copied directly to the CSV.
*/
List<string> thevalues = new List<string>();
int valuesRead = 0;
//NEEDS REVISION
int valueColumnsNum = 1;
if(keysUsed)
foreach (string head in headingList) {
valueColumnsNum *= codes[head].Count;
}
int j = 0;
while (!reader.EndOfStream && (!PcAxisConnection.DataPreviewOnly || DataPreviewRows == 0 || j<DataPreviewRows))
for (IEnumerator iter = new TupleIterator(listsForTuples); iter.MoveNext() && (!PcAxisConnection.DataPreviewOnly || DataPreviewRows == 0|| j < DataPreviewRows); j++) {
if (reader.EndOfStream)
break;
/*
* Re-encapsulate values into double-quotes.
*/
int parameterIndex = 0;
string value = "";
char[] chars = new char[1];
char[] oldchars = new char[1];
while (!reader.EndOfStream) {
reader.Read(chars, 0, 1);
if ((chars[0] == ',' || chars[0] == ' ' || chars[0] == ';' || chars[0] == '\n' || chars[0] == '\t') &&
(
keysUsed && thevalues.Count < keys.Count && oldchars[0] == '"'
|| (keysUsed && thevalues.Count >= keys.Count || !keysUsed)
)
) {
if (keysUsed && thevalues.Count < keys.Count) {
thevalues.Add(value.Replace("\"", ""));
value = "";
} else if (value.Trim() != "")
break;
} else if (chars[0] != '\r') {
value += chars[0];
}
oldchars[0] = chars[0];
}
value = value.Trim();
if (reader.EndOfStream && value == "")
break;
foreach (string str in thevalues) {
if (parameterIndex == timeValKeyIdx)
{
commandData.Parameters[parameterIndex].Value = transcodeTimeOutsideTlist(str, freq.ToString());
}
else
{
commandData.Parameters[parameterIndex].Value = str;
}
parameterIndex++;
}
foreach (object obj in (ArrayList)iter.Current) {
commandData.Parameters[parameterIndex].Value = obj.ToString();
parameterIndex++;
}
string obs_status = "A";
string valuetoshow = value;
if (valuetoshow == "\".\"" ||
valuetoshow == ".") {
valuetoshow = "";
obs_status = ".";
}
if (valuetoshow == "\"..\"" ||
valuetoshow == "..") {
valuetoshow = "";
obs_status = "..";
}
if (valuetoshow == "\"...\"" ||
valuetoshow == "...") {
valuetoshow = "";
obs_status = "...";
}
if (valuetoshow == "\"....\"" ||
valuetoshow == "....") {
valuetoshow = "";
obs_status = "....";
}
if (valuetoshow == "\".....\"" ||
valuetoshow == ".....") {
valuetoshow = "";
obs_status = ".....";
}
if (valuetoshow == "\"......\"" ||
valuetoshow == "......") {
valuetoshow = "";
obs_status = "......";
}
if (valuetoshow == "\".......\"" ||
valuetoshow == ".......") {
valuetoshow = "";
obs_status = ".......";
}
if (valuetoshow == "\"-\"" ||
valuetoshow == "-") {
valuetoshow = "";
obs_status = "-";
}
commandData.Parameters[parameterIndex].Value = valuetoshow;
parameterIndex++;
commandData.Parameters[parameterIndex].Value = obs_status;
parameterIndex++;
/*
* Execute Command
*/
commandData.ExecuteNonQuery();
valuesRead++;
if (keysUsed && valuesRead == valueColumnsNum) {
valuesRead = 0;
thevalues.Clear();
}
}
int paramIndex = 0;
//holds all the possible values that a ContVariable can have
List<string> contVariableValues = new List<string>();
foreach (ColumnInformation cinfo in keywordsUsed.Values) {
if (!cinfo.IsContVar) {
commandKeywordsSingleValue.Parameters[paramIndex].Value = cinfo.Value;
paramIndex++;
} else {
foreach (string key in cinfo.Values.Keys) {
if (!contVariableValues.Contains(key)) {
contVariableValues.Add(key);
}
}
}
}
/*
* Execute Command
*/
commandKeywordsSingleValue.ExecuteNonQuery();
foreach (string v in contVariableValues) {
string contvarvalue = v;
ArrayList codevalues = null;
ArrayList valuevalues = null;
if (!keysUsed || keysUsed && keys[contVariable] == "CODES") {
if (codes.TryGetValue(contVariable, out codevalues) && values.TryGetValue(contVariable, out valuevalues)) {
for (int k = 0; k < codes.Count; k++) {
if (valuevalues.Count > k && valuevalues[k].ToString() == v) {
contvarvalue = codevalues[k].ToString();
break;
}
}
}
}
commandContVariableKeywords.Parameters[0].Value = contvarvalue;
int pIndex = 1;
//holds all the possible values that a ContVariable can have
foreach (ColumnInformation cinfo in keywordsUsed.Values) {
if (cinfo.IsContVar) {
if (cinfo.Values.ContainsKey(v))
commandContVariableKeywords.Parameters[pIndex].Value = cinfo.Values[v];
else {
commandContVariableKeywords.Parameters[pIndex].Value = null;
//uncomment for getting the default value of the contVariable in case no value is provided
//commandContVariableKeywords.Parameters[pIndex].Value = cinfo.Value;
}
pIndex++;
}
}
commandContVariableKeywords.ExecuteNonQuery();
}
/*
* Close streams and writers/readers.
*/
reader.Close();
}
