We help IT Professionals succeed at work.

Prefered methods for "close" and "dispose" when accessing SQL data in C#

Rob Rudloff
Rob Rudloff asked
on
Hi.  
I was given the sample C# code below (I may have changed it a bit) as an example for executing a stored procedure.    I was trying different variants of the code (putting ".close" and ".dispose" everywhere) and then running the Visual Studio "Code Analysis" tool --- I keep getting warnings like "In method 'GETALL(ref DataTable)', call System.IDisposable.Dispose on object 'myCmnd' before all references to it are out of scope"

I get the same warnings for the Connection, the Command, the Reader, and the Adapter.

What would be the prefered method for using "close" and "dispose" in an example like this?
Thanks.
// value of string "conn" set elsewhere ...

public string GETALL(ref DataTable myDataTable)
{
    SqlConnection myConn = new SqlConnection(conn);
    SqlDataAdapter myAdapter = new SqlDataAdapter();
    
    SqlCommand myCmnd = new SqlCommand("GETALL_CUST", myConn);
    myCmnd.CommandType = CommandType.StoredProcedure;

    try
    {
        myConn.Open();
        SqlDataReader myReader = myCmnd.ExecuteReader();
        myDataTable.Load(myReader);
        myReader.Close();
        myReader.Dispose();
    }
    catch (SqlException ex)
    {
        MessageBox.Show("Some bad happened");
        return "Didn't work"
    }

    myCmnd.Dispose();
    myConn.Close();     
    myConnDispose();

    return "it worked";
}

Open in new window

Comment
Watch Question

anarki_jimbelSenior Developer
Commented:
The best way I believe would be u the "using" statement. It takes care of closing and disposing
Just have a look at some articles - it's pretty simple:
http://www.w3enterprises.com/articles/using.aspx
http://www.c-sharpcorner.com/UploadFile/mahesh/UsingKeyword01162007063733AM/UsingKeyword.aspx
anarki_jimbelSenior Developer

Commented:
Implementation for your method is below. I didn't test it, of course :)

About the original method. My feeling is that the method was written by not very literate person.
Why, for example, "(ref DataTable myDataTable)" is used? What the point for "ref" here!?
More - myAdapter variable is never used! This is rubbish, confusing rubbish!

Tell me if I'm wrong ...
public string GETALL(DataTable myDataTable)
        {
            using (SqlConnection myConn = new SqlConnection(conn))
            {
                SqlCommand myCmd = myConn.CreateCommand();

                command.CommandText = "GETALL_CUST";
                command.CommandType = CommandType.StoredProcedure;

                myConn.Open();
                SqlDataReader myReader = myCmnd.ExecuteReader();
                myDataTable.Load(myReader);
                myReader.Close();
            }
            return "it worked";
        }

Open in new window

Senior Developer
Commented:
Sorry - I forgot about try-catch. See below. Again, I'd use boolean type as return type. True - success, false - error.
public string GETALL(DataTable myDataTable)
        {
            try
            {
                using (SqlConnection myConn = new SqlConnection(conn))
                {
                    SqlCommand myCmd = myConn.CreateCommand();

                    command.CommandText = "GETALL_CUST";
                    command.CommandType = CommandType.StoredProcedure;

                    myConn.Open();
                    SqlDataReader myReader = myCmnd.ExecuteReader();
                    myDataTable.Load(myReader);
                    myReader.Close();
                }
                return "it worked";
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Some bad happened");
                return "Didn't work";
            }
        }

Open in new window

Rob RudloffIT Development Specialist

Author

Commented:
From the examples, it seems like the .Dispose method is not neccesary, true?  No one seems to use it ...
Vel EousResearch & Development Manager
Commented:
From the examples, it seems like the .Dispose method is not neccesary, true?  No one seems to use it ...

SqlConnection inherits from DbConnection which inherits from IDisposable.  The using statement automatically calls Object.Dispose on any object that inherits from IDisposable at the end of the using statements lifetime.  So explicitly calling .Dispose is not required and Microsoft actually suggests you use the using statement as best practice for any objects that inherit from IDisposable.

http://msdn.microsoft.com/en-us/library/yh598w02.aspx
anarki_jimbelSenior Developer

Commented:
Exactly - don't use Dispose.

Really, not too often you need to use Dispose manually. Mostly it's done by garbage collector, e.g.
Again, I have no idea why the person who wrote the code used "Dispose. But I already told - there are very bad mistakes in the sample.

By the way, I have some wrong naming in my code, like "command" instead of "myCmd". Hope you can identify this.
Rob RudloffIT Development Specialist

Author

Commented:
Thanks for your help.  That's just the help I needed.
Hello,

You don't need to dispose the connection, it is require only with sqlcommand.