OleDbCommand: to Dispose() or not to Dispose()? 1

[goaway1234]

A quick question here:

I am writing an app where I keep an OleDbConnection open for an extended period of time and use it to occasionally execute non-query sql statements. My question is: Should the OleDbCommand's that I create each time I execute some sql be disposed immediately? Is it necessary? Would it close the connection?

thanks,
Nick

 

[obislavu]

I suggest to Close() the Connection object if it is used occasionally.
Calling Dispose() on the OleDbCommand object doesn't close the Connection object. Here are two examples:

// Assume the Connection object is created somewhere 

SqlDataAdapter vSqlDataAdapter=new SqlDataAdapter();
if (m_SQLConnection.State==System.Data.ConnectionState.Closed) 
{
	m_SQLConnection.Open();
}
vSqlDataAdapter.SelectCommand = new SqlCommand("dbo.sp_Import",m_SQLConnection);
vSqlDataAdapter.SelectCommand.CommandType =CommandType.StoredProcedure;
SqlParameter vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
vSqlParameter.Direction = ParameterDirection.ReturnValue;
vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@Username", Environment.UserName);
//...

When you want to close the Connection object, call only Close().
When you need to activate it check the status and call Open() if it is not open.
When there is no need anymore for that object then call Close() and Dispose() like here:
if (m_SQLConnection !=null)
{
   m_SQLConnection.Close();
   m_SQLConnection.Dispose();
   m_SQLConnection = null;
}

Another way to accomplish that is to use using like here:

using (OleDbConnection OleDbConn = new OleDbConnection(""), OleDbCommand OleDbCmd = new OleDbCommand("sp_Import",OleDbConn))
{
    // use OleDbConn and OleDbCmd
}   // here the compiler will call Dispose() on OleDbConn and OleDbCmd

-obislavu-

 

[goaway1234]

this is what I was doing for the most part. I guess I was just confused as to the purpose of dispose, and why its task can't be done by calling finalize during GC. Any way, one star for knowing that Disposing a command doesn't invalidate the connection and for introducing me to the using statement.