The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database connection/transaction = static classes ?

I would like to ask if the database connection and transaction should be defined as static classes?

First, I wrote DatabaseConnection and DatabaseTransaction classes as static, because there is always only 1 connection and 1 transaction in my app (desktop Windows app).

Then, I tried to rewrite these classes to non-static, but now I have to pass the DatabaseConnection and DatabaseTransaction to every DB command I make, which looks somehow strange to me.

I have nearly no experience with database programming, so I am asking if there are any general rules how to make this? I work in C# if it depends.

Petr Veit
Wednesday, March 26, 2008
Please do not make your database connection a global variable. It _will_ haunt you later.
I wonder -- Why do you have transaction and connection as separate objects? Most api's would make transaction a property of connection or a subclass of connection.
Troels Knak-Nielsen Send private email
Wednesday, March 26, 2008
Troels: now I realize that the transaction is really a part of conenction.

So, can I do it like this:

1) Create the connection object at startup

2) Everytime I need to send some commands to the database,
- Open the connection (possibly begin a transaction)
- Execute the commands, the connection is always passed to the command method.
- Close the connection (+ commit transaction if needed)

3) Dispose the connection object when closing the app
Petr Veit
Wednesday, March 26, 2008
Here's a design proposition:

Is it possible for you to collect all database actions before you execute them? If you can do that, then you can create your objects once and pass them to each action, all in one block.

One way to do that is to define a delegate similar to:
delegate void DatabaseAction(Connection c, Transaction t);

All these delegate instances can be cached in some collection for later execution:

Connection c = ...
Transaction t = ...

foreach(DatabaseAction a in actions) a.Invoke(c, t);


You can also search for "unit of work" and look for inspiration.
Thomas Eyde Send private email
Wednesday, March 26, 2008
Database connections are huge resource hogs. Having a single connection open all the time is the old way to do things. The new way is to use connection pooling (which .NET typically does under the covers for you). So you create a new connection when you need it, do your work, and then close it when done. With pooling in place this is a very lightweight way to operate.
dood mcdoogle
Wednesday, March 26, 2008
dood: thanks for this tip. Do I have to always create a new connection instance and dispose it after I close the connection (for example in the using block)?

Or can I create only one conenction instance and then open-close it many times? The connection would be disposed when the application quits. Or do all the resources remain in memory when the connection is only closed (and not disposed)?

Petr Veit
Wednesday, March 26, 2008
You don't actually have to "dispose" of it. Create a new one, open it, use it, and then close it.
dood mcdoogle
Wednesday, March 26, 2008
> Or can I create only one conenction instance and then open-close it many times?
Not sure if it works, but I wouldn't do that even if it does.  One day you might want to implement multithreading - e.g. a BackgroundWorker that accesses the database.
Wednesday, March 26, 2008
Joe: so creating a new connection, then using it in the using{} block for a group of commands I need to do at one time and then dispose the conenction instance (automatically by the using block). Isn't transfering of the connection string many times during often conenction creation more time consuming?
Petr Veit
Wednesday, March 26, 2008
No, it's not more time consuming. The database pooling logic is typically keeping that connection open under the covers anyway. So if you open/use/close a connection it actually stays open for a while. When you need the next connection, the open call actually just fetches the next available one from the pool so it is very fast. This is the expected usage pattern.

The benefits of this approach are:

1) If you haven't used a connection in a while the pool can close it. Compare this to keeping a connection open the whole time your app is running. If the user walks away the connection can be closed and reopened the next time they come back instead of just staying open and hogging resources. Also, connections that stay open a long time end up being more ripe for problems and may end up being closed on you anyway so you would have to account for this in your code.

2) The connection pool allows you to handle multiple connections at a time. The pool can open several connections and manage their lifetimes for you.

3) It is a pretty easy usage pattern compared to trying to keep one global connection alive and available for use. This is especially true for multithreaded apps that might need connection synchronization.

4) This pattern applies to all different types of applications. You would use the same pattern for a web app for instance. So there is no reason to reinvent the wheel here just because your current requirements are for a desktop app that could get by with only one connection. That won't be the case at some point in the future so you had might as well learn this common usage pattern now.

Pick up an ADO.NET book or read up on ADO.NET connection pooling to get more details.
dood mcdoogle
Wednesday, March 26, 2008
dood: thanks again, understood completelly, now I have to get used to this approach.

The problem is that I have several objects that represent individual tables in the database (say CustomersTable, OrdersTable) and that can perform some operations in these tables.

So far, I've created these table instances at application startup togegther eith the connection and passed the global connection in their constructor:

// Create global connection that will be closed most of the time:
connection = new DababaseConnection(connectionString);

// Create table objects and pass the connection:
customersTable = new CustomersTable(connection);
ordersTable = new OrdersTable(connection);

Let's say I need to make some calculation in the customersTable: with my old approach, I could open the global connection, call any table methods that automatically used this connection and finally close the connection.

If I create a new connection instance for each group of commands, I have to call every table method with an argument of the connection to use. Or is there any other approach for this? It looks a bit strange to me, but I can get used to it if it's normally used.

Thank you,
Petr Veit
Thursday, March 27, 2008
As some have suggested already, you should manage carefully your connections. One simple way to do so is to use a factory patterns to obtain a connection instance each time you need it. The factory can then choose which policy to use to "create" connections (create one new each time, reuse from a pool, etc.).

However, you must be careful because in come cases, the factory's policy need to know if the request comes in the same context as a previous one (for example, the same session in a web application). Therefore, I use to combine this with an ExecutionContext object that encapsulates this information. Then, the factory has a method like getConnection(context) and releaseConnection(context).

Pablo Send private email
Thursday, March 27, 2008

I have the same basic setup. But I don't pass a connection into the table class. The table class itself will call a factory method to create a connection when/if it needs it. For example, I can create a Customer table object and manipulate it all I want without ever involving the database. It isn't until I call an appropriate "save" method on the Customer object that the database needs to get involved. At that point the save method itself will go get a connection (using a global factory method), build the appropriate update/insert SQL, execute it, and then close the connection.

The idea is that you shouldn't require your business logic to be aware of the data persistence aspects of your system. This means that business logic that calls my Customer table object shouldn't even have to know that a database is even involved. In fact, I could change it later and have the Customer table object persist itself to an XML file or some web service without changing a single line of business logic.
dood mcdoogle
Thursday, March 27, 2008

thanks for your suggestions (Pablo as well). What if you need one transaction to be active during work with several tables? From what I understood, the connection has to be open until the transaction is commited (?). If every table object used its own connection, the transaction couldn't finish.
Petr Veit
Thursday, March 27, 2008

>What if you need one transaction to be active during work >with several tables?

That's why i mentioned that my factories use a "context" object, which tell it if this request is in the same context than a privious one. A context, for me, is generally the same browser session. This allows, for instance, to iterate along the results of a query in multiple user interactions. 

In general, it is any object that could be used to identify a set of related operations.Your application is the only one who can tell when several db operations are  related or not.

Also, my factories also have a release method that allows the application to explicitly say the connection is not longer valid. For example, when the user leaves the page on which he make the query, I can release the connection even when the session is still active.


Pablo Send private email
Friday, March 28, 2008
> A context, for me, is generally the same browser session.
> This allows, for instance, to iterate along the results
> of a query in multiple user interactions.

If you're trying to build an application that can scale to handle large numbers of users, you'd better not be holding database connections (to say nothing of transactions!) open longer than a single HTTP request. 

When I need to let the user page through query results from request to request, I either design the tables to support executing a new query for every request, or I dump the query results into a uniquely-named ## table and give the user session the name of that table.  I don't hold a connection open from request to request, because I have no idea how much time is going to pass between requests, and connections are an expensive resource to lock up when they're not in use.

One very important side effect of connection pooling and SQL Server:  remember that temporary objects belong to the connection, not the user. 

This has (at least) two implications:  First, if one method creates a #Foo table and another method uses it, both methods must use the same connection.  Second, if a method creates a #Foo table and doesn't drop it, that table will be visible to any other method that happens to get the first method's connection from the pool.

Generally speaking, there are several scopes in which a database operation can occur; from (very roughly) highest to lowest, they are:  user, connection, ADO transaction, procedure, database transaction, SQL statement.  It's important to understand each of these scopes, and decide upon the appropriate scope for every operation that you're going to be executing.
Robert Rossney Send private email
Friday, March 28, 2008
The rule is open late / close early.  The defualt number of connection allowed in a pool is 100.  This isn't such a big number if you think of an ASP.NET application.

I setup my methods following this pattern.

public DataTable GetAccounts(Guid UserId)
            SqlCommand objCmd = this.GetNewCmd("dbo.GetAccounts");
            objCmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = UserId;
            return GetTable(objCmd);

The GetNewCmd() takes care of getting the connection string and making the connection object.  The GetTable() does the open connection, retrieves the data and then closes the connection.  It also logs any errors.  I also have GetDataSet(), ExecuteScalarString(), etc.

  All these helper function are in a base class that I inherit from in each project to make the specific classes that call stored proc.

Wednesday, April 16, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz