// returns a Open connection
public override void GetConnection()
{
string connectionString = null;
try {
connectionString = ConfigClass.GetElement(configDoc, "database", "OdbcConnString");
} catch (Exception e) {
Console.WriteLine("Error reading the config file");
Console.WriteLine(e.Message);
return;
}
con = new OdbcConnection(connectionString);
try {
con.Open();
} catch (Exception e) {
Console.WriteLine("Cannot establish connection with the database");
con = null;
}
}
public static String [] GetColumnDetails(XmlNode doc, int tableNum, string attr)
{
String tagName = "table" + tableNum;
int numColumns = Convert.ToInt32(ConfigClass.GetElement(doc, "tables", tagName, "numColumns"));
String [] columns = new String [numColumns];
for (int col = 1; col <= numColumns; col++)
{
XmlNodeList nodelist = doc.SelectNodes("//tables/" + tagName + "/column" + col + "/" + attr);
if (nodelist.Count == 0)
{
columns [col - 1] = "";
}
else
{
columns [col - 1] = nodelist [0].InnerText;
}
}
return(columns);
}
bool InsertData()
{
int numTables = Convert.ToInt32(ConfigClass.GetElement(node, "values", "numTables"));
string tableName;
for (int i = 1; i <= numTables; i++)
{
string tableTag = "table" + i;
tableName = ConfigClass.GetElement(node, "values", tableTag, "tableName");
int numRows = Convert.ToInt32(ConfigClass.GetElement(node, "values", tableTag, "numRows"));
int numCols = Convert.ToInt32(ConfigClass.GetElement(node, "values", tableTag, "numCols"));
for (int j = 1; j <= numRows; j++)
{
string rowTag = "row" + j;
string insertQuery = "Insert into " + tableName + " values (";
for (int k = 1; k <= numCols; k++)
{
string colTag = "column" + k;
insertQuery += ConfigClass.GetElement(node, "values", tableTag, rowTag, colTag);
insertQuery += ",";
}
insertQuery = insertQuery.Trim(',');
insertQuery += ")";
Console.WriteLine(insertQuery);
cmd.CommandText = insertQuery;
try {
cmd.ExecuteNonQuery();
} catch (Exception e) {
Console.WriteLine("Failed to insert row into the table:" +
tableName + " " + e.Message);
return(false);
}
}
}
return(true);
}
IDbConnection GetConnection(string database)
{
IDbConnection con = null;
string connStr = ConfigClass.GetElement(node, "database", "connectionString");
if (database == "oracle")
{
con = new OracleConnection(connStr);
}
else if (database == "mysql")
{
con = new MySqlConnection(connStr);
}
else if (database == "mssql")
{
con = new SqlConnection(connStr);
}
else if (database == "postgres")
{
con = new NpgsqlConnection(connStr);
}
return(con);
}
public void SetupDatabase()
{
foreach (string db in databases)
{
bool hasErrors = false;
Console.WriteLine("\n ******** Doing setup for {0} database ********\n", db);
if (Initialize(db) != true)
{
Console.WriteLine("Failed to do the initialisation for {0} database", db);
Console.WriteLine("Skipping setup for " + db);
hasErrors = true;
continue;
}
Console.WriteLine(" *** Running the following queries ***\n");
if (deleteTables.Equals("Y"))
{
if (DeleteTables() != true)
{
hasErrors = true;
}
}
if (createTables.Equals("Y"))
{
if (CreateTables() != true)
{
hasErrors = true;
}
}
if (insertData.Equals("Y"))
{
if (InsertData() != true)
{
hasErrors = true;
}
}
if (createStoredProc.Equals("Y"))
{
int numStoredProc = Convert.ToInt32(ConfigClass.GetElement(node,
"StoredProc", "NumStoredProc"));
for (int i = 1; i <= numStoredProc; i++)
{
if (CreateStoredProc(i) != true)
{
hasErrors = true;
}
}
if (hasErrors == true)
{
Console.WriteLine("There were errors while setting up the {0} database", db);
}
else
{
Console.WriteLine("Successfully set up the {0} database", db);
}
}
}
}
// 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 noOfTables = null;
string tableName = null;
int [] columnNos = null;
try {
noOfTables = ConfigClass.GetElement(configDoc, "tables", "numTables");
short numTables = Convert.ToInt16(noOfTables);
string noOfQueries = ConfigClass.GetElement(configDoc, "queries", "numQueries");
Console.WriteLine("**** Running Queries ****");
if (noOfQueries != null)
{
int numQueries = Convert.ToInt32(noOfQueries);
for (int index = 1; index <= numQueries; index++)
{
string queryStr = ConfigClass.GetElement(configDoc, "queries", "query" + index);
int tableNum = 0;
rdr = RunQuery(queryStr, ref columnNos, ref tableNum);
if (rdr == null)
{
continue;
}
CompareData(rdr, configDoc, columnNos, tableNum);
rdr.Close();
}
}
string storedProc = null;
try {
storedProc = ConfigClass.GetElement(configDoc, "StoredProcExists");
} catch (Exception e) {
return;
}
if (storedProc.Equals("Y"))
{
Console.WriteLine("\n**** Running tests for stored procedures *****\n");
int numStoredProc = Convert.ToInt32(ConfigClass.GetElement(configDoc,
"StoredProc", "NumStoredProc"));
for (int index = 1; index <= numStoredProc; index++)
{
string storedProcTag = "StoredProc" + index;
string type = ConfigClass.GetElement(configDoc, "StoredProc",
storedProcTag, "type");
string nameTemplate = ConfigClass.GetElement(configDoc, "StoredProc",
storedProcTag, "name");
if (type.Equals("generic"))
{
// There is stored proc correspoding to each table
// Run all such stored proc
for (short i = 1; i <= numTables; i++)
{
try {
tableName = ConfigClass.GetElement(configDoc, "tables",
"table" + i, "name");
} catch (XPathException e) {
Console.WriteLine(e.Message);
continue; // need not return here; try with the next one
}
string storedProcName = nameTemplate.Replace("{{TABLE}}", tableName);
rdr = QueryUsingStoredProc(cmd, storedProcName, null);
if (rdr == null)
{
continue;
}
CompareData(rdr, configDoc, null, i);
rdr.Close();
}
}
}
}
} catch (Exception e) {
Console.WriteLine("ERROR : " + e.Message);
Console.WriteLine("STACKTRACE : " + e.StackTrace);
} finally {
con.Close();
con = null;
}
}
void CompareData(IDataReader rdr,
XmlNode doc,
int [] columnNos,
int numTable)
{
int rowNum = 0;
string errorMsg = "";
string tableName = null;
try {
tableName = ConfigClass.GetElement(doc, "tables", "table" + numTable, "name");
} catch (Exception e) {
Console.WriteLine("ERROR : " + e.Message);
Console.WriteLine("STACKTRACE : " + e.StackTrace);
return;
}
while (rdr.Read())
{
rowNum++;
string columnValue = null;
for (int i = 0; i < rdr.FieldCount; i++)
{
errorMsg = "";
int columnNum = 0;
try {
if (columnNos == null)
{
columnNum = i + 1;
}
else
{
columnNum = columnNos [i];
}
columnValue = ConfigClass.GetElement(doc, "values", "table" + numTable,
"row" + rowNum, "column" + columnNum);
} catch (Exception e) {
Console.WriteLine("ERROR : " + e.Message);
Console.WriteLine("STACKTRACE : " + e.StackTrace);
}
object obj = null;
Console.Write("Table : {0} : ROW: {1} COL: {2}", tableName, rowNum, columnNum);
try {
obj = GetValue(rdr, i);
} catch (Exception e) {
Console.WriteLine("...FAIL");
errorMsg = "ERROR : " + e.Message;
errorMsg += "\nSTACKTRACE : " + e.StackTrace;
errorMsg += "\nProbably the 'GetFieldType()' method returned a wrong type!!";
Console.WriteLine(errorMsg);
obj = null;
continue;
}
if (AreEqual(obj, columnValue, ref errorMsg))
{
Console.WriteLine("...OK");
}
else
{
Console.WriteLine("...FAIL");
if (!errorMsg.Equals(""))
{
// There was some exception
Console.WriteLine(errorMsg);
}
else
{
// Comparison failed
Console.WriteLine("Expected : " + columnValue + " Got : " + obj);
}
}
}
Console.WriteLine("======================");
}
}
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;
}
}
void CompareData(int numTable, string [,] setOfChanges, int [] columnNos)
{
int row = 0;
string errorMsg = "";
string tableName = null;
try {
tableName = ConfigClass.GetElement(configDoc, "tables", "table" + numTable, "name");
} catch (Exception e) {
Console.WriteLine("ERROR : " + e.Message);
Console.WriteLine("STACKTRACE : " + e.StackTrace);
return;
}
foreach (DataTable dbTable in dataset.Tables)
{
if (!tableName.Equals(dbTable.TableName))
{
continue;
}
row = 0;
foreach (DataRow datarow in dbTable.Rows)
{
row++;
string columnValue = null;
int column = 0;
foreach (DataColumn datacolumn in dbTable.Columns)
{
column++;
errorMsg = "";
int columnNo = column;
if (columnNos != null)
{
columnNo = columnNos [column - 1];
}
if ((setOfChanges != null) && (setOfChanges [row - 1, columnNo - 1] != null))
{
columnValue = setOfChanges [row - 1, columnNo - 1];
}
else
{
try {
columnValue = ConfigClass.GetElement(configDoc, "values",
"table" + numTable, "row" + row, "column" + columnNo);
} catch (Exception e) {
Console.WriteLine("ERROR : " + e.Message);
Console.WriteLine("STACKTRACE : " + e.StackTrace);
}
}
object obj = null;
Console.Write("Table: {0} : ROW: {1} COL: {2}", tableName, row, columnNo);
try {
obj = datarow [datacolumn];
} catch (Exception e) {
Console.WriteLine("...FAIL");
errorMsg = "ERROR : " + e.Message;
errorMsg += "\nSTACKTRACE : " + e.StackTrace;
errorMsg += "\nProbably the 'DataType' property returned a wrong type!!";
Console.WriteLine(errorMsg);
obj = null;
continue;
}
if (AreEqual(obj, columnValue, ref errorMsg))
{
Console.WriteLine("...OK");
}
else
{
Console.WriteLine("...FAIL");
if (!errorMsg.Equals(""))
{
// There was some exception
Console.WriteLine(errorMsg);
}
else
{
// Comparison failed
Console.WriteLine("Expected : {0} Got: {1}", columnValue, obj);
}
}
}
Console.WriteLine("======================");
}
}
}
