INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Database

Database Connection Management by jmeckley
Posted: 27 May 09 (Edited 31 Jul 09)

There is plenty of code out there which tells you how the ado.net components work, but there is not a lot of information about proper usage.
most code in the asp.net world looks like this... samples are documented from bad to worst

CODE --> example 1

public void Page_Load(object sender, EventArgs e)
{
   if(IsPostBack) return;
   var id = Request.QueryString["state"];
   using(var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["key"].ConnectionString))
   using(var command = new SqlCommand("select * from customer where state = @state", connection))
   {
      connection.Open();
      
      command.AddParameterWithValue("state", state);
      CustomerGridView.DataSource = command.ExecuteQuery();
      CustomerGridView.DataBind();
   }
}
The problem here is that as other elements need data we need to create a new connection and this uses additional resources in the connection pool.

CODE --> example 2

public void Page_Load(object sender, EventArgs e)
{
   if(IsPostBack) return;
   var id = Request.QueryString["state"];
   var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["key"].ConnectionString");
   connection.Open();
   
   var command = new SqlCommand("select * from customer where state = @state", connection)
   command.AddParameterWithValue("state", state);
   CustomerGridView.DataSource = command.ExecuteQuery();
   CustomerGridView.DataBind();
   
   connection.Close();
}
here we are not disposing of the command or connection. if an exception is thrown we could have database resource stuck in limbo. This can create preformance issues and leave database connections hanging.

CODE --> example 3

<asp:GridView DataSourceID="CustomerDataSource" ...>
</asp:GridView>
<asp:SqlDataSource ID="CustomerDataSource" ... />
Evil. You cannot fix what you cannot debug. There is also no room for any business logic that may be required. Who knows what voodoo is happening within this black box.

OK, so where does that leave us? What is a better way to manage our database connections? One idea is the concept of a Unit of Work. To fully implement the concept Uncle Bob outlines is a very complex process which most ORM/DAL frameworks have solved. if the linked content is too abstract here are some key features if a Unit of Work.
1. Atomic: the changes that take place within the unit of work cannot be accessed outside of the unit of work.
2. All changes are either committed or rolled back. all or nothing there are no partial commits.
3. The unit of work has boundaries which are usually defined by the work flow process.
4. One database connection (per database) is used to manage all the CRUD operations. (there is more than one database, oracle and ms sql, then you will need 2 connections.)

How can we apply this to asp.net development?
We would need to define boundaries for the unit of work. It would need to be atonomous from other contexts. We want a few database connections as possible. web development in general is based on the idea of a request and a response. Each request/response pair is atomic. it does not know about requests before or after it and does not have any notion of concurrent requests. The pair can also be seen as a work flow. each request can be sent with a specific purpose.
Select all customers
View a specific customer
Add a customer
Delete a customer

the request/response appears to be a good boundary for a unit of work. With every decision there are trade offs and compromises. Lets list some of them off.
Pros:
A single point to manage our database connection.
This can "simplify" the code base by reducing duplicated code.
Simpler code often means fewer bugs.

Cons:
We may not need a connection/transaction for every request. Isn't this a waist of resources?
What if a specific scenario requires something unique in the unit of work?
The code becomes "complex" because it is scattered all over the application.

I quoted simple and complex because this is subjective. the code may be simpler because it's a few lines of code and easy to read. however it could be complicated because understanding the 3 lines of code will also require and understanding of the system wide architecture.
There is the argument of creating/opening a connection/transaction when one is not needed. While this is true I think the alternative is worse. Therefore I'm willing to use the resources on a connection that may not be needed, but simplify managing the connection itself.
As for a specific use case; if the scenario is that unique I will manage it outside the scope of the standard UoW and treat this as an exception to the rule.

Considering these factors we will go forth with treating the request/response as our unit of work. Asp.net allows use to tie into the framework's pipeline at various points. Impelmenting our own IHttpModule we can create a connection when the request begins and commit it when the request ends. Once the connection is created we will need to store the connection in a location that is accessible throughout the scope of the request.

The HttpRequest object itself has a number of containers where we can store the connection to access stored objects. Application, Session, Items, etc. Since the UoW is defined by the request a collection which is defined by the same constraints is a perfect option. Application is shared by all requests. Session is unique between users, but not requests. Both of these are too large in scope for our UoW. Items, however, is a collection of all the "stuff" pertaining to the current request. HttpContext.Items it is.

We now have a hook to manage the connection and a place to store it. how can we determine committing or rolling back our changes? We can use exception management to determine if we need to commit or rollback a transaction. When an exception is allowed to bubble up to the top of the asp.net stack we can access the exception using Server.GetLastError(). We don't care what the error is at this point, only that there is one. An exception would indicate something was unexpected and we don't want to commit changes.

enough talk lets see some code!

CODE --> UnitOfWorkHttpModule

namespace Web.modules
{
    public class UnitOfWorkModule : IHttpModule
    {
        private HttpApplication application;

        public void Init(HttpApplication context)
        {
            application = context;
            application.BeginRequest += start_unit_of_work;
            application.EndRequest += complete_unit_of_work;
        }

        private void start_unit_of_work(object sender, EventArgs e)
        {
            var connection_settings = ConfigurationManager.ConnectionStrings["foo"];
            var connection = create_connection(connection_settings);
            connection.Open();
            put_connection_into_curent_context(connection_settings, connection);
            put_transaction_into_curent_context(connection_settings, connection.BeginTransaction());
        }

        private void complete_unit_of_work(object sender, EventArgs e)
        {
            var connection_settings = ConfigurationManager.ConnectionStrings["foo"];
            complete_transaction(connection_settings);
            dispose_of_connection(connection_settings);
        }

        private void dispose_of_connection(ConnectionStringSettings settings)
        {
            var connection = (IDbConnection) application.Context.Items[settings.Name];
            connection.Close();
            connection.Dispose();
        }

        private void complete_transaction(ConnectionStringSettings settings)
        {
            var transaction = (IDbTransaction) application.Context.Items[settings.Name + "transaction"];
            if (application.Server.GetLastError() == null)
            {
                transaction.Commit();
            }
            else
            {
                transaction.Rollback();
            }
            transaction.Dispose();
        }

        private void put_transaction_into_curent_context(ConnectionStringSettings settings, IDbTransaction transaction)
        {
            application.Context.Items[settings.Name + "transaction"] = transaction;
        }

        private void put_connection_into_curent_context(ConnectionStringSettings settings, IDbConnection connection)
        {
            application.Context.Items[settings.Name] = connection;
        }

        private static IDbConnection create_connection(ConnectionStringSettings settings)
        {
            var database_provider_factory = DbProviderFactories.GetFactory(settings.ProviderName);
            var connection = database_provider_factory.CreateConnection();
            connection.ConnectionString = settings.ConnectionString;

            return connection;
        }

        public void Dispose()
        {
        }
    }
}
here the http module creates event handlers for BeginRequest and EndRequest. You'll notice I give the handlers explicit names like "start_unit_of_work". I find this better defines what the member is doing. HttpModule_BeginRequest doesn't tell me anything about what is happening. I'm also using the DbProviderFactories. I am accessing the ado.net components by interfaces rather than concrete implementations. We can access any type of database: Sql, Oracle, MySql, SqLite, dBase (yes we all have legacy apps), etc without changing a single line of code.

We need to add 2 sections to the web.config. one for the module, another for the connection

CODE --> web.config

<?xml version="1.0"?>
<configuration>
    <connectionStrings>
        <add name="foo" connectionString="Data Source=.\SqlExpress;Initial Catalog=[database name];User Id=[id];Password=[pwd]" providerName="System.Data.SqlClient"/>
    </connectionStrings>
    <system.web>
        <httpModules>
            <add name="Unit Of Work Module" type="Web.modules.UnitOfWorkModule, Web"/>
        </httpModules>
    </system.web>
</configuration>
the http module requires a unique name and the full type which is written The.Full.Path.To.UnitOfWorkModule, NameOfAssembly. Note the connection strings name is the name referenced throughout the code. The providerName is what the DbProviderFactories.GetFactory() uses to create the appropriate command.

How can we access the connection in a simple and consitent manner? We will create a manager object to manage the connection. It's a very simple object with one member. Current. Current will give us access to the current database connection.

CODE --> HttpConnectionManagerAdapter

namespace Web.uow
{
    public interface IConnectionManager
    {
        IDatabaseConnection current(string key);
    }
    
    public class HttpConnectionManagerAdapter : IConnectionManager
    {
        public IDatabaseConnection current(string key)
        {
            var connection = (IDbConnection) HttpContext.Current.Items[key];
            var transaction = (IDbTransaction) HttpContext.Current.Items[key + "transaction"];
            return new DatabaseConnection(connection, transaction);
        }
    }
}
You'll notice we are wrapping the connection with our own object called DatabaseConnection. the ado.net objects has a very rich API. However most times we are only concerned with a few specific aspects of the actual database connection; timeout, create command, etc. By wrapping the ado.net object we can reduce the surface area of the object, making it simpler to use and understand. We will wrap the IDbConnection and IDbCommand with our own implementations to simplify usage. We will also provide our own, strongly typed parameters which will provide a light layer of type checks for us. This also makes our lightweight framework opinionated. This will drive the client code to use the objects in a very specific manner, which will provide us with consistent code throughout our application. below is the code for these objects

CODE

    public interface IDatabaseConnection
    {
        IDatabaseCommand create_command(string sql_statement);
    }
    
    public class DatabaseConnection : IDatabaseConnection
    {
        private readonly IDbConnection connection;
        private readonly IDbTransaction transaction;

        public DatabaseConnection(IDbConnection connection, IDbTransaction transaction)
        {
            this.connection = connection;
            this.transaction = transaction;
        }

        public IDatabaseCommand create_command(string sql_statement)
        {
            var command = connection.CreateCommand();
            command.CommandText = sql_statement;
            command.Transaction = transaction;
            return new DatabaseCommand(command);
        }
    }
DatabaseConnection has one purpose. creating commands. since every command requires a sql statement we require the sql when the command is created. the actual command is created by the underlying connection. attach the command to the transaction and wrap the actual command in our own implementation.

CODE

    public interface IDatabaseCommand : IDisposable
    {
        void add_parameter(DbParameter parameter);
        DataTable find_records();
        void preform_data_altering_query();
        T get_a_specific_value<T>();
    }
    
    public class DatabaseCommand : IDatabaseCommand
    {
        private readonly IDbCommand command;

        public DatabaseCommand(IDbCommand command)
        {
            this.command = command;
        }

        public void Dispose()
        {
            command.Dispose();
        }

        public void add_parameter(DbParameter parameter)
        {
            var param = command.CreateParameter();
            param.ParameterName = parameter.name;
            param.Value = parameter.value;
            command.Parameters.Add(param);
        }

        public DataTable find_records()
        {
            var table = new DataTable();
            table.Load(command.ExecuteReader());
            return table;
        }

        public void preform_data_altering_query()
        {
            command.ExecuteNonQuery();
        }

        public T get_a_specific_value<T>()
        {
            var result = command.ExecuteScalar();
            if (result == DBNull.Value) return default(T);
            return (T) result;
        }
    }
DatabaseCommand is responsible for executing the designated operation. This object will
1. translate our DbParameter object to an IDbParameter for the actual command.
2. Load the data from the database into a datatable. This disconnects our database from the rest of the code base.
3. Casts a scalar value from object to the specified type

CODE

    public abstract class DbParameter
    {
        public string name { get; private set; }
        public object value { get; private set; }
        public DbType type { get; private set; }

        protected DbParameter(string name, object value, DbType type)
        {
            this.name = name;
            this.value = value;
            this.type = type;
        }
    }

    public class StringParameter : DbParameter
    {
        public StringParameter(string name, string value)
            :base(name, value, DbType.String)
        {
        }
    }

    public class IntegerParameter : DbParameter
    {
        public IntegerParameter(string name, int value)
            : base(name, value, DbType.Int32)
        {
        }
    }

    public  class LongParameter : DbParameter
    {
        public LongParameter(string name, long value)
            : base(name, value, DbType.Int64)
        {
        }
    }

    public class ShortParameter : DbParameter
    {
        public ShortParameter(string name, short value)
            : base(name, value, DbType.Int16)
        {
        }
    }

    public  class DateTimeParameter : DbParameter
    {
        public DateTimeParameter(string name, DateTime value)
            : base(name, value, DbType.DateTime)
        {
        }
    }

    public class FixedLengthStringParameter : DbParameter
    {
        public FixedLengthStringParameter(string name, string value)
            : base(name, value, DbType.StringFixedLength)
        {
        }
    }
By subclassing the DbParameter class we can create strongly typed parameters which provide compile time check thus reducing the chance of passing a string to parameter which requires a integer.

So now we have the components, we have our http module to manage the connection, how to we manage the commands? For that we can create objects to execute specific queries. Say we need to view customers by state. We can build a Customer Repository to find the customers for us. We are encapsulating the logic required to retrieve the customers and we now have a single point in the application to reference it.

CODE

    public class CustomerRepository
    {
        private readonly IConnectionManager manager;

        public CustomerRepository(IConnectionManager manager)
        {
            this.manager = manager;
        }

        public CustomerRepository()
            : this(new HttpConnectionManagerAdapter())
        {
        }

        public DataTable find_customers_by_state(string state)
        {
            var connection = manager.current("foo");
            using (var command = connection.create_command("select * from [customer] where [state] = @state"))
            {
                command.add_parameter(new StringParameter("state", state));
                return command.find_records();
            }
        }
        
        public int total_number_of_customers()
        {
            var connection = manager.current("foo");
            using (var command = connection.create_command("select count(1) from [customer]"))
            {
                return command.get_a_specific_value<int>();
            }
        }
    }
Very simple. We have an object which will return a data table of customers in the specified state (or the total number of customers). You'll also notice 2 constructors. The first requires an object which implements IConnectionManager. This is known as Dependency Inversion. The reasons for this are beyond the scope of this post, but there is plenty of information on the web about this.
We also have a default constructor. This is for convenience only when using the repository in client code. In fact if you are utilizing a Inversion of Control container (IoC) the default constructor can be removed altogether.

Now that we have a way to get a list of customers. lets display the customers on a web page.

CODE

    public partial class _Default : Page
    {
        private CustomerRepository repository;

        protected override void OnLoad(EventArgs e)
        {
            if(IsPostBack) return;
            var state = Request.QueryString["state"];
            repeater.DataSource = repository.find_customers_by_state(state);
            number_of_all_customers.Text = repository.total_number_of_customers().ToString();
            DataBind();
        }

        protected override void OnInit(EventArgs e)
        {
            repository = new CustomerRepository();
        }
    }
Simple, clean and elegant. All our code behind needs to do is ask the repository for customers in a given state. We then bind the page and we are done! I included another customer repository member to return the total number of customers. This only serves as an example of issuing another command against the same connection.

This post serves as an example of how to better manage your database connections in a simplified manner. While I would not recommend this exact code for enterprise level applications (for that I would defer to an established data access framework). This does serve it's purpose well in smaller, data centric applications providing a good balance of simplicity, maintainability and accessibility.

Back to Microsoft: ASP.NET FAQ Index
Back to Microsoft: ASP.NET Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close