Best Way To Reuse Database Connections

[mcfly1204]

I have been writing a good amount of code that is accessing a database frequently. Instead of creating the database connection every time I would like to access the database, is there a proper way to write the connection once so that it can be called and reused throughout the application? Is there a particular design pattern that would be appropriate for this? Thanks.

[C CSR]

Generically, you would create then open a unique connection per database; then per connection create and open your unique recordset(s) sequentially for that connection, doing your business, closing each and opening the next, etc. etc.. When your finished with all recordsets for that database, then close the connection. Opening/closing recordsets could hypothetically go on 24/7 on one connection, but that's goofy.

So you could have more than one connection, but typically to separate databases. Open and close each recordset, sequentially. One connection should not disturb the other connection if it is to a separate database. You can get an error overlapping recordsets on the same connection (I had a problem like that but I never found out exactly where the issue was--so I just stopped doing it because basically I didn't need to).

The one place you may have problems is using the same connection and opening more than one recordset without closing one of them first. Its kind'a in and out, next, in and out, next situation on the same connection.

There may be some other asynchronous techniques for heavy, intermittent traffic due to the construct in your programming, but I've never had to use them. Someone else might know about that--I'd be interested to know what they are.

[mcfly1204]

So best practice would be to open a connection for each database to be used when the program opens, and then close said connections when the program exits? if that is the case, that would mean that the application would have to exit cleanly to close the db connections, but what happens if the program were to crash?

[C CSR]

Theoretically, yes, you minimize the open & close connection latency. But, this assumes you always use every database and every table that you are connecting to and that you are consolidating your input/output routines. Not knowing the application, but guessing that operations are staggered while the application is running (i.e.; there is user interaction which breaks up the input/output process) it would be wasteful and risky to open every "to-be-used" connection as a standby. That's not what I wanted to get across.

In a simply scenario, I like to put all my database processing at one location in the string of events, open the connection and run them all sequentially and then let the connection close. The app could break right in the middle if it wants to, or it could break before or after. One question is "how long do you want the data to hang around in memory before you dispose of it." There's a compromise either way.

If I'm going to open several tables for input/output in one related routine, I meant I would open a connection, work on each table and then close the connection, as opposed to open/close the connection for each table in the routine. That's simple enough.
But your original question was also relating to the scope of where the call is made for the connection, to decrease duplication of the code itself. That, separately, is relating only to scope just as it would apply to variables.

So, you have a "where do my operations occur (scope), when do they or should they occur (events), and how long after I get the data do I wait before disposing of it (volatility)." Balance the duplication of code versus 1) memory hang-time, 2) stability of the connection during processing, 3) record availability for multiple users, 4) for "hand-shaking" or latency, 5) and everything I left out(!).

I really thought you were concerned about code distribution during development. After the code is loaded, I think your only compromise is memory. Connection resources are a distinct issue. As far as crashing, "what are the factors?"----- good luck with that.

And, remember the first word in my reply...

[PsychoCoder]

While there are varying feelings on using the Singleton Pattern but I think this may be the perfect spot for it. Here's an example on using the Singleton Pattern for your db connection

public sealed class DBSingleton
{
    private static readonly DBSingleton dbInstance = new DBSingleton();

    private readonly SqlConnection con =
        new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString);

    // Explicit static constructor to tell C# compiler
    // not to mark type as beforefieldinit
    static DBSingleton
    {
    }

    private DBSingleton
    {
    }

    public static DBSingleton Instance
    {
        get { return dbInstance; }
    }

    public SqlConnection GetDBConnection()
    {
        return con;
    }
}

This will allow for a single connection be used

[C CSR]

mcfly:

I'd like to get your feedback on the implemntation of McCutchen's solution here--after you try it. Sounds like the cure you were waiting for. Don't let us down :)

[Frequeric]

Ïðîñòèòóòêè

Âî íåìàëîì ýòî ÿâëåíèå ïîðîæäåíî âåêîâûìè ïðåäðàññóäêàìè, ñîãëàñíî êîòîðûì ïðîÿâëåíèå ñåêñóàëüíîñòè ñ÷èòàëîñü íåïðèñòîéíûì, à ñàìà òåìà — çàïðåòíîé. Òàêèå ÷óâñòâà äà¸ò íàì áëèçîñòü ñ íîâîé ïîäðóãîé, ÷òî ìû ïåðèäè÷åñêè è äåëàåì. ×åðíûå ïðîñòèòóòêè ïèòåðà Ïîäðîáíåå îá ýòîì ðàññòðîéñòâå ðàññêàçûâàþò â ýòîé ñòàòüå ñïåöèàëèñòû â ñåìåéíîé ìåäèöèíå è ñåêñîëîãèè. Ñîöèàëüíûå, êóëüòóðíûå è ïñèõîëîãè÷åñêèå êîìïîíåíòû â ÷åëîâå÷åñêîé ñåêñóàëüíîñòè ìîãóò íå òîëüêî îáóñëîâëèâàòü ðÿä ôèçèîëîãè÷åñêèõ ðåàêöèé, íî è ìåíÿòü èõ êîíñòàíòû. Óçáåêñêèå ïðîñòèòóòêè â ìîñêâå