how to make this variable available to other subs

[djc]

I have several subroutines (all inline code) that wind up using the same
database connection object variable. I have been declaring a new variable in
every sub. I just now came to a point where I want to call one sub that will
utilize a database connection from within another sub that is already
utilizing a database connection.

1) Can I just declare this database connection once outside of all
subroutines to make it global (the way I understand you make a var of global
scope) and use it for all my different command objects within the different
subs? And just call the Open and Close methods on it from within the
different subs?

I know that is how to make a global variable and have done it with other
things like simple strings and numbers but I am not sure if a database
connection object variable would have different requirements?

any input is appreciated. Thanks.

 

[Chad Devine]

Yes, you assume correctly that you can just declare outside the subs
and the variables will work within all subs.

An example:
Dim Conn As SqlConnection
Dim cmdSelect As SqlCommand
Dim SelectStr As String

Sub Page_Load(Sender As Object, E As EventArgs)
Conn = New
SqlConnection("server=SQLServer;trusted_connection=true;database=MyDatabase")
If Not IsPostBack Then
BindDataGrid
End IF
End Sub

Sub BindDataGrid
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)
Conn.Open()
dgrdServers.DataSource = cmdSelect.ExecuteReader()
dgrdServers.DataBind()
Conn.Close()
End Sub

 

[Chad Devine]

Alright... the code got messed up, so this time I'm going to hit the
preview button to make sure it's posting correctly.

Dim Conn As SqlConnection
Dim cmdSelect As SqlCommand
Dim SelectStr As String

Sub Page_Load(Sender As Object, E As EventArgs)
Conn = New
SqlConnection("server=SQLServer;trusted_connection=true;database=MyDatabase")
If Not IsPostBack Then
BindDataGrid
End IF
End Sub

Sub BindDataGrid
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)
Conn.Open()
dgrdServers.DataSource = cmdSelect.ExecuteReader()
dgrdServers.DataBind()
Conn.Close()
End Sub

 

[djc]

Thanks for the reply. In my case the connection string will be the same in
all cases so I would declare the connection object and set the connection
string property too, all outside any subs.

1) do I need to be concerned about the resource usage doing this? for
example, I currently declare the connection, open it, use it, close it, and
then set it equal to Nothing to release it's resources. If I declare outside
all subs then I can't set it to Nothing right? Or am a missing a fundemental
of a page's lifecycle here? Does every post back create a new page with all
new instances of these variables?

2) I just realized another detail: I already have the connection open in the
calling sub. The called sub also needs to open a connection to the database
though. Can the called sub just use the existing open connection from the
calling sub? Would it be better to just have the called sub create it's own
new connection that it opens and closes when it's done (resulting in 2 open
connections to the same database at one time)?

thanks again for the input. Any more is welcome.

 

[Chad Devine]

You won't be able to set the connection property outside of a sub, but
once you set it in say... the page_load sub it will be set publically
througout your <script runat="server"> </script> part of the page.

So you can't do this outside of a sub:
Dim cmdSelect As SqlCommand
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)

1) You can still set it to nothing within a sub, that will define the
variable publically thus releasing it's resources. I believe those
resources are negligible as once the page loads it's out of the
server's memory... but I guess you can never be too certain because I'm
not sure if that is a fact. I do know that you can set it to nothing,
and it will be nothing publically within the script tags. I'm pretty
sure every postback creates a new page with all the new instances, but
again I'm not 100%.

2)You can open the connection from a previous sub, or use a previous
connection's instance, for example:

--------------------------------------------------
Dim Conn As SqlConnection
Dim cmdSelect As SqlCommand

Sub Page_Load(Sender As Object, E As EventArgs)
Conn = New
SqlConnection("server=server;trusted_connection=true;database=database")
If Not IsPostBack Then
cmdSelect = New SqlCommand( "Select * from Servers ORDER BY ServerName
Asc;", Conn)

'We open the connection before binding the data to the datagrid.
Conn.Open()
BindDataGrid
End If
End Sub

Sub BindDataGrid
dgrdServers.DataSource = cmdSelect.ExecuteReader()
dgrdServers.DataBind()

'Here the connection closes from the previous sub.
Conn.Close()
End Sub
--------------------------------

As for the rest of your question, it's best to have only one connection
open to the database at one time. So, just use the existing connection
that's open and don't close it until you're done. You can use it in
other subs as long as you make the "Dim Conn As SqlConnection" outside
of the subs.

Hope I got all your questions.
Chad

 

[Clamps]

Yes, you can declare the connection globally (I assume you mean somewhere other than
global.aspx) like you would a string or integer and use the open and close methods as
you need the connection. Make sure that you are only attempting to access this
connection in a one-at-the-time fashion. If you attempt to use this connection as
'shared' you will get all kinds of concurrency problems and your prog will go nuts.








| I have several subroutines (all inline code) that wind up using the same
| database connection object variable. I have been declaring a new variable in
| every sub. I just now came to a point where I want to call one sub that will
| utilize a database connection from within another sub that is already
| utilizing a database connection.
|
| 1) Can I just declare this database connection once outside of all
| subroutines to make it global (the way I understand you make a var of global
| scope) and use it for all my different command objects within the different
| subs? And just call the Open and Close methods on it from within the
| different subs?
|
| I know that is how to make a global variable and have done it with other
| things like simple strings and numbers but I am not sure if a database
| connection object variable would have different requirements?
|
| any input is appreciated. Thanks.
|
|

 

[djc]

Thank you very much Chad. You have been very helpful!

 

[djc]

Thank you!