private static void Edit(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, DataTable dataTable, DataTable columns, int cellX, int cellY)
{
//Ask user fot new value
string set = "";
string columnString = "";
Console.Clear();
Console.WriteLine("Enter new value:");
string value = Console.ReadLine();
for (int i = 0; i < dataTable.Rows[0].ItemArray.Length; i++)
{
//Add to columnString "ColumnName = Value AND "
if ((string)columns.Rows[i].ItemArray[1] == "varchar" || (string)columns.Rows[i].ItemArray[1] == "character varying")
{
columnString += $"{columns.Rows[i].ItemArray[0]} = '{dataTable.Rows[cellY].ItemArray[i]}' AND ";
}
else if ((string)columns.Rows[i].ItemArray[1] == "integer")
{
columnString += $"{columns.Rows[i].ItemArray[0]} = {dataTable.Rows[cellY].ItemArray[i]} AND ";
}
//Add to set string "ColumnName = Value"
if (i == cellX)
{
if ((string)columns.Rows[i].ItemArray[1] == "varchar" || (string)columns.Rows[i].ItemArray[1] == "character varying")
{
set += $"{columns.Rows[i].ItemArray[0]} = '{value}'";
}
else if ((string)columns.Rows[i].ItemArray[1] == "integer" || (string)columns.Rows[i].ItemArray[1] == "int")
{
set += $"{columns.Rows[i].ItemArray[0]} = {value}";
}
}
}
//Delete last " AND " from column string
columnString = columnString.Substring(0, columnString.Length - 5);
if (serverType == "MSSQL Server")
{
//If server type is MSSQL Server update the table using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog = {databaseDialog.options[whichDatabase]}");
connection.Open();
SqlCommand command = new SqlCommand($"Update {tableDialog.options[whichTable]} set {set} where {columnString}", connection);
command.ExecuteNonQuery();
connection.Close();
command.Dispose();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL update the table using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database = {databaseDialog.options[whichDatabase]}");
connection.Open();
columnString = columnString.Substring(0, columnString.Length - 5);
NpgsqlCommand command = new NpgsqlCommand($"Update {tableDialog.options[whichTable]} set {set} where {columnString}", connection);
command.ExecuteNonQuery();
connection.Close();
command.Dispose();
connection.Dispose();
}
}
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 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 Add(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, TableOptions tableOptions)
{
Console.Clear();
//Ask user for column name and data type
Console.WriteLine("Enter column name:");
string columnName = Console.ReadLine();
Console.WriteLine("Enter column data type:");
string dataType = Console.ReadLine();
if (serverType == "MSSQL Server")
{
//If server type is MSSQL Server add new column using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog = {databaseDialog.options[whichDatabase]}");
SqlCommand command = new SqlCommand($"Alter table {tableDialog.options[whichTable]} add {columnName} {dataType};", connection);
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL add new column using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database = {databaseDialog.options[whichDatabase]}");
NpgsqlCommand command = new NpgsqlCommand($"Alter table {tableDialog.options[whichTable]} add {columnName} {dataType};", connection);
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
}
private static void Delete(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, TableOptions tableOptions, DataTable dataTable, DataTable columns, int whichRow)
{
Console.Clear();
Console.WriteLine("Are you sure, you want to delete this row? [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
{
RowDeleter.Delete(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions, dataTable, columns, whichRow);
}
string deleteString = "";
for (int i = 0; i < columns.Rows.Count; i++)
{
deleteString += $"{columns.Rows[i].ItemArray[0]} = ";
if ((string)columns.Rows[i].ItemArray[1] == "varchar" || (string)columns.Rows[i].ItemArray[1] == "character varying")
{
deleteString += $"'{dataTable.Rows[whichRow].ItemArray[i]}'AND ";
}
else if ((string)columns.Rows[i].ItemArray[1] == "int" || (string)columns.Rows[i].ItemArray[1] == "integer")
{
deleteString += $"{dataTable.Rows[whichRow].ItemArray[i]} AND ";
}
}
deleteString = deleteString.Substring(0, deleteString.Length - 5);
if (serverType == "MSSQL Server")
{
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog = {databaseDialog.options[whichDatabase]};");
SqlCommand command = new SqlCommand($"Delete from {tableDialog.options[whichTable]} where {deleteString};", connection);
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database = {databaseDialog.options[whichDatabase]};");
NpgsqlCommand command = new NpgsqlCommand($"Delete from {tableDialog.options[whichTable]} where {deleteString};", connection);
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
}
private void UpdateList(DatabaseDialog databaseDialog, int whichDatabase)
{
options.Clear();
DataTable dataTable = new DataTable();
if (ServerType == "MSSQL Server")
{
//If the server type is MSSQL Server, get all table names using SqlClient
SqlConnection connection = new SqlConnection(ConnectionString + $"Initial Catalog={databaseDialog.options[whichDatabase]};");
SqlDataAdapter adapter = new SqlDataAdapter("Select table_name From information_schema.tables;", connection);
connection.Open();
//Pass all the data to DataTable
adapter.Fill(dataTable);
//Add every table name in DataTable to the option list
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow row = dataTable.Rows[i];
Add((string)row.ItemArray[0]);
}
connection.Close();
adapter.Dispose();
dataTable.Dispose();
//Add the rest of the options to the list
Add("--Add new Table--");
Add("--Delete Database--");
Add("--Return--");
}
else if (ServerType == "PostgreSQL")
{
//If the server type is PostgreSQL, get all table names using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(ConnectionString + $"Database={databaseDialog.options[whichDatabase]};");
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("Select table_name From information_schema.tables where table_schema = 'public';", connection);
connection.Open();
//Pass all the data to DataTable
adapter.Fill(dataTable);
//Add every table name in DataTable to the option list
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow row = dataTable.Rows[i];
Add((string)row.ItemArray[0]);
}
connection.Close();
adapter.Dispose();
dataTable.Dispose();
//Add the rest of the options to the list
Add("--Add new Table--");
Add("--Delete Database--");
Add("--Return--");
}
}
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);
}
private static void Delete(string connectionString, string serverType, DatabaseDialog databaseDialog, int whichDatabase, TableDialog tableDialog, int whichTable, TableOptions tableOptions, DataTable columns, int whichColumn)
{
Console.Clear();
//Ask user for confirmation to delete
Console.WriteLine("Are you sure, you want to delete this column? [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 passes N or blank space, return to table options
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
}
else
{
//If user passes incorrect input, ask again
ColumnDeleter.Delete(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions, columns, whichColumn);
}
if (serverType == "MSSQL Server")
{
//If serverType is MSSQL Server, delete column using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog = {databaseDialog.options[whichDatabase]}");
SqlCommand command = new SqlCommand($"Alter table {tableDialog.options[whichTable]} drop column {columns.Rows[whichColumn].ItemArray[0]};", connection);
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
else if (serverType == "PostgreSQL")
{
//If serverType is PostgreSQL, delete column using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database = {databaseDialog.options[whichDatabase]}");
NpgsqlCommand command = new NpgsqlCommand($"Alter table {tableDialog.options[whichTable]} drop column {columns.Rows[whichColumn].ItemArray[0]};", connection);
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
}
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);
}
}
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 SelectCell(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 fill one DataTable with all the table content and the other one with column names, and data types using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog={databaseDialog.options[whichDatabase]}");
SqlDataAdapter adapter = new SqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]}", connection);
connection.Open();
adapter.Fill(dataTable);
SqlDataAdapter columnNames = new SqlDataAdapter($"Select column_name, data_type from information_schema.columns where table_name = '{tableDialog.options[whichTable]}'", connection);
columnNames.Fill(columns);
connection.Close();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL fill one DataTable with all the table content and the other one with column names, and data types using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database={databaseDialog.options[whichDatabase]}");
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]}", connection);
connection.Open();
adapter.Fill(dataTable);
NpgsqlDataAdapter columnNames = new NpgsqlDataAdapter($"Select column_name, data_type from information_schema.columns where table_name = '{tableDialog.options[whichTable]}'", connection);
columnNames.Fill(columns);
connection.Close();
}
//Set currently selected cell coordinates as 0,0
int cellY = 0;
int cellX = 0;
bool inside = true;
//Print table with highlighting currently selected cell
TablePrinter.PrintCellSelector(dataTable, columns, cellX, cellY);
while (inside)
{
//Wait for user input
if (Console.KeyAvailable)
{
ConsoleKeyInfo input = Console.ReadKey();
switch (input.Key)
{
case ConsoleKey.LeftArrow:
//If user input is left arrow, and currently selected cell's X coordinate is bigger than 0
//decrease X coordinate by one, and print table again
if (cellX > 0)
{
cellX--;
TablePrinter.PrintCellSelector(dataTable, columns, cellX, cellY);
}
break;
case ConsoleKey.RightArrow:
//If user input is Right arrow, and currently selected cell's X coordinate is smaller than amount of columns -1,
//increase X coordinate by one, and print table again
if (cellX < dataTable.Rows[0].ItemArray.Length - 1)
{
cellX++;
TablePrinter.PrintCellSelector(dataTable, columns, cellX, cellY);
}
break;
case ConsoleKey.UpArrow:
//If user input is Up arrow, and currently selected cell's Y coordinate is bigger than 0
//decrease Y coordinate by one, and print table again
if (cellY > 0)
{
cellY--;
TablePrinter.PrintCellSelector(dataTable, columns, cellX, cellY);
}
break;
case ConsoleKey.DownArrow:
//If user input is Down arrow, and currently selected cell's Y coordinate is smaller than amount of rows -1,
//increase Y coordinate by one, and print table again
if (cellY < dataTable.Rows.Count - 1)
{
cellY++;
TablePrinter.PrintCellSelector(dataTable, columns, cellX, cellY);
}
break;
case ConsoleKey.Escape:
//If user input is Escape, return to tableOptions
Console.Clear();
Console.WriteLine("Returning...");
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
break;
case ConsoleKey.Enter:
//If user input is enter get out of the loop
inside = false;
break;
}
}
}
//Start editing cell
Edit(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, dataTable, columns, cellX, cellY);
//Return to selecting the cell
SelectCell(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions);
}
public static void SelectColumn(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 fill one DataTable with all table contents and second with column names and data types using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog={databaseDialog.options[whichDatabase]}");
SqlDataAdapter adapter = new SqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]}", connection);
connection.Open();
adapter.Fill(dataTable);
SqlDataAdapter columnNames = new SqlDataAdapter($"Select column_name, data_type from information_schema.columns where table_name = '{tableDialog.options[whichTable]}'", connection);
columnNames.Fill(columns);
connection.Close();
}
else if (serverType == "PostgreSQL")
{
//If server type is PostgreSQL fill one DataTable with all table contents and second with column names and data types using Npgsql
NpgsqlConnection connection = new NpgsqlConnection(connectionString + $"Database={databaseDialog.options[whichDatabase]}");
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]}", connection);
connection.Open();
adapter.Fill(dataTable);
NpgsqlDataAdapter columnNames = new NpgsqlDataAdapter($"Select column_name, data_type from information_schema.columns where table_name = '{tableDialog.options[whichTable]}'", connection);
columnNames.Fill(columns);
connection.Close();
}
//Set selected columns at 0
int whichColumn = 0;
bool inside = true;
//Print table with highlighted column 0
TablePrinter.PrintColumnSelector(dataTable, columns, whichColumn);
while (inside)
{
//Wait for user input
if (Console.KeyAvailable)
{
ConsoleKeyInfo input = Console.ReadKey();
switch (input.Key)
{
case ConsoleKey.LeftArrow:
//If user input is Left Arrow, and selected column is bigger than 0
//decrease whichColumn by 1 and print table with highlighted current column
if (whichColumn > 0)
{
whichColumn--;
}
TablePrinter.PrintColumnSelector(dataTable, columns, whichColumn);
break;
case ConsoleKey.RightArrow:
//If user input is Right Arrow, and selected column is smaller than column amount - 1
//increase whichColumn by 1 and print table with highlighted current column
if (whichColumn < dataTable.Rows[0].ItemArray.Length - 1)
{
whichColumn++;
}
TablePrinter.PrintColumnSelector(dataTable, columns, whichColumn);
break;
case ConsoleKey.Escape:
//If user input is Escape return to tableOptions
Console.Clear();
Console.WriteLine("Returning...");
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
break;
case ConsoleKey.Enter:
//If user input is Enter leave loop
inside = false;
break;
}
}
}
//Start deleting currently selected column
ColumnDeleter.Delete(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions, columns, whichColumn);
//Return to column selecting
ColumnDeleter.SelectColumn(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions);
}
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 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 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);
}
public static void SelectRow(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 fill one DataTable with all table contents and second with column names and data types using SqlClient
SqlConnection connection = new SqlConnection(connectionString + $"Initial Catalog={databaseDialog.options[whichDatabase]}");
SqlDataAdapter adapter = new SqlDataAdapter($"SELECT * FROM {tableDialog.options[whichTable]}", connection);
connection.Open();
adapter.Fill(dataTable);
SqlDataAdapter columnNames = new SqlDataAdapter($"Select column_name, data_type from information_schema.columns where table_name = '{tableDialog.options[whichTable]}'", connection);
columnNames.Fill(columns);
connection.Close();
}
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 * FROM {tableDialog.options[whichTable]}", connection);
connection.Open();
adapter.Fill(dataTable);
NpgsqlDataAdapter columnNames = new NpgsqlDataAdapter($"Select column_name, data_type from information_schema.columns where table_name = '{tableDialog.options[whichTable]}'", connection);
columnNames.Fill(columns);
connection.Close();
}
//Set currently selected row to 0
int whichRow = 0;
bool inside = true;
//Print table with row 0 highlighted
TablePrinter.PrintRowSelector(dataTable, columns, whichRow);
while (inside)
{
if (Console.KeyAvailable)
{
ConsoleKeyInfo input = Console.ReadKey();
switch (input.Key)
{
case ConsoleKey.UpArrow:
if (whichRow > 0)
{
whichRow--;
}
TablePrinter.PrintRowSelector(dataTable, columns, whichRow);
break;
case ConsoleKey.DownArrow:
if (whichRow < dataTable.Rows.Count - 1)
{
whichRow++;
}
TablePrinter.PrintRowSelector(dataTable, columns, whichRow);
break;
case ConsoleKey.Escape:
Console.Clear();
Console.WriteLine("Returning...");
tableOptions.Start(databaseDialog, whichDatabase, tableDialog, whichTable);
break;
case ConsoleKey.Enter:
inside = false;
break;
}
}
}
RowDeleter.Delete(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions, dataTable, columns, whichRow);
RowDeleter.SelectRow(connectionString, serverType, databaseDialog, whichDatabase, tableDialog, whichTable, tableOptions);
}
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);
}
