What is the best method for making database connection (static, abstract, per request, ...)?

Question

I used lot of model for connecting to db, in my last project that i worked with C# & entity framework, i created static class for db connecting but i had problem with opening and closing connection for that give me error when more than 10-15 requests come together, i solved it with changing method of connecting to db with i connect now per request and removed all static methods and classes.

Now i want to know,

What is best model for making connection?

1. Should i close it after every query and open it before using or ...?
2. A connection in static class is good model (that i don`t need to create it, every time)?
3. Is there a good design pattern for this problem?
4. All of it is for the same question What is the best method for making database connection (static, abstract, per request, ...)?

For example i working on a sms sender web panel, I should send 100K sms per second, these sms collect with others and make a package that every package have 1~20 sms then i need to send 5K~100K packages per one second and when i send a package i should do these steps:

1. Update single sms to delivered or not delivered
2. Update user balance if delivered decrease user balance in useraccounts table
3. Update number of sms send count in user table
4. Update number of sms send count in mobile number table
5. Update number of sms send count in sender number table
6. Update package for delivered and failed sms in package table
7. Update package for how thread send this package in package table
8. Update thread table for how many sms send it by this tread and how many failed
9. Add account document for this transactions in AccountDocument table

All steps and lot of other things like logs, user interface and monitoring widgets, that should doing and i need DB connection for doing every single of this transactions.

Now, What is best model for connecting to DB? By human request or by thread request or by every single transaction..

Answer 1

answers to your questions:

1. Close it. .NET does connection pooling for you under the hood.

2. Create it. use the using (Connection conn = new ....) each time - this way, you'll make the most out of the .NET pooling mechanism.

3. you can use the .NET ThreadPool (or your own custom one), define the ThreadPool to use solely 10 thread in parallel and Enqueue work items one after another. this way no more then 10 connections will be used in the same time + it'll probably work faster. More about Custom ThreadPools: Custom ThreadPool Implementation

4. Per instance.

---

Here's my suggestion for an architecture:

1. Create a database table (queue) for pending SMS to be sent out.

2. each row will contain all the information needed for the sms + the current status.

3. create a worker process, perhaps a windows service which will sample this table constantly - let's say, each 5 seconds. it will select the TOP ~20 SMS with status = 'pending to be sent' (should be represented as int). and will update the status to 'sending'

4. each sms will be sent out using a custom threadpool on the windows service side.

5. in the end of the process, ALL the processed sms status will be updated to 'done' using a CTE (common table expression - you can send a cte with all the sms rows ids that have just been process to do a 'bulk update' to 'done' status).

6. you could make the status update stored procedure to be the same one as the 'getpending'. this way, you could select-for-update with no lock and make the database work faster.

7. this way, you can have more than just one processor service running (but then you'll have to loose the nolock).

remember to avoid as much locking as possible.

by the way, this is also good because you could send SMS from any place in your system by simply adding a row to the pending SMS table.

And one more thing, i would not recommend to use entity framework for this, as it has too much going on under the hood. All you need for this kind of task is to simply call 3-4 stored procedures, and that's it. Maybe take a look at Dapper-dot-NET - its a very lightweight MicroDal framework which in most cases works more than 10 times faster than EF (Entity Framework)

Answer 2

1. Should i close it after every query?

.Net does that for you so let it handle it, that's a garbage collector task. So don't bother disposing your objects manually, this is a good answer by Jon Skeet: https://stackoverflow.com/a/1998600/544283. However you could use the using(IDisposable){ } statement to force the GC to do it's work. Here is a nice article about resources reallocation: http://www.codeproject.com/Articles/29534/IDisposable-What-Your-Mother-Never-Told-You-About.

2. A connection in static class is good?

Never make a data context static! Data contexts are not thread safe or concurrent safe.

3. Is there a good design pattern for this problem?

As Belogix mentioned dependency injection and unit of work patterns are great, in fact entity framework is a unit of work itself. DI and UoW are a bit overrated though, it's not easy to implement if it's your first time handling an IoC container which if you're going that path I'd recommend Ninject. One other thing is you don't really need DI if you're not gonna run tests, the awesomeness of these patterns is to decouple, so you can test and mock without sweat.

In-short: If you're gonna run test against your code go for these patterns. If not, I'm providing you an example about how you could share your data context among the services you'd like. This is the answer to your fourth question.

4. What is the best method for making database connection (static, per request)?

Your context service:

public class FooContextService {
    private readonly FooContext _ctx;

    public FooContext Context { get { return _ctx; } }

    public FooContextService() {
        _ctx = new FooContext();
    }
}

Other services:

public class UnicornService {
    private readonly FooContext _ctx;

    public UnicornService(FooContextService contextService) {
        if (contextService == null)
            throw new ArgumentNullException("contextService");

        _ctx = contextService.Context;
    }

    public ICollection<Unicorn> GetList() {
        return _ctx.Unicorns.ToList();
    }
}

public class DragonService {
    private readonly FooContext _ctx;

    public DragonService(FooContextService contextService) {
        if (contextService == null)
            throw new ArgumentNullException("contextService");

        _ctx = contextService.Context;
    }

    public ICollection<Dragon> GetList() {
        return _ctx.Dragons.ToList();
    }
}

Controller:

public class FantasyController : Controller {
    private readonly FooContextService _contextService = new FooContextService();

    private readonly UnicornService _unicornService;
    private readonly DragonService _dragonService;

    public FantasyController() {
        _unicornService = new UnicornService(_contextService);
        _dragonService = new DragonService(_contextService);
    }

    // Controller actions
}

Second thoughts (almost an edit): If you need your context not to create the proxies for your entities therefore not having lazy loading either, you could overload your context service as follows:

public class FooContextService {
    private readonly FooContext _ctx;

    public FooContext Context { get { return _ctx; } }

    public FooContextService() : this(true) { }

    public FooContextService(bool proxyCreationEnabled) {
        _ctx = new FooContext();
        _ctx.Configuration.ProxyCreationEnabled = proxyCreationEnabled;
    }
}

NOTE:

• If you set the proxy creation enabled to false you will not have lazy loading out of the box.
• If you have api controllers you don't want to deal with any full blown object graph.

EDIT:

Some reading first:

• This link relates to a pre-release version of EF6: Entity Framework and Async.
• Scott Allen posted about this in his blog: Async in Entity Framework 6.0.
• If you're going to use Unit of Work I'd recommend to read this: Make the DbContext Ambient with UnitOfWorkScope.
• Darin Dimitrov's answer on Do asynchronous operations in ASP.NET MVC use a thread from ThreadPool on .NET 4.

Get this done:

(_context as IObjectContextAdapter).ObjectContext.Connection.Open();

This is a great article about Managing Connections and Transactions.

Entity framework exposes EntityConnection through the Connection property. Read as: public sealed class EntityConnection : DbConnection.

Considerations for managing connections: (taken from previous link)

• The object context will open the connection if it is not already open before an operation. If the object context opens the connection during an operation, it will always close the connection when the operation is complete.
• If you manually open the connection, the object context will not close it. Calling Close or Dispose will close the connection.
• If the object context creates the connection, the connection will always be disposed when the context is disposed.
• In a long-running object context, you must ensure that the context is disposed when it is no longer required.

Hope it helps.

Answer 3

I think per request scales the best. Use a thread-safe connection pool and make the connection scope coincide with the unit of work. Let the service that's responsible for transactional behavior and units of work check out the connection, use it, and return it to the pool when the unit of work is either committed or rolled back.

UPDATE:

10-12 seconds to commit a status update? You've done something else wrong. Your question as written is not sufficient to provide a suitable answer.

Daily NASDAQ volume is 1.3B transactions, which on an 8 hour day works out to ~45K transactions per second. Your volume is 2X that of NASDAQ. If you're trying to do it with one machine, I'd say that NASDAQ is using more than one server.

I'd also wonder if you could do without that status being updated using ACID. After all, Starbucks doesn't use two-phase commit. Maybe a better solution would be to use a producer/consumer pattern with a blocking queue to update those statuses when you can after they're sent.