public static void Add(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, TableOptions tableOptions)
{
DataTable dataTable = new DataTable();
if (serverType == "MSSQL Server")
{
//If server type is MSSQL Server fill dataTable with column names and data types using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog={databaseDialog.options[whichDatabase]}");
SqlDataAdapter adapter = new SqlDataAdapter($"Select Column_Name, Data_Type From INFORMATION_SCHEMA.COLUMNS where Table_Name = '{tableDialog.options[whichTable]}' ", connection);
connection.Open();
adapter.Fill(dataTable);
adapter.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL fill dataTable with column names and data types using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database={databaseDialog.options[whichDatabase]}");
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter($"Select Column_Name, Data_Type From INFORMATION_SCHEMA.COLUMNS where Table_Name = '{tableDialog.options[whichTable]}' ", connection);
connection.Open();
adapter.Fill(dataTable);
adapter.Dispose();
connection.Close();
connection.Dispose();
}
List <string> columnList = new List <string>();
string columns = "";
string values = "";
for (int i = 0; i < dataTable.Rows.Count; i++)
{
Console.Clear();
DataRow row = dataTable.Rows[i];
//Create string containing column names
columns += (string)row.ItemArray[0] + ", ";
//Ask user for new value in column
Console.WriteLine("Add " + (string)row.ItemArray[0] + " value:");
//Create string containing values
if ((string)row.ItemArray[1] == "varchar" || (string)row.ItemArray[1] == "character varying")
{
values += "'" + Console.ReadLine() + "', ";
}
else if ((string)row.ItemArray[1] == "int" || (string)row.ItemArray[1] == "integer")
{
values += Console.ReadLine() + ", ";
}
}
//Remove last commas from columns and values string
columns = columns.Substring(0, columns.Length - 2);
values = values.Substring(0, values.Length - 2);
dataTable.Dispose();
if (serverType == "MSSQL Server")
{
//If server type is MSSQL Server add new row using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog={databaseDialog.options[whichDatabase]}");
SqlCommand command = new SqlCommand($"INSERT INTO [{tableDialog.options[whichTable]}]({columns}) VALUES({values})", connection);
connection.Open();
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e);
Console.WriteLine();
Console.ReadLine();
}
command.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL add new row using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database={databaseDialog.options[whichDatabase]}");
NpgsqlCommand command = new NpgsqlCommand($"INSERT INTO {tableDialog.options[whichTable]} VALUES ({values})", connection);
connection.Open();
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e);
Console.WriteLine();
Console.ReadLine();
}
command.Dispose();
connection.Close();
connection.Dispose();
}
//At the end return to the tableOptions
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
}
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 static void Print(DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, TableOptions tableOptions, DataTable dataTable, DataTable columns)
{
Console.Clear();
//Print the table frames and content
ConsoleColor color = ConsoleColor.DarkGray;
Console.ForegroundColor = color;
Console.Write("|");
Console.ForegroundColor = ConsoleColor.White;
//Print first table name
Console.WriteLine($"{columns.Rows[0].ItemArray[0]}");
Console.SetCursorPosition(0, 1);
Console.ForegroundColor = color;
Console.WriteLine(new string('=', (columns.Rows.Count) * 20));
//Print every table name
for (int i = 1; i < columns.Rows.Count; i++)
{
Console.SetCursorPosition(i * 20, 0);
Console.Write("|");
Console.ForegroundColor = ConsoleColor.White;
Console.Write($"{columns.Rows[i].ItemArray[0]}");
Console.ForegroundColor = color;
Console.SetCursorPosition(i * 20, 1);
Console.Write("|");
}
Console.SetCursorPosition(columns.Rows.Count * 20, 0);
Console.Write("|");
Console.SetCursorPosition(columns.Rows.Count * 20, 1);
Console.Write("|");
Console.SetCursorPosition(0, dataTable.Rows.Count + 2);
Console.WriteLine(new string('=', (columns.Rows.Count) * 20));
Console.SetCursorPosition(0, 1);
Console.Write("|");
Console.SetCursorPosition(0, dataTable.Rows.Count + 2);
Console.Write("|");
Console.SetCursorPosition(0, 2);
//Print every item in the table
for (int i = 0; i < dataTable.Rows.Count; i++)
{
Console.Write("|");
DataRow row = dataTable.Rows[i];
for (int j = 1; j <= row.ItemArray.Length; j++)
{
var item = row.ItemArray[j - 1];
Console.ForegroundColor = ConsoleColor.White;
Console.Write(item);
if (item.ToString().Length == 0)
{
Console.Write("*Empty cell*");
}
Console.ForegroundColor = color;
Console.SetCursorPosition(20 * j, i + 2);
Console.Write("|");
Console.SetCursorPosition(20 * j, i + 3);
Console.Write("|");
Console.SetCursorPosition(20 * j + 1, i + 2);
}
Console.SetCursorPosition(0, i + 3);
}
Console.ForegroundColor = ConsoleColor.White;
dataTable.Dispose();
columns.Dispose();
//Wait for user to press any key
Console.ReadKey();
Console.WriteLine("Returning...");
//Return to the tableOptions
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
}
public static void Print(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, TableOptions tableOptions)
{
DataTable dataTable = new DataTable();
DataTable columns = new DataTable();
if (serverType == "MSSQL Server")
{
//If server type is MSSQL Server create new SqlConnection and SqlDataAdapter, and get all column names from table
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog={databaseDialog.options[whichDatabase]}");
SqlDataAdapter columnNames = new SqlDataAdapter($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableDialog.options[whichTable]}'", connection);
connection.Open();
//Fill DataTable with column names
columnNames.Fill(columns);
columnNames.Dispose();
//Get all data from the table
SqlDataAdapter adapter = new SqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]}", connection);
//Fill DataTable with all data
adapter.Fill(dataTable);
adapter.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL create new NpgsqlConnection and NpgsqlDataAdapter, and get all column names from table
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database={databaseDialog.options[whichDatabase]}");
NpgsqlDataAdapter columnNames = new NpgsqlDataAdapter($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableDialog.options[whichTable]}'", connection);
connection.Open();
//Fill DataTable with column names
columnNames.Fill(columns);
columnNames.Dispose();
//Get all data from the table
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]}", connection);
//Fill DataTable with all data
adapter.Fill(dataTable);
adapter.Dispose();
connection.Close();
connection.Dispose();
}
Console.Clear();
//Print the table frames and content
ConsoleColor color = ConsoleColor.DarkGray;
Console.ForegroundColor = color;
Console.Write("|");
Console.ForegroundColor = ConsoleColor.White;
//Print first table name
Console.WriteLine($"{columns.Rows[0].ItemArray[0]}");
Console.SetCursorPosition(0, 1);
Console.ForegroundColor = color;
Console.WriteLine(new string('=', (columns.Rows.Count) * 20));
//Print every table name
for (int i = 1; i < columns.Rows.Count; i++)
{
Console.SetCursorPosition(i * 20, 0);
Console.Write("|");
Console.ForegroundColor = ConsoleColor.White;
Console.Write($"{columns.Rows[i].ItemArray[0]}");
Console.ForegroundColor = color;
Console.SetCursorPosition(i * 20, 1);
Console.Write("|");
}
Console.SetCursorPosition(columns.Rows.Count * 20, 0);
Console.Write("|");
Console.SetCursorPosition(columns.Rows.Count * 20, 1);
Console.Write("|");
Console.SetCursorPosition(0, dataTable.Rows.Count + 2);
Console.WriteLine(new string('=', (columns.Rows.Count) * 20));
Console.SetCursorPosition(0, 1);
Console.Write("|");
Console.SetCursorPosition(0, dataTable.Rows.Count + 2);
Console.Write("|");
Console.SetCursorPosition(0, 2);
//Print every item in the table
for (int i = 0; i < dataTable.Rows.Count; i++)
{
Console.Write("|");
DataRow row = dataTable.Rows[i];
for (int j = 1; j <= row.ItemArray.Length; j++)
{
var item = row.ItemArray[j - 1];
Console.ForegroundColor = ConsoleColor.White;
Console.Write(item);
if (item.ToString().Length == 0)
{
Console.Write("*Empty cell*");
}
Console.ForegroundColor = color;
Console.SetCursorPosition(20 * j, i + 2);
Console.Write("|");
Console.SetCursorPosition(20 * j, i + 3);
Console.Write("|");
Console.SetCursorPosition(20 * j + 1, i + 2);
}
Console.SetCursorPosition(0, i + 3);
}
Console.ForegroundColor = ConsoleColor.White;
dataTable.Dispose();
columns.Dispose();
//Wait for user to press any key
Console.ReadKey();
Console.WriteLine("Returning...");
//Return to the tableOptions
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
}
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 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 static void Search(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, TableOptions tableOptions)
{
//Ask user for what to search in the table
Console.Clear();
Console.WriteLine("Enter what are you searching for:");
string searched = Console.ReadLine();
DataTable dataTable = new DataTable();
DataTable columns = new DataTable();
if (serverType == "MSSQL Server")
{
//If server type is MSSQL Server, search columns info using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog = {databaseDialog.options[whichDatabase]};");
SqlDataAdapter adapter = new SqlDataAdapter($"SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableDialog.options[whichTable]}';", connection);
connection.Open();
adapter.Fill(columns);
//Create string for SELECT conditions
string columnString = StringCreator(columns, searched);
//Search for content in table
adapter = new SqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]} WHERE {columnString};", connection);
adapter.Fill(dataTable);
connection.Close();
connection.Dispose();
//Print table
TablePrinter.Print(databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions, dataTable, columns);
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL, search columns info using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database = {databaseDialog.options[whichDatabase]};");
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter($"SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableDialog.options[whichTable]}';", connection);
connection.Open();
adapter.Fill(columns);
//Create string for SELECT conditions
string columnString = StringCreator(columns, searched);
//Search for content in table
adapter = new NpgsqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]} WHERE {columnString};", connection);
adapter.Fill(dataTable);
connection.Close();
connection.Dispose();
//Print tab;e
TablePrinter.Print(databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions, dataTable, columns);
}
}