IF you have used Data Adapters, you may have noticed that every call to the Fill method will instantiate a new SQL Connection. Say that you loop through a list of items and call the Data Adapter’s Fill method, each call will create a new SQL process (and can be seen in the SQL Server Activity Monitor); these processes each have their own SQL Connection established with the SQL Server – even when the queries originate from a single application request thread or method.

Not only does this require additional server resources, it is also a quick way to empty out the Connection Pool available in your system. Think of it as a DDOS attack that will ultimately block all communications to your server.

Backgound info

Here’s another example.:

  • You have a Customer Repository class with a method called InsertCustomer()
  • You have a Product Repository class with a method called AddProductsToCustomer()
  • You have a CustomerProductManager class that overseas additional logic.
  • In the CustomProductManager class you have a method called InsertCustomerWithProducts(Customer c, List<Product> p)


note
: In practice you probably wouldn’t have such detached relationships between Customer and Product. This is just to explain the issue at hand.

        public void InsertCustomerWithProducts(Customer c, List p)
        {
            CustomerRepository customerRep = new CustomerRepository();
            ProductRepositry productRep = new productRepository();

            // [a] Insert the customer, and set the Customer ID
            customerRep.InsertCustomer(c);

            // [b] Associate the product list to the custom
            productRep.AddProductsToCustomer(c, p);
        }

There are two issues with the above code:

Firstly:

  • Line 7: Will create a new connection from within the Customer Repository to the SQL database.
  • Line 9: Will create a second connection from within the Product Repository.

Secondly:

  • If this code is executed without MSDTC enabled, a runtime error will be thrown
    (see this article on MSDTC).

Both these issues are resolved by using a SQL Connection Scope!

Defining the SQLConnectionScope Class

    public class SqlConnectionScope : IDisposable
    {
        [ThreadStatic]
        private static int level;
        [ThreadStatic]
        private static SqlConnection connection;

        public static SqlConnection CurrentConnection
        {
            get { return connection; }
        }

        public SqlConnectionScope(string connectionString)
        {
            if (level == 0)
            {
                connection = new SqlConnection(connectionString);
                connection.Open();
            }
            level++;

        }

        public void Dispose()
        {
            level--;
            if (level <= 0)
            {
                connection.Close();
                connection.Dispose();
                connection = null;
                level = 0;
            }
        }
 }

Each time when a new instance of SQLConnectionScope is to be created, the code checks to see if there is already an opened connection. If there is no new connection is created or opened – the code will simply increment the internal counter.

So if we had a loop that attempted to open 5 connections using this SQLConnectionScope class –

  • There will still only be one opened connection per thread.
  • The internal counter will be 5


Using the SQLConnectionScope Class

Here’s a code snippet on how you can use this SQLConnectionScope in the ProductRepository classs:

        public void GetAllProducts()
        {
            string MyConnnectionString = "xxxxxxxxxxxxxxxxxxxxxxx";
            SqlConnectionScope scope = new SqlConnectionScope(MyConnnectionString);

            SqlCommand command = new SqlCommand("SELECT * FROM Product", scope.Connection);
            command.ExecuteReader();
        }

In the Customer-Product example described above, if all functions in the CustomerRepository and ProductRepository has similar code statements, the first instance of SQLConnectionScope will open the connection. All the other calls will simply increment the internal counter and reuse the opened connection!

Going on step further – Cleaning the code up

Of course you can wrap this SQLConnectionScope within a context class – and use that context class to implicitly grab a SQL Connection (whether it be new or old – since the SQLConnectionScope will handle this automatically).

    public class MyDataContext : IDisposable
    {
        [ThreadStatic]
        private static int level;
        [ThreadStatic]
        private static SqlConnectionScope connectionScope;

        public MyDataContext()
        {
            System.Data.SqlClient.SqlConnection sqlConnection;

            if (level == 0)
            {
                sqlConnection = new System.Data.SqlClient.SqlConnection("xxxxxxxxxxxxxxxxxxx");
                connectionScope = new SqlConnectionScope(sqlConnection);
            }
            level++;
        }

        public MyDataContext(string connectionString)
        {
            System.Data.SqlClient.SqlConnection sqlConnection;

            if (level == 0)
            {
                sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
                connectionScope = new SqlConnectionScope(sqlConnection);

                context.Connection.Open();
            }
            level++;
        }

        public static SqlConnectionScope CurrentConnectionScope
        {
            get
            {
                return connectionScope;
            }
        }

        void IDisposable.Dispose()
        {
            level--;
            if (level <= 0)
            {
                context.Connection.Close();
                context.Dispose();
                context = null;
                level = 0;
            }
        }
    }

With the new MyDataContext Class, we can now recode the InsertCustomerWithProducts() method as so:

        public void InsertCustomerWithProducts(Customer c, List p)
        {
            CustomerRepository customerRep = new CustomerRepository();
            ProductRepositry productRep = new productRepository();

            using (var ctx = new MyDataContext())
            {
                // [a] Insert the customer, and set the Customer ID etc
                customerRep.InsertCustomer(c);
                // [b] Associate the product list to the custom
                productRep.AddProductsToCustomer(c, p);
            }
        }

And then update all the Repository methods to use the MyDataContext Class and the SQLConnectionScope nested within:

        public void GetAllProducts()
        {
            using (var ctx = new MyDataContext())
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Product"
                                                  , ctx.CurrentConnectionScope.Connection);
                command.ExecuteReader();
            }
        }

Conclusion

I hope all this made sense. It may be a bit daunting at first to have 2 nested classes just to handle SQL Connections.
But the end result will be worth the while.

Rather than have every method in your Repository classe(s) open up a new connection to the SQL Server. This SQLConnectionScope class allows the re-use of already opened connections; minimising the overall connection count and using finite server resources.

I hope this article helped!! 🙂