SqlConnection in C# - Safe programming practice

Question

I found this code on the MSDN site here http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.open.aspx:

private static void OpenSqlConnection(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        Console.WriteLine("ServerVersion: {0}", connection.ServerVersion);
        Console.WriteLine("State: {0}", connection.State);
    }
}

My Question is... the site also notes that .Open() can throw InvalidOperationExceptions and SqlExceptions, but this example doesn't look like it handles them.

Is this just because they were being brief with the code, or is there a reason they're not worth handling here? are they possibly handld by the using construct in some way?

.Open() will throw an exception if the connectionString is invalid, the SqlConnection object is null or empty or if no connection could be made to the server (or local database). In that case, they assume that the connection string is valid and the using statement assure that connection isn't null. — DangerMonkey
– DangerMonkey, Commented Apr 30, 2012 at 14:21

Icarus · Accepted Answer · 2012-04-30 14:22:00Z

9

Is this just because they were being brief with the code, or is there a reason they're not worth handling here? are they possibly handld by the using construct in some way?

The using keyword is syntactic sugar for try/finally and even though possible exceptions won't be handled on the code you referenced, the SQL Connection will be disposed properly. They are probably not handling the possible exceptions explicitly because many prefer to let the exception bubble up to the the highest layer and handle the exception there.

answered Apr 30, 2012 at 14:22

Icarus

64k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Humphreys Over a year ago

Thanks everyone, that clears it up alot. I'm coming from a C++ backgorund, so I like to double check on some of these things to make sure they're not doing more than I think they are :)

P.Brian.Mackey · Accepted Answer · 2012-04-30 14:28:31Z

4

MSDN examples are written to provide an easy to read example, not to teach best practices. That's one reason why people should not copy/paste code without understanding it.

Per MSDN

The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called.

It will close the open connection (through the use of finally). It will not catch the exception being thrown. It does so by wrapping the enclosed statement in a try/finally. There is no catch.

edited Apr 30, 2012 at 14:28

answered Apr 30, 2012 at 14:20

P.Brian.Mackey

44.4k71 gold badges249 silver badges360 bronze badges

1 Comment

ChrisLively Over a year ago

Agreed. Part of the problem with doing best practices in example code is that it's much harder for newbies to figure out what's going on.

Dave Bish · Accepted Answer · 2012-04-30 14:20:43Z

3

It depends if there's anything you can "do" upon trapping these exceptions.

If not - its generally considered best practice to let exceptions bubble up the stack, until they reach a point where they can be handled in a meaningful way (which may just be logging a 500 error, in the case of a web-app)

answered Apr 30, 2012 at 14:20

Dave Bish

19.7k7 gold badges50 silver badges63 bronze badges

Comments

Oded · Accepted Answer · 2012-04-30 14:21:09Z

2

These cases are being handled.

The using statement gets translated to a proper dispose pattern that also handles disposal in cases of exceptions.

In this case, even if an exception is thrown, the connection will be disposed of.

The exception itself will bubble up.

See using Statement on MSDN for details.

answered Apr 30, 2012 at 14:21

Oded

501k102 gold badges900 silver badges1k bronze badges

3 Comments

John Humphreys Over a year ago

can you explain this a little more for me or give a link I can read?

Kendall Frey Over a year ago

They are not being handled.

Oded Over a year ago

@KendallFrey - The cases for disposal will be handled regardless of exceptions. Rephrased answer.

Arcturus · Accepted Answer · 2012-04-30 14:41:18Z

1

using (SqlConnection connection = new SqlConnection(connectionString))
    {

    }

is equivalent to

try
{

    SqlConnection connection = new SqlConnection(connectionString)
}
finally
{
   connection.Dispose();
}

"using" is just to make sure that the dispose() method on the object gets called (in this case to make sure the connection gets returned to the connection pool). "using" was never meant to replace catch.

On the projects I have worked on, typically we have a lot of try finally. Catch is used only at the highest level to log it. One reason catch should not be used to re throw errors (as opposed to logging it) is that catch is very resource intensive.

edited Apr 30, 2012 at 14:41

answered Apr 30, 2012 at 14:34

Arcturus

3,0802 gold badges21 silver badges11 bronze badges

Comments

Steve · Accepted Answer · 2012-04-30 14:33:51Z

0

This sample is confusing, while technically correct. In a real world application this sample "as is" has no value.
They don't even return the SqlConnection to a calling code.
So "they were being brief with the code" as you said.

In a real world scenario you could have a method like this

private static SqlConnection OpenSqlConnection(string connectionString) 
{ 
    SqlConnection connection = new SqlConnection(connectionString)
    connection.Open(); 
    return connection;    
}

and then use it in your code (albeit there is no much to gain)

using(SqlConnection cnn = OpenSqlConnection(connectionString))
{
    // Do your work here
    ....
}

of course the using statement hides all the works to catch exceptions and closing/disposing everything so, while, technically speaking the exceptions are handled, in reality you don't get any clues if something fails.

answered Apr 30, 2012 at 14:33

Steve

217k22 gold badges242 silver badges296 bronze badges

4 Comments

John Saunders Over a year ago

What do you mean, "you don't get any clues"? You get an exception, which is a very big "clue".

Steve Over a year ago

@JohnSaunders I was thinking about this. If using is like a try/finally block, what happen if inside the using block we get an exception? Will it bubbles up outside the block and we can take notice or will it be 'eaten' by the finally masked by the closing brace?

Steve Over a year ago

Just found this, reading now to understand better

John Saunders Over a year ago

Using is like Try/Finally, not like Try/Catch. It does not eat exceptions since it doesn't catch them.

Charls · Accepted Answer · 2012-04-30 14:50:56Z

0

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object.Try/catch is expensive. try/catch can affect compiler optimization and what programmer would use try/catch over doing something as simple as checking for null. It is just bad practice. Catching an exception will always be slower than doing a simple check. I'm not saying doing use them but don't use them in place of defensive programming.

Also, looking at the code the "open" command would be called only if there is a valid connection..so no worries....

"Using" is same as putting the object inside a try block and then calling Dispose in a finally block.

If you still need to handle any specific exceptions, include the try..catch..

edited Apr 30, 2012 at 14:50

answered Apr 30, 2012 at 14:29

Charls

3191 silver badge9 bronze badges

1 Comment

John Saunders Over a year ago

-1: using does nothing to prevent the application from crashing.

Collectives™ on Stack Overflow

SqlConnection in C# - Safe programming practice

7 Answers 7

1 Comment

1 Comment

Comments

3 Comments

Comments

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

1 Comment

Comments

3 Comments

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related