SqlCommand and Data

[Tom]

Hello guys
Please have a look on following paragraph from ".NET Data
Access Architecture Guide".
'''''''''''
If you use a SqlDataAdapter to generate a DataSet or
DataTable, note the following:
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 the third?

Some so-called experts tell me that "It is to clear the
internal reference set by data adapter." but they are
unable to clear which internal reference.

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

 

[William \(Bill\) Vaughn]

The third tip suggests that once a SqlCommand is created, ADO.NET assumes
that the underlying CommandText does not change. In ADO classic developers
often created a single command object and stuffed new CommandText instead of
creating a new Command object. This was not a good idea then and it still
isn't (a good idea).

In your case, you can simply call both SPs in a single Command.


--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________