Click here to Skip to main content
15,880,608 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Ok, so I have a fair bit of experience in doing ASP.Net pages in VB.Net. I am now working on a C# WinForms app. This app uses a SQLite DB for data storage. As I sit here programming it occurs to me that it would be a simple matter to open a connection to the database and leave it open for the duration of the application and make it available to all the classes to use. When doing web development this isn't practical because it is "stateless" and for most things you need a postback before you know whether or not you are doing anything with the database. So, I guess my question is, is it a good idea to have 1 connection for the whole winform app to use (with commands in each type to do the work)? If that makes sense? Do you winForm developers alway enclose a new connection with each method? e.g.

C#
class myclass1
{
  public void doSomething
{
  SQLiteConnection conn = new SQLiteConnection(myString);
  conn.Open();
  SQLiteCommand cmd = new SQLiteCommand("someSqlStatement", conn);
  SQLiteDataReader reader = cmd.ExecuteReader();
  //loop and do work.
  conn.close()
}
}

class myclass2
{
  public void doSomethingElse
{//get another connection again
  SQLiteConnection conn = new SQLiteConnection(myString);
  conn.Open();
  SQLiteCommand cmd = new SQLiteCommand("someSqlStatement", conn);
  SQLiteDataReader reader = cmd.ExecuteReader();
  //loop and do work.
  conn.close()
}
}


Or alternatively you could pass the connection to each object in its constructor and then reference it directly:

class myclass3
{
  public void doSomethingEasy
{//use "globalConn" which was passed in the constructor
  SQLiteCommand cmd = new SQLiteCommand("someSqlStatement", globalConn);
  SQLiteDataReader reader = cmd.ExecuteReader();
  //loop and do work.
  conn.close()
}
}

Since my other question is pretty unrelated to this one, I'll post it separately.

Thanks as always!
Posted
Updated 15-Jan-12 13:09pm
v2

1 solution

I haven't done anything with databases that wasn't a web app, so I could be wrong, but I'd lean toward having a single connection. As you illustrated, the code is much simpler when you don't have to worry about creating and destroying connections in every single method (though arguably you shouldn't necessarily have to do that even in a web application).

One issue you might run into, though, is if you have numerous concurrent users and you keep your connection open all the time, you might run out of connections on the database side!

Though I see in your example that you are closing the global connection, but not opening it - that won't work! ;)
 
Share this answer
 
Comments
rune711 15-Jan-12 22:37pm    
One thought I have is that, if you have a single connection, and your application is multi-threaded, you might end up with conflicts fighting for use of the connection. I am assuming that using separate instances of the connection in each procedure would lessen this issue? (My program is multi-threaded).
loctrice 15-Jan-12 23:08pm    
I usually use a base object that any "connected" object extends, or write a wrapper for database connections and have that as a property. You have to create a new connection every time though, and there has been one ocassion I had to change that for an Access database because it was too slow.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900