public static List <ForeignKey> getAllForeignKeys()
{
List <ForeignKey> foreignKeys = new List <ForeignKey>();
SqlConnection connection = MyDB.getConnection();
SqlCommand sqlCommand;
SqlDataReader dataReader;
try
{
connection.Open();
sqlCommand = new SqlCommand("SELECT [name], [object_id] ,[schema_id] ,[parent_object_id] ,[referenced_object_id] ," +
"[key_index_id] ,[is_disabled] ,[is_not_trusted] ,[delete_referential_action] ," +
"[delete_referential_action_desc] ,[update_referential_action] ,[update_referential_action_desc] ," +
"[is_system_named] FROM [sys].[foreign_keys]", connection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
// Console.WriteLine("LEYENDO " + dataReader.FieldCount);
int index = 0;
string name = dataReader.GetValue(index++).ToString();
long object_id = Int64.Parse(dataReader.GetValue(index++).ToString());
int schema_id = Int32.Parse(dataReader.GetValue(index++).ToString());
long parent_object_id = Int64.Parse(dataReader.GetValue(index++).ToString());
long referenced_object_id = Int64.Parse(dataReader.GetValue(index++).ToString());
int key_index_id = Int32.Parse(dataReader.GetValue(index++).ToString());
bool is_disabled = Boolean.Parse(dataReader.GetValue(index++).ToString());
bool is_not_trusted = Boolean.Parse(dataReader.GetValue(index++).ToString());
int delete_referential_action = Int32.Parse(dataReader.GetValue(index++).ToString());
string delete_referential_action_desc = dataReader.GetValue(index++).ToString();
int update_referential_action = Int32.Parse(dataReader.GetValue(index++).ToString());
string update_referential_action_desc = dataReader.GetValue(index++).ToString();
bool is_system_named = Boolean.Parse(dataReader.GetValue(index++).ToString());
foreignKeys.Add(new ForeignKey(name, object_id, schema_id, parent_object_id, referenced_object_id,
key_index_id, is_disabled, is_not_trusted, delete_referential_action, delete_referential_action_desc,
update_referential_action, update_referential_action_desc, is_system_named));
}
sqlCommand.Dispose();
dataReader.Close();
}
catch (Exception error)
{
Console.WriteLine("Error: " + error.StackTrace);
}
finally
{
connection.Close();
}
return(foreignKeys);
}
public static List <Trigger> getAllTriggersFromObject(long parent_id)
{
List <Trigger> triggers = new List <Trigger>();
SqlConnection connection = MyDB.getConnection();
SqlCommand sqlCommand;
SqlDataReader dataReader;
try
{
connection.Open();
sqlCommand = new SqlCommand("SELECT [name] ,[object_id] ,[parent_class] ,[parent_class_desc] ," +
"[parent_id] ,[type] ,[type_desc] ,[is_ms_shipped] ,[is_disabled] ,[is_not_for_replication] ," +
"[is_instead_of_trigger] FROM [sys].[triggers] WHERE parent_id='" + parent_id + "'", connection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
// Console.WriteLine("LEYENDO " + dataReader.FieldCount);
int index = 0;
string name = dataReader.GetValue(index++).ToString();
long object_id = Int64.Parse(dataReader.GetValue(index++).ToString());
int parent_class = Int32.Parse(dataReader.GetValue(index++).ToString());
string parent_class_des = dataReader.GetValue(index++).ToString();
long queried_parent_id = Int64.Parse(dataReader.GetValue(index++).ToString());
string type = dataReader.GetValue(index++).ToString();
string type_desc = dataReader.GetValue(index++).ToString();
bool is_ms_shipped = Boolean.Parse(dataReader.GetValue(index++).ToString());
bool is_disabled = Boolean.Parse(dataReader.GetValue(index++).ToString());
bool is_not_for_replication = Boolean.Parse(dataReader.GetValue(index++).ToString());
bool is_instead_of_trigger = Boolean.Parse(dataReader.GetValue(index++).ToString());
triggers.Add(new Trigger(name, object_id, parent_class, parent_class_des,
queried_parent_id, type, type_desc, is_ms_shipped, is_disabled,
is_not_for_replication, is_instead_of_trigger));
}
sqlCommand.Dispose();
dataReader.Close();
}
catch (Exception error)
{
Console.WriteLine("Error: " + error.StackTrace);
}
finally
{
connection.Close();
}
return(triggers);
}
public static void selectAllAndWrite(string sql, string full_file_dir, string header = "")
{
SqlConnection connection = MyDB.getConnection();
SqlCommand sqlCommand;
SqlDataReader dataReader;
try
{
connection.Open();
String output = header;
sqlCommand = new SqlCommand(sql, connection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
int number_fields = dataReader.FieldCount;
for (int i = 0; i < number_fields; i++)
{
output = output + dataReader.GetValue(i);
if (i != number_fields - 1)
{
output = output + "|";
}
}
output = output + "\n";
}
MyFileManager.writeTXT(output, full_file_dir);
sqlCommand.Dispose();
dataReader.Close();
}
catch (Exception error)
{
Console.WriteLine("Error: " + error.Message);
}
finally
{
connection.Close();
}
}
public static List <PrimaryKey> getAllPrimaryKeys()
{
List <PrimaryKey> primaryKeys = new List <PrimaryKey>();
SqlConnection connection = MyDB.getConnection();
SqlCommand sqlCommand;
SqlDataReader dataReader;
try
{
connection.Open();
sqlCommand = new SqlCommand("SELECT name, object_id, schema_id, parent_object_id, type FROM [sys].[key_constraints]", connection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
// Console.WriteLine("LEYENDO " + dataReader.FieldCount);
int index = 0;
string name = dataReader.GetValue(index++).ToString();
long object_id = Int64.Parse(dataReader.GetValue(index++).ToString());
int schema_id = Int32.Parse(dataReader.GetValue(index++).ToString());
long parent_object_id = Int64.Parse(dataReader.GetValue(index++).ToString());
string type = dataReader.GetValue(index++).ToString();
primaryKeys.Add(new PrimaryKey(name, object_id, schema_id, parent_object_id, type));
}
sqlCommand.Dispose();
dataReader.Close();
}
catch (Exception error)
{
Console.WriteLine("Error: " + error.StackTrace);
}
finally
{
connection.Close();
}
return(primaryKeys);
}
public static List <CatalogDatabase> getAllCatalogDatabase()
{
List <CatalogDatabase> databases = new List <CatalogDatabase>();
SqlConnection connection = MyDB.getConnection();
SqlCommand sqlCommand;
SqlDataReader dataReader;
try
{
connection.Open();
sqlCommand = new SqlCommand("SELECT name, database_id, create_date FROM sys.databases", connection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
// Console.WriteLine("LEYENDO " + dataReader.FieldCount);
int index = 0;
string name = dataReader.GetValue(index++).ToString();
int database_id = Int32.Parse(dataReader.GetValue(index++).ToString());
string create_date = dataReader.GetValue(index++).ToString();
databases.Add(new CatalogDatabase(name, database_id, create_date));
}
sqlCommand.Dispose();
dataReader.Close();
}
catch (Exception error)
{
Console.WriteLine("Error: " + error.StackTrace);
}
finally
{
connection.Close();
}
return(databases);
}
public static List <TableDB> getAllTables()
{
List <TableDB> tables = new List <TableDB>();
SqlConnection connection = MyDB.getConnection();
SqlCommand sqlCommand;
SqlDataReader dataReader;
try
{
connection.Open();
sqlCommand = new SqlCommand("SELECT name, object_id, schema_id FROM [sys].[tables]", connection);
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
// Console.WriteLine("LEYENDO " + dataReader.FieldCount);
int index = 0;
string name = dataReader.GetValue(index++).ToString();
long object_id = Int64.Parse(dataReader.GetValue(index++).ToString());
int schema_id = Int32.Parse(dataReader.GetValue(index++).ToString());
tables.Add(new TableDB(name, object_id, schema_id));
}
sqlCommand.Dispose();
dataReader.Close();
}
catch (Exception error)
{
Console.WriteLine("Error: " + error.StackTrace);
}
finally
{
connection.Close();
}
return(tables);
}
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);
}