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