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(); }