public void InitializeConnection()
{
try
{
connector = new SQLConnector(Dashboard.SqlInfoData.Data_Source, Dashboard.SqlInfoData.Initial_Catalog, Dashboard.SqlInfoData.User_Id, Dashboard.SqlInfoData.Password);
connector.InitializeConnection();
connector2 = new SQLConnector(Dashboard.SqlInfoData.Data_Source, Dashboard.SqlInfoData.Initial_Catalog, Dashboard.SqlInfoData.User_Id, Dashboard.SqlInfoData.Password);
connector2.InitializeConnection();
Logger.Info("Successfully Loaded Both SQL Connectors.");
} catch (Exception e) {
Logger.Error(e, "One or both of the SQL Connectors failed to load.");
throw;
}
}
public static List <String> GetDatabaseList()
{
SQLConnector conn = new SQLConnector("");
conn.InitializeConnection();
conn.Open();
var temp = new List <String>();
var reader = conn.ReadResults(conn.CreateCommand("SELECT name FROM master.sys.databases where name NOT IN ('master','model','msdb','tempdb')"));
while (reader.Read())
{
temp.Add(reader[0].ToString());
}
conn.Close();
return(temp);
}
public void RetrieveTables_DatabaseTablesAreNotOrdered_TablesIsOrdered()
{
// Arrange
CreateTestDatabase();
var database = new Database("CustomerDatabase");
List <string> unsortedTables = new List <string>();
SQLConnector conn = new SQLConnector("");
string sql = $"USE [{database.Name}] SELECT Name FROM sys.tables WHERE is_ms_shipped = 0";
// Act
database.RetrieveTables();
conn.InitializeConnection();
conn.Open();
var reader = conn.ReadResults(conn.CreateCommand(sql));
while (reader.Read())
{
unsortedTables.Add(reader[0].ToString());
}
reader.Close();
conn.Close();
unsortedTables.Sort();
// Assert
Console.WriteLine($"Unsorted List Contains: {unsortedTables.Count} Items");
Console.WriteLine("Items:");
for (int i = 0; i < unsortedTables.Count; i++)
{
Console.WriteLine($"Item # {i}: {unsortedTables[i]}");
}
Console.WriteLine($"Sorted List Contains: {database.Tables.Count} Items");
for (int i = 0; i < database.Tables.Count; i++)
{
Console.WriteLine($"Item # {i}: {database.Tables[i]}");
}
Assert.IsTrue(database.Tables.SequenceEqual(unsortedTables));
// Cleanup
DeleteTestDatabase();
}
public void RetrieveTables()
{
Tables = new List <String>();
SQLConnector conn = new SQLConnector("");
conn.InitializeConnection();
conn.Open();
string sql = $"USE [{Name}] SELECT Name FROM sys.tables WHERE is_ms_shipped = 0 order by Name";
Console.WriteLine($"Getting Tables Using: {sql}");
var reader = conn.ReadResults(conn.CreateCommand(sql));
while (reader.Read())
{
Tables.Add(reader[0].ToString());
}
conn.Close();
}
private void DeleteTestDatabase()
{
SQLConnector conn;
conn = new SQLConnector("");
conn.InitializeConnection();
string sql;
try // Delete previously created database.
{
sql = File.ReadAllText(@"..\..\DBScripts\DropTestDatabase.sql");
conn.Open();
conn.ReadResults(conn.CreateCommand(sql));
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private SQLConnector connectToSQL()
{
SQLConnector conn = null;
try
{
conn = new SQLConnector(Dashboard.SqlInfoData.Data_Source, Dashboard.SqlInfoData.Initial_Catalog, Dashboard.SqlInfoData.User_Id, Dashboard.SqlInfoData.Password);
conn.InitializeConnection();
}
catch (Exception ex)
{
if (conn != null && conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
Logger.Error(ex, $"An error occurred while attempting to estbalish SQL Connection. " +
$"Data Source: {Dashboard.SqlInfoData.Data_Source}, " +
$"Initial Catalog: {Dashboard.SqlInfoData.Initial_Catalog}, " +
$"User ID: {Dashboard.SqlInfoData.User_Id}, " +
$"Password: {Dashboard.SqlInfoData.Password}");
}
return(conn);
}
private void CreateTestDatabase()
{
SQLConnector conn;
conn = new SQLConnector("");
conn.InitializeConnection();
string sql;
try // Attempt to create the desired database.
{
sql = File.ReadAllText(@"..\..\DBScripts\CreateTestDatabase.sql");
conn.Open();
conn.ReadResults(conn.CreateCommand(sql)).Close();
sql = File.ReadAllText(@"..\..\DBScripts\AddTable.sql");
conn.ReadResults(conn.CreateCommand(sql)).Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
