C# : Multiple SQL connetions? | Tech Off

turrican
Condemnation without investigation is the height of ignorance! - Albert Einstein

I got a WCF service which connects to DB and gets|sets some data via a WCF client. I only do this "SqlConnection sqlConn = new SqlConnection(..." once in the constructor of my class and then I do several SqlCommand MyCommand = sqlConn.CreateCommand(); sometimes at the same time.

Now my question is : Bare in mind several queries are run at the same time by one and|or more client(s), as in if I have a "void listmystuff(string tableID);" it can be called several times from one client or sometimes from different clients but with different "tableID" variable. Do I need to do a "new SqlConnection(..." for each of my queries? I mean, Do I need to put the "new SqlConnection(..." inside my void or is it enough to put it ONCE in the constructor of my class? Also : My class gets created only ONCE as ONE object for each client but it gets used several times ( still same object ) and at the same time sometimes but different "tableID".

I hope I managed to ask this clear enough. My issue is I get a weird error sometimes that sais "Some field is not part of my table" so I was thinking perhaps since I do the "new SqlConnection(..." only once and using the same SqlConnection "everywhere", maybe they get mixed up or something?

Thank you.

Sven Groot
You can't have everything; after all, where would you put it?

You should be able to use the single connection object. In fact, if you use multiple objects in rapid succession they'll use the same connection anyway due to connection pooling.

Do not however keep the connection alive while the app isn't actually using the database. Open the connection, do whatever commands you have to, then close it as soon as possible.

turrican
Condemnation without investigation is the height of ignorance! - Albert Einstein

In reply to Sven Groot

#Sep 9th @ 10:00 PM

Ok but I do not close this at all... my bad.

Now, wouldn't it then be better if I just did the below inside each "void" instead?

1. "SqlConnection sqlConn = new SqlConnection(..."

<my query>

2. close and dispose

jugatsu
Boomer zuner

In reply to turrican

#Sep 9th @ 10:07 PM

using(SqlConnection sqlConn = new SqlConnection(...))
{
<your query>
}

Assures the SqlConnection is closed and disposed automatically no matter how the code block is exited.

turrican
Condemnation without investigation is the height of ignorance! - Albert Einstein

In reply to jugatsu

#Sep 9th @ 11:43 PM

So I should put that "using" inside each "void", right? It basically won't slow down my application or anything doing a new sql connection for each query?

Although, My main question is if I just use "one" connection, will the queries get mixed up giving me wrong results?

Sven Groot
You can't have everything; after all, where would you put it?

In reply to turrican

#Sep 10th @ 12:11 AM

Although, My main question is if I just use "one" connection, will the queries get mixed up giving me wrong results?

No, they shouldn't. EDIT: Unless you're using the same SqlConnection object across more than one thread? I'm not entirely sure but I think that's not safe.

There is no real performance hit from recreating the connection object because ADO.NET uses connection pooling so it reuses the actual connection to the database if possible even when you create a new SqlConnection object.

jugatsu
Boomer zuner

In reply to turrican

#Sep 10th @ 12:11 AM

Yes, the using statement goes inside your method, i.e.:

public void YourMethod()
{
    using(SqlConnection sqlConn = new SqlConnection(...))
    {
        <your code>
    }
}

Or you can declare the object outside the method (or inside, but before the using statement) and initialize it in the using statement.

You can do this with any class that implements IDisposable.

turrican
Condemnation without investigation is the height of ignorance! - Albert Einstein