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); }