The C# using statement, SQL, and SqlConnection

Question

Is this possible using a using statement C# SQL?

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

What if there’s a error while opening the connection?

The using statement is try and finally
No catch

So if I catch outside the using brackets will the catch catch the connection opening error?

If not, how to implement this with using the using statement a shown above?

score 19 · Accepted Answer · answered Jun 20 '10 at 12:02

It's possible to do so in C# (I also see that code is exactly shown in MSDN http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx). However, if you need to be defensive and for example log potential exceptions which would help troubleshooting in a production environment, you can take this approach:

private static void CreateCommand(string queryString,
string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
           connectionString))
    {
        try
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            command.Connection.Open();
            command.ExecuteNonQuery();
        }
        catch (InvalidOperationException)
        {
            //log and/or rethrow or ignore
        }
        catch (SqlException)
        {
            //log and/or rethrow or ignore
        }
        catch (ArgumentException)
        {
            //log and/or rethrow or ignore
        }
    }
}

score 5 · Answer 2 · answered Jun 20 '10 at 11:48

If you want to catch any error then you'll need to wrap everything in try - catch block. using blocks simply ensure that non-managed resources are disposed, they cannot handle exceptions.

Also,SqlCommand implements IDisposable, so I'd suggest putting that in a using block as well.

score 2 · Answer 3 · answered Jun 20 '10 at 11:49

2

Just write it out explicitely:

SqlConnection connection = new SqlConnection(connectionString);
try
{
    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}
catch (Exception e)
{
    // ...handle, rethrow. Also, you might want to catch
    // more specific exceptions...
}
finally
{
    connection.Close();
}

answered Jun 20 '10 at 11:49

Willem van Rumpt

6,490
2
32
44

How so not? It's what using the SqlConnection in a using block would end up calling. – Willem van Rumpt Jun 20 '10 at 11:59
some logic problem am i right? a catch occurs then fine catch handled and finally works. but what if error not occur? connection opened-work done-using closes the connection implicitly and then finally also tries to close the closed connection! ERROR..... RIGHT? – user287745 Jun 20 '10 at 12:25
Nope. In both cases (using "using", and writing it out explicitely) the finally block will be called. Resulting in SqlConnection.Close() being called. – Willem van Rumpt Jun 20 '10 at 12:28
1

There's still no need to do the `using` stuff manually; that could just be `try { using(var connection = new SqlConnection(connectionString) {...} } catch (Exception e) {...}` - no need for the `finally` / explicit `connection.Close()` – Marc Gravell Dec 19 '12 at 08:21
Agreed, it's just my personal preferred way of writing it in these case (try-catch-finally); it lines up nicely IMO. But to each his own, of course. On a lighter note: Really? A comment on a question that was Asked & Answered in 2010? – Willem van Rumpt Dec 19 '12 at 09:23
This is not the same as a `Using` block as it stands because `End Using` will call `connection.Dispose` – Matt Wilko Jul 25 '13 at 07:31
As does SqlConnection.Close() (Or rather: Dispose() calls Close(), to be more specific) – Willem van Rumpt Jul 25 '13 at 11:19

score 1 · Answer 4 · answered Jun 20 '10 at 11:48

1

Yes, you can put the using block in a try block, and the following catch will catch any errors related to the try block.

answered Jun 20 '10 at 11:48

Femaref

60,705
7
138
176

score 0 · Answer 5 · answered Jul 01 '16 at 11:19

Add a unique index to the database for the fields and catch the error.

Don't reinstantiate the SQL Connection for each row. Opening and closing a connection is resource intensive. Try something like this:

protected void btn_insert_Click(object sender, EventArgs e)
{
    string connStr = "your connection string";
    SqlCommand cmd;

    using (SqlConnection con = new SqlConnection(connStr))
    {
        con.Open();
        foreach (GridViewRow g1 in GridView1.Rows)
        {
            try
            {
                cmd = new SqlCommand("command text", con);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                //Ignore the relevant Sql exception for violating a sql unique index
            }
        }
    }
}

The C# using statement, SQL, and SqlConnection

5 Answers5

Linked