// returns a Open connection
public override void GetConnection()
{
string connectionString = null;
try {
connectionString = ConfigClass.GetElement(configDoc, "database", "connectionString");
} catch (Exception e) {
Console.WriteLine("Error reading the config file");
Console.WriteLine(e.Message);
con = null;
return;
}
con = new SqlConnection(connectionString);
try {
con.Open();
} catch (SqlException e) {
Console.WriteLine("Cannot establish connection with the database");
Console.WriteLine("Probably the database is down");
con = null;
} catch (InvalidOperationException e) {
Console.WriteLine("Cannot open connection!! Probably the connection is already open!!");
con = null;
} catch (Exception e) {
Console.WriteLine("Cannot open connection ");
con = null;
}
}
bool Initialize(string database)
{
curDatabase = database;
node = (XmlNode)ConfigurationSettings.GetConfig(database);
con = GetConnection(database);
try {
con.Open();
} catch (Exception e) {
createStoredProc = deleteTables = createTables = insertData = null;
Console.WriteLine(e.Message);
Reset();
return(false);
}
cmd = con.CreateCommand();
createStoredProc = ConfigClass.GetElement(node, "createStoredProc");
deleteTables = ConfigClass.GetElement(node, "deleteTables");
createTables = ConfigClass.GetElement(node, "createTables");
insertData = ConfigClass.GetElement(node, "insertData");
string noOfTables = ConfigClass.GetElement(node, "tables", "numTables");
int numTables = Convert.ToInt32(noOfTables);
tabinfo = new TableInfo [numTables];
for (int i = 1; i <= numTables; i++)
{
tabinfo [i - 1].Initialize(node, i);
}
return(true);
}
public void Initialize(XmlNode doc, int tableNum)
{
string tableTag = "table" + tableNum;
name = ConfigClass.GetElement(doc, "tables", tableTag, "name");
columns = ConfigClass.GetColumnDetails(doc, tableNum, "name");
types = ConfigClass.GetColumnDetails(doc, tableNum, "type");
}
public virtual void MakeChanges(int tableNum, ref string errorMsg)
{
string numchanges = null;
try {
numchanges = ConfigClass.GetElement(configDoc, "values", "table" + tableNum, "changes", "numChanges");
} catch (Exception e) {
return;
}
int noChanges = Convert.ToInt32(numchanges);
string tableName = ConfigClass.GetElement(configDoc, "values", "table" + tableNum, "tableName");
int numRows = Convert.ToInt32(ConfigClass.GetElement(configDoc, "values", "table" + tableNum, "numRows"));
int numCols = Convert.ToInt32(ConfigClass.GetElement(configDoc, "values", "table" + tableNum, "numCols"));
setOfChanges = new string [numRows, numCols];
for (int x = 0; x < numRows; x++)
{
for (int y = 0; y < numCols; y++)
{
setOfChanges [x, y] = null;
}
}
int dbTableNo = -1;
foreach (DataTable dbTable in dataset.Tables)
{
dbTableNo++;
if (tableName.Equals(dbTable.TableName))
{
break;
}
}
for (int index = 1; index <= noChanges; index++)
{
string tagname = "change" + index;
int row = Convert.ToInt32(ConfigClass.GetElement(configDoc, "values",
"table" + tableNum, "changes", tagname, "row"));
int col = Convert.ToInt32(ConfigClass.GetElement(configDoc, "values",
"table" + tableNum, "changes", tagname, "col"));
string value = ConfigClass.GetElement(configDoc, "values",
"table" + tableNum, "changes", tagname, "value");
setOfChanges [row - 1, col - 1] = value;
DataRow drow = dataset.Tables [dbTableNo].Rows [row - 1];
DataColumn dcol = dataset.Tables [dbTableNo].Columns [col - 1];
object dataSetValue = drow [dcol];
try {
drow [dcol] = ConvertToType(dataSetValue.GetType(), value, ref errorMsg);
} catch (Exception e) {
drow [dcol] = DBNull.Value;
}
}
}
bool CreateStoredProc(int storedProcNum)
{
string name = ConfigClass.GetElement(node, "StoredProc", "StoredProc" + storedProcNum, "name");
string type = ConfigClass.GetElement(node, "StoredProc", "StoredProc" + storedProcNum, "type");
int numStatements = Convert.ToInt32(ConfigClass.GetElement(node, "StoredProc",
"StoredProc" + storedProcNum, "template", "numStmts"));
string [] templates = new string [numStatements];
for (int i = 1; i <= numStatements; i++)
{
templates [i - 1] = ConfigClass.GetElement(node, "StoredProc", "StoredProc" + storedProcNum, "template", "stmt" + i);
}
if (type.Equals("generic"))
{
// To be created for all tables
for (int tableNum = 0; tableNum < tabinfo.Length; tableNum++)
{
string storedProcName = name.Replace("{{TABLE}}", tabinfo [tableNum].name);
Console.WriteLine("Creating : " + storedProcName);
for (int index = 1; index <= numStatements; index++)
{
string SPtemplate = templates [index - 1];
SPtemplate = SPtemplate.Replace("{{TABLE}}", tabinfo [tableNum].name);
string listOfColumns = String.Join(",", tabinfo [tableNum].columns);
SPtemplate = SPtemplate.Replace("{{TABLE}}", tabinfo [tableNum].name);
SPtemplate = SPtemplate.Replace("{{COLUMNS}}", listOfColumns);
int beg = 0;
while ((beg = SPtemplate.IndexOf("{{COLUMN_")) >= 0)
{
int end = SPtemplate.IndexOf("}}", beg + 9);
string strToBeReplaced = SPtemplate.Substring(beg, end - beg + 2);
beg += 9;
int columnNum = Convert.ToInt32(SPtemplate.Substring(beg, end - beg));
SPtemplate = SPtemplate.Replace(strToBeReplaced,
tabinfo [tableNum].columns [columnNum]);
}
SPtemplate = SPtemplate.Replace("\r", "");
cmd.CommandText = SPtemplate;
Console.WriteLine(SPtemplate);
cmd.ExecuteNonQuery();
}
}
}
else
{
// To be implemented
}
return(true);
}
public static string [] GetColumnNames(XmlNode doc, int tableNum)
{
string tagName = "table" + tableNum;
int numColumns = Convert.ToInt16(ConfigClass.GetElement(doc, "tables", tagName, "numColumns"));
string [] columns = new string [numColumns];
for (int col = 1; col <= numColumns; col++)
{
columns[col - 1] = ConfigClass.GetElement(doc, "tables", tagName, "column" + col, "name");
}
return(columns);
}
IDbConnection GetConnection(string database)
{
IDbConnection con = null;
string connStr = ConfigClass.GetElement(node, "database", "connectionString");
if (database == "oracle")
{
con = new OracleConnection(connStr);
}
else if (database == "mssql")
{
con = new SqlConnection(connStr);
}
return(con);
}
// 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);
}
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("======================");
}
}
}
