I usually don't have anything to add to a conversation because frankly
my EE knowledge is near zero, but in this case I have some experience.

The first thing is, I would suggest opening a connection for each
transaction.  Keeping open a perpetual connection to the database
doesn't seem like a good idea to me (its certainly not something I
would suggest).  As already pointed out, this can cause problems if
you are using a "number of users" restricted version of SQL Server.

On the other hand, opening a connection for each separate database
call would have too much overhead for no good reason.

Me, personally, I have been using Web Services for data access rather
than using hard connections from the client.  I am sure others will
say that this is a bad approach, or at least one that ends up adding
over head, but I have found it to be a good way of keeping
connectivity clean.  The added bonus is that this forces you to use
the "open only when needed" rule, as you cannot hold open any objects
(easily) in a .Net service.

I would also say if you are doing something intensive, you may want to
offload it to a separate thread.  I have found that data processing
that locks the GUI (IE, on the app thread) causes users to basically
go nuts and start clicking things incessently.  Create a thread for
data processing and use a delegate to communicate to the GUI.  If you
use Web Services, then do asynchronous rather than synchronous calls.

Another thing I would suggest (this is coming from being burnt a
couple thousand times on this) make sure you separate your Data Access
Layer from your GUI.  It is even a good idea (again, IMHO) to create a
separate DLL for all your data access.  This is particularly good if
you believe that there is ever a chance that you will switch database
engines.  Of course, if you use a webservice, it basically forces you
into doing this anyways.  Be careful about how much business logic you
embed in your GUI.  This is also an area where you might want to put
the code in a separate DLL.

Thats my 0.02 -- for what its worth --

Aleksei

On 30 June 2011 09:05,  <jimf@webstudios.co.uk> wrote:
>
>
> =A0 =A0 =A0 =A0Dirty and lazy, but effective:-
> =A0 =A0 =A0 =A0try
> =A0{
> =A0 =A0returnDAdapter.GetMatrixDetails(matrixPosition, promotionType,
> storeID);
> =A0}
> =A0catch(Exceptione)
> =A0{
> =A0 =A0 Logging.WriteLogInformation(e);
> =A0}
> =A0finally
> =A0{
> =A0 =A0 DAdapter.Connection.Close();
> =A0 =A0 DAdapter.Dispose();
> =A0}
>
> =A0 =A0 =A0 =A0 =A0BODY { font-family:Arial, Helvetica, sans-serif;font-s=
ize:12px; }
> =A0On Thu 30/06/11 1:44 PM , Gerhard Fiedler lists@connectionbrazil.com
> sent:
> =A0V G wrote:
> =A0> Totally off topic, but I'm writing a desktop program (in Visual C#
> for
> =A0> MS SQL Server 2005, of all things), for a client who needs to
> search
> =A0> his already existing database. Basically a search program for his
> =A0> database that will present various import/export functions, search
> =A0> parameters, etc.
> =A0>
> =A0> Very simple question: Every time my "Search" button is pressed in
> the
> =A0> program, should I create (and close at the end) a new database
> =A0> connection? Is this good programming practice? Or should I have
> one
> =A0> open database connection for the lifetime (while the program is
> open)
> =A0> of the program and execute multiple queries (in sequence) from
> that
> =A0> single connection?
> =A0>
> =A0> I know it doesn't *really* matter, but, you know, principles...
> =A0In many applications it doesn't really matter, and yours may be one
> of
> =A0those. In some it does... for example, when you're doing a lot of
> =A0queries and the time opening a connection is prohibitive (use and
> re-use
> =A0one connection), or when you may have many client apps open that
> don't
> =A0use the database a lot (open and close). There isn't really a single
> =A0"best practice" way to do it; it depends on the application
> =A0requirements.
> =A0In any case, make sure you close the connection not only in the
> course
> =A0of normal processing, but also when exception occur that throw you
> out
> =A0of the normal code path. Typically you'd wrap a database connection
> in
> =A0an object that closes the connection when it is destroyed.
> =A0Gerhard
> =A0--
> =A0http://www.piclist.com [1] PIC/SX FAQ & list archive
> =A0View/change your membership options at
> =A0http://mailman.mit.edu/mailman/listinfo/piclist [2]
>
>
> Links:
> ------
> [1] http://www.piclist.com
> [2] http://mailman.mit.edu/mailman/listinfo/piclist
> --
> http://www.piclist.com PIC/SX FAQ & list archive
> View/change your membership options at
> http://mailman.mit.edu/mailman/listinfo/piclist
>

--=20
http://www.piclist.com PIC/SX FAQ & list archive
View/change your membership options at
http://mailman.mit.edu/mailman/listinfo/piclist
.