SqlCommand

[Tom]

Hello guys
Please have a look on following paragraph from ".NET Data
Access Architecture Guide".
'''''''''''
Although you can repeatedly use the same SqlCommand object
to execute the same command multiple times, do not reuse
the same SqlCommand object to execute different commands.
1) You do not need to explicitly open or close the
database connection. The SqlDataAdapter Fill method opens
the database connection and then closes the connection
before it returns. If the connection is already open, Fill
leaves the connection open.
2) If you require the connection for other purposes,
consider opening it prior to calling the Fill method. You
can thus avoid unnecessary open/close operations and gain
a performance benefit.
3) Although you can repeatedly use the same SqlCommand
object to execute the same command multiple times, do not
reuse the same SqlCommand object to execute different
commands. (????)
'''''''''''
Now can anybody explain reasons behind last one?
Check the two functions I attach below.
I use the first style often (but never use the second one,
I always use different data adapters to fill data into
dataset from different tables)
Please tell me that where should I apply the third rule.


Private Sub UsingSameCommandToFillReader()
Dim mySqlConnection As SqlClient.SqlConnection
Dim mySqlCommand As SqlClient.SqlCommand
Dim mySqlDataReader As SqlClient.SqlDataReader

mySqlConnection = New SqlClient.SqlConnection("My
Connection String")
mySqlConnection.Open()
mySqlCommand = New SqlClient.SqlCommand()
mySqlCommand.Connection = mySqlConnection

'First Call
mySqlCommand.CommandType = CommandType.StoredProcedure
mySqlCommand.CommandText = "spFirstProcedure"
mySqlCommand.Parameters.Add("@ID", 1)
mySqlDataReader = mySqlCommand.ExecuteReader()
Do While mySqlDataReader.Read
'---- Get Values
Loop
mySqlDataReader.Close()

'Second Call
mySqlCommand.Parameters.Clear()
mySqlCommand.CommandType = CommandType.StoredProcedure
mySqlCommand.CommandText = "spSecondProcedure"
mySqlCommand.Parameters.Add("@Name", "Test")
mySqlDataReader = mySqlCommand.ExecuteReader()
Do While mySqlDataReader.Read
'---- Get Values
Loop
mySqlDataReader.Close()

'Close the Connection
mySqlConnection.Close()
End Sub

Private Sub UsingSameCommandToFillDataSet()
'Please note that I just want to fill the dataset, no
update required.
Dim mySqlConnection As SqlClient.SqlConnection
Dim mySqlCommand As SqlClient.SqlCommand
Dim mySqlDataAdapter As SqlClient.SqlDataAdapter
Dim myDataSet As DataSet

mySqlConnection = New SqlClient.SqlConnection("My
Connection String")
mySqlConnection.Open()
mySqlCommand = New SqlClient.SqlCommand()
mySqlCommand.Connection = mySqlConnection

'First Call
mySqlCommand.CommandType = CommandType.StoredProcedure
mySqlCommand.CommandText = "spFirstProcedure"
mySqlCommand.Parameters.Add("@ID", 1)

mySqlDataAdapter.SelectCommand = mySqlCommand
mySqlDataAdapter.Fill(myDataSet)
'Can I uncomment follwing line to remove the reference of
sqlcommand from data adapter
'mySqlDataAdapter.SelectCommand = Nothing

'Second Call
mySqlCommand.Parameters.Clear()
mySqlCommand.CommandType = CommandType.StoredProcedure
mySqlCommand.CommandText = "spSecondProcedure"
mySqlCommand.Parameters.Add("@Name", "Test")

mySqlDataAdapter.SelectCommand = mySqlCommand
mySqlDataAdapter.Fill(myDataSet)

'Close the Connection
mySqlConnection.Close()
End Sub


Regards
Tom

 

[Cor]

Hi Tom

First of all Tom, it maybe depends if you put things global or create them
in the routines (in the form or separate classes) every time.

But some comments.

Although you can repeatedly use the same SqlCommand object
to execute the same command multiple times, do not reuse
the same SqlCommand object to execute different commands.

For what is this method than
cmd.commandtext = "xxxxxxxxx"

1) You do not need to explicitly open or close the
database connection. The SqlDataAdapter Fill method opens
the database connection and then closes the connection
before it returns. If the connection is already open, Fill
leaves the connection open.

That is so easy to test for yourself, just put a conn.close before the Fill.
(I did not get an error doing that)

2) If you require the connection for other purposes,
consider opening it prior to calling the Fill method. You
can thus avoid unnecessary open/close operations and gain
a performance benefit.

I always keep the Internet user in mind, sometimes he just closes his
browser and plug of his computer.

3) Although you can repeatedly use the same SqlCommand
object to execute the same command multiple times, do not
reuse the same SqlCommand object to execute different
commands. (????)

I don't understand it maybe either, but see my comment with 1.

Just some thoughts.

Cor