public static List <Anomaly> detectAnomaliesWithData()
{
List <Anomaly> anomalies = new List <Anomaly>();
List <ForeignKey> foreignKeys = ForeignKey.getAllForeignKeys();
List <PrimaryKey> primaryKeys = PrimaryKey.getAllPrimaryKeys();
for (int i = 0; i < foreignKeys.Count; i++)
{
SqlCommand sqlCommand;
SqlDataReader dataReader;
SqlConnection connection = MyDB.getConnection();
try
{
connection.Open();
string sql = "DBCC CHECKCONSTRAINTS('" + foreignKeys[i].name + "')";
sqlCommand = new SqlCommand(sql, connection);
dataReader = sqlCommand.ExecuteReader();
string output = "ForeignKey: ";
bool exist_data = false;
while (dataReader.Read())
{
int number_fields = dataReader.FieldCount;
for (int j = 0; j < number_fields; j++)
{
output = output + dataReader.GetValue(j);
exist_data = true;
if (j != number_fields - 1)
{
output = output + "|";
}
}
output = output + "\n";
}
if (exist_data)
{
anomalies.Add(new Anomaly(foreignKeys[i].parent_object_id, Anomaly.TYPE_WITH_DATA, output));
// Console.WriteLine("\nANOMALLY DBCC");
// Console.WriteLine(output);
}
sqlCommand.Dispose();
dataReader.Close();
}
catch (Exception error)
{
Console.WriteLine("Error: " + error.StackTrace);
}
finally
{
connection.Close();
}
}
for (int i = 0; i < primaryKeys.Count; i++)
{
SqlCommand sqlCommand;
SqlDataReader dataReader;
SqlConnection connection = MyDB.getConnection();
try
{
connection.Open();
string sql = "DBCC CHECKCONSTRAINTS('" + primaryKeys[i].name + "')";
sqlCommand = new SqlCommand(sql, connection);
dataReader = sqlCommand.ExecuteReader();
string output = "PrimaryKey: ";
bool exist_data = false;
while (dataReader.Read())
{
int number_fields = dataReader.FieldCount;
for (int j = 0; j < number_fields; j++)
{
output = output + dataReader.GetValue(j);
exist_data = true;
if (j != number_fields - 1)
{
output = output + "|";
}
}
output = output + "\n";
}
if (exist_data)
{
anomalies.Add(new Anomaly(primaryKeys[i].parent_object_id, Anomaly.TYPE_WITH_DATA, output));
// Console.WriteLine("\nANOMALLY DBCC");
// Console.WriteLine(output);
}
sqlCommand.Dispose();
dataReader.Close();
}
catch (Exception error)
{
Console.WriteLine("Error: " + error.StackTrace);
}
finally
{
connection.Close();
}
}
//TODO: agregar checkconstraints
return(anomalies);
}