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?

Kevin Le - Khnle · Accepted Answer · 2010-06-20 12:02:53Z

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
        }
    }
}

David Neale · Accepted Answer · 2010-06-20 11:48:19Z

6

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.

answered Jun 20, 2010 at 11:48

David Neale

17.1k6 gold badges64 silver badges87 bronze badges

Comments

Willem van Rumpt · Accepted Answer · 2010-06-20 11:49:30Z

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, 2010 at 11:49

Willem van Rumpt

6,5602 gold badges34 silver badges44 bronze badges

7 Comments

Willem van Rumpt Over a year ago

How so not? It's what using the SqlConnection in a using block would end up calling.

user287745 Over a year ago

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?

Willem van Rumpt Over a year ago

Nope. In both cases (using "using", and writing it out explicitely) the finally block will be called. Resulting in SqlConnection.Close() being called.

Marc Gravell Over a year ago

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()

Willem van Rumpt Over a year ago

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?

|

Femaref · Accepted Answer · 2010-06-20 11:48:46Z

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, 2010 at 11:48

Femaref

61.5k8 gold badges145 silver badges176 bronze badges

Comments

Michael · Accepted Answer · 2016-07-01 11:19:24Z

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
            }
        }
    }
}

Collectives™ on Stack Overflow

The C# using statement, SQL, and SqlConnection

5 Answers 5

Comments

Comments

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related