Multithread server and communication with SQL

Question

Hi, I would like to ask you for help, since my approach was not good. On the server side, there are clients connected (each client is represented by my Client class). Each client needs to comunicate with DB behind the server, also there is a need of multiple connections at the same time.

I am considering to have SQLConnection and SQLReader instance in each client instance, that would surely work but I am not sure whether there is not any better way. Of course performance is everything what matters. Thanks!

EDIT: The usual traffic is about 5 requests/sec (Opening connection is so often is laggy).Maximum number of users is not higher than 100.

Answer 1

I typically let the data-fetching methods create connections as needed. I am yet to come across a situation where this is not fast enough. Don't forget that (at least with SqlConnection) there is some internal connection pooling to increase performance. This approach will effectively take away the need to maintain data objects per client and so on.

Simple example:

private static IEnumerable<SomeType> GetSomeData(string someInput)
{
    IEnumerable<SomeType> result = null;
    using (SqlConnection conn = new SqlConnection(GetConnectionString()))
    {
        // put whatever code is needed to populate the result here
    }
    return result;
}

You could also look into using Linq-to-SQL, which may result in very simple and performant data access code.

Answer 2

I am thinking of something like a connection pool. We just create a limited number of Connection objects and share them among the clients. Since every client is not using the connection all the times I think this is possible.