Clicky

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"; }                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 

Select allOpen in new window

asked 12/06/2011 05:03

RobRud's gravatar image

RobRud ♦♦


9 Answers:
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
link

answered

anarki_jimbel's gravatar image

anarki_jimbel

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 ...
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
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";
        }
link

answered 2011-12-06 at 13:17:29

anarki_jimbel's gravatar image

anarki_jimbel

Sorry - I forgot about try-catch. See below. Again, I'd use boolean type as return type. True - success, false - error.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
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";
            }
        }
link

answered 2011-12-06 at 13:33:00

anarki_jimbel's gravatar image

anarki_jimbel

From the examples, it seems like the .Dispose method is not neccesary, true?  No one seems to use it ...
link

answered 2011-12-06 at 13:36:35

RobRud's gravatar image

RobRud

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
link

answered 2011-12-06 at 14:21:23

Tchuki's gravatar image

Tchuki

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.
link

answered 2011-12-06 at 14:26:18

anarki_jimbel's gravatar image

anarki_jimbel

Thanks for your help.  That's just the help I needed.
link

answered 2011-12-06 at 14:40:06

RobRud's gravatar image

RobRud

Hello,

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

answered 2011-12-06 at 18:24:54

samirbhogayta's gravatar image

samirbhogayta

link

answered 2011-12-06 at 21:34:32

anarki_jimbel's gravatar image

anarki_jimbel

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×59
×156
×2
×1
×1
×1
×1
×1
×37

Asked: 12/06/2011 05:03

Seen: 404 times

Last updated: 12/11/2011 07:48