472,331 Members | 1,627 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,331 software developers and data experts.

How Do I Pass a SqlCommand "by value"


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?
Feb 24 '06 #1
10 3809
Pass a clone of the SqlCommand object.

John Bailo wrote:

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?

Feb 24 '06 #2

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?
Ken Allen wrote:
Pass a clone of the SqlCommand object.

John Bailo wrote:

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?

Feb 24 '06 #3
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

Feb 24 '06 #4
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

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"John Bailo" wrote:

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?

Feb 24 '06 #5
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.

Feb 24 '06 #6
Hi
You can use Clone method.
SqlCommand cmd=new SqlCommand();
.....

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

Feb 24 '06 #7

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/de...thodsTopic.asp

Yury wrote:
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

Feb 24 '06 #8
Mike wrote:
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.
Feb 24 '06 #9
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

Feb 25 '06 #10

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);
}


Mike wrote:
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

Feb 25 '06 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Karunakararao | last post by:
Hi all Presently i am sending data to database filed like this "EquipmentFilterDevPrimaryId = 0" i need Instead of "0" (NULL)i need store...
12
by: Jose Fernandez | last post by:
Hello. I'm building a web service and I get this error. NEWS.News.CoverNews(string)': not all code paths return a value This is the WebMethod...
2
by: IkBenHet | last post by:
Hello, I am uploading a file using this form in ASP.NET. I have also added a simpel textfield: <form runat="server"...
2
by: David Parker | last post by:
I know this question has been discussed, probably multiple times, but I can't seem to access archives.postgresql.org today.... I need to select...
10
by: John Bailo | last post by:
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...
2
by: lauralucas | last post by:
Hello I'm trying to use a connection string stored in web.config here is the web.config part <appSettings> <add key="NDDbase"...
2
by: r_ahimsa_m | last post by:
Hello, I am learning PHP5. I need to parse XML file and I found a solution in some book on PHP5 ("PHP5 Programming" by Rasmus Lerdors and...
21
by: raylopez99 | last post by:
In the otherwise excellent book C# 3.0 in a Nutshell by Albahari et al. (3rd edition) (highly recommended--it's packed with information, and is a...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.