How Do I Pass a SqlCommand "by value"

[John Bailo]

I want to pass a SqlCommand object as a input parameter to a method.

I want to pass the SqlCommand "by value" so that any updates to the
original object are *not* reflected in the object within my method.

How can I do this?

 

[Ken Allen]

Pass a clone of the SqlCommand object.

 

[John Bailo]

Are you talking about .MemberwiseClone() ?

That seems to be a protected method so I cannot access it.

How else can I clone the SqlCommand object?

 

[Guest]

John,
In my opinion the safest and least error-prone way to do this is to create a
new SqlCommand -- inside your method -- and copy all the properties and
any parameters from the one that was passed in. Then you can do whatever you
want to the new guy and nothing in the original will change.

SqlCommand is a reference type and follows reference type semantics and
behavior. Note that there is slightly different treatment between VB.NET and
C# in this regard.
Peter

 

[Yury]

use Clone():

class Program
{
static void Main(string[] args)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "test";
cmd.Parameters.Add("@test", SqlDbType.BigInt);

SomeMethod((SqlCommand)cmd.Clone());

Console.WriteLine(cmd.Parameters.Count);
Console.WriteLine(cmd.CommandText);
Console.ReadLine();
}

static void SomeMethod(SqlCommand cmd)
{
cmd.CommandText = "test1";
cmd.Parameters.Clear();
}
}

result :
1
test

 

[Mike]

You cannot pass a reference type "by value". Let's review "by val"/
"by ref".

By default when you pass a variable reference into a method, it depends
on if that variable type is a reference type or a value type. Most
types, like SqlCommand, are a reference type. All the basic types like
int, bool, long, are value types.

Value types are stored on the "stack". When they are passed into a
method, that method gets it's own copy of that varible value. So that
value is now on the stack twice. So any changes to the copy is not
reflected in the calling method.

reference types are stored in the "heap". A pointer to that object
instance is passed into a method. That method gets it's own pointer to
that same object instance. Since that method has a pointer to the
orginal object, any changes to that object are affecting the "original"
object. There is NO way to get around this. What the calling method
cannot do is reassign that instance to a new instance of that type.
Well it can, but then it now points to that new instance, and any
changes to it does not affect the original.

There are two operators you can apply to a method parameter "out" and
"ref". When you apply either of these to a reference type the calling
method can reassign the object to a new instance. Then the keyword
causes that reassignment to affect the orginal calling methods pointer
to the object to now also point at the new instance.

So, in short, you can prevent a called method from reassigning a
varible to a new instance, but you cannot prevent it from modifying the
instance that you pass in.

But what you can do is pass in a copy of the original object. You can
do that for any type that supports "ICloneable" via the Clone() method.
The SqlCommand class does implement ICloneable, however, it has
"hidden" the method. You would have to cast your command to ICloneable
to do the clone. I've never tested this, so I'm not sure if it would
work.

Code:
SqlCommand myCommand = new SqlCommand(...)
ICloneable myCloneableComand = (ICloneable)myCommand;
SomeClass obj = new SomeClass()
obj.SomeMethod(myCloneableComand.Clone());
// anything SomeMethod did is not reflected in 'myCommand'.

Beware, however, as I believe this is just a shallow copy. Meaning
that any properties that are a reference type will represent the same
object in both SqlCommand instances. For instance, the Connection
property is a SqlConnection. So any change made to the connection of
the command passed into that "SomeMethod" will affect the original
'myCommand" instance as well.

I question what you would want to do in "obj.SomeMethod"? If it just
needs to react to a given property of the command, just pass in that
property value only. Can you give an example of what you are trying to
accomplish.

 

[Aboulfazl Hadi]

Hi
You can use Clone method.
SqlCommand cmd=new SqlCommand();
.....

DoSomeThings(cmd.Clone());
......

 

[John Bailo]

Is .Clone() only available in .NET 2.0 ?

I'm using 1.1 and VS.2003 and it doesn't seem to be a member of SqlCommand:

http://msdn.microsoft.com/library/d...SystemDataSqlClientSqlCommandMethodsTopic.asp

 

[John Bailo]

Code:
SqlCommand myCommand = new SqlCommand(...)
ICloneable myCloneableComand = (ICloneable)myCommand;
SomeClass obj = new SomeClass()
obj.SomeMethod(myCloneableComand.Clone());
// anything SomeMethod did is not reflected in 'myCommand'.

This would be all I would need, do you think it would work:

ICloneable myCloneableComand = (ICloneable)cmd;
SqlCommand cmd1 = (SqlCommand) myCloneableComand.Clone();

Beware, however, as I believe this is just a shallow copy. Meaning
that any properties that are a reference type will represent the same
object in both SqlCommand instances. For instance, the Connection
property is a SqlConnection. So any change made to the connection of
the command passed into that "SomeMethod" will affect the original
'myCommand" instance as well.

How about the Parameters collection?

That is all I really care about.

I question what you would want to do in "obj.SomeMethod"? If it just
needs to react to a given property of the command, just pass in that
property value only. Can you give an example of what you are trying to
accomplish.

I want to set up a set of SqlCommands to run on the ThreadPool.

I iterate through a loop, change the value of parameters of SqlCommand.

Then I want to put the SqlCommand on a method which will run
..ExecuteNonQuery() and add this to a ThreadPool.

 

[Mike]

The Parameters collection is a reference type. The same would occur as
with the connection and transaction properties. If multiple threads
access their own clone of the command, they will most likely be working
with the same parameters collection. So I would discourage passing
command clones to multiple threads.

You also don't want to use the same SqlConnection instance
(SqlCommand.Connection property) with multiple commands on separate
threads. You would get runtime errors that the connection is currently
in use when two commands are being executed with that connection at the
same time. So you may not see a problem when only you access a page on
localhost, but you would if you had simultaneous users.

There's no harm in building the connection and command multiple times
(once per thread). That wouldn't be the cause of any performance
problems. The execution of a command, and it's use of a database
resource is the more precious resource.

I would recommend you take a look at an open source component I
authored at:
http://sourceforge.net/projects/xqs-data/
http://sourceforge.net/projects/xqs-provider/

These components use object pooling where each object in the pool
instantiates it's own connections so that you don't get multiple
commands executing on the same connection.

Michael Lang

 

[John Bailo]

I went ahead and implemented that slightly modified version of your code
and it seems to work perfectly:


ICloneable myCloneableComand = (ICloneable)cmd;
SqlCommand cmd1 = (SqlCommand) myCloneableComand.Clone();
ThreadPool.QueueUserWorkItem (
new WaitCallback (RunInsertCommand), cmd1);

public void RunInsertCommand (SqlCommand cmd1){

SqlConnection con1 = new SqlConnection(connString);
cmd1.Connection=con1;

try
{
con1.Open();
cmd1.ExecuteNonQuery();
}
catch(Exception e) {
Debug.WriteLine(e.ToString());
}
finally
{
con1.Close();
}}

private void RunInsertCommand(object ocmd)
{
RunInsertCommand ((SqlCommand) ocmd);
}