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 UsingSameComman dToFillReader()
Dim mySqlConnection As SqlClient.SqlCo nnection
Dim mySqlCommand As SqlClient.SqlCo mmand
Dim mySqlDataReader As SqlClient.SqlDa taReader

mySqlConnection = New SqlClient.SqlCo nnection("My
Connection String")
mySqlConnection .Open()
mySqlCommand = New SqlClient.SqlCo mmand()
mySqlCommand.Co nnection = mySqlConnection

'First Call
mySqlCommand.Co mmandType = CommandType.Sto redProcedure
mySqlCommand.Co mmandText = "spFirstProcedu re"
mySqlCommand.Pa rameters.Add("@ ID", 1)
mySqlDataReader = mySqlCommand.Ex ecuteReader()
Do While mySqlDataReader .Read
'---- Get Values
Loop
mySqlDataReader .Close()

'Second Call
mySqlCommand.Pa rameters.Clear( )
mySqlCommand.Co mmandType = CommandType.Sto redProcedure
mySqlCommand.Co mmandText = "spSecondProced ure"
mySqlCommand.Pa rameters.Add("@ Name", "Test")
mySqlDataReader = mySqlCommand.Ex ecuteReader()
Do While mySqlDataReader .Read
'---- Get Values
Loop
mySqlDataReader .Close()

'Close the Connection
mySqlConnection .Close()
End Sub

Private Sub UsingSameComman dToFillDataSet( )
'Please note that I just want to fill the dataset, no
update required.
Dim mySqlConnection As SqlClient.SqlCo nnection
Dim mySqlCommand As SqlClient.SqlCo mmand
Dim mySqlDataAdapte r As SqlClient.SqlDa taAdapter
Dim myDataSet As DataSet

mySqlConnection = New SqlClient.SqlCo nnection("My
Connection String")
mySqlConnection .Open()
mySqlCommand = New SqlClient.SqlCo mmand()
mySqlCommand.Co nnection = mySqlConnection

'First Call
mySqlCommand.Co mmandType = CommandType.Sto redProcedure
mySqlCommand.Co mmandText = "spFirstProcedu re"
mySqlCommand.Pa rameters.Add("@ ID", 1)

mySqlDataAdapte r.SelectCommand = mySqlCommand
mySqlDataAdapte r.Fill(myDataSe t)
'Can I uncomment follwing line to remove the reference of
sqlcommand from data adapter
'mySqlDataAdapt er.SelectComman d = Nothing

'Second Call
mySqlCommand.Pa rameters.Clear( )
mySqlCommand.Co mmandType = CommandType.Sto redProcedure
mySqlCommand.Co mmandText = "spSecondProced ure"
mySqlCommand.Pa rameters.Add("@ Name", "Test")

mySqlDataAdapte r.SelectCommand = mySqlCommand
mySqlDataAdapte r.Fill(myDataSe t)

'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