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
