SqlConnection in C # - Secure Programming Practices

Question

I found this code on MSDN 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 ... the site also notes that .Open() can call InvalidOperationExceptions and SqlExceptions, but this example does not look like it is processing them.

Is it just because they were concise with the code, or is there a reason they are not standing here? are they possibly somehow related to the use of the construct?

Answer 1

Is it just because they were concise with the code, or is there a reason they are not standing here? maybe they are using the design in some way?

The using keyword is syntactic sugar for try/finally , and although possible exceptions will not be handled in the code you referenced, SQL Connection will be correctly placed. They probably do not handle possible exceptions because many people prefer that the exception get to the highest level and handle the exception.

Answer 2

MSDN examples are written to provide an easy-to-read example, and not to teach best practices. This is one of the reasons why people should not copy / paste code without understanding it.

In MSDN

The using statement calls the Dispose method on the object in (when you use it, as shown above), it also calls the object itself out of scope as soon as Dispose is called.

It will close the open connection (using finally). He will not catch the exception. He does this by wrapping the attached statement in try / finally. No catch.

Answer 3

It depends on if there is anything that you can “do” while catching these exceptions.

If this is not the case, it is usually considered best practice to allow exceptions to stack up until they reach a point where they can be handled in a meaningful way (which can simply log a 500 error in the case of a web application)

Answer 4

These cases are being handled.

The using statement translates to the correct layout template, which also handles deletion in case of exceptions.

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

The exception itself will bubble.

See Using Statement on MSDN for more details.

Answer 5

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

equivalently

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

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

In the projects that I worked on, we usually have a lot of attempts. Catch is used only at the highest level to register it. One of the reasons why the catch should not be used for repeated errors (as opposed to registration) is that the catch is very resource intensive.

Answer 6

The using statement ensures that Dispose is called even if an exception occurs when you call methods on the object. Try / catch is expensive. try / catch can affect compiler optimization, and which programmer will use try / catch, doing something as simple as checking for null. This is just bad practice. Catching an exception will always be slower than a simple check. I am not saying that they use them, but do not use them instead of defensive programming.

In addition, looking at the code, the "open" command will be called only if a valid connection exists. Do not worry....

Using is the same as placing an object inside a try block, and then calling Dispose in a finally block.

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

Answer 7

This example is confusing, although technically correct. In the real world, this “as is” pattern does not matter.
They do not even return SqlConnection to the calling code.
Therefore, "they were concise with the code," as you said.

In a real world scenario, you might have this method

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

and then use it in your code (although there is not much of it)

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

of course, the using statement hides all the work in order to catch exceptions and close / delete everything this way, while technically speaking, exceptions are handled, in fact you don't get any hints if something fails.