passing DB connection object to functions

[maboo]

I have more of a design question not syntax.
I have a large function that will call several other small functions and most of them will hit my DB. Would it hurt my performance if i created aconnection object in my large function and the passed it to each small function instead of opening a new one inside of each small function.
I know that opening just one is better but I don't know how much of a hit I would get if I started passing around the connection to functions.

thanks
Maboo

 

[macleod1021]

I'm not sure how much of a performance gain you'll get, but if you're going to be passing a lot of db access functions just to get one item, you may want to consider creating a stored procedure. That will allow you a faster response than parsing through a lot of code. It'll make it a lot easier to maintain too.

 

[maboo]

Thanks for the reply macleod1021
Most of the hits to the Db are updates and inserts. I can't lump them all together cause they depend on alot of if-else's.
I'm really only worried about the opening of connection objects. IS opening a new one a better way to go?
Or should I create and open 1, then pass that connection to the smaller functions?

Thanks

Maboo

 

[jebenson]

One thing to think about when doing your updates and inserts is transactions. Are your updates and inserts related in some way, such that a failure in one could leave your data in a corrupted or incomplete state? If so, then using one connection is the way to go because you can pass a transaction object with it. Then if any one of the updates or inserts fails you can roll back the entire transaction and leave the database in the same state as when the process started. The key is for all of the updates and inserts to use the same transaction.

Another thing to consider is not passing the connection as a parameter, but making it global (in a module) and referencing it from wherever it is needed. You could do the same with the transaction as well.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[ThatRickGuy]

Passing the connection byRef (or using a global object) might save a bit of memory, but ADO pools connections. So whether you have 1 connection object or 20, only one connection will be opened (so long as they all have identical connection strings).

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[maboo]

jebenson,
Using transactions is a good idea, thanks.
I could use the global conn object for some of the function calls but not all since I call a seperate dll.
Thanks for the info jeb.

maboo

 

[maboo]

they do have identical conn strings ThatRickGuy.
Do you think opening and closing the connection 4-6 times in a function will hurt it at all?

Thanks

maboo

 

[ThatRickGuy]

I would say go with Jeb's global idea. Create a public class that allows you to get a global connection. Then, don't bother closing the connection, let the global class handle it.

for example:

Namespace MyNameSpace
 Public Class Connection
  private shared m_Connection as SqlConnection

  public shared function GetConnection() as SqlConnection
   if m_connection is nothing then
     m_connection = new SqlConnection
     m_connection.open
   end if

   return m_connection
  end sub

  public shared sub CloseConnection()
   if not m_connection is nothing then
    m_connection.close
    m_connection.dispose
    m_connection = nothing
   end if
  end sub
 end class
end namespace

Then from anywhere in your code that has a reference to the dll with that class in it you can call:

MyNamespace.Connection.Getconnection and it will return an open connection object. In the closing method for your app add MyNamespace.Connection.CloseConnection and everything should get closed and cleaned up.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.