public static void Add(string connectionString, string serverType, DatabaseDialog databaseDialog, TableDialog tableDialog, TableOptions tableOptions)
{
//Ask user for the name of new database
Console.Clear();
Console.WriteLine("Enter new database name:");
string databaseName = Console.ReadLine();
if (serverType == "MSSQL Server")
{
//If server type is MSSQL Server, create new database using SqlClient
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand($"CREATE DATABASE {databaseName};", connection);
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL, create new database using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString);
NpgsqlCommand command = new NpgsqlCommand($"CREATE DATABASE {databaseName};", connection);
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
//At the end return to the databaseDialog
databaseDialog.Start(tableDialog, tableOptions);
}
public void Start(DatabaseDialog databaseDialog, int whichDatabase, TableOptions tableOptions)
{
//At start update list of options
UpdateList(databaseDialog, whichDatabase);
//Start the Control() method and set returned value as whichTable
int whichTable = Control();
if (whichTable == options.Count - 3)
{
//If --Add new Table-- is selected, start adding new table
TableAdder.Add(ConnectionString, ServerType, databaseDialog, whichDatabase, this, tableOptions);
}
else if (whichTable == options.Count - 2)
{
//If --Delete Database-- is selected start deleting currently selected database
DatabaseDeleter.Delete(ConnectionString, ServerType, databaseDialog, whichDatabase, this, tableOptions);
}
else if (whichTable == options.Count - 1)
{
//If --Return-- is selected, return to databaseDialog
databaseDialog.Start(this, tableOptions);
}
else
{
//If a table is selected, open list of options for this table
tableOptions.Start(databaseDialog, whichDatabase, this, whichTable);
}
}
public static void Delete(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, TableOptions tableOptions)
{
//Ask user for confirmation to delete database
Console.Clear();
Console.WriteLine("Are you sure, you want to delete this database? [Type Y for Yes, or N for No, N is default]");
string input = Console.ReadLine();
if (input == "Y")
{
}
else if (input == "N" || input == "")
{
//If user inputs N, or leaves blank space return to the tableDialog
tableDialog.Start(databaseDialog, whichDatabase, tableOptions);
}
else
{
//If the user inputs invalid data, start all over.
DatabaseDeleter.Delete(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, tableOptions);
}
if (serverType == "MSSQL Server")
{
//If server type is MSSQL Server create SqlConnection and SqlCommand
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
//Terminate all connections to the database, then delete the database
SqlCommand command = new SqlCommand($"Use master alter database {databaseDialog.options[whichDatabase]} set single_user with rollback immediate Drop database {databaseDialog.options[whichDatabase]}", connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL Server create NpgsqlConnection and NpgsqlCommand
NpgsqlConnection connection = new NpgsqlConnection(connectionString);
connection.Open();
//Terminate all connections to the database, then delete the database.
NpgsqlCommand command = new NpgsqlCommand($"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '{databaseDialog.options[whichDatabase]}'; Drop database {databaseDialog.options[whichDatabase]}", connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
//At the end return to databaseDialog,
databaseDialog.Start(tableDialog, tableOptions);
}