/// <summary>
/// Compares the data record against a known true record. Each key or index from each data record is compared to ensure it exists in the other.
/// </summary>
/// <param name="datTrueRecord">The known true record</param>
public void CompareKeysToTrueRecord(DataRecord datTrueRecord)
{
string strLocalKeys, strExternalKeys, strException;
strException = "";
//Look through all local keys to see if external data record has them
strLocalKeys = "";
foreach (string strKey in this)
{
if (datTrueRecord.ContainsKey(strKey) == false) //External data record does not have this key
{
strLocalKeys = strLocalKeys + strKey + ", ";
}
}
if (strLocalKeys.CompareTo("") != 0) //found keys, trim trailing comma
{
strLocalKeys = strLocalKeys.Substring(0, strLocalKeys.Length - 2);
strException = strException + "Test data record (local) contains the following keys which the True data record (external) does not have: " + strLocalKeys;
}
//Look through all external keys to see if local record has them
strExternalKeys = "";
foreach (string strKey in datTrueRecord)
{
if (this.ContainsKey(strKey) == false) //External data record has a key that isn't in local
{
strExternalKeys = strExternalKeys + strKey + ", ";
}
}
if (strExternalKeys.CompareTo("") != 0) //found keys, trim trailing comma
{
strExternalKeys = strExternalKeys.Substring(0, strExternalKeys.Length - 2);
strException = strException + "True data record (external) contains the following keys which the Test data record (local) does not have: " + strLocalKeys;
}
if (strException.CompareTo("") != 0) //Found discrepencies, throw an exception
{
throw new Exception(strException);
}
}
/// <summary>
/// Submits an SQL query that expects multiple records as a result
/// </summary>
/// <param name="strSQL">The select query</param>
/// <returns>Returns an enumerated list of DataRecords, each DataRecord is one row from the results</returns>
public IEnumerable <DataRecord> SelectQueryMultipleRecords(string strSQL)
{
SqlDataReader odbRead;
SqlCommand odbCommand;
int iCtr;
string strName;
List <DataRecord> lRecords;
DataRecord oNewRecord;
CheckConnection();
lRecords = new List <DataRecord>();
odbCommand = new SqlCommand(strSQL, cdbConn);
odbCommand.CommandTimeout = cTimeoutSecs;
odbRead = odbCommand.ExecuteReader();
if (odbRead.HasRows == false)
{
odbRead.Close();
throw new Exception("The SQL query given retrned 0 rows. The query was:\n" + strSQL);
}
while (odbRead.Read() == true)
{
oNewRecord = new DataRecord();
oNewRecord.AddValue("FoundInDB", true);
for (iCtr = 0; iCtr < odbRead.FieldCount; iCtr++)
{
strName = odbRead.GetName(iCtr);
oNewRecord.AddValue(strName, odbRead[strName].ToString());
}
lRecords.Add(oNewRecord);
}
odbRead.Close();
return(lRecords);
}
/// <summary>
/// Submits an SQL query that expects a single record as a result, then returns the first record found
/// </summary>
/// <param name="strSQL">The select query</param>
/// <returns>REturns the first row of the record set</returns>
public DataRecord SelectQueryOneRecord(string strSQL) {
SqlDataReader odbRead;
SqlCommand odbCommand;
DataRecord oRecord;
int iCtr;
string strName;
CheckConnection();
oRecord = new DataRecord();
odbCommand = new SqlCommand(strSQL, cdbConn);
odbCommand.CommandTimeout = cTimeoutSecs;
odbRead = odbCommand.ExecuteReader();
if (odbRead.HasRows == false) {
oRecord.AddValue("FoundInDB", false);
odbRead.Close();
throw new Exception("The SQL query given retrned 0 rows. The query was:\n" + strSQL);
}
odbRead.Read();
oRecord.AddValue("FoundInDB", true);
for (iCtr = 0; iCtr < odbRead.FieldCount; iCtr++) {
strName = odbRead.GetName(iCtr);
oRecord.AddValue(strName, odbRead[strName].ToString());
}
odbRead.Close();
return oRecord;
}
/// <summary>
/// Submits an SQL query that expects multiple records as a result
/// </summary>
/// <param name="strSQL">The select query</param>
/// <returns>Returns an enumerated list of DataRecords, each DataRecord is one row from the results</returns>
public IEnumerable<DataRecord> SelectQueryMultipleRecords(string strSQL) {
SqlDataReader odbRead;
SqlCommand odbCommand;
int iCtr;
string strName;
List<DataRecord> lRecords;
DataRecord oNewRecord;
CheckConnection();
lRecords = new List<DataRecord>();
odbCommand = new SqlCommand(strSQL, cdbConn);
odbCommand.CommandTimeout = cTimeoutSecs;
odbRead = odbCommand.ExecuteReader();
if (odbRead.HasRows == false) {
odbRead.Close();
throw new Exception("The SQL query given retrned 0 rows. The query was:\n" + strSQL);
}
while (odbRead.Read() == true) {
oNewRecord = new DataRecord();
oNewRecord.AddValue("FoundInDB", true);
for (iCtr = 0; iCtr < odbRead.FieldCount; iCtr++) {
strName = odbRead.GetName(iCtr);
oNewRecord.AddValue(strName, odbRead[strName].ToString());
}
lRecords.Add(oNewRecord);
}
odbRead.Close();
return lRecords;
}
/// <summary>
/// Compares all values in the data record against the values in another datarecord with known true values
/// </summary>
/// <param name="datTrueRecord">The known true record</param>
public void CompareValuesToTrueRecord(DataRecord datTrueRecord) {
String strSkippedKeys, strException;
strException = "";
strSkippedKeys = "";
foreach (string strKey in this) {
if (datTrueRecord.ContainsKey(strKey) == true) { //True record has this key, compare it
if (this.String(strKey).CompareTo(datTrueRecord.String(strKey)) != 0) { //Values don't match
strException = strException + "The values in Key '" + strKey + "' do not match. Test (local) value: '" + this.String(strKey) + "' True (external) value: '" + datTrueRecord.String(strKey) + "\n";
}
} else { //True record does not have this key report it
strSkippedKeys = strSkippedKeys + strKey + ", ";
}
}
if (strSkippedKeys.CompareTo("") != 0) { //skipped keys, trim trailing comma
strSkippedKeys = strSkippedKeys.Substring(0, strSkippedKeys.Length - 2);
strException = strException + "Skipped comparing some values, True data record (external) does not contain the following keys: " + strSkippedKeys;
}
if (strException.CompareTo("") != 0) { //Found discrepencies, throw an exception
throw new Exception(strException);
}
}
/// <summary>
/// Compares the data record against a known true record. Each key or index from each data record is compared to ensure it exists in the other.
/// </summary>
/// <param name="datTrueRecord">The known true record</param>
public void CompareKeysToTrueRecord(DataRecord datTrueRecord) {
string strLocalKeys, strExternalKeys, strException;
strException = "";
//Look through all local keys to see if external data record has them
strLocalKeys = "";
foreach (string strKey in this) {
if (datTrueRecord.ContainsKey(strKey) == false) { //External data record does not have this key
strLocalKeys = strLocalKeys + strKey + ", ";
}
}
if (strLocalKeys.CompareTo("") != 0) { //found keys, trim trailing comma
strLocalKeys = strLocalKeys.Substring(0, strLocalKeys.Length - 2);
strException = strException + "Test data record (local) contains the following keys which the True data record (external) does not have: " + strLocalKeys;
}
//Look through all external keys to see if local record has them
strExternalKeys = "";
foreach (string strKey in datTrueRecord) {
if (this.ContainsKey(strKey) == false) { //External data record has a key that isn't in local
strExternalKeys = strExternalKeys + strKey + ", ";
}
}
if (strExternalKeys.CompareTo("") != 0) { //found keys, trim trailing comma
strExternalKeys = strExternalKeys.Substring(0, strExternalKeys.Length - 2);
strException = strException + "True data record (external) contains the following keys which the Test data record (local) does not have: " + strLocalKeys;
}
if (strException.CompareTo("") != 0) { //Found discrepencies, throw an exception
throw new Exception(strException);
}
}
