Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Managing SQL Server Connections

What is the the best practice for SQL connections?

Currently I am using the following:

using (SqlConnection sqlConn = new SqlConnection(CONNECTIONSTRING))
{
    sqlConn.Open();
    // DB CODE GOES HERE
}

I have read that this is a very effective way of doing SQL connections. By default the SQL pooling is active, so how I understand it is that when the using code ends the SqlConnection object is closed and disposed but the actual connection to the DB is put in the SQL connection pool. Am i wrong about this?

like image 966
Neale Avatar asked May 26 '09 17:05

Neale


People also ask

How do I monitor SQL Server connections?

In SQL Server Management Studio, right click on Server, choose "Activity Monitor" from context menu -or- use keyboard shortcut Ctrl + Alt + A .

How many connections SQL Server can handle?

By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance.

What is Connection Manager in SQL Server?

Connection managers are used by the data flow components that extract and load data in different types of data stores, and by the log providers that write logs to a server, SQL Server table, or file.


1 Answers

That's most of it. Some additional points to consider:

  • Where do you get your connection string? You don't want that hard-coded all over the place and you may need to secure it.
  • You often have other objects to create as well before your really use the connection (SqlCommand, SqlParameter, DataSet, SqlDataAdapter), and you want to wait as long as possible to open the connection. The full pattern needs to account for that.
  • You want to make sure your database access is forced into it's own data layer class or assembly. So a common thing to do is express this as a private function call:

.

private static string connectionString = "load from encrypted config file";
private SqlConnection getConnection()
{
    return new SqlConnection(connectionString);
}

And then write your sample like this:

using (SqlConnection sqlConn = getConnection())
{
    // create command and add parameters

    // open the connection
    sqlConn.Open();

   // run the command
}

That sample can only exist in your data access class. An alternative is to mark it internal and spread the data layer over an entire assembly. The main thing is that a clean separation of your database code is strictly enforced.

A real implementation might look like this:

public IEnumerable<IDataRecord> GetSomeData(string filter)
{
    string sql = "SELECT * FROM [SomeTable] WHERE [SomeColumn] LIKE @Filter + '%'";

    using (SqlConnection cn = getConnection())
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        cmd.Parameters.Add("@Filter", SqlDbType.NVarChar, 255).Value = filter;
        cn.Open();

        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                yield return (IDataRecord)rdr;
            }
        }
    }
}

Notice that I was also able to "stack" the creation of the cn and cmd objects, and thus reduce nesting and only create one scope block.

Finally, a word of caution about using the yield return code in this specific sample. If you call the method and don't complete your DataBinding or other use right away it could hold the connection open for a long time. An example of this is using it to set a data source in the Load event of an ASP.NET page. Since the actual data binding event won't occur until later you could hold the connection open much longer than needed.

like image 90
Joel Coehoorn Avatar answered Nov 16 '22 23:11

Joel Coehoorn