IDataReader RunQuery(string queryStr, ref int [] columnNos, ref int tableNum) { string regexp = "\\b(Select|select) (?<columnList>(COLUMNS|((COLUMN\\d+,)*(COLUMN\\d+)))) from (?<tableName>TABLE\\d+)( order by (?<OrderBy>COLUMN\\d+))*"; Match m = Regex.Match(queryStr, regexp, RegexOptions.ExplicitCapture); if (!m.Success) { Console.WriteLine("Incorrect query format!!!"); return(null); } columnNos = null; while (m.Success) { string tableTag = m.Result("${tableName}"); tableNum = Convert.ToInt32(tableTag.Replace("TABLE", "")); string tableName = ConfigClass.GetElement(configDoc, "tables", tableTag.ToLower(), "name"); queryStr = queryStr.Replace(tableTag, tableName); for (int i = 0; i < m.Groups.Count; i++) { Group g = m.Groups [i]; CaptureCollection cc = g.Captures; for (int j = 0; j < cc.Count; j++) { string matchedVal = cc [j].Value; if (matchedVal.Equals("COLUMNS")) { string [] columnNames = ConfigClass.GetColumnNames(configDoc, tableNum); queryStr = queryStr.Replace("COLUMNS", String.Join(",", columnNames)); columnNos = new int [columnNames.Length]; for (int index = 1; index <= columnNos.Length; index++) { columnNos [index - 1] = index; } } else if (matchedVal.StartsWith("COLUMN")) { // May be a column name or a comma // separated list of columns string [] listOfColumns = matchedVal.Split(','); if (columnNos == null) { columnNos = new int [listOfColumns.Length]; int colIndex = 0; foreach (string str in listOfColumns) { int columnNo = Convert.ToInt32(str.Replace("COLUMN", "")); columnNos [colIndex++] = columnNo; } } foreach (string str in listOfColumns) { string columnName = ConfigClass.GetElement(configDoc, "tables", tableTag.ToLower(), str.ToLower(), "name"); queryStr = queryStr.Replace(str, columnName); } } } } m = m.NextMatch(); } IDataReader rdr = null; cmd.CommandText = queryStr; try { rdr = cmd.ExecuteReader(); } catch (Exception e) { Console.WriteLine("ERROR : " + e.Message); Console.WriteLine("\nSTACKTRACE : " + e.StackTrace); return(null); } return(rdr); }
// Method that actually runs the entire test : Connects to a database, // retrieves values from different tables, and compares them against // the values that we had entered public void RunTest() { GetConnection(); if (con == null) { return; } CreateCommand(); if (cmd == null) { return; } string noOfQueries = null; string errorMsg = ""; string query = null; try { noOfQueries = ConfigClass.GetElement(configDoc, "queries", "numQueries"); int numQueries = Convert.ToInt32(noOfQueries); string tableName = null; int [] columnNos = null; int tableNum = 0; Console.WriteLine("\n**** Testing Data Retrieval using datasets*****\n"); for (int i = 1; i <= numQueries; i++) { errorMsg = ""; try { query = ConfigClass.GetElement(configDoc, "queries", "query" + i); query = FrameQuery(query, ref columnNos, ref tableNum); tableName = ConfigClass.GetElement(configDoc, "tables", "table" + tableNum, "name"); } catch (XPathException e) { Console.WriteLine(e.Message); continue; // need not return here; try with the next one } try { PopulateDataSetFromTable(query, tableName); } catch (Exception e) { Console.WriteLine("Table : {0} : Unable to fill the dataset!!!", tableName); Console.WriteLine("ERROR : " + e.Message); Console.WriteLine("STACKTRACE : " + e.StackTrace); continue; } CompareData(tableNum, setOfChanges, columnNos); } string [] columnNames = null; string noOfTables = ConfigClass.GetElement(configDoc, "tables", "numTables"); int numTables = 0; if (noOfTables != null) { numTables = Convert.ToInt32(noOfTables); } for (int i = 1; i <= numTables; i++) { setOfChanges = null; try { tableName = ConfigClass.GetElement(configDoc, "tables", "table" + i, "name"); columnNames = ConfigClass.GetColumnNames(configDoc, i); } catch (XPathException e) { Console.WriteLine(e.Message); continue; // need not return here; try with the next one } try { query = "Select " + String.Join(",", columnNames) + " from " + tableName; PopulateDataSetFromTable(query, tableName); } catch (Exception e) { Console.WriteLine("Table : {0} : Unable to fill the dataset after " + "updating the database!!!", tableName); Console.WriteLine("ERROR : " + e.Message); Console.WriteLine("STACKTRACE : " + e.StackTrace); continue; } if (dataset == null) { Console.WriteLine("Unable to populate the dataset!!!"); continue; } MakeChanges(i, ref errorMsg); if (dataset.HasChanges() == false) { Console.WriteLine("\nTable : {0} : No Changes for this table in the config file", tableName); continue; } else { if (ReconcileChanges(tableName, ref errorMsg) == false) { Console.WriteLine("Table : {0} : Unable to " + "update the database !!!", tableName); Console.WriteLine(errorMsg); continue; } else { Console.WriteLine("\nTable : {0} : Updated " + "using datasets", tableName); } } Console.WriteLine("\nTable : {0} : Refilling the dataset\n", tableName); // Clear the data in the dataset dataset.Clear(); //Fill again from the database dataAdapter.Fill(dataset, tableName); CompareData(i, setOfChanges, null); } } catch (Exception e) { Console.WriteLine("ERROR : " + e.Message); Console.WriteLine("STACKTRACE : " + e.StackTrace); } finally { con.Close(); con = null; } }