im writing a service that will be on a app server
this is one of many service's to process Recordsets from Sql Server...
1 service may have 20 Processes to Complete
example
main class PreProcess()
method CleanNames();
method updateNames();
methodr ValidateContracts();
Various things must be completed
Suedo Example below
My question is
in the main class make my connection and keep it for all sub members
or connect in each member
A. This Way opens connection...closes connect

method cleanNames()
1 get connection
2 get recordset
3. Close Connection
4 process Recordset
5. Open Connection
4 Commit Changes
6. Close Connection()
finished cleanames()
or
method cleanNames()
1 get connection
2 get recordset
3 process Recordset
4 Commit Changes
5. Close Connection()
finished cleanames()
or
this final way is in the parent Class or Calling Function
sends in the connections holds it open till the service is
completed of all jobs its Suppose to do
then releases the connection

method cleanNames(oconn) connection as parameter
1 get recordset
2 process Recordset
3 Commit Changes
finished cleanames()

Thanks
DaveP

DaveP (dvs_bis (AT) sbcglobal (DOT) net) writes:
I'm not really sure I understand, but if the members are intended to
be separate threads, you should definitely have one connection per
member.

If they are just different tasks that a single-threaded service will
perform, it's more of a toss-up, but I think the idiom today is to stick
with local connections. Keep in mind that ADO .Net maintains a connection
pool where it lingers to disconnected connections and then reuse them
if there is a request for a connect with the same properties within
some timefram (60 seconds, I believe).




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx