Access to SQL DB in multithread server app

Question

In my server application I want to use DB (SQL Server) but I am quite unsure of the best method. There are clients whose requests comes to threadpool and so their processing is async. Every request usually needs to read or write to DB, so I was thinking about static method which would create connection, execute the query and return the result. I'm only afraid whether opening and closing connection is not too slow and whether some connection limit could not be reached? Is this good approach?

Answer 1

I have had exactly the same problem like you. Had huge app that i started making multithreaded. Benefit over having one connection open and being reused is that you can ask DB multiple times for data as new connection is spawned on request (no need to wait for other threads to finish getting data), and if for example you loose connection to sql (and it can happen when network goes down for a second or two) you will have to always check if connection is open before submitting query anyway.

This is my code for getting Database rows in MS SQL but other stuff should be done exactly the same way. Keep in mind that the sqlConnectOneTime(string varSqlConnectionDetails) has a flaw of returning null when there's no connection so it needs some modifications for your needs or the query will fail if sql fails to establish connection. You just need to add proper code handling there :-) Hope it will be useful for you :-)

    public const string sqlDataConnectionDetails = "Data Source=SQLSERVER\\SQLEXPRESS;Initial Cata....";

    public static string sqlGetDatabaseRows(string varDefinedConnection) {
        string varRows = "";
        const string preparedCommand = @"
                    SELECT SUM(row_count) AS 'Rows'
                    FROM sys.dm_db_partition_stats
                    WHERE index_id IN (0,1)
                    AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0;";
        using (var varConnection = Locale.sqlConnectOneTime(varDefinedConnection))
        using (var sqlQuery = new SqlCommand(preparedCommand, varConnection))
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            while (sqlQueryResult.Read()) {
                varRows = sqlQueryResult["Rows"].ToString();
            }
        return varRows;
    }


    public static SqlConnection sqlConnectOneTime(string varSqlConnectionDetails) {
        SqlConnection sqlConnection = new SqlConnection(varSqlConnectionDetails);
        try {
            sqlConnection.Open();
        } catch (Exception e) {
            MessageBox.Show("Błąd połączenia z serwerem SQL." + Environment.NewLine + Environment.NewLine + "Błąd: " + Environment.NewLine + e, "Błąd połączenia");
        }
        if (sqlConnection.State == ConnectionState.Open) {
            return sqlConnection;
        }
        return null;
    }

Summary:

Defined one global variable with ConnectionDetails of your SQL Server

One global method to make connection (you need to handle the null in there)

Usage of using to dispose connection, sql query and everything when the method of reading/writing/updating is done.

Answer 2

The one thing that you haven't told us, that would be useful for giving you an answer that's appropriate for you is what level of load you're expecting your server application to be under.

For pretty much any answer to the above question though, the answer would be that you shouldn't worry about it. ADO.net/Sql Server provides connection pooling which removes some of the overhead of creating connections from each "var c = new SqlConnection(connectionString)" call.

Answer 3

Surprised that no one mentioned connection pooling. If you think you are going to have a large number of requests, why not just setup a pool with a min pool size set to say 25 (arbitrary number here, do not shoot) and max pool size set to say 200. This will decrease the number of connection attempts and make sure that if you are not leaking connection handles (something that you should take explicit care to not let happen), you will always have a connection waiting for you. Reference article on connection pooling: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx Another side note, why the need to have the connection string in the code? Set it in the web.config or app.config for the sake of maintainability. I had to "fix" code that did such things and I always swore copiously at the programmer responsible for such things.

Answer 4

IMHO the best is to rely on the ADO.NET connection pooling mechanism and don't try to handle database connections manually. Write your data access methods like this:

public void SomeMethod()
{
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        connection.Open();
        command.CommandText = "SELECT Field1 FROM Table1";
        using (var reader = command.ExecuteReader())
        {
            while(reader.Read())
            {
                // do something with the results
            }
        }
    }
}

Then you can call this method from wherever you like, make it static, call it from threads whatever. Remember that calling Dispose on the connection won't actually close it. It will return it to the connection pool so that it can be reused.