Database Connection Passing Problem

[shade73]

Hey all. I currently have two seperate namespaces and I'm trying to
pass a connection around to them. I want to use the same connection &
leave it open for 6 methods & then close it. However, all 6 of those
methods use that same connection.

So my solution was to pass the SqlConnection as a parameter in each
method. This works, but it goes super slow. When I changed it back
and put the methods all back in the same namespace & didnt pass the
connection object things went *much* faster. Is there a better (i.e.
quicker) way to accomplish this same thing? Maybe passing a connection
string and starting a new connection every time?? I just don't know.
Any help would be greatly appreciated!

namespace MyNameSpace
{
public static getThisID(string strName, sqlConnection sqlDbCon)
{
System.Data.SqlClient.SqlCommand sqlCommand = new
System.Data.SqlClient.SqlCommand();

// I then assign the connection for this command as the passed
sqlDbCon
sqlCommand.Connection = sqlDbCon;

// do the sql command execute code and then exit

}
}
namespace stupid
{
// pass the connection in every method
MyNameSpace.Database.getThisID(string strName, sqlConnection
sqlDbCon)
MyNameSpace.Database.getAnotherID(string strName, sqlConnection
sqlDbCon)
MyNameSpace.Database.getAndYetAnother(string strName, sqlConnection
sqlDbCon)
(8 times)
}

[Jon Skeet [C# MVP]]

shade73 <tk******@gmail.com> wrote:

Hey all. I currently have two seperate namespaces and I'm trying to
pass a connection around to them. I want to use the same connection &
leave it open for 6 methods & then close it. However, all 6 of those
methods use that same connection.

So my solution was to pass the SqlConnection as a parameter in each
method. This works, but it goes super slow. When I changed it back
and put the methods all back in the same namespace & didnt pass the
connection object things went *much* faster. Is there a better (i.e.
quicker) way to accomplish this same thing? Maybe passing a connection
string and starting a new connection every time?? I just don't know.
Any help would be greatly appreciated!

The namespace should be absolutely irrelevant.

Passing the connection around should be fine. It sounds very odd that
it would be slow. On the other hand, if you don't actually need the
methods to be operating on the same transaction, you could just open
and close the connection each time and rely on the connection pool.
Aside from performance, that would be the simpler way to keep things
clean and make sure you always release things appropriately.

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

[shade73]

Ok, I'll try opening and closing the connection. It will if nothing
else like you said it would be quite a bit cleaner. Thanks for the
quick response

[Peter Bromberg [C# MVP]]

shade73,

Either "pass the connection string around" or put it in an appSettings or
connectionStrings node in your config file and get it from there. Open the
connection in each class / method just before it is used, and close it
immediately thereafter. This is the preferred best practice in almost all
cases; it allows your connections to be handled by Connection Pooling which
is quite efficient at its job.
Peter

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


"shade73" wrote:

Hey all. I currently have two seperate namespaces and I'm trying to
pass a connection around to them. I want to use the same connection &
leave it open for 6 methods & then close it. However, all 6 of those
methods use that same connection.

So my solution was to pass the SqlConnection as a parameter in each
method. This works, but it goes super slow. When I changed it back
and put the methods all back in the same namespace & didnt pass the
connection object things went *much* faster. Is there a better (i.e.
quicker) way to accomplish this same thing? Maybe passing a connection
string and starting a new connection every time?? I just don't know.
Any help would be greatly appreciated!

namespace MyNameSpace
{
public static getThisID(string strName, sqlConnection sqlDbCon)
{
System.Data.SqlClient.SqlCommand sqlCommand = new
System.Data.SqlClient.SqlCommand();

// I then assign the connection for this command as the passed
sqlDbCon
sqlCommand.Connection = sqlDbCon;

// do the sql command execute code and then exit

}
}
namespace stupid
{
// pass the connection in every method
MyNameSpace.Database.getThisID(string strName, sqlConnection
sqlDbCon)
MyNameSpace.Database.getAnotherID(string strName, sqlConnection
sqlDbCon)
MyNameSpace.Database.getAndYetAnother(string strName, sqlConnection
sqlDbCon)
(8 times)
}

[Nick Hounsome]

I don't know what your problem is but I do know that it has absolutely
nothing to do with namespaces so there must be something that you are not
telling us.

Are they in different dlls?
What do you mean by supper slow?

"shade73" <tk******@gmail.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

Hey all. I currently have two seperate namespaces and I'm trying to
pass a connection around to them. I want to use the same connection &
leave it open for 6 methods & then close it. However, all 6 of those
methods use that same connection.

So my solution was to pass the SqlConnection as a parameter in each
method. This works, but it goes super slow. When I changed it back
and put the methods all back in the same namespace & didnt pass the
connection object things went *much* faster. Is there a better (i.e.
quicker) way to accomplish this same thing? Maybe passing a connection
string and starting a new connection every time?? I just don't know.
Any help would be greatly appreciated!

namespace MyNameSpace
{
public static getThisID(string strName, sqlConnection sqlDbCon)
{
System.Data.SqlClient.SqlCommand sqlCommand = new
System.Data.SqlClient.SqlCommand();

// I then assign the connection for this command as the passed
sqlDbCon
sqlCommand.Connection = sqlDbCon;

// do the sql command execute code and then exit

}
}
namespace stupid
{
// pass the connection in every method
MyNameSpace.Database.getThisID(string strName, sqlConnection
sqlDbCon)
MyNameSpace.Database.getAnotherID(string strName, sqlConnection
sqlDbCon)
MyNameSpace.Database.getAndYetAnother(string strName, sqlConnection
sqlDbCon)
(8 times)
}