C # IDisposable Usage: Best Practice

Question

I wrapped OracleConnection and OracleCommand objects in USING statements for a while, however, after starting the code analyzer, I found that OracleParameter also implements IDisposable. Is the following code correct? Is there a better technique for readability or structure? At first glance, this is just clogged with USING statements:

using (OracleConnection conn = new OracleConnection(connectionstring)) { conn.Open(); using (OracleCommand cmd = new OracleCommand(sql, conn)) { cmd.BindByName = true; using (OracleParameter param1 = new OracleParameter("p1", OracleDbType.Int32, System.Data.ParameterDirection.Input)) { param1.Value = int.Parse(value1); cmd.Parameters.Add(param1); } using (OracleParameter param2 = new OracleParameter("p2", OracleDbType.Varchar2, System.Data.ParameterDirection.Input)) { param2.Value = value2; cmd.Parameters.Add(param2); } using (OracleDataReader dr = cmd.ExecuteReader()) { // loop data here... } } } 

Answer 1

You want to get rid of the parameters only at the very end of their use, including during the request (and, possibly, when reading the results):

 using (OracleConnection conn = new OracleConnection(connectionstring)) { conn.Open(); using (OracleCommand cmd = new OracleCommand(sql, conn)) { cmd.BindByName = true; using (OracleParameter param1 = new OracleParameter("p1", OracleDbType.Int32, System.Data.ParameterDirection.Input)) using (OracleParameter param2 = new OracleParameter("p2", OracleDbType.Varchar2, System.Data.ParameterDirection.Input)) { param1.Value = int.Parse(value1); cmd.Parameters.Add(param1); param2.Value = value2; cmd.Parameters.Add(param2); using (OracleDataReader dr = cmd.ExecuteReader()) { // loop data here... } } } } 

Note that you can put multiple using statements in a string. This is because, like the if ,

• The A using statement is considered a simple statement (even with a block); and
• The using statement can accept either a block or the statement below it.

Answer 2

I'm not sure if this will work. Note that at the end of use, both parameters should have been removed. The fact that your cmd.Parameters object still holds a reference to them does not exclude what might happen in the OracleParameter Dispose method. For all intensive purposes, the developer of this particular object can clear the fields that your OracleCommand .

There is some danger. If you are absolutely sure that you want to properly manage your OracleParameters , I suggest you get rid of them after using OracleDataReader .

Remember that you usually call Dispose when you finish using this object. You say that it frees up all the resources that it holds in the pool. If you have not finished using the object, do not delete it prematurely.

Answer 3

 using (OracleConnection conn = new OracleConnection(connectionstring)) using (OracleCommand cmd = new OracleCommand(sql, conn)) using (OracleParameter param1 = new OracleParameter("p1", OracleDbType.Int32, System.Data.ParameterDirection.Input)) using (OracleParameter param2 = new OracleParameter("p2", OracleDbType.Varchar2, System.Data.ParameterDirection.Input)) } conn.Open(); cmd.BindByName = true; param1.Value = int.Parse(value1); cmd.Parameters.Add(param1); param2.Value = value2; cmd.Parameters.Add(param2); using (OracleDataReader dr = cmd.ExecuteReader()) { // loop data here... } } 

Answer 4

No, this is not true because you delete the parameters before you even use them.

Instead you should like it

 OracleParameter param1 = new OracleParameter("p1", OracleDbType.Int32, System.Data.ParameterDirection.Input); param1.Value = int.Parse(value1); cmd.Parameters.Add(param1); OracleParameter param2 = new OracleParameter("p2", OracleDbType.Varchar2, System.Data.ParameterDirection.Input); param2.Value = value2; cmd.Parameters.Add(param2); using (OracleDataReader dr = cmd.ExecuteReader()) { // loop data here... } param1.dispose(); param2.dispose(); 

Answer 5

You can see the source code of the connection and the command, can you discard the parameters? if connection objects or commands delete the template, wrap the parameters and delete them when they are deleted. you have to worry about it. which, I think, will / should.

Answer 6

This code is invalid. the parameters you create are still used outside the scope of using , because you add them to the collection of parameters, but the using statement invokes Dispose for the parameters in control leaving the scope. This means that when the time comes to use the parameters inside the command, they will be lost from already

Answer 7

According to MSDN, you need to use using objects for Connection and DataReader . I have never seen using (or .Dispose() ) used with ADO.NET parameter objects. If it were necessary or even desirable, I think that it would have worked for some time over the past 10 years.