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?
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:
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..
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:
EDIT:
Some reading first:
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)
Hope it helps.
using(IDisposable){ } statement to force the GC to do it's work" is also inaccurate - the using statement doesn't force the GC to do anything. - default.kramer"Don't bother disposing your objects manually": Who does dispose each object manually? Even if you manually dispose each object the GC won't do anything automatically, I was just saying that the GC is pretty smart about how and when to free resources. And "use the using{} statement to force the GC to do it's work": Are you implying that a resource inside a using block doesn't call the Dispose method on that resource when it's scope is done? If so, why is a requirement to implement IDisposable when using a using statement? I'll change the answer to reflect this. - EstebanIDisposable. A using block calls Dispose; the GC calls the Finalizer. A common "safety net" is to have the Finalizer ensure that Dispose was called, but in a well-behaved application this safety net should not be needed - all IDisposables should be disposed without any GC involvement. - default.kramer
answers to your questions:
Close it. .NET does connection pooling for you under the hood.
Create it. use the using (Connection conn = new ....) each time - this way, you'll make the most out of the .NET pooling mechanism.
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
Per instance.
Here's my suggestion for an architecture:
Create a database table (queue) for pending SMS to be sent out.
each row will contain all the information needed for the sms + the current status.
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'
each sms will be sent out using a custom threadpool on the windows service side.
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).
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.
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)
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.