By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,855 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,855 IT Pros & Developers. It's quick & easy.

Passing in SqlConnection by ref

P: n/a
LP
Hello,

Here's the scenario:

Object A opens a Sql Db connection to execute number of SqlCommands. Then it
needs to pass this connection to a constructor of object B which in turn
executes more commands on the same connection.
I have an understanding that if SqlConnection is passed as "value"
(unboxed), object B will create its own copy of SqlConnection, so when
object A closes its connection, it remains open for object B's copy. Is this
correct? Is not the right way to pass it by ref? What are "the best
practices"?

Thank you.
Nov 16 '05 #1
Share this Question
Share on Google+
17 Replies


P: n/a
The SqlConnection is an object and will be passed by reference by default.

Just as a suggestion, you should typically create your SqlConnections in a
using block and access it using a try/catch/finally block, making sure to
close the connection in the finally block.

DalePres
MCAD, MCDBA, MCSE

"LP" <lp@a.com> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...
Hello,

Here's the scenario:

Object A opens a Sql Db connection to execute number of SqlCommands. Then
it
needs to pass this connection to a constructor of object B which in turn
executes more commands on the same connection.
I have an understanding that if SqlConnection is passed as "value"
(unboxed), object B will create its own copy of SqlConnection, so when
object A closes its connection, it remains open for object B's copy. Is
this
correct? Is not the right way to pass it by ref? What are "the best
practices"?

Thank you.

Nov 16 '05 #2

P: n/a
In C#, when you pass this by ref:

public void MyMethod(ref SqlConnection oConn)

it is a pointer "of sorts" back to the original object. You'll
to be sure that you trap all errors in subsequent method calls
with this reference so the master method can ensure the
object gets disposed of and the connection closed.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx


"LP" <lp@a.com> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...
Hello,

Here's the scenario:

Object A opens a Sql Db connection to execute number of SqlCommands. Then
it
needs to pass this connection to a constructor of object B which in turn
executes more commands on the same connection.
I have an understanding that if SqlConnection is passed as "value"
(unboxed), object B will create its own copy of SqlConnection, so when
object A closes its connection, it remains open for object B's copy. Is
this
correct? Is not the right way to pass it by ref? What are "the best
practices"?

Thank you.

Nov 16 '05 #3

P: n/a
I am curious to know why is the ref essential or needed in "ref
SqlConnection oConn"? Since SqlConnection is a reference type, MyMethod will
be referring to the same object that was created from the function that
called Mymethod. Am I missing something basic?

--
Ajay Kalra [MVP - VC++]
aj*******@yahoo.com
"Robbe Morris" <in**@turnkeytools.com> wrote in message
news:#X**************@TK2MSFTNGP10.phx.gbl...
In C#, when you pass this by ref:

public void MyMethod(ref SqlConnection oConn)

it is a pointer "of sorts" back to the original object. You'll
to be sure that you trap all errors in subsequent method calls
with this reference so the master method can ensure the
object gets disposed of and the connection closed.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx


"LP" <lp@a.com> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...
Hello,

Here's the scenario:

Object A opens a Sql Db connection to execute number of SqlCommands. Then it
needs to pass this connection to a constructor of object B which in turn
executes more commands on the same connection.
I have an understanding that if SqlConnection is passed as "value"
(unboxed), object B will create its own copy of SqlConnection, so when
object A closes its connection, it remains open for object B's copy. Is
this
correct? Is not the right way to pass it by ref? What are "the best
practices"?

Thank you.


Nov 16 '05 #4

P: n/a
LP
thank you guys
I am a bit confused. On one hand SqlConnection is an object and passed by
reference (which is a "memory address" or "pointer") by default but on
another hand ref keyword passes in "a pointer"
Does ref keyword makes any difference when dealing with refernce types? What
am I missing here?

"Robbe Morris" <in**@turnkeytools.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
In C#, when you pass this by ref:

public void MyMethod(ref SqlConnection oConn)

it is a pointer "of sorts" back to the original object. You'll
to be sure that you trap all errors in subsequent method calls
with this reference so the master method can ensure the
object gets disposed of and the connection closed.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx


"LP" <lp@a.com> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...
Hello,

Here's the scenario:

Object A opens a Sql Db connection to execute number of SqlCommands. Then it
needs to pass this connection to a constructor of object B which in turn
executes more commands on the same connection.
I have an understanding that if SqlConnection is passed as "value"
(unboxed), object B will create its own copy of SqlConnection, so when
object A closes its connection, it remains open for object B's copy. Is
this
correct? Is not the right way to pass it by ref? What are "the best
practices"?

Thank you.


Nov 16 '05 #5

P: n/a
LP
> I am curious to know why is the ref essential or needed in "ref
SqlConnection oConn"? Since SqlConnection is a reference type
me too

"Ajay Kalra" <aj*******@yahoo.com> wrote in message
news:u1**************@TK2MSFTNGP12.phx.gbl... I am curious to know why is the ref essential or needed in "ref
SqlConnection oConn"? Since SqlConnection is a reference type, MyMethod will be referring to the same object that was created from the function that
called Mymethod. Am I missing something basic?

--
Ajay Kalra [MVP - VC++]
aj*******@yahoo.com
"Robbe Morris" <in**@turnkeytools.com> wrote in message
news:#X**************@TK2MSFTNGP10.phx.gbl...
In C#, when you pass this by ref:

public void MyMethod(ref SqlConnection oConn)

it is a pointer "of sorts" back to the original object. You'll
to be sure that you trap all errors in subsequent method calls
with this reference so the master method can ensure the
object gets disposed of and the connection closed.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx


"LP" <lp@a.com> wrote in message
news:uO**************@TK2MSFTNGP10.phx.gbl...
Hello,

Here's the scenario:

Object A opens a Sql Db connection to execute number of SqlCommands. Then it
needs to pass this connection to a constructor of object B which in turn executes more commands on the same connection.
I have an understanding that if SqlConnection is passed as "value"
(unboxed), object B will create its own copy of SqlConnection, so when
object A closes its connection, it remains open for object B's copy. Is this
correct? Is not the right way to pass it by ref? What are "the best
practices"?

Thank you.



Nov 16 '05 #6

P: n/a
DalePres <don-t-spa-m-me@lea-ve-me-a-lone--.com> wrote:
The SqlConnection is an object and will be passed by reference by default.
No it won't. There's a big difference between an object being passed by
reference and a reference being passed by value.

See http://www.pobox.com/~skeet/csharp/parameters.html
Just as a suggestion, you should typically create your SqlConnections
in a using block and access it using a try/catch/finally block,
making sure to close the connection in the finally block.


The point of the using block is that that will automatically close the
connection for you. There's no need for a separate finally block.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #7

P: n/a
LP <lp@a.com> wrote:
thank you guys
I am a bit confused. On one hand SqlConnection is an object and passed by
reference (which is a "memory address" or "pointer") by default but on
another hand ref keyword passes in "a pointer"
It's not passed *by* reference - the value which is passed *is* a
reference to start with. "ref" essentially passes the *variable* by
reference - changes to the value of the variable (not just the value of
the object it refers to) are then visible to the caller.

Unfortunately a lot of people are very loose with their terminology,
which causes confusion.
Does ref keyword makes any difference when dealing with refernce types? What
am I missing here?


See http://www.pobox.com/~skeet/csharp/parameters.html

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #8

P: n/a
LP wrote:
Hello,

Here's the scenario:

Object A opens a Sql Db connection to execute number of SqlCommands. Then it
needs to pass this connection to a constructor of object B which in turn
executes more commands on the same connection.
I have an understanding that if SqlConnection is passed as "value"
(unboxed), object B will create its own copy of SqlConnection, so when
object A closes its connection, it remains open for object B's copy. Is this
correct? Is not the right way to pass it by ref? What are "the best
practices"?


No, that's not true. SqlConnection is a reference type so you can just
pass on the member variable:

// in A
private SqlConnection _myConnection;

//...

_myConnection = new SqlConnection(_connectionString);

// pass on to B

B myB = new B(_myConnection);

Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Nov 16 '05 #9

P: n/a
"LP" <lp@a.com> wrote:
thank you guys
I am a bit confused. On one hand SqlConnection is an object and passed by
reference (which is a "memory address" or "pointer") by default but on
another hand ref keyword passes in "a pointer"
Does ref keyword makes any difference when dealing with refernce types? What
am I missing here?


It's understandable you're confused. I understand what's happening and after
reading the explanations I'm confused. :-)

Let's try it this way...

When you pass an object to a method using the default calling syntax, you are
always passing a copy of the reference to the object (the address).

If the method has the argument declared as ref, you are passing the address of
the address, meaning you could replace the original object. Let's look at a
couple of examples.

Let's say the connection argument is called "conn". In the called method if you
did:

conn.ConnectionString = "..."

The original object (the one you passed in) would have its connection string
changed regardless of whether it was passed by reference or by value.

The real difference comes in where your method creates a new object using the
same reference variable (pointer).

private void MyMethod( SqlConnection conn )
{
conn = new SqlConnection(...);
}

private void MyMethod( ref SqlConnection conn )
{
conn = new SqlConnection(...);
}

In the first example, there are now two SqlConnection objects, one pointed to by
the copy of the address and one pointed to by the original reference variable
(in the calling routine). When you return to the calling routine you would find
the original unchanged. You've said "Here's a copy of the original address to
the object. If you put a new address in its place that's cool, I still have the
original."

In the second example you're telling the framework to replace the original
connection object (the one passed in) with the new one. You've said "Here's
where you'll find the original pointer to the object. Please replace it with a
new pointer."

Yet another way to look at it is this:
a) Passing an object by value is like sending a fax. You've still got the
original in case someone screws up the faxed copy.

b) Passing an object by ref is like sending the original document by courier. If
they mark it up and send it back you've got no way to get back to the original.

--
Craig Wagner, craig.wagner(at)comcast.net
Portland, OR

"Don't ban high-performance vehicles, ban low-performance drivers!"
Nov 16 '05 #10

P: n/a
LP
Craig, Now, that's what I call a good explanation. Thanks a lot.

My concern was if I pass in connection to another method, I wanted to make
sure that both objects will be working with same instance of connection
object. So it can closed/disposed in one place. I thought I needed to pass
it in with "ref" to accomplish that.
From what you're saying as long as I don't create a new instance in a
receiving method, it will be the same instance of conn object, since it's a
reference type. So "ref" is not really required, though it would more or
less do the same thing (it's a true pointer as oppose to reference type).
Please let me know if my interpretations are correct.

Thanks again.

"Craig Wagner" <cr************@hotmail.com> wrote in message
news:2n********************************@4ax.com...
"LP" <lp@a.com> wrote:
thank you guys
I am a bit confused. On one hand SqlConnection is an object and passed by
reference (which is a "memory address" or "pointer") by default but on
another hand ref keyword passes in "a pointer"
Does ref keyword makes any difference when dealing with refernce types? Whatam I missing here?
It's understandable you're confused. I understand what's happening and

after reading the explanations I'm confused. :-)

Let's try it this way...

When you pass an object to a method using the default calling syntax, you are always passing a copy of the reference to the object (the address).

If the method has the argument declared as ref, you are passing the address of the address, meaning you could replace the original object. Let's look at a couple of examples.

Let's say the connection argument is called "conn". In the called method if you did:

conn.ConnectionString = "..."

The original object (the one you passed in) would have its connection string changed regardless of whether it was passed by reference or by value.

The real difference comes in where your method creates a new object using the same reference variable (pointer).

private void MyMethod( SqlConnection conn )
{
conn = new SqlConnection(...);
}

private void MyMethod( ref SqlConnection conn )
{
conn = new SqlConnection(...);
}

In the first example, there are now two SqlConnection objects, one pointed to by the copy of the address and one pointed to by the original reference variable (in the calling routine). When you return to the calling routine you would find the original unchanged. You've said "Here's a copy of the original address to the object. If you put a new address in its place that's cool, I still have the original."

In the second example you're telling the framework to replace the original
connection object (the one passed in) with the new one. You've said "Here's where you'll find the original pointer to the object. Please replace it with a new pointer."

Yet another way to look at it is this:
a) Passing an object by value is like sending a fax. You've still got the
original in case someone screws up the faxed copy.

b) Passing an object by ref is like sending the original document by courier. If they mark it up and send it back you've got no way to get back to the original.
--
Craig Wagner, craig.wagner(at)comcast.net
Portland, OR

"Don't ban high-performance vehicles, ban low-performance drivers!"

Nov 16 '05 #11

P: n/a
LP <lp@a.com> wrote:
Craig, Now, that's what I call a good explanation. Thanks a lot.

My concern was if I pass in connection to another method, I wanted to make
sure that both objects will be working with same instance of connection
object. So it can closed/disposed in one place. I thought I needed to pass
it in with "ref" to accomplish that.
From what you're saying as long as I don't create a new instance in a
receiving method, it will be the same instance of conn object, since it's a
reference type. So "ref" is not really required, though it would more or
less do the same thing (it's a true pointer as oppose to reference type).
Please let me know if my interpretations are correct.


That sounds about right. Don't use ref unless you need to though - it
would suggest to someone reading the code that you were going to change
the value of the variable within the method.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #12

P: n/a
"LP" <lp@a.com> wrote:
Craig, Now, that's what I call a good explanation. Thanks a lot.
No problem. Hope it helped.
reference type. So "ref" is not really required, though it would more or
less do the same thing (it's a true pointer as oppose to reference type).
Please let me know if my interpretations are correct.


Using "ref" is not required. There is only one instance of the object.

However, the interpretation isn't quite right.

When passing by value, you're sending the location of the object in memory to
the called routine (i.e. the address, or a pointer, or a reference, they're all
synonymous). But the important thing to remember is that you're sending a copy
of the address. Assigning something to that variable in the called routine will
not orphan the original object. A fax.

When passing by reference, you're sending the address of the address. You're
telling the called routine where the pointer to the object is located. If the
called routine does a 'new' operation and assigns it to the passed-in variable
you will lose the original object. Not good.

I just thought of another analogy. It's a slight twist on my original but it may
be more clear (although it still isn't 100% accurate the whole way through).

Passing by value is like attaching a document to an e-mail.

Passing by reference is like putting the UNC of the document in the e-mail.

The difference in the analogy is that in the first case you actually end up with
two copies of the document, which is the part where the analogy breaks down,
because you don't have two copies of the object in the case we're discussing.

--
Craig Wagner, craig.wagner(at)comcast.net
Portland, OR

"Don't ban high-performance vehicles, ban low-performance drivers!"
Nov 16 '05 #13

P: n/a
LP
Craig, I am sorry, but I cant leave at this.

I think I understand the concepts, but I think your analogies are flawed.
When you say sending a fax or a copy of document attachment, you're implying
that if a recipient changes the content of the document (data of the
object), the caller then wont be able to see them. However I don't think
that is technically correct. Reference types are passed by reference by
default which means that a copy of REFERENCE is made not an object (or
object content/data) itself. So any changes made by a recipient will BE seen
by a sender. Where in fax or email attachment it is not the case. But if a
recipient purges a reference (as oppose to object data) by say setting it to
null or new Object Instance, sender still has the original address to an
object (content/data).
So, not sure if fax or email attachment works in this case. The code bellow
demonstrates just that:

private void btnFax_Click(object sender, System.EventArgs e)

{

StringBuilder Fax = new StringBuilder();

Fax.Append("Original Fax");

FaxTo(Fax);

MessageBox.Show( Fax.ToString());

}

private void FaxTo(StringBuilder recievedFax)

{
recievedFax.Append(" -- Added more content to original");

recievedFax = null;

}

The better analogy would be if it makes sense.
I posted a document on a website. Then I am sending you an email with an URL
to that website where you go to and change its content which will be visible
by me as well. But if you lose the URL it's ok, because I still have that
original link. And adding a "ref" is like giving you an ability to change or
destroy URL to the website, so no one can ever get to it.
Makes sense?
Thanks
Nov 16 '05 #14

P: n/a
LP <lp@a.com> wrote:
I think I understand the concepts, but I think your analogies are flawed.
When you say sending a fax or a copy of document attachment, you're implying
that if a recipient changes the content of the document (data of the
object), the caller then wont be able to see them. However I don't think
that is technically correct. Reference types are passed by reference by
default which means that a copy of REFERENCE is made not an object (or
object content/data) itself.


No, reference types are *not* passed by reference. The reference is
passed by value, which is not the same thing.

See http://www.pobox.com/~skeet/csharp/parameters.html

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #15

P: n/a
"LP" <lp@a.com> wrote:
Craig, I am sorry, but I cant leave at this.

I think I understand the concepts, but I think your analogies are flawed.
I said the analogies break down at a certain point. They were not intended to
convey what the sender sees if you change the item. They were simply intended to
convey the difference between sending a copy of the address and the address of
the address.
object), the caller then wont be able to see them. However I don't think
that is technically correct.
It's not. Again, the analogy wasn't intended to convey that aspect of it.
Reference types are passed by reference by
default which means that a copy of REFERENCE is made not an object (or
object content/data) itself. So any changes made by a recipient will BE seen
by a sender.
Yes.
recipient purges a reference (as oppose to object data) by say setting it to
null or new Object Instance, sender still has the original address to an
object (content/data).


Yes.

--
Craig Wagner, craig.wagner(at)comcast.net
Portland, OR

"Don't ban high-performance vehicles, ban low-performance drivers!"
Nov 16 '05 #16

P: n/a
Jon is right but I would like to explain this subject in an easier way to
understand.

I think that what everybody should remember is that by default (without any
'ref' or 'out' keyword) is that ANY kind of parameters are ALWAYS passed by
value. If it is a Reference type then a copy of the reference is passed. If
it is a Value type then an copy of the value is passed. Simple isn't it?

If you see it that way there is never any possible way to get confused as
there is nothing to remember, parameters are by default passed by value
whatever the type of the parameter is. If you add the "ref" or "out" keyword
then it is passed by reference whatever the type of the parameter is.

To be honest I think that maybe the naming convention are confusing and we
should say "passed by copy" instead of "passed by value". Then people would
not get confused between the parameter type and the fact that we pass the
parameter by value or reference. As same adjectives describe different
things.

Now a little off topic:
Now a more interesting question is when to use "ref" and "out"?
I personally sometimes use the "out" keyword when a method needs to return
more than 1 object. It is a way to mark a parameter as being of an output
type and that the calling method can get results in those "out" parameters.
I see the use of ref for similar purpose. Anyway, each time you will need to
use "ref" and "out", it will be for very specific cases as even when you
pass a reference type by value the method can still change the members of
that object and the calling method will still see the change. Suits for 99%
of cases, I believe.

Francois.

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
LP <lp@a.com> wrote:
I think I understand the concepts, but I think your analogies are flawed. When you say sending a fax or a copy of document attachment, you're implying that if a recipient changes the content of the document (data of the
object), the caller then wont be able to see them. However I don't think
that is technically correct. Reference types are passed by reference by
default which means that a copy of REFERENCE is made not an object (or
object content/data) itself.


No, reference types are *not* passed by reference. The reference is
passed by value, which is not the same thing.

See http://www.pobox.com/~skeet/csharp/parameters.html

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Nov 16 '05 #17

P: n/a
LP
I am glad we are all on the same page now.

"Craig Wagner" <cr************@hotmail.com> wrote in message
news:40********************************@4ax.com...
"LP" <lp@a.com> wrote:
Craig, I am sorry, but I cant leave at this.

I think I understand the concepts, but I think your analogies are flawed.
I said the analogies break down at a certain point. They were not intended

to convey what the sender sees if you change the item. They were simply intended to convey the difference between sending a copy of the address and the address of the address.
object), the caller then wont be able to see them. However I don't think
that is technically correct.


It's not. Again, the analogy wasn't intended to convey that aspect of it.
Reference types are passed by reference by
default which means that a copy of REFERENCE is made not an object (or
object content/data) itself. So any changes made by a recipient will BE seenby a sender.


Yes.
recipient purges a reference (as oppose to object data) by say setting it tonull or new Object Instance, sender still has the original address to an
object (content/data).


Yes.

--
Craig Wagner, craig.wagner(at)comcast.net
Portland, OR

"Don't ban high-performance vehicles, ban low-performance drivers!"

Nov 16 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.