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);
}
public void Start(TableDialog tableDialog, TableOptions tableOptions)
{
//Update list of databases when started
UpdateList();
//Start the Control() method, and set the returned value as whichDatabase
int whichDatabase = Control();
if (whichDatabase == options.Count - 1)
{
//If --Create new Database-- is selected, start adding new database
DatabaseAdder.Add(ConnectionString, ServerType, this, tableDialog, tableOptions);
}
else
{
//If database is selected, start tableDialog, passing which database was selected
tableDialog.Start(this, whichDatabase, tableOptions);
}
}
public static void Delete(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, TableOptions tableOptions)
{
Console.Clear();
Console.WriteLine("Are you sure, you want to delete this table? [Type Y for Yes, or N for No, N is default]");
string input = Console.ReadLine();
if (input == "Y")
{
}
else if (input == "N" || input == "")
{
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
}
else
{
TableDeleter.Delete(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions);
}
if (serverType == "MSSQL Server")
{
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog ={databaseDialog.options[whichDatabase]};");
connection.Open();
SqlCommand command = new SqlCommand($"Drop table {tableDialog.options[whichTable]}", connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database ={databaseDialog.options[whichDatabase]};");
connection.Open();
NpgsqlCommand command = new NpgsqlCommand($"Drop table {tableDialog.options[whichTable]}", connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
tableDialog.Start(databaseDialog, whichDatabase, tableOptions);
}
public static void Add(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, TableOptions tableOptions)
{
//Ask user for new table name, and amount of columns inside.
Console.Clear();
Console.WriteLine("Enter table name:");
string tableName = Console.ReadLine();
Console.Clear();
Console.WriteLine("Enter amount of columns in the table:");
int howManyColumns = 0;
try
{
howManyColumns = Int32.Parse(Console.ReadLine());
}
catch
{
Console.WriteLine("Invalid amount of columns");
TableAdder.Add(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, tableOptions);
}
string columnString = "";
if (serverType == "MSSQL")
{
//If server type is MSSQL Server, create string of column names, and their data types, putting column names in brackets.
for (int i = 1; i <= howManyColumns; i++)
{
Console.Clear();
Console.WriteLine($"Column {i} name:");
string newColumn = Console.ReadLine();
columnString += $"[{newColumn}] ";
Console.WriteLine($"Column {i} datatype:");
columnString += $"{Console.ReadLine()}, ";
}
//Remove last space and comma from column string
columnString = columnString.Substring(0, columnString.Length - 2);
//Create new SqlConnection adding Initial Catalog value to the connection string
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog={databaseDialog.options[whichDatabase]};");
connection.Open();
//Create new table using gathered data
SqlCommand command = new SqlCommand($"CREATE Table {tableName}({columnString})", connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL, create string of column names, and their data types.
for (int i = 1; i <= howManyColumns; i++)
{
Console.Clear();
Console.WriteLine($"Column {i} name:");
string newColumn = Console.ReadLine();
columnString += $"{newColumn} ";
Console.WriteLine($"Column {i} datatype:");
columnString += $"{Console.ReadLine()}, ";
}
//Remove last space and comma from the column string.
columnString = columnString.Substring(0, columnString.Length - 2);
//Create new NpgsqlConnection adding Database value to the connection string
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database={databaseDialog.options[whichDatabase]};");
connection.Open();
//Create new table using gathered data
NpgsqlCommand command = new NpgsqlCommand($"CREATE Table {tableName} ({columnString})", connection);
Console.WriteLine($"CREATE Table {tableName} ({columnString})", connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
//At the end return to the tableDialog
tableDialog.Start(databaseDialog, whichDatabase, tableOptions);
}
