Sqlcommand with Using Statement

When is using block used for in C#? How to use using block in C#?

Because it also implements IDisposable.

The purpose of Using statement is that when control will reach end of using it will dispose that object of using block and free up memory. its purpose is not only for auto connection close, basically it will dispose connection object and obviously connection also closed due to it.

Its purpose is to free up the resources that we used inside the Using statement.

According to MSDN:

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. 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. Within the using block, the object is read-only and cannot be modified or reassigned.

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler. The code example earlier expands to the following code at compile time (note the extra curly braces to create the limited scope for the object):

NOTE:

You can instantiate the resource object and then pass the variable to the using statement, but this is not a best practice. In this case, the object remains in scope after control leaves the using block even though it will probably no longer have access to its unmanaged resources. In other words, it will no longer be fully initialized. If you try to use the object outside the using block, you risk causing an exception to be thrown. For this reason, it is generally better to instantiate the object in the using statement and limit its scope to the using block.

SqlConnection and SqlCommand with using statement

I think that the way you have it is fine, because the using statement will ensure that the object is disposed of either way.

The C# using statement, SQL, and SqlConnection

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

C# SQL Connection, Command, and Using statements - explicitly close and dispose, or not?

You right described Dispose method called at the end of block using.
https://msdn.microsoft.com/en-us/library/yh598w02.aspx

GC call Finalize() method (not Dispose() directly) of custom object at random time.
https://msdn.microsoft.com/en-us/library/system.object.finalize.aspx

Usually Close method includes in Dispose method and its not needed call both, you must read docs for concrete class.

At your case i would change code to

 public Musician GetMusician(int recordId)
 {
     Musician objMusician = null;

     using(SqlConnection con = new SqlConnection(_connectionString))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "selectMusician";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@id", recordId);

                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        objMusician = new Musician((int) reader["id"]);
                        objMusician.Name = (string) reader["name"];
                    }

                    if objMusician != null)
                    {
                        objMusician.Albums = Albums.GetAlbums((int)objMusician.ID);
                        objMusician.Tours = Tours.GetTours((int)objMusician.ID);
                        objMusician.Interviews = Interviews.GetInterviews((int)objMusician.ID);
                    }
                }
            }
        }    
    return objMusician;
}

SqlCommand (Using Statement / Disposing issue)

The DataAdapter.Fill command will open and close the connection itself, so you don't need the cmd.Connection.Open(). (Ref: the remarks section in http://msdn.microsoft.com/en-us/library/377a8x4t.aspx .)

Using Using for the SqlConnection has the effect of calling .Close on it for you.

The variable cmd becomes eligible for garbage collection once it is out of scope (or earlier if .NET determines it isn't going to be used again).

In your example 2, I'm not sure it's such a good idea to dispose of the cmd before the DataAdapter has used it.

---

[Information from user "JefBar Software Services" in Should I call Dispose on a SQLCommand object? ] At the time of writing, calling .Dispose on an SqlCommand has no effect because of the code in its constructor:

public SqlCommand() : base() {
    GC.SuppressFinalize(this);
}

SQL Connection constructor outside the using statement

From documentation;

As a rule, when you use an IDisposable object, you should declare and
instantiate it in a using statement.

and

You can instantiate the resource object and then pass the variable to
the using statement, but this is not a best practice. In this case,
the object remains in scope after control leaves the using block even
though it will probably no longer have access to its unmanaged
resources. In other words, it will no longer be fully initialized. If
you try to use the object outside the using block, you risk causing an
exception to be thrown. For this reason, it is generally better to
instantiate the object in the using statement and limit its scope to
the using block.

Based on your example, if somethings goes wrong in constructor, of Open method, there is nothing using can do since you use it as a resource after you initialize it.

Sure this is the best way;

using(var Connect = new SqlConnection(IST_DBConnect.SQLConnectionString))
using(var command = Connect.CreateCommand())
{
   // 
} // <-- Both Connect and command will disposed here no matter exception is thrown or not

Using statement - is this more useful for the sql connection than the sql adapter?

Yes, you should use the using statement to dispose everything that implements IDisposable. The using statement is not really about garbage collection, it's about resources (other than memory). Both DataSet and SqlDataAdapter are instances of IDisposable.

The using block means that you guarantee that the resources that the object holds are disposed in a timely, deterministic manner. Without using, there is no guarantee when these resources will be freed. This can lead to resource leaks, such as running out of file handles.

Foreach and SqlConnection Using Statement

You'll have to try it and measure it to be certain, but I doubt you'll see a huge difference either way. Connections are pooled by .NET, so creating them in a loop (I'm assuming you just have a handful of tables, not several hundred) should not be a significant bottleneck. You'll likely spend a LOT more time in network I/O and actually populating the DataSet (which does have a lot of overhead).

Bottom line - fine one way that works (so you have something to revert back to) and try it multiple ways and see if one method makes a significant difference to the application overall.

Why use a using statement with a SqlTransaction?

A using statement should be used every time you create an instance of a class that implements IDisposable within the scope of a block. It ensures that the Dispose() method will be called on that instance, whether or not an exception is thrown.

In particular, your code only catches managed exceptions, then destroys the stack frame by throwing a new exception instead of rethrowing the existing one.

The correct way to do it is:

using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["T3"])) {
    cn.Open();
    using (SqlTransaction tr = cn.BeginTransaction()) {
        //some code
        tr.Commit();
    }
}

Note that if your class has instance members of types that implement IDisposable, then your class must implement IDisposable itself, and dispose of those members during its own Dispose() call.

---

Related Topics