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