Database connection efficiency

[Chris Marks]

I've posted this on a couple forums, but in order to gain a wider
audience, and possible responses I thought i'd post it here too!

I normally connect to a database as follows:

using (SqlConnection connection = new SqlConnection
("connectionString"))
{
connection.Open();
using (SqlCommand command = new SqlComamnd
("storedProc",connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@P1",Value);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
// rest of code to populate tables, etc here
}
}
}
}

However, shortly I'm going from typically serving say 200 users, to
more like 60,000 users, and as such I'd like to ensure that I'm doing
everything I can to create efficient code. I've been looking at other
examples, using the "try/catch/finally" methods, but further reading
supports using "using" more so, due to the fact that it will always
clean up.

With regard to trapping errors in the commands/connections - should I
be using a try/catch/finally block within my "using" blocks?

TIA!

Chris

Thanks!

[Gunawan Hadikusumo]

better this way :

Dim objComm As SqlCommand
Try
objComm = New SqlCommand()
objComm.Connection = New SqlConnection("blah blah connectin string")

,,,,blah...blah...

Catch ex As Exception
,,,,blah...blah...

Finally
If objconn IsNot Nothing Then
objconn.Close()
objconn.Dispose()
End If
If objComm IsNot Nothing Then
objComm.Dispose()
End If
End Try

[Chris Marks]

Apart from the advantage of catching errors there, is that way
*really* any more efficent? (bar it being in VB ;) )

The using blocks handle the disposing, and closing of the objects/
instances created you see... so I guess it's overhead what i'm looking
at. I must try to find some tool to compare two scripts side by side
I guess!

On May 27, 11:40 pm, Gunawan Hadikusumo <john.hadikus...@gmail.com>
wrote:

> better this way :
>
>         Dim objComm As SqlCommand
>         Try
>             objComm = New SqlCommand()
>             objComm.Connection = New SqlConnection("blah blah connectin string")
>
>             ,,,,blah...blah...
>
>         Catch ex As Exception
>             ,,,,blah...blah...
>
>         Finally
>             If objconn IsNot Nothing Then
>                 objconn.Close()
>                 objconn.Dispose()
>             End If
>             If objComm IsNot Nothing Then
>                 objComm.Dispose()
>             End If
>         End Try
>

> > Thanks!- Hide quoted text -
>
> - Show quoted text -

[Cerebrus]

Alright, here's my opinion:

The "Using" construct is functionally equivalent to a Try-Finally
construct. It is a developer convenience and the compiler will convert
the former to the latter. So, there is no difference in performance in
the two. However, the Try-Finally construct does not provide for a
structured way to handle exceptions (it omits the Catch block and all
exceptions are bubbled up to the calling code. This is the point you
need to consider... Does your code require all exceptions to be
handled here itself ? If so, you will have to refactor into Try-Catch-
Finally constructs. If not, you're good with using Using ! (pun
intended)

The code you presented is very well formed. It is hard to be more
efficient except for a single point that I noticed - You open the
Connection early on. For highest performance, you should open your
Connection as late as possible and dispose of it as soon as possible.

IOW, the Connection.Open() call should be just before the
SqlDataReader is created.

[The_Fruitman]

I agree with Cerebrus. The biggest difference between the two
examples of code in this thread is where the handling of exceptions is
done. The other item you may want to verify for efficiency is the
stored procedure code. Can the result set be refined at all and still
allow your code to function correctly? Do you need everything that the
stored procedure returns?