Reinitializing SqlCommand object

Hi All,
Can the same connection object and the command object be reinitialised and
re-used through-out the project? Is the code below permissible?
Dim DBConnection As SqlConnection
Dim oCmd As SqlCommand
Dim sStmt As String
DBConnection.Open()
sStmt = "select count(*)......"
oCmd = New SqlCommand(sStmt, DBConnection)
oCmd.CommandText = CommandType.Text
ocmd.ExecuteScalar()
.....
...
...
sStmt = "insert ..."
oCmd = New SqlCommand(sStmt, DBConnection)
oCmd.CommandText = CommandType.Text
DBConnection.Open()
ocmd.ExecuteNonQuery()
.....
...
...
sStmt = "update......"
oCmd = New SqlCommand(sStmt, DBConnection)
oCmd.CommandText = CommandType.Text
DBConnection.Open()
ocmd.ExecuteNonQuery()
.....
...
...
oCmd.Connection.Close()
Would previous instances of SqlCommand object be garbage collected
automatically?
kdkd,
This is a .NET question, not SQL Server, but I'll field it.

> Can the same connection object and the command object be
> reinitialised and re-used through-out the project?
First off, in your code, you are not reusing the *object* you are reusing
the *reference* (the variable name) . There is a difference.
Second, you say "through-out the project" which to me implies global usage.
That's a bad, bad, bad practice. Declare, instantiate, Open, Use, and then
Close your connection only when you need it. No more.
If you have a series of commands to execute one after another, the pattern I
would recommend is this:
Dim myCommand as SqlCommand
Dim myConnection as New SqlConnection(ConnectionString)
myConnection.Open()
myCommand = New SqlCommand(sql,myConnection)
myCommand.ExecuteNonQuery
myCommand = New SqlCommand(sql,myConnection)
myCommand.ExecuteNonQuery
...
myConnection.Close()

> Would previous instances of SqlCommand object be
> garbage collected automatically?
Garbage collection in .NET is not immediate. It's "when it gets to it." But
yes, it will be collected. "myConnection = nothing" is uneeded and not
recommended.
Even though a connection is automatically closed when GC'ed, I still
recommend an explicit "myConnection.Close()." This will free up the ADO.NET
connection pool.
And one final note, consider looking at MSDN naming guidelines. In .NET, we
do not use "sStmt" or "oCmd".
--Alex Papadimoulis
"kd" wrote:

> Hi All,
> Can the same connection object and the command object be reinitialised and
> re-used through-out the project? Is the code below permissible?
> Dim DBConnection As SqlConnection
> Dim oCmd As SqlCommand
> Dim sStmt As String
> DBConnection.Open()
> sStmt = "select count(*)......"
> oCmd = New SqlCommand(sStmt, DBConnection)
> oCmd.CommandText = CommandType.Text
> ocmd.ExecuteScalar()
> ......
> ....
> ....
> sStmt = "insert ..."
> oCmd = New SqlCommand(sStmt, DBConnection)
> oCmd.CommandText = CommandType.Text
> DBConnection.Open()
> ocmd.ExecuteNonQuery()
> ......
> ....
> ....
> sStmt = "update......"
> oCmd = New SqlCommand(sStmt, DBConnection)
> oCmd.CommandText = CommandType.Text
> DBConnection.Open()
> ocmd.ExecuteNonQuery()
> ......
> ....
> ....
> oCmd.Connection.Close()
> Would previous instances of SqlCommand object be garbage collected
> automatically?
> kd|||Hi Alex,
Thanks for the guidelines.
Regards,
kd
"Alex Papadimoulis" wrote:
> kd,
> This is a .NET question, not SQL Server, but I'll field it.
>
> First off, in your code, you are not reusing the *object* you are reusing
> the *reference* (the variable name) . There is a difference.
> Second, you say "through-out the project" which to me implies global usage
.
> That's a bad, bad, bad practice. Declare, instantiate, Open, Use, and then
> Close your connection only when you need it. No more.
> If you have a series of commands to execute one after another, the pattern
I
> would recommend is this:
> Dim myCommand as SqlCommand
> Dim myConnection as New SqlConnection(ConnectionString)
> myConnection.Open()
> myCommand = New SqlCommand(sql,myConnection)
> myCommand.ExecuteNonQuery
> myCommand = New SqlCommand(sql,myConnection)
> myCommand.ExecuteNonQuery
> ...
> myConnection.Close()
>
> Garbage collection in .NET is not immediate. It's "when it gets to it." Bu
t
> yes, it will be collected. "myConnection = nothing" is uneeded and not
> recommended.
> Even though a connection is automatically closed when GC'ed, I still
> recommend an explicit "myConnection.Close()." This will free up the ADO.NE
T
> connection pool.
> And one final note, consider looking at MSDN naming guidelines. In .NET, w
e
> do not use "sStmt" or "oCmd".
> --Alex Papadimoulis
>
> "kd" wrote:
>|||Hi,
Would you be able to provide links for naming guidelines in .NET?
Thanks
kd
"Alex Papadimoulis" wrote:
> kd,
> This is a .NET question, not SQL Server, but I'll field it.
>
> First off, in your code, you are not reusing the *object* you are reusing
> the *reference* (the variable name) . There is a difference.
> Second, you say "through-out the project" which to me implies global usage
.
> That's a bad, bad, bad practice. Declare, instantiate, Open, Use, and then
> Close your connection only when you need it. No more.
> If you have a series of commands to execute one after another, the pattern
I
> would recommend is this:
> Dim myCommand as SqlCommand
> Dim myConnection as New SqlConnection(ConnectionString)
> myConnection.Open()
> myCommand = New SqlCommand(sql,myConnection)
> myCommand.ExecuteNonQuery
> myCommand = New SqlCommand(sql,myConnection)
> myCommand.ExecuteNonQuery
> ...
> myConnection.Close()
>
> Garbage collection in .NET is not immediate. It's "when it gets to it." Bu
t
> yes, it will be collected. "myConnection = nothing" is uneeded and not
> recommended.
> Even though a connection is automatically closed when GC'ed, I still
> recommend an explicit "myConnection.Close()." This will free up the ADO.NE
T
> connection pool.
> And one final note, consider looking at MSDN naming guidelines. In .NET, w
e
> do not use "sStmt" or "oCmd".
> --Alex Papadimoulis
>
> "kd" wrote:
>|||kd,
[url]http://msdn.microsoft.com/library/en-us/cpgenref/html/cpconnamingguidelines.asp[/u
rl]
-- Alex Papadimoulis
"kd" wrote:
> Hi,
> Would you be able to provide links for naming guidelines in .NET?
> Thanks
> kd
> "Alex Papadimoulis" wrote:
>