private void TakingRequests()
{
while (true)
{
TransferUtilities.ClientStates currentMode;
TransferUtilities.RecieveBytes(out currentMode, _clientStream);
switch (currentMode)
{
case TransferUtilities.ClientStates.ConnectionToServer:
ConnectionToServer();
break;
case TransferUtilities.ClientStates.SelectTable:
SelectTable();
break;
case TransferUtilities.ClientStates.Query:
ExecuteQuery();
break;
case TransferUtilities.ClientStates.Edit:
EditData();
break;
case TransferUtilities.ClientStates.DisconectFromServer:
return;
default:
Console.WriteLine("What is this invalid state?");
break;
}
}
}
public void GetInfoFromServer(DataGridView dataGridView, ListBox lstBox)
{
TransferUtilities.SendBytes(TransferUtilities.ClientStates.ConnectionToServer, _serverStream);
DataSet dataSet;
TransferUtilities.RecieveBytes(out dataSet, _serverStream);
dataGridView.DataSource = dataSet?.Tables[0]?.DefaultView;
List <string> list;
TransferUtilities.RecieveBytes(out list, _serverStream);
if (list != null)
{
foreach (var item in list)
{
if (item != "STUDENT_MARKS" && item != "sysdiagrams")
{
lstBox.Items.Add(item);
}
}
}
lstBox.SelectedIndex = lstBox.FindString("PERSON");
}
public void ExecuteQuery(DataGridView dataGridView, string query)
{
TransferUtilities.SendBytes(TransferUtilities.ClientStates.Query, _serverStream);
TransferUtilities.SendBytes(query, _serverStream);
DataSet dataSet;
TransferUtilities.RecieveBytes(out dataSet, _serverStream);
dataGridView.DataSource = dataSet?.Tables[0]?.DefaultView;
}
public void DisconnectFromServer()
{
if (!_activeConnection)
{
return;
}
_activeConnection = false;
TransferUtilities.SendBytes(TransferUtilities.ClientStates.DisconectFromServer, _serverStream);
_serverStream.Close();
_server.Close();
}
public void SelectTable(DataGridView dataGridView, ListBox lstBox)
{
dataGridView.DataSource = null;
TransferUtilities.SendBytes(TransferUtilities.ClientStates.SelectTable, _serverStream);
var tableName = lstBox.SelectedItem.ToString();
TransferUtilities.SendBytes(tableName, _serverStream);
DataSet dataSet;
TransferUtilities.RecieveBytes(out dataSet, _serverStream);
dataGridView.DataSource = dataSet?.Tables[tableName]?.DefaultView;
}
private void SelectTable()
{
string tableName;
TransferUtilities.RecieveBytes(out tableName, _clientStream);
var dataSet = GetDataFromTable(tableName);
if (dataSet == null)
{
return;
}
Console.WriteLine("Sending dataSet bytes.");
TransferUtilities.SendBytes(dataSet, _clientStream);
Console.WriteLine("Finish.");
}
public void UpdateTable(DataGridView dataGridView, ListBox lstBox, string newValue)
{
if (dataGridView.CurrentCell == null)
{
return;
}
TransferUtilities.SendBytes(TransferUtilities.ClientStates.Edit, _serverStream);
var editTable = new Dictionary <string, string>
{
{ "tableName", lstBox.SelectedItem.ToString() },
{ "ID", (dataGridView.CurrentCell.RowIndex + 1).ToString() },
{ "columnName", dataGridView.Columns[dataGridView.CurrentCell.ColumnIndex].Name },
{ "newValue", newValue }
};
TransferUtilities.SendBytes(editTable, _serverStream);
SelectTable(dataGridView, lstBox);
}
public void ListenClients()
{
while (true)
{
Console.WriteLine("Looking for clients.");
_client = _server.AcceptTcpClient();
Console.WriteLine($"Client was found! IP - {_client.Client.RemoteEndPoint}"); // RemoteEndPoint OR LocalEndPoint
_clientStream = _client.GetStream();
string dbInfo;
TransferUtilities.RecieveBytes(out dbInfo, _clientStream);
var connectionToDbInfo = dbInfo.Split('/');
// [0] = login, [1] = password, [3] = db name
_connection = new SqlConnection(
$@"Server = localhost;
User Id = {connectionToDbInfo[0]};
Password = {connectionToDbInfo[1]};
Network Library = DBMSSOCN;
Initial Catalog = {connectionToDbInfo[2]}"
);
try
{
_connection.Open();
Console.WriteLine("Connected to database.");
}
catch (Exception ex)
{
Console.WriteLine("Debug Mode\n" + ex.Message + "\n");
}
TakingRequests();
_clientStream.Close();
_client.Close();
}
}
private void ExecuteQuery()
{
var dataSet = new DataSet();
string query;
TransferUtilities.RecieveBytes(out query, _clientStream);
try
{
var adapter = new SqlDataAdapter(query, _connection);
Console.WriteLine("Execute Query.");
adapter.FillSchema(dataSet, SchemaType.Source);
adapter.Fill(dataSet);
Console.WriteLine("Executed successfuly!");
TransferUtilities.SendBytes(dataSet, _clientStream);
}
catch (Exception ex)
{
Console.WriteLine("Debug Mode\n" + ex.Message + "\n");
}
}
private void ConnectionToServer()
{
var dataSet = GetDataFromTable();
if (dataSet == null)
{
return;
}
Console.WriteLine("Sending dataSet bytes.");
TransferUtilities.SendBytes(dataSet, _clientStream);
Console.WriteLine("Finish.");
var list = GetAllTableNames();
if (list == null)
{
return;
}
Console.WriteLine("Sending list bytes.");
TransferUtilities.SendBytes(list, _clientStream);
Console.WriteLine("Finish.");
}
private void EditData()
{
Dictionary <string, string> editTable;
TransferUtilities.RecieveBytes(out editTable, _clientStream);
var updateQuery = $@"UPDATE {editTable["tableName"]}
SET {editTable["columnName"]} = N'{editTable["newValue"]}'
WHERE {editTable["tableName"]}_ID = {editTable["ID"]}";
try
{
Console.WriteLine($"Editing table {editTable["tableName"]}.");
using (var command = new SqlCommand(updateQuery, _connection))
{
command.ExecuteNonQuery();
}
Console.WriteLine("Update successful!");
}
catch (Exception ex)
{
Console.WriteLine("Debug Mode\n" + ex.Message + "\n");
}
}
public void ConnectToDatabase(string login, string password, string database)
{
var dbInfo = $@"{login}/{password}/{database}";
TransferUtilities.SendBytes(dbInfo, _serverStream);
}
