Opening And Closing DB in different Sub Routiens

If Someone could please show me some example VB code where i can open the my Sqlconnection in the Page_Load subroutien... and then close that SqlConnection in the Page_Unload routine. I want to be able to execute Sql command without having to re-open and re-close the connection.

Thanks,
Greg

Hello greg,

> open the my Sqlconnection in the Page_Load subroutien...
> and then close that SqlConnection in the Page_Unload routine.

the trick is declaring a private member to keep reference to the connection object. Here is some sample code:

Private Conn As SqlConnection

Protected Sub Page_Load(...)
Conn = DAL.StartConnection()
End Sub

Protected Sub Page_Unload(...)
DAL.StopConnection(Conn)
End Sub

Protected Sub Submit_Click(...)
DAL.PerformSubmit(Conn, ...)
End Sub

Btw, this is not quite recommended practice, as you may guess... ;)

In any case, HTH. -LV

|||So Then would it be better practice to just open, and close the database connection every time i make a database call. even if it might happen multiple times, and in multiple subroutiens on any given page?|||

There really is very little overhead involved with opening and closing an SqlConnection. The Connections are usually pooled, and opening and closing the connection is extremely quick. That being said, if you want to implement it, and you won't need 2 connections open at the same time, then I would implement something like:

Private Conn as SqlConnection

Private Sub DoOpen

If Conn IS NOTHING then conn=new sqlconnection({Your connect string})

if Conn.state=closed then conn.open

end sub

Protected Sub page_Unload

If Conn.state<>closed then conn.close

end sub

Then whenever you would normally open your connection, issue a call to DoOpen. That way if you don't actually need to open the connection anywhere on your page, you won't bother doing so.

|||

> So Then would it be better practice to just open, and close the database connection every time i make a database call. even if it might happen multiple times, and in multiple subroutiens on any given page?

That's common practice and works quite well in common scenarios. After all, keeping a connection open involves its own overhead, so its rather a question of balance.

Indeed, what counts more is: are you sure you need all those queries and you couldn't do the same into a single or few stored procedures? (I'm not claiming you have too many queries; this just is general advice.)

-LV