• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

SqlConnection Close and or Dispose

Hello Experts,

Is it a good practice to use both Close and Displose?


For example:

    protected void RetrieveSomeValues()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "YourStoredProcedure";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;

            try
            {
                conn.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                ddlCourseName.DataSource = rdr;
                ddlCourseName.DataBind();
            }

            catch (Exception ex)
            {
                ex.Message.ToString();
            }

            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
    }
0
asp_net2
Asked:
asp_net2
  • 13
  • 12
  • 11
  • +1
2 Solutions
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, by enclosing your disposable object in a using keyword you will guarantee that your object will be disposed when your exit the code scope, so, is redundant to call Close and Dispose inside your using scope. So it is not a good practice.

You only need this:
protected void RetrieveSomeValues()
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "YourStoredProcedure";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        try
        {
            conn.Open();

            SqlDataReader rdr = cmd.ExecuteReader();

            ddlCourseName.DataSource = rdr;
            ddlCourseName.DataBind();
        }

        catch (Exception ex)
        {
            ex.Message.ToString();
        }
    }
}

Open in new window

0
 
käµfm³d 👽Commented:
Is it a good practice to use both Close and Displose?
Yes. However, one thing you may not be aware of is that a using statement automatically calls the Dispose method on the object(s) you declare with it, and in this case the Dispose method of SqlConnection calls Close under the hood. This is why you can only use a using statement with classes which implement the IDisposable interface.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:

 in this case the Dispose method of SqlConnection calls Close under the hood
Of course, in this case, for the SqlConnection, calling Close and Dispose is redundant, because that I consider that is not a good practice, but it can be different for another class that implement the IDisposable interface as Kaufmed said, for example, a Stream class can have a inner stream but is possible that when your call the Dispose method of that Stream instance it leave open the inner Stream, that depend of how it is implemented.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
asp_net2Author Commented:
So is it always a good idea to wrap code into a "using" statement? If not, then should I call Close and Dispose or just Close or just Dispose?
0
 
asp_net2Author Commented:
How would I know which Class(es) implement the IDisposable interface for future reference so that I know when I need to use Close/Displose or either or by itself?

So far, I only work with CRUD against Databases. Every once and a while I need to upload/download PDF or Images to the Database for storage rather than the File System.
0
 
käµfm³d 👽Commented:
So is it always a good idea to wrap code into a "using" statement?
Not always. I have read that wrapping up web service client objects in a using can often lead to weird (unmeaningful) exceptions being raised.

If not, then should I call Close and Dispose or just Close or just Dispose?
If a class exposes a Close method, then you should probably call it--don't rely on dispose doing it for you. Some classes call Close within their Dispose methods (e.g. SqlConnection), but not all of them (as yv989c noted). It's up to the author of the class as to what Dispose does, and you'll need to refer to the documentation for the class as to what Dispose does or doesn't do.

The intent of the Dispose method is generally to release any un-managed resources your class may be holding (e.g. network connections, file handles, graphics handles, etc.).
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Is a good idea, because you dont need to worry is your code raise an exception, in this case your connection will be closed.

By using the using keyword you are doing this internally:
protected void RetrieveSomeValues()
{
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString);
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "YourStoredProcedure";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            conn.Open();

            SqlDataReader rdr = cmd.ExecuteReader();

            ddlCourseName.DataSource = rdr;
            ddlCourseName.DataBind();
        }
        catch (Exception ex)
        {
            ex.Message.ToString();
        }
        finally
        {
             conn.Dispose();
        }
}

Open in new window

0
 
käµfm³d 👽Commented:
P.S.

I see that my first comment above is a tad vague. Generally speaking, using a using is a good idea. The only scenario that I've seen/read about that it is slightly unreliable is when using web service client classes.
0
 
P1ST0LPETECommented:
I think it has become best practice in the modern day to wrap your SQL connection in a using statement due to close and dispose being handled for you.  However, that is not to say that a using statement is needed in all cases.

Also, check out the MSDN documentation:
If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes, the underlying connection is returned back to the connection pool. On the other hand, if Pooling is set to false or no, the underlying connection to the server is closed.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx
0
 
asp_net2Author Commented:
@yv989c:

Should I be wrapping my SqlCommand properties into the Try as you stated in post 36911096 or is that user preference?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, no, that is not necessary for the SqlCommand class, it implement the IDisposable interface but when you call Dispose on that class it do nothing (confirmed with .net reflector), however the SqlTransaction class requires it.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
ehhh sorry I misunderstood your last question :p ... that was just an example, yes is that user preference, the important thing is to be sure implement the using keyword for these object that you know take system resources, like sockets, file handlers, memory, etc...
0
 
asp_net2Author Commented:
@yv989c,

Ok, what article/site can I find this information out on when programming and exactly what am I looking for in the link you can supply me?

Will the .NET Framework Class Library link work? if so, how and where do I drill down to find this information? I get very overwellmed when looking at that site.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, as kaufmed said:

It's up to the author of the class as to what Dispose does, and you'll need to refer to the documentation for the class as to what Dispose does or doesn't do.

For the .net framework classes you have a huge source of information, this is MSDN:
http://msdn.microsoft.com/en-us/library/gg145045(v=VS.100).aspx

For more info about the using statement:
http://msdn.microsoft.com/en-us/library/yh598w02.aspx

I dont understand this question:
Will the .NET Framework Class Library link work?
Can you clarify please.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
I understand (sorry my english is not good), you are talking about the .NET Framework Class Library on MSDN, right?
Then the previous link is what do you need, first you must know what namespace contain the class that you want to explore, example, SqlConnection resides in System.Data.SqlClient namespace, so when you see the documentation you must find the System.Data.SqlClient namespace, inside that you will find the SqlConnection class docs.

Also you can do a simple Google search, like:
System.Data.SqlClient.SqlConnection (to be more specific)
OR
SqlConnection

For sure the first result will be the class documentation on MSDN.
0
 
asp_net2Author Commented:
>> I understand (sorry my english is not good), you are talking about the .NET Framework Class Library on MSDN, right?
 
Yes, but what I need to know is what do I look for once that is pulled up? Methods, Events, Properties etc...
0
 
P1ST0LPETECommented:
Yes, you can find lots of information about programming using the .NET framework at MSDN (Microsoft Developer Network) http://msdn.microsoft.com/en-us/

However, as you discovered, it can be quite overwhelming when first encountering the site.  The information is all namespaced based, so if you know the namespaces of class libraries, then you can find them in their relative locations on msdn.  However, it can also be much easier to just google exactly what you ware looking for, for example if you were to google "sqlconnection.close", then the very top link would be to msdn already drilled down to the correct spot.
0
 
käµfm³d 👽Commented:
I agree--searching for the exact namespace-class combination usually does the trick. Searching for just the class name can often inter-mix Java documentation in your results, since some classes have the same names in both languages. One of the nice things about MSDN is that there are links to other related areas at the bottom of the page. For instance, if you search "sqlconnection.close", as P1ST0LPETE suggested, you get something like this (first search result) at the bottom:

Screenshot of Additional Resources
There are links to the documentation of the class itself as well as the containing namespace. If you were to go to the class' documentation page, you would find a list of all the methods, properties, and events of that class. The "See Also" section of the MSDN pages can be indispensable at times  = )
0
 
asp_net2Author Commented:
To all,

I will defenity read those articles about this post. In the meantime to get me going does the following look ok to use for Retrieving Data and Inserting Data?

    protected void RetrieveCourseValues()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "HealthCourses_RetrieveCoursesValues";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;

            try
            {
                conn.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                ddlCourseName.DataSource = rdr;
                ddlCourseName.DataBind();
            }

            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }
    }

    protected void btn_InsertCourseProgram_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "HealthCourses_InsertCourseProgram";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;

            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }
    }
0
 
käµfm³d 👽Commented:
Other than swallowing exceptions and using the general Exception as opposed to named exceptions (e.g. SqlException), it should be fine for your data access. I can't recall if you can data-bind to a DataReader, so you may want to check line 16 above.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
That looks good, I have rewrote it with my style ;) also notice the using on the SqlDataReader object:
protected void RetrieveCourseValues()
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand("HealthCourses_RetrieveCoursesValues", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            conn.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                ddlCourseName.DataSource = rdr;
                ddlCourseName.DataBind();
            }
        }
        catch (Exception ex)
        {
            //ex.Message.ToString(); ???
        }
    }
}

protected void btn_InsertCourseProgram_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HealthCourses"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand("HealthCourses_InsertCourseProgram", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //ex.Message.ToString(); ???
        }
    }
}

Open in new window


Also, is a good practice to use the full name of your objects in your Sql Server DB, example, if your stored procedure HealthCourses_InsertCourseProgram is contained in the dbo schema then you must call it with like:
SqlCommand cmd = new SqlCommand("dbo.HealthCourses_InsertCourseProgram", conn);

Open in new window

0
 
asp_net2Author Commented:
Yes, you can data bind to SqlDataReader, DataTable, DataSet.

What do you mean by the following below?

>> Other than swallowing exceptions and using the general Exception as opposed to named exceptions (e.g. SqlException), it should be fine for your data access
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
@kaufmed
I can't recall if you can data-bind to a DataReader
Yes he can.
0
 
käµfm³d 👽Commented:
Other than swallowing exceptions
Let's say you deployed this application and you no longer had access to the source code (we're in Pretend-Land now). Looking at the catch clauses of your two functions above, if an exception occurred, how would you know where it happened?

using the general Exception as opposed to named exceptions (e.g. SqlException)
I believe it's generally advocated that you do:

try
{
    conn.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        ddlCourseName.DataSource = rdr;
        ddlCourseName.DataBind();
    }
}
catch (SqlException ex)      // <<====  Here
{
    //ex.Message.ToString(); ???
}

Open in new window


instead of:

try
{
    conn.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        ddlCourseName.DataSource = rdr;
        ddlCourseName.DataBind();
    }
}
catch (Exception ex)      // <<====  Here
{
    //ex.Message.ToString(); ???
}

Open in new window

0
 
asp_net2Author Commented:
@yv989c,

I like your style better than mine, shorter = cleaner code :). I have a couple questions with your method.

Why did you use the using statement for SqlDataReader below?

using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                ddlCourseName.DataSource = rdr;
                ddlCourseName.DataBind();
            }

Also, why is it important to use dbo. for Stored Procedures like you did below? And when I create my Stored Procedures should I be using dbo. to reference the names of the Tables? If so, why?

SqlCommand cmd = new SqlCommand("dbo.HealthCourses_InsertCourseProgram", conn);
0
 
asp_net2Author Commented:
@kaufmed,

I guess I better also study Exceptions to :) So how does one know which Exception to use and why to use it?
0
 
käµfm³d 👽Commented:
Hate to say it, but it's in the documentation. There's no real way to know what exceptions are thrown for a class you didn't write unless you consult the documentation. You other alternative would be to decompile the class using something like .NET Reflector (non-free) or dotPeek (free, for now).

It would probably be much simpler to consult the documentation  ; )
0
 
käµfm³d 👽Commented:
Actually, you have one more method:  run your code without any try/catch and wait for it to blow up. You'll see the specific exception in the pop-up in VS. This isn't exactly professional or desired, though  = )
0
 
asp_net2Author Commented:
@kaufmed,

What form of documentation for learning the .NET Framework is helpful to learn from?

.NET Framework Class Library
.NET Reflector
dotPeek
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
@asp_net2:

1. Because SqlDataReader implement the IDisposable interface (from DbDataReader), and has a Close method that will be called automatically when your code exit the using scope.

2. Is not important to use dbo (it can be any schema name), is important for Sql Server to know the explicitly name of your object (like dbo.HealthCourses_InsertCourseProgram assuming that your SP resides in that schema), otherwise it will need to work a little more to find the correct object, that is something more complex to discuss in this question I think, because it involve concepts like db schema, etc... that are out of topic, I hope you understand.
0
 
asp_net2Author Commented:
Yes, I'm still having trouble understanding this whole IDisposable Interface stuff. I want to make sure I write clean, efficient and fast code without making mistakes even though I understand that is how you learn.I would have made a mistake by not using the using statement for DataReader :( Can you explain to me how I can find this and why as if you where takling to a 5th grader?
0
 
käµfm³d 👽Commented:
What form of documentation for learning the .NET Framework is helpful to learn from?

.NET Framework Class Library  -- Definitely
.NET Reflector                           -- No
dotPeek                                    -- No

The reason I say "no" to the last two is that you are looking at the reverse engineered versions of the compiled DLLs that MS (or others) have written. Not that you can't look at them if you like, but you really should only ever need to look at such code out of curiosity or because of some extraordinarily weird quirk in the Framework that causes your code to malfunction in a way that seems illogical. The latter is extremely rare.

Outside of the documentation, the best way to learn the Framework is PRACTICE. Even trivial projects will give you insight into what you can and can't do with the framework. Hell, a lot of my practice comes right from this site and answering questions for others. Often times I will see a question that I don't know the answer to, but it sounds intriguing, so I will research how to do it and work the code out. As you work on more and more projects, you will slowly assimilate concepts for later use. And there's always us (EE) to fall back on  ; )
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello asp_net2:
About the DataReader that was not a big mistake, I'm sure that when the SqlConnection is closed the reader resources will be released too, but is a good practice to Dispose / Close the objects that allow this if you are done with these.

In general I got the feeling that you are a good apprentice because you take care of what you are doing :)
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
and sorry for my several grammatical errors :p
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Very wise words @kaufmed

Outside of the documentation, the best way to learn the Framework is PRACTICE. Even trivial projects will give you insight into what you can and can't do with the framework. Hell, a lot of my practice comes right from this site and answering questions for others. Often times I will see a question that I don't know the answer to, but it sounds intriguing, so I will research how to do it and work the code out. As you work on more and more projects, you will slowly assimilate concepts for later use. And there's always us (EE) to fall back on  ; )
0
 
käµfm³d 👽Commented:
I want to make sure I write clean, efficient and fast code
The C/C++ guys will crucify me for this, but worry about clean code first, not optimized code. The reason is at some point you'll have to go back and perform some kind of maintenance and it's a great time saver if you don't have to spend hours, if not days trying to understand what you were thinking six months ago.

Hardware has changed immensely since the old days, so memory concerns are not as critical as they used to be. I'm not advocating you disregard the cleanup your objects, but if you spend all your time worrying about how to optimize your managed code, then you'll probably never get any projects completed. Optimizations will become automatic to you as you write more and more code. If you focus on working code first, you can go back later and perform optimizations when you encounter bottlenecks. If you over-optimize in the beginning, you'll probably end up spending more time un-optimizing your code at some later date. Older compilers couldn't handle long variable names; today, this is of little consequence. It's much easier to give your variables a verbose name that indicate what/how they are used than it is to try and figure out what your abbreviation is supposed to mean. The more your code self-documents itself, the less commenting you have to do  = )
0
 
käµfm³d 👽Commented:
@yv989c

= D
0
 
asp_net2Author Commented:
Thank you both. You both are true .NET Jedi Knights :) I only hope that one day I can become a .NET Jedi Knight :)

Thanks again for all the explainations!!!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 13
  • 12
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now