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:
>

Reinitialize pull subscription

i am using merge replication for data sync. in the client i have utility
written in vb that use
merge object. Clients get the dynamic snapshots. My question is:
when i run the job and create a new snapshot for a user,
how can i force the client to use this new sanpshot instead of getting
incremental changes?
thanks so much
by the way i use anonymous pull subscription..
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:efuOUegNGHA.3576@.TK2MSFTNGP15.phx.gbl...
> i am using merge replication for data sync. in the client i have utility
> written in vb that use
> merge object. Clients get the dynamic snapshots. My question is:
> when i run the job and create a new snapshot for a user,
> how can i force the client to use this new sanpshot instead of getting
> incremental changes?
> thanks so much
>
|||If you are referring to an existing client, you would have to reinitialize
the subscription. However I don't see why you would want to do this. Please
can you provide a bit more background info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||for some clients , that do not synchronized for a long time, retention
period expired
therefore some data is lost. so i create a new snapshot with fresh data for
them and i want them to use this new snapshot.
as i said before , they are using a vb utility to synchronize (anonymous
pull subscription).
you know , this utility works like merge agent. i want to do something in
the publisher to make it get the new snapshot.
if the clients remove the utility which deletes the database and install
again , it is working fine. but i dont want them to do that.
thanks so much
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ekMsamhNGHA.3284@.TK2MSFTNGP14.phx.gbl...
> If you are referring to an existing client, you would have to reinitialize
> the subscription. However I don't see why you would want to do this.
> Please can you provide a bit more background info.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||OK - please take a look at "sp_reinitmergepullsubscription".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)