cjrancur

Using PIOLEDB in ACE

Discussion created by cjrancur on Jul 23, 2010
Latest reply on Jul 27, 2010 by andreas

I have questions about using PIOLEDB within ACE.  Particularly, I'd like to know suggestions for dealing with memory issues associated with the unmanaged code behind OLEDB in vb.net.  With unmanaged code outside of ACE, I've learned to use the dispose method on the connection, command, dataadapter, and dataset objects when I'm done with them, to prevent memory leaks in the native part of the OLEdB calls. 

 

Here's what puzzles me.  For PI SDK connections, I have heard recommendations that the connection should be made once, and only revived if it is lost for some reason.  However, PIOLEDB uses the PI SDK underneath PIOLEDB, and most ODBC/OLEDB recommendations are to connect and close rapidly, then dispose of objects no longer being used.  So how should connections to PIOLEDB be handled?

 

Now add the ACE layer into the picture.  If PIOLEDB calls are made at each execution of an ACE module, won't there be a gradual memory leak if the objects are not disposed each time?  But if PIOLEDB relies on an SDK connection underneath, and an SDK connection should be left open, how should these apparently conflicting rules of thumb be reconciled in this case?

 

Is it worth the trouble to rewrite an ACE exe that uses PIOLEDB, in order to perform similar operations with loops and with the SDK instead? 

 

I have attached a code snippet from within an existing ACE exe I use.  Yesterday, I considered adding the cnn.close statement, shown in bold and dated 7/22/2010, in the Finally segment of the try/catch block within a using statement for the OLEDB connection.  Do you think addition of that statement is a good thing, a bad thing, or maybe it's irrelevant and unimportant?  Would you recommend a different structure for using OLEDB within ACE?  Should PIOLEDB be avoided within ACE?

 

 

 


Using cnn As New OleDb.OleDbConnection()
   Dim cmd As OleDb.OleDbCommand = cnn.CreateCommand 

   Try
      cnn.ConnectionString = My.Settings.PIOLEDBConnectionString
      cnn.Open()
      cmd.CommandType = CommandType.Text
      cmd.CommandText = strUpdateAttributes 

      '"UPDATE pipoint..vfclass " & "SET " & strSettings1 & strSettings2 & strSettings3 & strSettings4 & " " & "WHERE tag='" & TestResultsRow.Item "TagName").ToString & "' "
     
'CJR 1/13/2010, Execute the OleDB UPDATE to PI values if the connection to the PI Historian is good, else try
     
'to reconnect and send message to PI control server if PI Historian server is inaccessible.
     
If cnn.State = ConnectionState.Open Then
        
cmd.ExecuteNonQuery()
     
Else
        
cnn.Open() 
        
'try once more to open connection to historian PI server

        
If cnn.State = ConnectionState.Open Then
           
cmd.ExecuteNonQuery()
        
Else
           
SendMessageToPI("PI Server "
                            &
My.Settings.PIHistorianServerName
                            &
" cannot be reached via PIOleDB. Tagname "
                            & TestResultsRow.Item(
"TagName").ToString
                            &
" has not yet been sent to PI.",
                            
"Update_PI_Attributes")
        
End If
     
End If

   Catch
ex As Exception
     
SendMessageToPI(ex.Message, "Update_PI_Attributes ex 1")
   Finally
     
cnn.Close()
     
'Added 7/22/2010
     
cmd.Dispose()
   End Try
End Using

 

Outcomes