469,621 Members | 1,806 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,621 developers. It's quick & easy.

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

Nov 20 '05 #1
1 5342
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
Nov 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Donnie Darko | last post: by
10 posts views Thread by Henrik Dahl | last post: by
3 posts views Thread by Jason Huang | last post: by
reply views Thread by Agnes | last post: by
10 posts views Thread by John Bailo | last post: by
1 post views Thread by job | last post: by
reply views Thread by =?Utf-8?B?ZGF2ZQ==?= | last post: by
6 posts views Thread by Frank Hauptlorenz | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.