Hello!
I would like to ask for tips on how to handle database concurrency in a client server application in c#. I am using SQL Server 2008 r2 as my server.
I have minimum to zero ideas on how to handle such and would really appreciate any help that you guys can give.
In order for my client and server to communicate, I have created three classes for it.
I have my first class which I call the ClientConnectionManager, this class manages all incoming connections between the client and the server, it is the one that adds, and removes clients within its own hashmap of users.
public class ClientConnectionManager
{
private static RichTextBox serverLogsContainer;
public static Hashtable users = new Hashtable(50);
public static Hashtable connections = new Hashtable(50);
private TcpClient tcpClient;
private Thread thrListener;
private TcpListener tcpListener;
private bool isServerRunning = false;
public ClientConnectionManager(RichTextBox console)
{
ClientConnectionManager.serverLogsContainer = console;
}
public void StartServer()
{
this.thrListener = new Thread(new ThreadStart(WaitConnection));
this.thrListener.Start();
ClientConnectionManager.serverLogsContainer.AppendText("Server Started");
}
private void OpenServerPort()
{
this.tcpListener = new TcpListener(IPAddress.Any, 1986);
this.tcpListener.Start();
this.isServerRunning = true;
}
private void WaitConnection()
{
while (isServerRunning)
{
tcpClient = tcpListener.AcceptTcpClient();
ClientConnection tcpConnection = new ClientConnection(tcpClient);
for (; ;)
{
ConnectionThreadMessages returnValue = tcpConnection.WaitForClientRequests();
if (returnValue == ConnectionThreadMessages.Disconnect)
{
RemoveUser(tcpClient); //Remove user from client list
break;
}
else if (returnValue == ConnectionThreadMessages.AddUser)
{
AddNewUser(tcpClient, tcpConnection.newClientName); //Add user to client list
}
}
}
}
public static void AddNewUser(TcpClient client, string userName)
{
ClientConnectionManager.users.Add(userName, client);
ClientConnectionManager.connections.Add(client, userName);
ClientConnectionManager.UpdateLog("\nConnection accepted from " + connections[client]);
}
public static void RemoveUser(TcpClient tcpUser)
{
if (connections[tcpUser] != null)
{
UpdateLog("\n" + connections[tcpUser] + " was disconnected.");
users.Remove(connections[tcpUser]);
connections.Remove(tcpUser);
}
}
public static void UpdateLog(string message)
{
ClientConnectionManager.serverLogsContainer.Invoke(new MethodInvoker(delegate { serverLogsContainer.AppendText(message); }));
}
}
And then I have a class called ClientServerCommunicationMessage which I use to relay information between the client and the server. If the object is to be sent by the client, it would contain, the type of query the client would like to make, the storedprocedure it would like to call and the parameters if there are any. After the server receives this object the server sends a reply using another instance of the same type that contains an acknowledgement that whatever query the client has to make has been approved, the object also contains a dataset in which the queried data is placed.
Here is the code for that:
[Serializable]
public class ClientServerCommunicationMessage
{
private string queryFlag;
private string clientIdentity;
private string password;
private ClientResponses clientResponses;
private ServerResponse serverResponse;
private List<Parameter> listOfQueryParameters;
private DataSet queryResults;
public string QueryFlag
{
get { return queryFlag; }
set { queryFlag = value; }
}
public string ClientIdentity
{
get { return clientIdentity; }
set { clientIdentity = value; }
}
public string Password
{
get { return password; }
set { password = value; }
}
public ClientResponses ClientResponses
{
get { return clientResponses; }
set { clientResponses = value; }
}
public ServerResponse ServerResponse
{
get { return serverResponse; }
set { serverResponse = value; }
}
public List<Parameter> ListOfQueryParameters
{
get { return listOfQueryParameters; }
set
{
for (int i = 0; i < value.Count; i++)
{
this.listOfQueryParameters.Add(value[i]);
}
}
}
public DataSet QueryResults
{
get { return queryResults; }
set { queryResults = value; }
}
public ClientServerCommunicationMessage() { }
}
And for the last class I have something that I call a ClientConnection. This class is the one that intercepts requests from the user and acts accordingly according to the request. This is the class I am having most trouble with.
I am having troubles with thinking up of a way to:
1) make sure there is a delay between the conversation of the server and the client i.e., when the user sends his message, there should be a delay wherein the clientwould have to wait for the reply of the server; I don't know how to do that.
2) How to handle concurrency in the database when I have I am updating the database in the same time as someone is querying from it.
3) How to make the client or the server wait for a set of consecutive message objects between them i.e., I have to make sure that when a user logs in he is existing in the database, so that would be one message object, I would then reply to the client with another object, and the client reads that object's certain member that indicates if whether he is in the database or not, after receiving the client then sends his password within another object, in which the server processes it and returns another object indicating that the login was a success.
Here is my incomplete and messy messy code for the ClientConnection
public class ClientConnection
{
public string newClientName;
private TcpClient clientTCPConnection;
private NetworkStream messageStreamReader = null;
private ClientServerCommunicationMessage connectionMessage = new ClientServerCommunicationMessage();
private ClientResponses _clientResponses;
private ServerResponse _serverResponse;
private DatabaseDataAccessor dataAccessorObject;
byte[] messageStreamBuffer;
public ClientConnection(TcpClient client)
{
this.clientTCPConnection = client;
}
public ConnectionThreadMessages WaitForClientRequests()
{
while ((GetClientRequestType(clientTCPConnection) != ClientResponses.DisconnectClient))
{
this.dataAccessorObject = new DatabaseDataAccessor();
if (connectionMessage.ClientResponses == ClientResponses.Operations_RequestForConnection)
{
ClientServerCommunicationMessage serverReplyMessage = new ClientServerCommunicationMessage();
ConnectionThreadMessages returnValue = ConnectionThreadMessages.KeepConnected;
if ((bool)dataAccessorObject.ExecuteScalar(connectionMessage.ListOfQueryParameters,
EnumValueUtil.GetStringValueOfEnumValue(StoredProcedures.Operations_CheckIfKServeEmployeeExists)))
{
}
else
{
}
serverReplyMessage.ServerResponse = ServerResponse.Server_ConnectionApproved;
messageStreamReader = clientTCPConnection.GetStream();
messageStreamBuffer = CommunicationMessageConverter.ObjectToByteArray(serverReplyMessage);
messageStreamReader.Write(messageStreamBuffer, 0, messageStreamBuffer.Length);
this.newClientName = connectionMessage.ClientIdentity;
return returnValue;
}
else if (connectionMessage.ClientResponses == ClientResponses.AccountsManagement_RequestForConnection)
{
//same as above
}
//else if handle sofia login request
else if (connectionMessage.ClientResponses == ClientResponses.AccountsManagement_RequestForQuery)
{
ClientConnectionManager.UpdateLog("\nNew " +
connectionMessage.ClientResponses.ToString() +
" requsted by " +
ClientConnectionManager.connections[clientTCPConnection]);
ClientServerCommunicationMessage serverReplyMessage = new ClientServerCommunicationMessage();
serverReplyMessage.ServerResponse = ServerResponse.Server_QueryApproved; //SET SERVER RESPONSE
//GET QUERY FLAG -- JOB OF DBMANAGER
//CHECK QUERY LIST IF QUERY FLAG EXISTS -- JOB OF DBMANAGER
//CREATE ENUM FOR QUERY LIST FOR EACH MODULE
//IF QUERY EXISTS GET PARAMETERS
//PASS EVERYTHING TO DB MANAGER
//GET DATASET FROM THE MANAGER
//STORE EVERYTHING IN NEW TRANSACTION OBJECT
//WRITE TRANSACTION OBJECT ON THE WIRE
//serverReplyMessage.QueryFlag = "query";
//messageStreamReader = clientTCPConnection.GetStream();
//messageStreamBuffer = TCPConverter.ObjectToByteArray(serverReplyMessage);
//messageStreamReader.Write(messageStreamBuffer, 0, messageStreamBuffer.Length);
return ConnectionThreadMessages.KeepConnected;
}
else if (connectionMessage.ClientResponses == ClientResponses.Operations_RequestForQuery)
{
ClientConnectionManager.UpdateLog("\nNew " +
connectionMessage.ClientResponses.ToString() +
" requsted by " +
ClientConnectionManager.connections[clientTCPConnection]);
ClientServerCommunicationMessage serverReplyMessage = new ClientServerCommunicationMessage();
serverReplyMessage.ServerResponse = ServerResponse.Server_QueryApproved; //SET SERVER RESPONSE
//check if query exists
serverReplyMessage.QueryResults =
this.dataAccessorObject.ExecuteQueryStatement(connectionMessage.ListOfQueryParameters, connectionMessage.QueryFlag);
messageStreamReader = clientTCPConnection.GetStream();
messageStreamBuffer = CommunicationMessageConverter.ObjectToByteArray(serverReplyMessage);
messageStreamReader.Write(messageStreamBuffer, 0, messageStreamBuffer.Length);
return ConnectionThreadMessages.KeepConnected;
}
this.dataAccessorObject = null;
}
return ConnectionThreadMessages.Disconnect;
}
private ClientResponses GetClientRequestType(TcpClient client)
{
NetworkStream clientRequestsStreamReader = client.GetStream();
messageStreamBuffer = new byte[512000];
clientRequestsStreamReader.Read(messageStreamBuffer, 0, messageStreamBuffer.Length);
connectionMessage = (ClientServerCommunicationMessage)CommunicationMessageConverter.ByteArrayToObject(messageStreamBuffer);
return connectionMessage.ClientResponses;
}
private void CloseConnection()
{
//tcpClient.Close();
//streamReader.Close();
//streamWriter.Close();
}
}
There is only one instance for the ClientConnectionManager, and the ClientConnection is threaded within the ClientConnectionManager.
SORRY for the lengthy post, I am really at a loss here.
THANK YOU FOR READING!